Using AWS IAM Authentication for PostgreSQL with RDS or Aurora
As of this writing, Amazon Web Services (AWS) allows authentication to PostgreSQL, MySQL, and MariaDB RDS or Aurora databases using IAM (Identity and Access Management) authentication. Instead of using a typical database user id and password, when using IAM authentication, the user would instead use an IAM user id and an authentication token to authenticate. The authentication token expires after roughly 15 minutes. If creating new database connections after that 15 minute interval, a new authentication token would need to be generated.
To enable IAM authentication on the Postgres RDS or Aurora instance, users can use the Amazon RDS console. In the Database Authentication section of RDS database instances, choose the "Pasword and IAM database authentication" option when modifying or creating a new DB instance.
After the IAM authentication is enabled on the database, an IAM user would need to be created via the IAM section of the AWS console. The user needs an IAM policy that allows it to connect to the AWS database. More information on creating an IAM policy and an IAM user can be found in the AWS documentation. Essentially, the IAM policy needs to allow the rds-db:connect option for an RDS resource.
After the IAM user is created and a policy is attached to the user giving the user the ability to connect to the RDS or Aurora database, an identical user name must be created on the RDS / Aurora database. For example, if a user named iam_test_user was created, the same user (iam_test_user) must be created on the RDS/Aurora database. Below are example statements for creating IAM users on Postgres.
PostgreSQL Create IAM User
CREATE USER iam_test_user;
GRANT rds_iam TO iam_test_user;
After creating the IAM user on the PostgreSQL database instance, the user would still need the appropriate grants to be able to perform specific actions such as querying data, inserting data, etc.
A PostgreSQL example that would grant select, insert, update, and delete on all tables in a schema named public would be the following:
PostgreSQL IAM Grant Statement
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO iam_test_user;
To make authentication with the quickly expiring IAM authentication tokens easier, newer versions of RazorSQL support using a password file (a file containing nothing but the database password) or a pgpass formatted file (a file containing the host, port, database name, user, and password) for authentication. A user could then run an IAM authentication token regeneration script on the user's machine that would regenerate the password file in an interval less than every 15 minutes. RazorSQL would re-read the password file or pgpass file before connecting or reconnecting to the database, thus saving the user from having to re-enter the token every 15 minutes.
