Secret Rotation Lambda Unable to log in after rotating secret

Question

## Issue
We have an Aurora PostgreSQL version 14.5 RDS cluster.  We have a secret in SecretsManager with credentials for a user we want to rotate the password for.  When rotating the secret, the Lambda gets stuck at the `setSecret` step with the error `Unable to log into database with previous, current, or pending secret`.  We have determined that this relates to the `password_encryption` option in the cluster parameter group.  If we set it to `md5` (whereas the default is, I believe, `scram-sha-256`) the rotation will work again _after_ we update it manually.  We can then rotate it as many times as we want.

### Question
How can we get the secret rotation to work while using the default cluster parameter group for an Aurora PostgreSQL cluster?

### To reproduce
1. Have a secret [formatted as expected](https://docs.aws.amazon.com/secretsmanager/latest/userguide/reference_secret_json_structure.html#reference_secret_json_structure_rds-postgres).
2. Have a Lambda running the [python code provided by AWS](https://github.com/aws-samples/aws-secrets-manager-rotation-lambdas/blob/master/SecretsManagerRDSPostgreSQLRotationSingleUser/lambda_function.py).
3. Have a version 14.5 Aurora PostgreSQL cluster using the `default.aurora-postgresql14` cluster parameter group.
4. Click the "Rotate secret immediately" button in the console
5. In Lambda logs, see the error `setSecret: Unable to log into database with previous, current, or pending secret of secret arn arn:aws:secretsmanager:....`

### How to Recover
1. Create a new cluster parameter group that is a copy of `default.aurora-postgresql14`
2. Change the `password_encryption` to be `md5`
3. Apply this new parameter group to the cluster
3. Cancel the secret rotation:  `aws secretsmanager cancel-rotate-secret --secret-id ....`
4. Manually change the password on the user to a new one
5. Update the secret with the new password
6. click the "Rotate secret immediately" button in the console

Accepted Answer

I was able to figure it out!  We're using the [aws python lambda docker image](https://gallery.ecr.aws/lambda/python).  I had to compile the pg tools from source.  The pg tools installable from yum on this container will only install major version 9 of the tools which is not compatible with the scram style of password encryption.
```dockerfile
RUN yum install -y wget gcc tar make libpqxx-devel gzip && \
    yum install -y https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-7-x86_64/postgresql14-libs-14.5-1PGDG.rhel7.x86_64.rpm && \
    wget https://ftp.postgresql.org/pub/source/v14.5/postgresql-14.5.tar.gz && \
	tar -xf  postgresql-14.5.tar.gz && \
	cd postgresql-14.5 && \
	./configure --with-python --without-readline --without-zlib && \
	make && \
	make install && \
	export PATH="/usr/local/pgsql/bin:$PATH" && \
    python3 -m pip install --upgrade pip setuptools wheel boto3 pygresql
```

Answer

Hi,

may not answer directly your question, but have you considered ditching the lambda in favour of the native rds-secret manager integration released a few months ago?

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/rds-secrets-manager.html

Let me know, regards

Secret Rotation Lambda Unable to log in after rotating secret

Issue

Question

To reproduce

How to Recover

Relevant content