We are what our thoughts have made us; so take care of what you think. Words are secondary. Thoughts live, they travel far. Each thought we think is tinged with our own character. - Swami Vivekananda

AWS Aurora PostgreSQL is an engine set up in AWS RDS, making the process of launching new database instances, scaling, configuring, and securing them much easier. This frees you to focus on your business and applications. The use of AWS Aurora has increased in recent years as part of the serverless trends. It comes with different incredible features ready to use, and one of them is credential rotation.

Credential rotation is the process of periodically updating a secret, and it holds key importance in large organizations. Services like Databases are critical due to their essential data content, making them high priorities in the secure asset management process. AWS Aurora PostgreSQL is one of the most popular engines for deploying databases, often used together with AWS Secret Manager. This first article aims to explain the strategies for rotating credentials using these two technologies.

System and People accounts

In the context of data, it is common to divide accounts into people and system accounts. Aurora PostgreSQL can be used for both types, although there are no differences in functionality between accounts of either type (they are created as roles). This article will specifically focus on system accounts, as people accounts are commonly accessed through IAM database authentication, which will be covered in another post.

Strategies

Aurora PostgreSQL doesn't automatically rotate credentials (unless for non-admin accounts). This implies additional work to define the correct strategy and architecture. The strategies can be resumed in two main approaches:

Single user rotation strategy

This strategy is used to support rotation in systems that do not require high availability. This is because the rotation process can result in downtime for new connections, as the changed password becomes immediately unavailable. In PostgreSQL, the process is very, very simple and is just one query (two using the creation account):

  • Create the account 
CREATE USER admin WITH PASSWORD 'passsword';
  • Rotate the credential
ALTER USER admin WITH PASSWORD 'new_password';

What happen next is that the current open connections will be still alive until the session expired, or it will be close, and the new connections will fail. Therefore, the system has to be able to retrieve the new credentials saved in AWS secret manager.

Alternating users rotation strategy

In systems with high availability requirements, downtime is almost intolerable. Typically, these systems are highly transactional, making the implementation of a strategy to retrieve new credentials during peak times challenging. Even though rotations can be scheduled to occur during low-peak times, the process of retrieving new credentials is complex.

The alternating users rotation strategy supports the use of two users simultaneously, using a simple role to maintain ownership of the resources. On the SQL side, this is how it looks:

  • Create the common role
create role admin nologin;
  • Create both users
CREATE USER admin_a WITH PASSWORD 'passsword';
-- by default the second user is not able to login
CREATE USER admin_b WITH PASSWORD 'passsword' nologin;
  • Inherit the role's permissions into the accounts
grant admin to admin_a;
grant admin to admin_b;
  • Change the ownership
alter role admin_a set role admin;
alter role admin_b set role admin;
  • Rotate credentials

To rotate credentials, there is a logic to choose which credentials need to be updated (only one can be rotated). This logic is based on the tag usage in AWS Secret Manager, which will be explained in the architecture section.

ALTER admin_a  WITH PASSWORD 'new_password';

ALTER admin_b WITH PASSWORD 'new_password';

The alternating users rotation strategy explained before simplifies a considerable part of the process. In contrast, other strategies often suggest having two separate users and cloning permissions each time the credentials got to rotate, making the process much more complex.

Architecture

Understanding what these strategies accomplish, we will select the alternating users rotation strategy, assuming that we want to guarantee high availability for our system. The architecture below shows a complete system interactions rotating credential in AWS.

Figure 1: Architecture using AWS Aurora 

AWS Secret Manager allows invoking a Lambda function to rotate credentials, Show Figure 2. There are already some examples on GitHub showing how it works, which will be covered in future articles. Essentially, the main logic is located in the AWS Lambda rotation, supported by tags to 'version' the two credentials, allowing them to exist at the same time

Figure 2: How Secret Manager rotation works 

In AWS Secret Manager, tags are used to manage credentials status, AWSPREVIOUS and AWSCURRENT allow maintaining two credentials alive, the logic is:

  1. Secret Manager trigger the lambda rotation
  2. A new credential is created, and t it is tagged with AWSPENDING
  3. The credential is tested in PostgreSQL, validating that it works
  4. The credential with AWSPENDING tag change to be AWSCURRENT and the credential tagged as AWSCURRENT change to be AWSPREVIOUS
  5. Tags that are no longer needed are deleted

The next time the AWS Fargate Task retrieves the credentials from AWS Secret Manager, it will get the credentials tagged as AWSCURRENT, updated in the last rotation. It was the architecture proposal, in the part 2  we will go deeper into the implementation.