Charles Guebels

Different ways to be authenticated in an RDS database

In this article, we will see four main ways to connect an RDS database instance:

• With username/passwords
• With username/passwords using AWS Secrets Manager
• Through AWS IAM authentication
• Through Keberos authentication

For each method we will explain the setup phase, the connection and the credentials rotation procedures. The main pros and cons will be also listed.

The method to use is mainly related to the required security level and the control we have on the application code. Indeed, for the most secure connection option, the application code must be adapted to handle the credentials rotation. Therefore, you have to interact with the application development team to make it possible.

This article focuses on the AWS configurations required to implement the different authentication methods. Since the code implementation varies following application languages and tehcnologies used, we do not provide code examples. However, the main concepts and documentation references we will be provided, when possible, to help the reader to implement the appropriate authentication method for his application.

Table of Contents

1. Username / password
2. Username / password with AWS Secrets Manager
3. IAM authentication
4. Kerberos
5. Conclusion

Username / password

It is the simplest approach to be authenticated by a database instance. A new user is created in the database and a password is associated to this user. The credentials are afterwards communicated to the teams.

Setup

The user creation is simple:

CREATE USER <USER_NAME> WITH PASSWORD '<PASSWORD>';
GRANT CONNECT ON DATABASE <DATABASE_NAME> TO <USER_NAME>;

CLI connection

In a terminal, the command below can be used to open a connection with username/password for a PostgreSQL database:

1
PGPASSWORD=<PASSWORD> psql -U <USERNAME> <DATABASE_NAME> --host=<HOSTNAME>

Application connection and rotation strategy

No matter about the application language or technology used, with this method we don’t have other choice than hardcode the database credentials directly in the code or better in environment variables loaded by the application. For the rest of this section, we will assume that the credentials are stored in environment variables.

To rotate the database credentials with this authentication method and without service interruption we have to follow the steps below:

1. Create another database user with different credentials but with exact same permissions as the user with the credentials we want to rotate.
2. Update environment variables where the credentials are stored.
3. Restart the application instances to load the updated the environment variables. The restart must happen with a deployment strategy which guarantee the availability of the service (not the all at once strategy for example). This step implies that at least two instances of the application are running (with a load balancer in front for example) before restarting the application.
4. Once all the application instances have been restarted, we can remove the old database user.

Pros and cons

• (+) Easy to understand.
• (+) Compatible with all RDS database types.
• (+) No additional costs.
• (+) The application doesn’t need to be modified to handle the rotation.
• (-) The credentials are visible in environment variables configuration. For example in the Infrastructure As Code templates responsible for configuring an EC2 with the correct environment variables.
• (-) Difficult to automate so we are not encouraged to rotate credentials often.
• (-) Need to redeploy the application.
• (-) Need to have several application instances running during the rotation to assure the high availability.

Username / password with AWS Secrets Manager

This method is similar to the previous one except that this one avoids to store the database credentials directly in the Infrastructure as Code template. Indeed, the AWS Secret Manager service is used to store the credentials, and the API / SDK is used to retrieve the values during the application runtime.

The big advantage with AWS Secret Manager is that this service is able to automatically rotate the password based on a lambda function at a given frequency.

Setup

First of all, a user must be created in the database instance (see above for the command example).

After, a secret in AWS Secrets Manager must be created. In in the AWS web console the steps below must be followed:

1. Open AWS Secrets Manager and click on Store a new secret.
2. Select Credentials for Amazon RDS database as secret type.
3. Enter the database user username and password.
4. Choose a KMS encryption key.
5. In the Database section, the targeted RDS instance must be selected.
6. Enter a secret name, a description and the optional other information as permissions and replication.
7. Do not configure the rotation.
8. Review and finalize the secret creation.

If we check the content of the newly created secret, we can see that AWS automatically creates a JSON string containing:

• the database user username.
• the database user password.
• the database host.
• the database port.
• the database engine.
• the cluster identifier.

If in your infrastructure a script is used to create the database user, this secret can be used by the script to get the credentials and the database information (see next section for CLI command to retrieve secret values).

CLI connection

A database CLI connection using this method is similar to the first method but it is now needed to call the AWS Secret Manager service before to get the credentials.

1
2
3
4
5
6
7
8
9
10
11
#Retrieve the current secret value
SECRET_VALUE="$(aws secretsmanager get-secret-value --secret-id <SECRET_NAME> | jq --raw-output '.SecretString')"

#Retrieve the different information in the secret
DB_USERNAME = "$(echo $SECRET_VALUE | jq -r .username)"
DB_PASSWORD = "$(echo $SECRET_VALUE | jq -r .password)"
DB_HOST = "$(echo $SECRET_VALUE | jq -r .host)"
DB_NAME = "$(echo $SECRET_VALUE | jq -r .dbClusterIdentifier)"

#Open the database connection
PGPASSWORD=$DB_PASSWORD psql -U $DB_USERNAME postgres --host=$DB_HOST

Application connection and rotation strategy

Now that the secret has been created, we can use it in the application to establish a connection to the database, or we can continue the configuration and enable the secret rotation feature to improve the security. These two cases are explained in the sections below.

Do not enable rotation

In this case, no additional secret configuration is needed.

To retrieve the secret in a JAVA application we can implement a custom script using the AWS Secrets Manager SDK to retrieve database credentials before establishing the connection. The second option is to use a dedicated AWS library named aws-secretsmanager-jdbc to automatically retrieve the secret before establishing the database connection.

Enable rotation

In this case, an additional secret configuration is needed. There are two possible rotation strategies

• Single user rotation strategy
• Alternating users rotation strategy

The configuration and the use of these two rotation strategies are described below.

Enabling the password rotation does not change the way to retrieve secrets in the application, so the methods described in the previous section are still valid in the two sections below.

Single user rotation strategy

This rotation strategy simply updates the password of the database user. Follow the instructions below to enable this rotation strategy:

1. Open the secret in AWS Secrets Manager.
2. On the “Rotation configuration” section, click on “Edit rotation”.
3. On the new displayed screen:
   • Enable “Automatic rotation”.
   • Configure the rotation schedule.
   • Choose “Create a rotation function” to create a lambda responsible to the password update.
   • Specify the lambda name suffix.
   • Select “no” for the “Use separate credentials to rotate this secret” field.
   • Save the configuration
4. A CloudFormation stack is created and ran to create the different resources to rotate the password.

Please note that this method will create a lambda for us but we can also choose to create our own password rotation lambda. The generated lambda is already configured to have the correct permissions, the correct security group, VPC and subnet.

It is also important to note that, with this strategy, during the password rotation, there is a short moment where the database user password and the AWS secret are not aligned. Unfortunately, if we try to use the secret to retrieve the password and initiate a database connection, during this unsynchronized time frame, the connection will fail due to invalid credentials. This is why it is important to implement a retry mechanism.

CAUTION 1: By default the field named dbname is not included in the secret so the lambda in charge of the rotation will use a default database name. For example if it is a PostgreSQL database, the lambda will use the “postgres” as default database name. You can consult the default values according to the database engines in this page. If the database has a specific name we have to manually add the field dbname to the secret.

CAUTION 2: To access the database, the automatically generated rotation lambda is configured to run in the same VPC and subnet than the database. As it is a good practice to isolate a database in a private subnet, the lambda will run in this private subnet as well. Please note that it is very important that this subnet can access to internet (through a NAT Gateway for example) otherwise the lambda will not be able to access the Secret Manager Service endpoint to change the password. We can also edit the lambda to implement several other network configurations making this lambda able to access the database and the Secret Manager Service. The use of a VPC endpoint targeting Secret Manager is also possible if access to Internet is not desired. More information about this subject can be found here.

Pros/cons of this rotation strategy:

• (+) No need of superuser credentials.
• (-) High availability is not guarantee without retry mechanism.

More information about this rotation strategy can be found in this page.

Alternating users rotation strategy

This rotation strategy focuses on the high availability during the password rotation, in contrary to the Single user rotation strategy described above.

This strategy is based on database user clone. Indeed, during the first password rotation the concerned database user is cloned and the password of this clone is updated. Once the clone is done, the secret in Secrets Manager is updated. For the next rotation it is the password of the initial user which is updated. The advantage with this process is that there is no moment, during the rotation, where the password in Secrets Manager does not correspond to a valid database user. As this mechanism is not trivial, please find below an example of the four consecutive rotations:

Contrary to the first rotation strategy above (single user rotation strategy), another database user is created by the lambda in charge of the rotation. It implies that this lambda needs to use an additional database user with permissions to clone the user. It is why, during the rotation configuration, we have to specify another AWS secret containing the credentials of a database user with admin permissions.

Follow the instructions below to enable this rotation strategy. Due to the permission constraint described just above, the procedure is split into two big steps: the creation of the admin secret and rotation configuration of a specific secret/database user.

Create a secret with admin permissions:

1. In AWS Secrets Manager, click on Store a new secret.
2. Select Credentials for Amazon RDS database as secret type.
3. Enter the username and password of a database user with admin permissions.
4. Choose a KMS encryption key.
5. In the Database section, the targeted RDS instance must be selected.
6. Enter a secret name, a description and the optional other information as permissions and replication.
7. Review and finalize the secret creation.

Of course the rotation of this secret/database user can also be configured.

Enable the rotation of an existing database user

1. Open the secret in AWS Secrets Manager.
2. On the “Rotation configuration” section, click on “Edit rotation”.
3. On the new displayed screen:
   • Enable “Automatic rotation”.
   • Configure the rotation schedule.
   • Choose “Create a rotation function” to generate a lambda responsible to the password update.
   • Specify the lambda name suffix.
   • Select “yes” for the “Use separate credentials to rotate this secret” field and select the secret (created just above) containing the admin database credentials.
   • Save the configuration
4. A Cloud Formation stack is created and ran to create the different resources to rotate the password.

The two “CAUTION” explained in section Single user rotation strategy are also valid here.

Pros/Cons of this rotation strategy:

• (+) High availability.
• (-) Need of superuser credentials.
• (-) After a rotation, the previous user and password are still valid, so in case of a rotation is triggered to counter a password leak, it is needed to rotate two times.

More information about this rotation strategy can be found in this page.

Pros and cons

• (+) The database password is not present in the Infrastructure as Code templates.
• (+) Possibility to configure the password rotation.
• (+) No need of an application restart mechanism, the application continues to run even after a password rotation.
• (+) Compatible with common database engines.
• (-) If the automatic secret rotation is enabled, the application code must be updated to handle this rotation if not foreseen during the first development phases.
• (-) AWS secret cost. Depending on the number of users (and then secrets) and the secret retrieval frequency, it can be more or less “expensive”. Generally 0.4$ per secret per month and 0.05$ per 10 000 retrieval. If the secret is encrypted with a custom key, the KMS service costs must also be considered. For each rotation a lambda run is already billed.

IAM authentication

This connection method uses a very useful feature provided by the integration of the AWS IAM service with the AWS RDS service. This method allows to use an AWS IAM role to establish a connection with an RDS database thanks to a temporary authentication token.

It is a very powerful and secure way to connect a database, however the management of the authentication token in the application code can discourage developers to use it. Below, we will list some ideas to reduce the complexity.

Unfortunately, for the moment this feature is only available for RDS database engine of type MariaDB, MySQL and PostgreSQL.

Setup

Please find below the steps to be able to use IAM authentication method:

1. Enable IAM DB authentication on the database instance: for that it is just needed to edit the configuration of the RDS database instance and to enable the option Password and IAM database authentication in the Database authentication section.

2. Create a user in the database: Indeed, even if the authentication of the database user will be managed by IAM, we still have to create the user in the database instance. To do that we have to connect to the database using a user with the CREATE USER permission and execute the command below.

   For MySQL and MariaDB:

    CREATE USER <USERNAME> IDENTIFIED WITH AWSAuthenticationPlugin as 'RDS';
   

   For PostgreSQL:

    CREATE USER <USERNAME> WITH LOGIN; 
    GRANT rds_iam TO <USERNAME>;
   

   Please note that all the database user permissions must be granted, as usual, via SQL commands. The AWS IAM service is only in charge of the authentication, not the authorization.

3. Create an IAM policy allowing the database connection: to do that it is just needed to create a policy allowing the rds-db:connect action:

    {
    "Statement": [
        {
            "Action": [
                "rds-db:connect"
            ],
            "Effect": "Allow",
            "Resource": "<RDS_CLUSTER_ARN>/<DATABASE_USERNAME>"
        }
    ],
    "Version": "2012-10-17"
    }
   

4. Assign the IAM policy to an IAM role

CLI connection

Once we assume the role created at the step 4 above we can execute the commands below to get a temporary authentication token and connect to the database:

1
2
3
4
5
6
7
8
9
#Generate and store the temporary authentication token
TOKEN="$(aws rds generate-db-auth-token --hostname <RDS_HOSTNAME> --port <RDS_PORT> --region <REGION> --username <DATABASE_USERNAME>)"

#Download certificate
wget https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

#Open the database connection
psql "host=<RDS_HOSTNAME> port=<RDS_PORT> sslmode=verify-full sslrootcert=rds-combined-ca-bundle.pem dbname=<DATABASE_NAME> user=<DATABASE_USERNAME> password=$token"

When we get an authentication token, we have 15 minutes to use it to establish the database connection, but once the connection is done, it remains connected even after 15 minutes. In fact the connection stays open until the application or the database engine close it (max connection time, idle timeout, etc).

Application connection and rotation strategy

Since this connection method only uses temporary authentication tokens, using it, means that we have to inevitably manage the token rotation in live without service interruption. To do that we have at least the two solutions below, for JAVA applications.

AWS JDBC Drivers

AWS maintains two JDBC drivers for JAVA applications: AWS JDBC Driver for MySQL and AWS JDBC Driver for PostgreSQL (currently in Public Preview version !).

These two drivers can be added to a JAVA application code thanks to the Maven dependencies. These libraries provide several features documented here (MySQL driver) and here (PostgreSQL driver), but the one which interests us is the support of the AWS IAM Database Authentication.

Custom code

It is also possible to develop our own application code to generate a new connection token for our application at regular intervals.

To develop it, we can use the AWS SDK to generate the authentication token and pass this token to the library responsible to manage the data-source in the application.

If a connection pool is configured in the application, there is no problem about the expiry of these connections. Indeed, as said above, once opened, the connection is valid even after the authentication token validity (15 minutes).

Pros and cons

• (+) Secret rotation by design.
• (+) Most secure connection method.
• (+) No need of an application restart mechanism, the application continues to run even after a password rotation.
• (+) No additional costs.
• (-) Only possible for MariaDB, MySQL and PostgreSQL RDS instance type.
• (-) Application code must support the token rotation.

Kerberos

The last database authentication method we will see is probably not the most popular one neither the most cloud oriented one.

Kerberos is an authentication protocol able to secure the password exchange phase and to deliver a ticket to the authenticated client. With this ticket, the authenticated client can access applications compatible with Kerberos and connected to the same user directory. The Kerberos ticket is valid during a certain time (generally 9 hours), avoiding the clients to do the authentication process each time they want to use an application.

The use of this authentication protocol is possible with the AWS RDS databases using MySQL and PostgreSQL engines, under the condition that the RDS database is linked to a directory in the AWS Managed Microsoft AD service. This service hosts a Microsoft Active Directory (AD). What we just have to know about an AD, in the scope of this article, is that it is (among others) a user repository where we can manage their credentials, their groups etc.

Setup

Assuming that an AWS Managed Microsoft AD is already created in the same account, region and VPC; the steps below must be followed to setup this kind of authentication for an RDS database:

1. Creation of an IAM role on which the AWS managed policy named AmazonRDSDirectoryServiceAccess, allowing the RDS database to access the AD, is attached.
2. On the RDS database creation / edit page, choose Kerberos authentication in the Database authentication section and specify the directory ID (starting by “d-“) of the AD in the Directory field.
3. Still on the RDS creation / edit page, in the field name IAM role in section Additional configuration, choose the IAM role created at step 1.

4. As for the IAM authentication method, it is needed to create the users accounts in the database using the master account.

   For MySQL

    CREATE USER '<USERNAME>'@'%' IDENTIFIED WITH 'auth_pam';
   

   For PostgreSQL:

    CREATE USER "<USERNAME>" WITH LOGIN; 
    GRANT rds_ad TO "<USERNAME>";    
   

5. For the MySQL databases, enable SSL/TLS connection to prevent the passwords from being sent in plain text.

    UPDATE mysql.user SET ssl_type = 'any' WHERE ssl_type = '' AND PLUGIN = 'auth_pam' and USER = '<USERNAME>';
    FLUSH PRIVILEGES;
   

   It implies that the database clients must use SSL/TLS to establish the connection.

Application connection and rotation strategy

The use of Kerberos in an application is not in the scope of this article but at the end it doesn’t differ at lot compared to the user/password authentication method with the use of AWS Secrets Manager to make the credentials accessible in a secure way. For the rotation strategy as well, the Kerberos strategy is similar to the username/password strategy. The only difference is that there is no need to duplicate the user after a password update. Indeed as we saw above, once the application is authenticated, it receives a Kerberos ticket with a certain validity duration. Even if the account password has been updated, the ticket is still valid. To handle the password update in a high availability context, we can imagine the steps below:

1. Change the password of the user in the AD.
2. In AWS Secrets Manager, update the secret containing the password of the user. This secret is read by the application each time a new authentication process is needed (a bit before the end of validity of the current Kerberos ticket).

Pros and cons

• (+) It is possible to implement a high availability solution since the AWS Managed Microsoft AD service is highly available by default.
• (-) Only compatible with PostgreSQL and MySQL engines.
• (-) There are AWS costs: The use of a directory in AWS Managed Microsoft AD and a secret in Secrets Manager.
• (-) Difficult to automate so we are not encouraged to rotate credentials often.
• (-) The application code must be updated to handle this rotation if not foreseen during the first development phases.

Conclusion

We saw in this blog post more or less complex methods to connect an application to an RDS database. We can note that the most secure method are more complex to setup and must be directly managed in the application code.