Least privilege and DB access audit with Aurora IAM authentication

9 min readJul 6, 2024

Unfortunately, there are many companies that don’t worry about access to their databases; many employees and applications use the same DB user/password, and passwords may not be rotated for months or even years.

AWS Cloud provides many valuable features and shared practices that help us maintain high security levels. In this post, I will demonstrate how to keep the database secure, implement the Least Privilege Principle, and live without long-term passwords.

The principle of least privilege states that identities should only be permitted to perform the smallest set of actions necessary to fulfill a specific task. This balances usability, efficiency, and security. Operating under this principle helps limit unintended access and helps track who has access to what resources.

The use-case is the following:

There are several IAM users (personal user for each employee)
Users should connect to the Aurora database with short-term credentials
Connection to DB should be encrypted
User should have a limited permission (specific database)
Activity should be logged (actions of users in the database)

Aurora MySQL IAM authentication

Let’s create a new Aurora (MySQL Compatible) cluster:

A password of the Master user can be automatically generated and stored in the AWS Secrets Manager:

Enable IAM database authentication:

Enable Log exports (especially Audit logs):

You can use the high-performance Advanced Auditing feature in Amazon Aurora MySQL to audit database activity. To do so, you enable the collection of audit logs by setting several DB cluster parameters. When Advanced Auditing is enabled, you can use it to log any combination of supported events.

We use the server_audit_logging parameter to enable or disable Advanced Auditing.

We use the server_audit_events parameter to specify what events to log.

We use the server_audit_incl_users and server_audit_excl_users parameters to specify who gets audited. By default, all users are audited.

Two IAM users (bob and john) were created for this example:

Every user has the following IAM policy, which allows IAM authentication to the current MySQL cluster and the PostgreSQL cluster for the following example:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "MySQLconnect",
            "Effect": "Allow",
            "Action": [
                "rds-db:connect"
            ],
            "Resource": [
                "arn:aws:rds-db:us-east-1:394106777563:dbuser:cluster-GKAQI2MDB3J7T4O7FWVEFXCRP4/${aws:username}"
            ]
        },
        {
            "Sid": "PostgreSQLconnect",
            "Effect": "Allow",
            "Action": [
                "rds-db:connect"
            ],
            "Resource": [
                "arn:aws:rds-db:us-east-1:394106777563:dbuser:cluster-HFHAWLRZ6LMZWM4FYHBUUAVWVY/${aws:username}"
            ]
        }
    ]
}

Cluster-ID for the policy can be found here:

${aws:username} is a policy variable for defining dynamic values inside policies.

So, the IAM user “bob” will have to use the MySQL user “bob”. The same logic applies to the user “john”.

We follow the Least Privilege Principle from the IAM point of view. Let’s do the same from the MySQL side. I use the initially created “admin” user to complete the configuration:

$ mysql -h demo-instance-1.ckyodhbxdqid.us-east-1.rds.amazonaws.com -u admin -p 
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 6123
Server version: 8.0.32 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>

Creating a user “bob” with the capability to use the IAM authentication plugin. Setting a requirement for user “bob” to use SSL (encrypted connection) and granting him ALL PRIVILEGES only to database “demo”:

CREATE USER bob IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
ALTER USER 'bob'@'%' REQUIRE SSL;


GRANT ALL PRIVILEGES ON `demo`.* TO `bob`@`%` 
FLUSH PRIVILEGES;

MySQL [(none)]> show grants for bob;
+-----------------------------------------------+
| Grants for bob@%                              |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `bob`@`%`               |
| GRANT ALL PRIVILEGES ON `demo`.* TO `bob`@`%` |
+-----------------------------------------------+
2 rows in set (0.001 sec)

Make sure that the IAM Auth plugin is applied to the user:

MySQL [(none)]> select user,plugin,host from mysql.user where user like 'bob';
+------+-------------------------+------+
| user | plugin                  | host |
+------+-------------------------+------+
| bob  | AWSAuthenticationPlugin | %    |
+------+-------------------------+------+
1 row in set (0.001 sec)

You can use Secure Socket Layer (SSL) or Transport Layer Security (TLS) from your application to encrypt a connection to a database running Db2, MariaDB, Microsoft SQL Server, MySQL, Oracle, or PostgreSQL.
SSL/TLS connections provide a layer of security by encrypting data that moves between your client and DB instance or cluster. Optionally, your SSL/TLS connection can perform server identity verification by validating the server certificate installed on your database.

Amazon RDS provides the following CAs to sign the DB server certificate for a database.

The user is ready. He will need to download an SSL bundle according to the AWS document in order to connect to the database using SSL/TLS:

wget https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem

Let’s use environment variables for simplicity:

export RDSHOST="demo.cluster-ckyodhbxdqid.us-east-1.rds.amazonaws.com"
export DBPASS="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 3306 --region us-east-1 --username bob)"

“aws rds generate-db-auth-token” command will provide you with a string like this:

demo.cluster-ckyodhbxdqid.us-east-1.rds.amazonaws.com:3306/?Action=connect&DBUser=bob&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAVXQUYF7NXP2EL5FM%2F20240706%2Fus-east-1%2Frds-db%2Faws4_request&X-Amz-Date=20240706T130402Z&X-Amz-Expires=900&X-Amz-SignedHeaders=host&X-Amz-Signature=db304de6c18e98cd546fbb04dcb331187dd9720cabc2436f71b94d278dc3f765

With IAM database authentication, you use an authentication token when you connect to your DB instance. An authentication token is a string of characters that you use instead of a password. After you generate an authentication token, it’s valid for 15 minutes before it expires. If you try to connect using an expired token, the connection request is denied.

mysql --host=$RDSHOST --port=3306 --ssl-ca=global-bundle.pem --user=bob --password=$DBPASS

The token is only used for authentication and doesn’t affect the session after it is established.

MySQL [(none)]> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| bob@%          |
+----------------+

Let’s make some changes in the database “demo”:

MySQL [demo]> INSERT INTO Persons (PersonID, LastName, FirstName, Address, City) VALUES (1, 'Doe', 'Jane', 'Street434', 'Paris');
Query OK, 1 row affected (0.007 sec)

MySQL [demo]> select * from Persons;
+----------+----------+-----------+-----------+--------+
| PersonID | LastName | FirstName | Address   | City   |
+----------+----------+-----------+-----------+--------+
|        1 | Doe      | John      | Street123 | London |
|        1 | Doe      | Jane      | Street434 | Paris  |
+----------+----------+-----------+-----------+--------+
1 row in set (0.001 sec)

And we can see the following in the CloudWatch Log group:

The same configuration was done for user “john”:

CREATE USER john IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
ALTER USER 'john'@'%' REQUIRE SSL;


GRANT ALL PRIVILEGES ON `demo`.* TO `john`@`%`; 
FLUSH PRIVILEGES;

Generated token for “john” and connected:

export RDSHOST="demo.cluster-ckyodhbxdqid.us-east-1.rds.amazonaws.com"


export DBPASS="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 3306 --region us-east-1 --username john)"

mysql --host=$RDSHOST --port=3306 --ssl-ca=global-bundle.pem --user=john --password=$DBPASS

I deleted a row from the table, and we can see it in the logs. Let’s apply a simple filter in CloudWatch.

%demo-instance-1\,john.*QUERY%

Aurora PostgreSQL IAM authentication

Let’s do the same for PostgreSQL. I have created a cluster and applied a custom parameter group:

By using the PostgreSQL Audit extension (pgAudit) with your Aurora PostgreSQL DB cluster, you can capture the detailed records that are typically needed by auditors or to meet regulatory requirements. For example, you can set up the pgAudit extension to track changes made to specific databases and tables, to record the user who made the change, and many other details.

“pgaudit” library should be added to the “shared_preload_libraries” parameter:

Reboot the writer instance of your Aurora PostgreSQL DB cluster so that your change to the shared_preload_libraries parameter takes effect.

When the instance is available, verify that pgAudit has been initialized. Use psql to connect to the writer instance of your Aurora PostgreSQL DB cluster, and then run the following command:

postgres=> SHOW shared_preload_libraries;
             shared_preload_libraries
--------------------------------------------------
 rdsutils,pg_stat_statements,pgaudit,writeforward
(1 row)

With pgAudit initialized, you can now create the extension. You need to create the extension after initializing the library because the pgaudit extension installs event triggers for auditing data definition language (DDL) statements.

postgres=> CREATE EXTENSION pgaudit;
CREATE EXTENSION

Find the pgaudit.log parameter in the list and set to the appropriate value for your use case. For example, setting the pgaudit.log parameter to write as shown in the following image captures inserts, updates, deletes, and some other types changes to the log.

The object auditing capability is activated when you create the rds_pgaudit role on your system and then add this role to the pgaudit.role parameter in your custom parameter parameter group.

postgres=> CREATE ROLE rds_pgaudit;
CREATE ROLE

Modify the parameter group and reboot the writer instance:

Run the following command to confirm that the pgaudit.role is set to rds_pgaudit.

postgres=> SHOW pgaudit.role;
 pgaudit.role
--------------
 rds_pgaudit
(1 row)

Select the log types to publish to Amazon CloudWatch Logs

To use IAM authentication with PostgreSQL, connect to the DB instance as the master user or a different user who can create users and grant privileges. After connecting, create database users and then grant them the rds_iam role, as shown in the following example.

postgres=> CREATE USER bob;
CREATE ROLE

postgres=> GRANT rds_iam TO bob;
GRANT ROLE

postgres=> \c demo;
psql (15.7, server 15.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "demo" as user "postgres".

demo=> GRANT ALL ON SCHEMA public TO bob;
GRANT
demo=> GRANT ALL PRIVILEGES ON DATABASE "demo" to bob;
GRANT

Connect to the PostgreSQL database using “psql” as the user “bob”:

export RDSHOST="psql.cluster-ckyodhbxdqid.us-east-1.rds.amazonaws.com"
export PGPASSWORD="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region us-east-1 --username bob )"

psql "host=$RDSHOST port=5432 sslmode=verify-full sslrootcert=/home/ec2-user/global-bundle.pem dbname=demo user=bob password=$PGPASSWORD"

psql (15.7, server 15.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

demo=> SELECT current_user;
 current_user
--------------
 bob
(1 row)

Create a test table and insert a raw:

demo=> CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT production UNIQUE(date_prod)
);
CREATE TABLE
demo=> \dt
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | films | table | bob
(1 row)


demo=> INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
INSERT 0 1
demo=> select * from films;
 code  |  title  | did | date_prod  |  kind  |   len
-------+---------+-----+------------+--------+----------
 UA502 | Bananas | 105 | 1971-07-13 | Comedy | 01:22:00
(1 row)

And we can see it in CloudWatch logs:

Conclusion

In this post, I demonstrated how to follow the principle of the least privilege, connect to the Aurora (MySQL and PostgreSQL) database with temporary short-term credentials using IAM Authentication, use an encrypted DB connection, and log all actions in a database for audit purposes using CloudWatch.

Least privilege and DB access audit with Aurora IAM authentication

Aurora MySQL IAM authentication

Aurora PostgreSQL IAM authentication

Conclusion

Written by Oleksii Bebych