CODEWITHSUNDEEP
pythonamazon-web-servicesaws-lambdaamazon-rdsamazon-aurora
Rony Tesler
Rony Tesler

Reputation: 1401

Aurora - password authentication failed for user "postgres" - after password rotation

I'm using python lambda functions. In the beginning of the file I call this function:

def create_db_engines():
    session = boto3.session.Session()
    client = session.client(
        service_name='secretsmanager',
        region_name=os.environ['region']
    )

    try:
        get_secret_value_response = client.get_secret_value(
            SecretId=os.environ['rds_credentials']
        )
    except ClientError as e:
        print('ERROR getting secret ' + str(e))
        raise e
 # Decrypts secret using the associated KMS key.
    secret = json.loads(get_secret_value_response['SecretString'])
    username = secret['username']
    password = secret['password']
    print('password:' + password) # added temporarily for debugging
    engine = create_engine(
        f"postgresql://{username}:{password}@{os.environ['db_endpoint']}/postgres")

    engine_async = create_async_engine(
        f"postgresql+asyncpg://{username}:{password}@{os.environ['db_endpoint']}/postgres")
    return engine, engine_async

I see in the log that the password is correct and I'm able to connect with it in pgadmin. But I still get the error "password authentication failed for user "postgres"" and I don't know why. If I rotate the password in rds it works ok, but it stops working in the next rotation.

Upvotes: 0

Views: 40

Answers (1)

Fedi Bounouh
Fedi Bounouh

Reputation: 1366

If the issue occurs only after you rotate the password directly, and after sometime you can connect without any problem, the issue can be with the connection pool that hold stale connections, which can persist and reuse the old password. to fix it add the pool_pre_ping option when creating the engine. It checks connections before using them.

engine_async = create_async_engine(
    f"postgresql+asyncpg://{username}:{password}@{os.environ['db_endpoint']}/postgres",
    pool_pre_ping=True
)

This ensures that stale connections are discarded and fresh ones are created.

BUT, if when you rotate the password, the new password doesn't work manually also, then you need to check your rotation function, and make sure it is updating RDS credentials correctly.

Upvotes: 0

Related Questions