Simplify database authentication management with the Amazon Aurora PostgreSQL pg_ad_mapping extension

February 15, 2025

Authentication is a critical component of security in enterprise environments, acting as the gatekeeper that protects sensitive data and resources from unauthorized access. It not only defends against both internal and external threats but also fosters accountability and traceability within systems. This article delves into Kerberos authentication for Amazon Aurora PostgreSQL-Compatible Edition, utilizing AWS Directory Service for Microsoft Active Directory. We will particularly focus on the new pg_ad_mapping extension and its role in enhancing access control management.

Aurora PostgreSQL authentication

Before we explore the intricacies of Kerberos authentication, it’s essential to review the authentication mechanisms available in Aurora PostgreSQL. By default, password authentication is enabled for all database clusters. Additionally, Aurora supports AWS Identity and Access Management (IAM) database authentication and Kerberos authentication. Each method functions independently, allowing users to access the database using one method at a time. In PostgreSQL, users are assigned specific roles: rds_iam for IAM database authentication, rds_ad for Kerberos authentication, and no specific roles for password authentication.

Password authentication involves user account administration managed by the database, while IAM database authentication employs authentication tokens that expire 15 minutes after generation, necessitating the creation of a new token for reconnection. In contrast, Kerberos authentication integrates with Microsoft Active Directory (AD), providing centralized authentication and single sign-on (SSO) capabilities, along with short-lived tickets for improved security. With support for one- and two-way forest trust relationships established at the AD level, Aurora PostgreSQL clusters offer robust security and streamlined access management. For further details, refer to Database authentication with Amazon Aurora.

Now that we have outlined the authentication methods available in Aurora, let’s delve into the advantages of Kerberos authentication and its SSO mechanism, particularly focusing on Kerberos AD security groups for access control in Aurora PostgreSQL using the pg_ad_mapping extension.

AD security groups for Aurora PostgreSQL access control

Kerberos authentication provides a robust and streamlined security solution, enhancing user experience, centralizing access management, and integrating seamlessly with existing infrastructure for efficient and secure access control. Prior to versions 14.10 and 15.5, Amazon Aurora PostgreSQL supported only Kerberos-based authentication for individual users, necessitating explicit provisioning of each AD user to the database for access. This process was cumbersome and added operational overhead to access management.

With the introduction of Amazon Aurora PostgreSQL versions 14.10 and 15.5, AWS has enhanced access control by integrating with AD security groups through the pg_ad_mapping extension. This extension simplifies access management by allowing administrators to use AD security groups instead of manually provisioning each user. These groups, which reflect business requirements, are managed by AD administrators and dictate access permissions. Database cluster administrators can create specific DB roles aligned with business needs and establish mappings between AD security groups and these roles at the DB cluster level. Consequently, database users can access database clusters using their AD credentials, with access permissions automatically adjusting based on their AD security group memberships.

When an AD user logs in, the pg_ad_mapping extension checks their group memberships and assigns them the corresponding database roles. If an AD user belongs to multiple groups, the role with the highest weight is prioritized, ensuring that the most appropriate role is applied based on defined business priorities.

To learn how the pg_ad_mapping extension operates, one must first connect to an Aurora PostgreSQL cluster and install the pg_ad_mapping extension, as demonstrated in the following code snippet. Ensure that shared_preload_libraries includes the pg_ad_mapping library:

CREATE EXTENSION IF NOT EXISTS pg_ad_mapping CASCADE;

The extension creates several functions:

pgadmap_set_mapping: This function adds a new mapping entry for an AD security group to a database role. For instance, in PowerShell, you can retrieve the SID value of the AD group and map the AD security group databaseops to the database role databaseops_role using the SID with a weight of 7:

C:> Get-ADGroup -Identity databaseops | select SID 
SID 
--- 
S-1-5-21-2773742795-607290122-2880450345-1609 

psql> select pgadmap_set_mapping('databaseops', 'databaseops_role', 'S-1-5-21-2773742795-607290122-2880450345-1609', 7);

pgadmap_read_mapping: This function lists all defined mappings between AD security groups and DB roles. For example, you can run a query using pgadmap_read_mapping to retrieve all AD security group to DB role mappings:

psql> select * from pgadmap_read_mapping();

pgadmap_reset_mapping: This function deletes a specific AD group to DB role mapping or resets all mappings. If no arguments are provided, all mappings are reset:

psql> select pgadmap_reset_mapping('S-1-5-21-2773742795-607290122-2880450345-1609', 'databaseops_role', 7);

For further information, refer to Using AD security groups for Aurora PostgreSQL access control.

Solution overview

This solution leverages the capabilities of the pg_ad_mapping extension to empower groups of enterprise users from an AWS Managed Microsoft AD server. This is achieved by mapping AD groups to database roles and enabling authentication to Amazon Aurora PostgreSQL through Kerberos authentication.

The following AD users are part of this solution:

AD User	AD Group
maria	app1dev
krishna	app2dev
frank	app2dev, app1dev

The architecture of the solution is illustrated in the accompanying diagram.

Prerequisites

For this walkthrough, you will need an AWS account with the necessary IAM permissions to launch the provided AWS CloudFormation template.

This solution will incur costs for the following AWS services (prices are examples for the us-east-1 region):

Amazon Aurora PostgreSQL (db.r6g.large), Standard, 1GB storage, 100 IOPS

Approx. [cyberseo_openai model=”gpt-4o-mini” prompt=”Rewrite a news story for a technical publication, in a calm style with creativity and flair based on text below, making sure it reads like human-written text in a natural way. The article shall NOT include a title, introduction and conclusion. The article shall NOT start from a title. Response language English. Generate HTML-formatted content using

tag for a sub-heading. You can use only

, and HTML tags if necessary. Text: Authentication serves as the foundational pillar of security in any enterprise environment, playing a pivotal role in safeguarding sensitive data and resources from unauthorized access. It serves as the gatekeeper, ensuring that only legitimate users with proper credentials gain access to the database environment. It not only protects against internal and external threats, but also helps maintain accountability and traceability within the system.
In this post, we look into Kerberos authentication for Amazon Aurora PostgreSQL-Compatible Edition using AWS Directory Service for Microsoft Active Directory, and particularly the new pg_ad_mapping extension and how it can help you manage access control more efficiently.

Aurora PostgreSQL authentication

Before we dive deep, let’s review the authentication mechanisms supported by Aurora PostgreSQL.
By default, password authentication is enabled for all database (DB) clusters. Additionally, Aurora offers AWS Identity and Access Management (IAM) database authentication and Kerberos authentication. Each method operates independently, allowing users to access the database using one method at a time. For PostgreSQL, users are assigned a specific role: rds_iam for IAM database authentication, rds_ad for Kerberos authentication, and no specific roles for password authentication.
Password authentication involves database-controlled user account administration, whereas IAM database authentication uses authentication tokens that expire 15 minutes after generation, requiring the generation of a new token to re-establish the connection. In contrast, Kerberos authentication integrates with Microsoft Active Directory (AD), offering centralized authentication and single sign-on (SSO) benefits, along with the use of short-lived tickets for enhanced security. With support for one- and two-way forest trust relationships set up at the AD level, Aurora PostgreSQL clusters provide robust security and streamlined access management. See Database authentication with Amazon Aurora for additional details. Additionally, you can employ Kerberos authentication on Amazon Relational Database Service (Amazon RDS) and Aurora in conjunction with AWS Managed Microsoft AD, facilitating trust relationships with on-premises AD for unified authentication of enterprise users. For additional details, see Preparing on-premises and AWS environments for external Kerberos authentication for Amazon RDS.
Now that we’ve discussed Aurora database authentication methods, including Kerberos, let’s examine how you can harness the benefits of Kerberos authentication and its SSO mechanism on Aurora. Additionally, we also dive deeper into Kerberos AD security groups for Aurora PostgreSQL access control using the pg_ad_mapping extension.

AD security groups for Aurora PostgreSQL access control

Kerberos authentication offers a strong and streamlined security solution, enhancing the user experience, centralizing access management, and integrating seamlessly with existing infrastructure for efficient and secure access control.
Prior to versions 14.10 and 15.5, Amazon Aurora PostgreSQL supported only Kerberos-based authentication with AD for individual users. This required explicit provisioning of each AD user to the database for access. However, this process proved cumbersome, leading to additional operational overhead in managing access management processes.
From Amazon Aurora PostgreSQL versions 14.10 and 15.5 onwards, in addition to AD user authentication, AWS provides an enhanced access control mechanism by integrating with AD security groups using the pg_ad_mapping extension.
The Aurora PostgreSQL pg_ad_mapping extension streamlines access management and mapping of AD security groups to database roles. Instead of manually provisioning each AD user to the DB cluster, administrators can now use AD security groups. These groups, reflecting business requirements, are managed by AD administrators and determine access permissions. DB cluster administrators create specific DB roles aligned with business needs, and then establish mappings between AD security groups and these roles at the DB cluster level. Consequently, database users can access database clusters using their AD credentials, with access permissions automatically adjusting based on their AD security group memberships.
When an AD user signs in, the pg_ad_mapping extension checks their group memberships and assigns them the corresponding database roles. If an AD user is mapped to more than one group, the role with the highest weight is assigned precedence. This ensures that the most appropriate role is applied based on the defined business priorities.
To learn how the pg_ad_mapping extension works, we first need to connect to an Aurora PostgreSQL cluster and install the pg_ad_mapping extension, as shown in the following code. Make sure that shared_preload_libraries is set to include the pg_ad_mapping library.

CREATE EXTENSION IF NOT EXISTS pg_ad_mapping CASCADE;

The extension creates the following functions:

pgadmap_set_mapping: Use this function to add a new mapping entry for an AD security group to a role in the database. The following example, in PowerShell, gets the SID value of the AD group and adds a mapping of the AD security group databaseops to the database role databaseops_role using the SID with weight 7. The weight helps when an AD user is associated with multiple AD groups, and the role with the highest weight gets precedence in such cases. See the following code:
C:> Get-ADGroup -Identity databaseops | select SID SID --- S-1-5-21-2773742795-607290122-2880450345-1609 psql> select pgadmap_set_mapping('databaseops', 'databaseops_role', 'S-1-5-21-2773742795-607290122-2880450345-1609', 7);

pgadmap_read_mapping: Use this function to list all the already defined mappings between the AD security groups and DB roles. The following example runs a query using the pgadmap_read_mapping function to get all AD security group to DB role mappings:
psql> select * from pgadmap_read_mapping(); ad_sid | pg_role | weight | ad_grp -----------------------------------------------+------------------+--------+------------- S-1-5-21-2773742795-607290122-2880450345-1609 | databaseops_role | 7 | databaseops (1 row)

pgadmap_reset_mapping – Use this function to delete a specific AD group to DB role mapping or to reset all mappings. If no arguments are provided, all AD group to DB role mappings are reset. The following example resets the mapping of the AD security group databaseops to the database role databaseops_role:
psql> select pgadmap_reset_mapping('S-1-5-21-2773742795-607290122-2880450345-1609', 'databaseops_role', 7);

See Using AD security groups for Aurora PostgreSQL access control for additional details.
Now that we have reviewed the pg_ad_mapping extension and its functionality, let’s dive into the solution.

Solution overview

The solution harnesses the capabilities of the pg_ad_mapping extension to empower groups of enterprise users from an AWS Managed Microsoft AD server. This is achieved by mapping AD groups to database roles and enabling authentication to Amazon Aurora PostgreSQL through Kerberos authentication.
We use the following AD users as part of this solution:

AD User AD Group

maria app1dev

krishna app2dev

frank app2dev, app1dev

The following diagram illustrates the solution architecture.

Prerequisites

For this walkthrough, you should have an AWS account with the appropriate IAM permissions to launch the provided AWS CloudFormation template.
This solution will incur costs for the following AWS services (prices shown are examples for us-east-1 region):

Amazon Aurora PostgreSQL (db.r6g.large), Standard, 1GB storage, 100 IOPS Approx. $0.33 (USD) per hour

AWS Managed Microsoft AD (Standard Edition) Approx. $0.14 (USD) per hour

Windows EC2 instance (t2.medium) Approx. $0.07 (USD) per hour

Total Approx. $0.54 (USD) per hour

See AWS Pricing to learn more.

Deploy the solution with AWS CloudFormation

We use a CloudFormation stack to deploy this solution. The stack creates all the necessary resources, including the following:

Networking components such as VPC and subnet resources

An AWS Managed Microsoft AD server to set up users and groups for testing Kerberos authentication using groups on Amazon Aurora PostgreSQL

An Aurora PostgreSQL database cluster

A Windows Amazon Elastic Compute Cloud (Amazon EC2) instance

To get started, complete the following steps:

Sign in to the AWS Management Console with your IAM user name and password

Choose Launch Stack and open it in a new tab

On the Create stack page, provide all necessary parameters for the Aurora database and AWS Managed Microsoft AD. Include your appropriate IP address for RemoteAccessCIDR

Select the check box to acknowledge the creation of IAM resources

Choose Create stack

Wait for the stack creation to complete
On the Events tab, you can review different events that occur during the stack creation process. After the stack creation is finished, the status will change to CREATE_COMPLETE. This process might take approximately 30 minutes to complete.

On the CloudFormation stack Outputs tab, take note of the parameter values. You use these values to complete the remaining steps of the solution.

Add an AD security group and user

Complete the following steps to create an AD group and user:
Install the Microsoft Remote Desktop (RDP) tool if it’s not already installed on your workstation.

Connect to the Amazon EC2 Windows machine using Microsoft Remote Desktop (RDP) and select Add PC

For the PC name, enter the EC2 instance’s public IP address, which can be found in the CloudFormation output tab under the parameter EC2InstancePublicIP. Then select Add

Choose the options menu (three dots) and select Connect.

For the Username, enter Admin@DomainName, using your AD domain name, which can be found in the CloudFormation output tab under the parameter DomainName. For the Password, enter the value of the parameter pMicrosoftADPW, which was provided as an input parameter during CloudFormation stack creation. Then, choose Continue.

When prompted with a certificate warning, choose Continue to confirm.

Open PowerShell.

In PowerShell, run the following commands to enable Kerberos authentication for the Aurora PostgreSQL cluster and to create AD groups and AD users. When prompted, enter passwords that meet Windows security requirements. You can run these commands one at a time or create a PowerShell script to run them all at once:
$Env:Path += ";C:Program FilesAmazonAWSCLIV2;C:Temppgsqlbin" $mariaPassword = Read-Host "Enter user Maria password:" $krishnaPassword = Read-Host "Enter user Krishna password:" $frankPassword = Read-Host "Enter user Frank password:" $DbClusterId = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='RDSClusterId'].OutputValue" --output text) $ADRole = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='RDSADRole'].OutputValue" --output text) $AD = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='DirectoryID'].OutputValue" --output text) $DbCluster = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='RDSClusterEndpoint'].OutputValue" --output text) $DbSecretARN = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='DbSecretARN'].OutputValue" --output text) $result = (aws rds modify-db-cluster --db-cluster-identifier $DbClusterId --domain $AD --domain-iam-role-name $ADRole) New-ADGroup -Name "app1dev" -SamAccountName app1dev -GroupCategory Security -GroupScope Global -DisplayName "Application Team 1" -Path "OU=Users,OU=corp,DC=corp,DC=example,DC=com" -Description "Members of this group are Application Team 1" New-ADGroup -Name "app2dev" -SamAccountName app2dev -GroupCategory Security -GroupScope Global -DisplayName "Application Team 2" -Path "OU=Users,OU=corp,DC=corp,DC=example,DC=com" -Description "Members of this group are Application Team 2" New-ADUser –SamAccountName "maria" –GivenName "maria" -Name "Maria G" –Surname "T" –AccountPassword (ConvertTo-SecureString -AsPlainText "$mariaPassword" -Force) –Enabled $true –Path "OU=Users,OU=corp,DC=corp,DC=example,DC=com" –CannotChangePassword $false –ChangePasswordAtLogon $false –PasswordNeverExpires $true -UserPrincipalName maria@corp.example.com New-ADUser –SamAccountName "krishna" –GivenName "krishna" -Name "Krishna S" –Surname "W" –AccountPassword (ConvertTo-SecureString -AsPlainText "$krishnaPassword" -Force) –Enabled $true –Path "OU=Users,OU=corp,DC=corp,DC=example,DC=com" –CannotChangePassword $false –ChangePasswordAtLogon $false –PasswordNeverExpires $true -UserPrincipalName krishna@corp.example.com New-ADUser –SamAccountName "frank" –GivenName "frank" -Name "Frank J" –Surname "J" –AccountPassword (ConvertTo-SecureString -AsPlainText "$frankPassword" -Force) –Enabled $true –Path "OU=Users,OU=corp,DC=corp,DC=example,DC=com" –CannotChangePassword $false –ChangePasswordAtLogon $false –PasswordNeverExpires $true -UserPrincipalName frank@corp.example.com Add-ADGroupMember -Identity "app1dev" -Members maria,frank Add-ADGroupMember -Identity "app2dev" -Members krishna,frank Get-ADGroup -filter * -SearchBase "OU=CORP, DC=CORP, DC=EXAMPLE, DC=COM" | Select Name Get-ADGroupMember -Identity app2dev -Recursive | Get-ADUser -Properties displayname, name | Select SamAccountName Get-ADGroupMember -Identity app1dev -Recursive | Get-ADUser -Properties displayname, name | Select SamAccountName

The output should look like the following screenshot:

Enable the pg_ad_mapping extension, add database roles, and create mappings

Next, you enable the pg_ad_mapping extension, add database roles, and create mappings between the AD security groups and database roles, as shown in the following table.

AD Group Database Role Weight

app1dev app1dev 7

app2dev app2dev 9

Run the following PowerShell commands to verify that Kerberos authentication is enabled on your Aurora cluster:
$DbClusterId = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='RDSClusterId'].OutputValue" --output text) $result = (aws rds describe-db-clusters --db-cluster-identifier $DbClusterId --query 'DBClusters[*].DomainMemberships[*].Status[][]' --output text) echo $DbClusterId $result

Expected output should be similar to:

adtest-auroradbcluster-4rv4soa9x1cm kerberos-enabled

Note: If you don’t see kerberos-enabled in the output, wait for few minutes and try the commands again.

From PowerShell, run the following in psql to create the pg_ad_mapping extension and add the AD security group to database role mapping:
$SID1 = (Get-ADGroup -Identity app1dev).SID.Value $SID2 = (Get-ADGroup -Identity app2dev).SID.Value $DbCluster = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='RDSClusterEndpoint'].OutputValue" --output text) $DbSecretARN = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='DbSecretARN'].OutputValue" --output text) $DbPassword = ( (aws secretsmanager get-secret-value --secret-id $DbSecretARN --query 'SecretString' --output text | ConvertFrom-Json).password ) $env:PGPASSWORD = $DbPassword ; psql -h $DbCluster -U postgres -d postgres -p 5432 -v SID1=$SID1 -v SID2=$SID2 -c " create extension if not exists pg_ad_mapping cascade; create role app1dev login; create role app2dev login; grant connect on database postgres,adtest to app1dev, app2dev; select pgadmap_set_mapping('app1dev', 'app1dev', '$SID1', 7); select pgadmap_set_mapping('app2dev', 'app2dev', '$SID2', 9); select * from pgadmap_read_mapping();"

The result should look like the following screenshot:

Now, add users to allow remote access on the Windows EC2 instance. In the Windows Control Panel, choose System and Security:

Under System, choose Allow remote access.

Under Remote Desktop, choose Select Users.

Choose Add.

Enter the usernames (for example, maria; Krishna; frank), choose Check Names, and then choose OK.

Close the EC2 RDP session and reconnect to the EC2 Windows machine using RDP with the same EC2InstancePublicIP public IP, but as user maria@DomainName and the password given earlier when creating the AD user. The AD domain name can be found in the CloudFormation output tab under the parameter DomainName.

Open PowerShell and run the following commands to connect to the database using psql using AD user maria@CORP.EXAMPLE.COM:
$env:Path += ";C:Program FilesAmazonAWSCLIV2;C:Temppgsqlbin;" $DbCluster = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='RDSClusterEndpoint'].OutputValue" --output text) whoami psql -h $DbCluster -d postgres -p 5432 -U maria@CORP.EXAMPLE.COM -c "select current_user;"

The result should look like the following screenshot. The user maria is authenticated as the app1dev database role as per the mapping rule:

Repeat the previous step (step 8) for AD users krishna and frank, and connect to EC2 Windows using RDP.

AD User: krishna@CORP.EXAMPLE.COM AD User: frank@CORP.EXAMPLE.COM

The AD user krishna is a member of the AD group app2dev and it is authenticated to the database role app2dev as per the mapping rule. The AD user frank is member of two AD groups, app1dev and app2dev. However, the mapping rule for the app2dev database role has a higher weight, set to 9. As a result, the user frank is authenticated as database role app2dev.

Let’s now examine a scenario when an AD user is moved from one AD group to another AD group, and how the pg_ad_mapping extension handles such scenarios.

Move an AD user to another group

The pg_ad_mapping extension handles changing an AD user’s group, provided that the new AD group is already mapped to a database role. In this example, we move the AD user maria from the app1dev AD group to the app2dev AD group and test the authentication for the user maria to validate that the change went into effect. Complete the following steps:

Close the EC2 RDP session and reconnect to your EC2 Windows machine using RDP with the same EC2InstancePublicIP public IP, but as user Admin@DomainName and the password given in the CloudFormation stack.

Open PowerShell and run the following command to update the AD user maria to change the AD group to app2dev:
Remove-ADGroupMember -Identity "app1dev" -Members maria -Confirm:$False Add-ADGroupMember -Identity "app2dev" -Members maria -Confirm:$False

The output should look like the following screenshot:

Close the EC2 RDP session for the AD user admin and reconnect to the EC2 Windows machine using RDP with the same EC2InstancePublicIP public IP, but as AD user maria@DomainName and the password given during AD user creation.

Open PowerShell and run the following commands to test the Aurora AD group authentication for AD user maria:
C:> Get-ADGroup -Identity databaseops | select SID SID --- S-1-5-21-2773742795-607290122-2880450345-1609 psql> select pgadmap_set_mapping('databaseops', 'databaseops_role', 'S-1-5-21-2773742795-607290122-2880450345-1609', 7);

0 The output should look like the following screenshot:

In this example, we explored a scenario where we changed the group for AD user maria from app1dev to app2dev. This transition was seamless for Aurora AD group authentication, and the user maria was successfully authenticated to the app2dev database role.
In the next section, we review the auditing and logging mechanisms when using AD group authentication in Aurora.

AD logging and auditing

You can use the dictionary view pg_stat_gssapi to identify the identity of the AD security principal of an authenticated user, as shown in the following screenshot.

For PostgreSQL version 16 and above, you can use the system_user session information function to get the authentication method and identity user presented during the authentication cycle before they were assigned a database role.

As of this writing, the AD user identity isn’t visible in the audit logs. You can enable the log_connections parameter to log DB session establishment. For more information, see log_connections. The output for this includes the AD user identity, as shown in the following screenshot. The backend PID associated with this output can then help attribute actions back to the actual AD user.

Security best practices for Aurora PostgreSQL

Use IAM policies to designate permissions for managing Amazon Aurora resources, including actions like creating, describing, modifying, and deleting DB clusters, tagging resources, and adjusting security groups.

Use security groups to regulate database access by specifying IP addresses or EC2 instances permitted to connect to your DB cluster, with the firewall initially blocking all access except for rules defined within the associated security group.

Use Aurora encryption to safeguard your DB clusters and snapshots while at rest, using the AES-256 encryption algorithm, an industry standard, to encrypt data on the Aurora storage.

Use Transport Layer Security (TLS) 1.2 or 1.3 when connecting to Aurora MySQL-Compatible or Aurora PostgreSQL-Compatible DB clusters.

For details regarding security considerations with Aurora PostgreSQL, see Security with Amazon Aurora PostgreSQL.

Clean up

To clean up your resources, delete the CloudFormation template using the AWS CloudFormation console.

Conclusion

In this post, you explored Kerberos authentication with AD groups in Amazon Aurora PostgreSQL, delving into the functionality of the pg_ad_mapping extension. Through this exploration, you’ve gained insight into efficiently using its capabilities to manage mapping rules between AD groups and database roles. With its seamless integration with Active Directory, it streamlines access management and enhances security by enabling efficient mapping of AD groups to database roles. This not only simplifies authentication processes but also means that access privileges are accurately assigned based on organizational requirements.
Upgrade your database authentication system today and take advantage of the latest features offered by our new extension. Strengthen your data protection, streamline user access, and stay ahead in the game of cybersecurity.
See Using AD security groups for Aurora PostgreSQL access control for additional details.
Leave your suggestions and questions in the comments section.

About the Authors

Krishna Sarabu is a Senior Database Engineer with Amazon Web Services. He focuses on containers, application modernization, infrastructure, and open-source database engines Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL. He enjoys working with customers to help design, deploy, and optimize relational database workloads on AWS.
Priyanka Sadhu is a Senior Solutions Architect at AWS, based in New York. She joined AWS in 2022 and collaborates with small and medium-sized businesses (SMBs) to develop innovative solutions that address their business challenges and expedite their digital and cloud transformation. With extensive experience in databases and analytics from her previous roles, she continues to focus on these areas as her technical expertise.
” temperature=”0.3″ top_p=”1.0″ best_of=”1″ presence_penalty=”0.1″ ].33 (USD) per hour

AWS Managed Microsoft AD (Standard Edition)

tag for a sub-heading. You can use only

Windows EC2 instance (t2.medium)

tag for a sub-heading. You can use only

Total

tag for a sub-heading. You can use only

For more information, visit AWS Pricing.

Deploy the solution with AWS CloudFormation

This solution is deployed using a CloudFormation stack, which creates all necessary resources, including:

Networking components such as VPC and subnet resources
An AWS Managed Microsoft AD server for setting up users and groups to test Kerberos authentication with Amazon Aurora PostgreSQL
An Aurora PostgreSQL database cluster
A Windows Amazon Elastic Compute Cloud (Amazon EC2) instance

To get started, follow these steps:

Sign in to the AWS Management Console with your IAM credentials.
Select Launch Stack and open it in a new tab.
On the Create stack page, provide all necessary parameters for the Aurora database and AWS Managed Microsoft AD, including your IP address for RemoteAccessCIDR.
Check the box to acknowledge the creation of IAM resources.
Select Create stack.
Wait for the stack creation to complete. You can monitor progress on the Events tab. The status will change to CREATE_COMPLETE once finished, which may take approximately 30 minutes.
On the CloudFormation stack Outputs tab, note the parameter values for subsequent steps.

Add an AD security group and user

To create an AD group and user, ensure that the Microsoft Remote Desktop (RDP) tool is installed on your workstation.

Connect to the Amazon EC2 Windows machine using RDP and select Add PC.
For the PC name, enter the EC2 instance’s public IP address, found in the CloudFormation output tab under the parameter EC2InstancePublicIP, then select Add.
Choose the options menu (three dots) and select Connect.
For the Username, enter Admin@DomainName, using your AD domain name from the CloudFormation output tab under the parameter DomainName. Enter the password from the pMicrosoftADPW parameter provided during CloudFormation stack creation, then choose Continue.
When prompted with a certificate warning, choose Continue.
Open PowerShell.
Run the following commands to enable Kerberos authentication for the Aurora PostgreSQL cluster and create AD groups and users. When prompted, enter passwords that meet Windows security requirements. You can execute these commands individually or create a PowerShell script to run them all at once:

$Env:Path += ";C:Program FilesAmazonAWSCLIV2;C:Temppgsqlbin"

$mariaPassword = Read-Host "Enter user Maria password:"

$krishnaPassword = Read-Host "Enter user Krishna password:"

$frankPassword = Read-Host "Enter user Frank password:"

$DbClusterId = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='RDSClusterId'].OutputValue" --output text)

$ADRole = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='RDSADRole'].OutputValue" --output text)

$AD = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='DirectoryID'].OutputValue" --output text)

$DbCluster = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='RDSClusterEndpoint'].OutputValue" --output text)

$DbSecretARN = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='DbSecretARN'].OutputValue" --output text)

$result = (aws rds modify-db-cluster --db-cluster-identifier $DbClusterId --domain $AD --domain-iam-role-name $ADRole)

New-ADGroup -Name "app1dev" -SamAccountName app1dev -GroupCategory Security -GroupScope Global -DisplayName "Application Team 1" -Path "OU=Users,OU=corp,DC=corp,DC=example,DC=com" -Description "Members of this group are Application Team 1"

New-ADGroup -Name "app2dev" -SamAccountName app2dev -GroupCategory Security -GroupScope Global -DisplayName "Application Team 2" -Path "OU=Users,OU=corp,DC=corp,DC=example,DC=com" -Description "Members of this group are Application Team 2"

New-ADUser –SamAccountName "maria" –GivenName "maria" -Name "Maria G" –Surname "T" –AccountPassword (ConvertTo-SecureString -AsPlainText "$mariaPassword" -Force) –Enabled $true –Path "OU=Users,OU=corp,DC=corp,DC=example,DC=com" –CannotChangePassword $false –ChangePasswordAtLogon $false –PasswordNeverExpires $true -UserPrincipalName maria@corp.example.com

New-ADUser –SamAccountName "krishna" –GivenName "krishna" -Name "Krishna S" –Surname "W" –AccountPassword (ConvertTo-SecureString -AsPlainText "$krishnaPassword" -Force) –Enabled $true –Path "OU=Users,OU=corp,DC=corp,DC=example,DC=com" –CannotChangePassword $false –ChangePasswordAtLogon $false –PasswordNeverExpires $true -UserPrincipalName krishna@corp.example.com

New-ADUser –SamAccountName "frank" –GivenName "frank" -Name "Frank J" –Surname "J" –AccountPassword (ConvertTo-SecureString -AsPlainText "$frankPassword" -Force) –Enabled $true –Path "OU=Users,OU=corp,DC=corp,DC=example,DC=com" –CannotChangePassword $false –ChangePasswordAtLogon $false –PasswordNeverExpires $true -UserPrincipalName frank@corp.example.com

Add-ADGroupMember -Identity "app1dev" -Members maria,frank

Add-ADGroupMember -Identity "app2dev" -Members krishna,frank

Get-ADGroup -filter * -SearchBase "OU=CORP, DC=CORP, DC=EXAMPLE, DC=COM" | Select Name

Get-ADGroupMember -Identity app2dev -Recursive | Get-ADUser -Properties displayname, name | Select SamAccountName

Get-ADGroupMember -Identity app1dev -Recursive | Get-ADUser -Properties displayname, name | Select SamAccountName

The output should resemble the following screenshot:

Enable the pg_ad_mapping extension, add database roles, and create mappings

Next, enable the pg_ad_mapping extension, add database roles, and establish mappings between the AD security groups and database roles:

AD Group	Database Role	Weight
app1dev	app1dev	7
app2dev	app2dev	9

Run the following PowerShell commands to verify that Kerberos authentication is enabled on your Aurora cluster:

$DbClusterId = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='RDSClusterId'].OutputValue" --output text)

$result = (aws rds describe-db-clusters --db-cluster-identifier $DbClusterId --query 'DBClusters[*].DomainMemberships[*].Status[][]' --output text)

echo $DbClusterId $result

The expected output should resemble:

adtest-auroradbcluster-4rv4soa9x1cm 
kerberos-enabled

Note: If kerberos-enabled does not appear in the output, wait a few minutes and try the commands again.

From PowerShell, execute the following in psql to create the pg_ad_mapping extension and add the AD security group to database role mapping:

$SID1 = (Get-ADGroup -Identity app1dev).SID.Value
$SID2 = (Get-ADGroup -Identity app2dev).SID.Value

$DbCluster = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='RDSClusterEndpoint'].OutputValue" --output text)

$DbSecretARN = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='DbSecretARN'].OutputValue" --output text)

$DbPassword = ((aws secretsmanager get-secret-value --secret-id $DbSecretARN --query 'SecretString' --output text | ConvertFrom-Json).password)
$env:PGPASSWORD = $DbPassword ;
psql -h $DbCluster -U postgres -d postgres -p 5432 -v SID1=$SID1 -v SID2=$SID2 -c "
create extension if not exists pg_ad_mapping cascade;
create role app1dev login;
create role app2dev login;
grant connect on database postgres,adtest to app1dev, app2dev;
select pgadmap_set_mapping('app1dev', 'app1dev', '$SID1', 7);
select pgadmap_set_mapping('app2dev', 'app2dev', '$SID2', 9);
select * from pgadmap_read_mapping();"

The result should look like the following screenshot:

Add users to allow remote access on the Windows EC2 instance. In the Windows Control Panel, select System and Security.
Under System, choose Allow remote access.
Under Remote Desktop, select Select Users.
Click Add.
Enter the usernames (e.g., maria; krishna; frank), choose Check Names, and then select OK.
Close the EC2 RDP session and reconnect to the EC2 Windows machine using RDP as user maria@DomainName with the password provided during AD user creation. The AD domain name can be found in the CloudFormation output tab under the parameter DomainName.
Open PowerShell and run the following commands to connect to the database using psql with AD user maria@CORP.EXAMPLE.COM:

$env:Path += ";C:Program FilesAmazonAWSCLIV2;C:Temppgsqlbin;"
$DbCluster = (aws cloudformation describe-stacks --query "Stacks[?StackName=='adtest'][].Outputs[?OutputKey=='RDSClusterEndpoint'].OutputValue" --output text)
whoami
psql -h $DbCluster -d postgres -p 5432 -U maria@CORP.EXAMPLE.COM -c "select current_user;"

The result should look like the following screenshot, confirming that user maria is authenticated as the app1dev database role according to the mapping rule:

Repeat the previous step for AD users krishna and frank, connecting to EC2 Windows using RDP.

AD User: `krishna@CORP.EXAMPLE.COM`	AD User: `frank@CORP.EXAMPLE.COM`

The AD user `krishna` is a member of the AD group `app2dev` and is authenticated to the database role `app2dev` according to the mapping rule.	The AD user `frank` is a member of two AD groups, `app1dev` and `app2dev`. However, the mapping rule for the app2dev database role has a higher weight, set to 9. Consequently, user `frank` is authenticated as database role app2dev.

Next, we will examine a scenario where an AD user is moved from one AD group to another and how the pg_ad_mapping extension manages such transitions.

Move an AD user to another group

The pg_ad_mapping extension effectively handles changes to an AD user’s group, provided that the new AD group is already mapped to a database role. In this example, we will move the AD user maria from the app1dev AD group to the app2dev AD group and test her authentication to verify that the change has taken effect.

Close the EC2 RDP session and reconnect to your EC2 Windows machine using RDP as user Admin@DomainName with the password provided in the CloudFormation stack.
Open PowerShell and run the following command to update the AD user maria to change her AD group to app2dev:

Remove-ADGroupMember -Identity "app1dev" -Members maria -Confirm:$False
Add-ADGroupMember -Identity "app2dev" -Members maria -Confirm:$False

The output should resemble the following screenshot:

Close the EC2 RDP session for the AD user admin and reconnect to the EC2 Windows machine using RDP as AD user maria@DomainName with the password provided during her creation.
Open PowerShell and run the following commands to test the Aurora AD group authentication for AD user maria:

C:> Get-ADGroup -Identity databaseops | select SID 
SID 
--- 
S-1-5-21-2773742795-607290122-2880450345-1609 

psql> select pgadmap_set_mapping('databaseops', 'databaseops_role', 'S-1-5-21-2773742795-607290122-2880450345-1609', 7);

The output should resemble the following screenshot:

In this example, we explored the scenario of changing the group for AD user maria from app1dev to app2dev. This transition was seamless for Aurora AD group authentication, allowing user maria to be successfully authenticated to the app2dev database role.

Next, we will review the auditing and logging mechanisms associated with AD group authentication in Aurora.

AD logging and auditing

Utilize the dictionary view pg_stat_gssapi to identify the identity of the AD security principal of an authenticated user. For PostgreSQL version 16 and above, the system_user session information function can be employed to retrieve the authentication method and identity user presented during the authentication cycle before they were assigned a database role.

As of this writing, the AD user identity is not visible in the audit logs. However, enabling the log_connections parameter will log database session establishments, including the AD user identity. For more information, refer to log_connections. The output from this includes the AD user identity, as shown in the following screenshot. The backend PID associated with this output can then be used to attribute actions back to the actual AD user.

Security best practices for Aurora PostgreSQL

Utilize IAM policies to designate permissions for managing Amazon Aurora resources, including actions like creating, describing, modifying, and deleting DB clusters, tagging resources, and adjusting security groups.
Implement security groups to regulate database access by specifying IP addresses or EC2 instances permitted to connect to your DB cluster, with the firewall initially blocking all access except for rules defined within the associated security group.
Employ Aurora encryption to safeguard your DB clusters and snapshots while at rest, utilizing the AES-256 encryption algorithm, an industry standard, to encrypt data on Aurora storage.
Use Transport Layer Security (TLS) 1.2 or 1.3 when connecting to Aurora MySQL-Compatible or Aurora PostgreSQL-Compatible DB clusters.

For detailed security considerations with Aurora PostgreSQL, see Security with Amazon Aurora PostgreSQL.

Clean up

To clean up your resources, delete the CloudFormation template using the AWS CloudFormation console.

About the Authors

Krishna Sarabu is a Senior Database Engineer with Amazon Web Services, focusing on containers, application modernization, infrastructure, and open-source database engines such as Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL. He enjoys collaborating with customers to design, deploy, and optimize relational database workloads on AWS.

Priyanka Sadhu is a Senior Solutions Architect at AWS, based in New York. She joined AWS in 2022 and works with small and medium-sized businesses (SMBs) to develop innovative solutions that address their business challenges and expedite their digital and cloud transformation. With extensive experience in databases and analytics from her previous roles, she continues to focus on these areas as her technical expertise.

Tech Optimizer