Every Team/Products owns a RDS(Mysql/Postgres) instance. Infra Team will use superuser account to create new application users with permissions to the databases as required. These new user accounts are not bound to any individual, so it is shared within the team and all team members use the same credentials access the DB

There were multiple issues with this approach -

• Since the user account typically has readwrite permissions, anyone can delete tables and rows. With shared credentials, we were unable to identify who executed the queries.
• An alternative to shared credentials would be creating individual user accounts for each user. But with a large team, this becomes difficult to manage.
• Even with the individual user accounts, passwords are set and shared by the admins, so we couldn’t establish that the actions were performed only by that user.
• As Per the enterprise password policy, passwords have to be rotated, which adds more overhead to the operations.

IAM Based Authentication

AWS has introduced IAM authentication for RDS with SQL and PSQL. This method allows you to connect to the DB with an authentication token generated with the help IAM policy attached to a role or user.

Authentication Token

Authentication token is generated using AWS cli or AWS SDK which is valid for 15 minutes and this is generated with the help of the role/user created.

Benefits

• You can manage the access to the resource and log each activity by every user so that you have a good track of what the updates or deletions made by every individual logging.
• Since the token is randomly generated using this instead of a password results in greater security.
• Authentication with IAM requires SSL verification for every connection, thereby providing security to the data in transmit as well.

Limitations

• IAM database authentication as a mechanism for temporary, personal access to databases.
• IAM database authentication only for workloads that can be easily retried.
• Don’t use IAM database authentication if your application requires more than 256 new connections per second.

Download AWS CA Certificate for using the below url

https://s3.amazonaws.com/rds-downloads/rds-ca-2019-root.pem

Reference URL: https://lightsail.aws.amazon.com/ls/docs/en_us/articles/amazon-lightsail-download-ssl-certificate-for-managed-database

Generate Authentication token

aws rds generate-db-auth-token --hostname <rds hostname> --port <portnumber> --region <region> --username <username> --profile <aws sso profile>

RDS hostname : AWS doesn't support password generate using cname,  we need to make sure we are using AWS RDS Endpoint. Endpoint can be taken from AWS RDS  Console.

Port Number :  Mysql - 3306 & Postgres - 5432

Username: iamreadonly

aws sso profile :   AWS profile which has permission to generate token.

Example:

aws rds generate-db-auth-token --hostname rdsmysql-services-us-east-1.rds.amazonaws.com --port 3306 --region us-east-1 --username iamreadonly --profile wego

When connecting with RDS using IAMREADONLY user, SSL must be enabled and configured SSL Mode to use verify-ca

Note: If you get as Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled

https://github.com/TablePlus/TablePlus/issues/384