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

EDIT: Revoking the rds_iam postgres role from the db user solved the problem. This role should be assigned only when using IAM Auth to connect directly to the DB (not the proxy). This is described in the AWS docs here:

In the direct database IAM authentication case, you selectively choose database users and configure them to be identified with a special authentication plugin. You can then connect to those users using IAM authentication.

In the proxy use case, you provide the proxy with Secrets that contain some user's user name and password (native authentication). You then connect to the proxy using IAM authentication. Here, you do this by generating an authentication token with the proxy endpoint, not the database endpoint. You also use a user name that matches one of the user names for the secrets that you provided.

---

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