AWS RDS IAM Login pitfalls and checklist

So, you’re struggling with your IAM role login?

psql: error: FATAL: password authentication failed for user "mydbuser"
psql: error: FATAL: PAM authentication failed for user "mydbuser"

This list is specifically for PostgreSQL, accessed by ECS containers and developers, but most of it would apply to any IAM compatible setup.

1. First, check that you have allowed IAM logins to your database instance. Click “Modify” on your instance, and set it to “Password and IAM database authentication”:
   
2. Make sure you have created a database user to log in as, using CREATE USER in your database. This is not your IAM user, and does not need to have the same name.
3. Make sure the user is assigned the rds_iam role (GRANT rds_iam TO mydbuser). This should be visible when you do \du in psql, as “Member of” rds_iam
   • The user also needs access to the tables/databases you want them to access
   • A database user (again, not your IAM user) with the rds_iam role can not log in using their assigned password. …so if you’re migrating from database passwords to IAM and have applications still running, create a new user for this.
   • If the rds_iam role is not assigned, you will get “password authentication failed” instead of “PAM authentication failed” if you fail at logging in as the user
4. For ECS containers, create a new role for the tasks to run as. This role needs to allow the rds-db:connect action on your database and user, all of which could be specified as “any” (*) if you want.
   The AWS Console will help you with this:
   
   
   • You could testing it out with arn:aws:rds-db:*:*:dbuser:*/* to allow everything, but that’s hardly recommended for production use.
   • The rds-db:connect action is not included in AmazonRDSFullAccess default policy
   • Your role should be added to the ECS task definition as the “Task role”, not the “Task execution role”
     
   • A fully qualified ARN looks like this:

     "arn:aws:rds-db:eu-central-1:827164351102:dbuser:db-6QRASSC7DASSW7P551LXR666CU/mydbuser"

   • A completely open ARN looks like this:

     "arn:aws:rds-db:*:*:dbuser:*/*"

5. For developers, you want the same policy as in the previous point, just applied to your user, or a group you’re in
6. Make sure you are using SSL. For psql you can do this by setting PGSSLMODE=require (or verify-ca if you’ve downloaded the AWS certificate bundle)
7. If your AWS account is part of an AWS Organizations setup, you need to add rds-db:* to the service control policy (SCP) of the organization unit that the account belongs to
8. If you’re a developer using AWS MFA, you need to have an active, functional session to obtain valid database tokens. As of 2021-12-07, the aws rds generate-db-auth-token command is perfectly happy with returning a token anyway, it just won’t work, and you’ll get “PAM authentication failed” once more
9. When creating db-auth-tokens from the CLI, it’s also important to use the correct, and full, database endpoint
   I.e. you can’t use production-db.mydomain.com, 88.51.256.88 (the DB IP address), or any other reference to it, you need the hostname to be what’s listed as the “Endpoint” in the AWS Console, something like production-db.cdracwecaau0.eu-central-1.rds.amazonaws.com
10. Point 8 goes for ECS containers as well. You need to have it generating auth tokens for the “Endpoint” of the database, not anything else, so check your connection string. Also check it for SSL.
11. Access tokens are only valid for 15 minutes by default
12. When your container is correctly set up and running, you can run an interactive bash shell in it and check that a valid AccessKey is created by running
    curl 169.254.170.2$AWS_CONTAINER_CREDENTIALS_RELATIVE_URI
    This is what the AWS libraries will use to obtain the credentials, so it’s good to know they’re working. The result will also return the name of the role you assigned to the container.
13. A one-liner for testing your DB connection, inside a container (you’d have to install the relevant tools) or at a console:
    

    DBENDPOINT="production-db.cdracwecaau0.eu-central-1.rds.amazonaws.com" DBPORT=5432 DBREGION="eu-central-1" DBUSER="mydbuser" DBNAME="business_app"; PGPASSWORD=$(aws rds generate-db-auth-token --region "$DBREGION" --hostname "$DBENDPOINT" --port "$DBPORT" --username "$DBUSER") PGSSLMODE=require psql -h "$DBENDPOINT" -U "$DBUSER" -d "$DBNAME" -p "$DBPORT" <<<"SELECT 'SUCCESS' AS OK"

14. For C#
    • You can generate credentials based on the environment variables provided by AWS by doing Amazon.Runtime.FallbackCredentialsFactory.GetCredentials(false)
    • You can generate a DB AuthToken directly using Amazon.RDS.Util.RDSAuthTokenGenerator.GenerateAuthToken(host, port, username)
    • You use the DB AuthToken as the password in your connection string
    • The System.Data.Common.DbConnectionStringBuilder class is very useful here, allowing you to easily parse a connection string, replace the passwords and generate an updated string without having to care about formatting, escapting and the like
    • Remember to add SSL mode:
      connectionStringBuilder.Add(“SSL Mode”, “Require”);
      connectionStringBuilder.Add(“Trust Server Certificate”, “true”);
    • Also, see this excellent blog post by Steve Gordon (archive) on how the AWS libraries go about obtaining your AccessKey