RDS Proxy successfully connected after that disconnecting by showing "RDS Proxy supports only IAM or MD5 authentication"

Question

I have followed these steps to connect RDS Proxy to connect RDS from lambda

https://aws.amazon.com/blogs/compute/using-amazon-rds-proxy-with-aws-lambda/

Whenever I'm running in lambda, it's connecting but later whenever we execute query it will disconnect by showing this message

FATAL: RDS Proxy supports only IAM or MD5 authentication.

While troubleshooting

1)I have added AmazonRDSDataFullAccess to role.

2)I have added below one's also to policy

        {
            "Effect": "Allow",
            "Action": "kms:Decrypt",
            "Resource": "arn:aws:kms:eu-west-1:[acct-id]:key/*",
            "Condition": {
                "StringEquals": {
                    "kms:ViaService": "secretsmanager.eu-west-1.amazonaws.com"
                }
            }
        }

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "rds-db:connect"
            ],
            "Resource": [
                "arn:aws:rds-db:ue-west-1:[acct-id]:dbuser:prx-ABCDEFGHIJKL01234/*"
            ]
        }
    ]
}

3)Created a new read-write role within my actual RDS instance same as IAM name

4)Only thing is that I couldn't create DefaultEncryptionKey instead I was getting my secret key only to select

export PGPASSWORD="$(aws rds generate-db-auth-token --hostname ${host} --port 5432 --region eu-west-1 --username iamuser)"

psql -h ${host} -p 5432 -d postgres -U iamuser

psql (14.4, server 13.4)
SSL connection (protocol: TLSv1.3, cipher:***, bits: 256, compression: off)
Type "help" for help.

postgres=> select current_user;
FATAL:  RDS Proxy supports only IAM or MD5 authentication
SSL connection has been closed unexpectedly
The connection to the server was lost. Attempting reset: Succeeded.
psql (14.4, server 13.4)
SSL connection (protocol: TLSv1.3, cipher: ***, bits: 256, compression: off)

Answer 1

I have the same issue. Fixed by creating a new user in the PostgreSQL database and using that user for the proxy.

With the default user:

$ export RDSHOSTNAME="mycluster.proxy-xxxxxxx"
$ export RDSREGION="eu-central-1"
$ export PGDATABASE="mydatabase"
$ export PGUSER="mydefaultuser"
$ export PGHOST="${RDSHOSTNAME}.${RDSREGION}.rds.amazonaws.com"
$ export PGSSLROOTCERT="/tmp/rds-ca.pem"
$ export PGSSLMODE="verify-full"
$ export PGPASSWORD="$(aws rds generate-db-auth-token --hostname ${PGHOST} --port 5432 --region ${RDSREGION} --username ${PGUSER})" 
$ psql
psql (13.7 (Debian 13.7-0+deb11u1), server 13.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

mydatabase=> select * from mytable;
FATAL:  RDS Proxy supports only IAM or MD5 authentication.
SSL connection has been closed unexpectedly
The connection to the server was lost. Attempting reset: Succeeded.
psql (13.7 (Debian 13.7-0+deb11u1), server 13.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

Create a new user for the proxy:

CREATE ROLE rdsproxyuser WITH LOGIN PASSWORD '123456';
GRANT ALL PRIVILEGES ON DATABASE mydatabase to rdsproxyuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO rdsproxyuser;

$ export RDSHOSTNAME="mycluster.proxy-xxxxxxx"
$ export RDSREGION="eu-central-1"
$ export PGDATABASE="mydatabase"
$ export PGUSER="rdsproxyuser"
$ export PGHOST="${RDSHOSTNAME}.${RDSREGION}.rds.amazonaws.com"
$ export PGSSLROOTCERT="/tmp/rds-ca.pem"
$ export PGSSLMODE="verify-full"
$ export PGPASSWORD="$(aws rds generate-db-auth-token --hostname ${PGHOST} --port 5432 --region ${RDSREGION} --username ${PGUSER})" 
$ psql
psql (13.7 (Debian 13.7-0+deb11u1), server 13.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

mydatabase=> select * from mytable;
id | column1 | column2 | column3
---+---------+---------+---------
SNIP

My main guess is that AWS RDS doesn’t use MD5 for storing the password of the default account but scram-sha-256 which is not supported by the proxy. https://www.postgresql.org/docs/13/auth-password.html