Close Menu

    Simplify database authentication management with the Amazon Aurora PostgreSQL pg_ad_mapping extension

    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.

    ArchitectureDiag

    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:

    1. Sign in to the AWS Management Console with your IAM user name and password
    2. Choose Launch Stack and open it in a new tab
      Launch Stack
    3. 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
    4. Select the check box to acknowledge the creation of IAM resources
    5. Choose Create stack
    6. 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.
    7. 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.

    1. Connect to the Amazon EC2 Windows machine using Microsoft Remote Desktop (RDP) and select Add PC
    2. 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
    3. Choose the options menu (three dots) and select Connect.
    4. 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.
    5. When prompted with a certificate warning, choose Continue to confirm.
    6. Open PowerShell.
    7. 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
    1. 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.

    2. 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:

    3. Now, add users to allow remote access on the Windows EC2 instance. In the Windows Control Panel, choose System and Security:
    4. Under System, choose Allow remote access.
    5. Under Remote Desktop, choose Select Users.
    6. Choose Add.
    7. Enter the usernames (for example, maria; Krishna; frank), choose Check Names, and then choose OK.

    8. 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.
    9. 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:

    10. 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:

    1. 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.
    2. 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:

    3. 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.
    4. Open PowerShell and run the following commands to test the Aurora AD group authentication for AD user maria: 
      $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
select session_user, current_user, principal from pg_stat_gssapi where pid = pg_backend_pid();
q

      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.

    Source: Read More

    Hostinger

    Related Posts

    Leave A Reply