Using external Kerberos authentication with Amazon Aurora PostgreSQL

In the first post in this series, Preparing on-premises and AWS environments for external Kerberos authentication for Amazon RDS, we built the infrastructure for a one-way forest trust between an on-premises Microsoft Active Directory (AD) domain (trust: incoming) and an AWS Managed Microsoft AD domain (trust: outgoing) provided by AWS Directory Service. In this post, we walk through how to join Amazon Aurora PostgreSQL-Compatible Edition clusters to an AWS Managed Microsoft AD and demonstrate user authentication for Aurora PostgreSQL using external Kerberos and Microsoft AD.

Prerequisites

Before we start, make sure that you have prepared your on-premises and AWS environments, as shown in the following architectural diagram, including one on-premises AD user joedoe@onprem and one Windows and one Linux client, which are joined to the on-premises AD domain onprem.local.

Creating Aurora PostgreSQL clusters and joining them to an AWS Managed Microsoft AD domain

We first create Aurora PostgreSQL clusters in aws-acc-1 and aws-acc-2 and join the clusters to AWS Managed Microsoft AD.

1. Log in to the Amazon RDS console from aws-acc-1.
2. Create the DB subnet group aws-acc-1-db-subnet-group, which includes the three subnets in aws-acc-1-rds-vpc.

3. Create the security group aws-acc-1-rds-vpc-allow-connection to allow connection from on-premises clients (in on-prem-client-vpc) to access Aurora PostgreSQL via TCP on port 5432.

4. On the Amazon RDS console, create the Aurora PostgreSQL cluster apg-acc-1-kerberos-11-7 in the DB subnet group aws-acc-1-db-subnet-group.
5. For VPC security group, select Choose existing.
6. For Existing VPC security groups, enter the security group you created.
7. For Database authentication options, select Password and Kerberos authentication.
8. Choose Browse Directory.

9. In the Choose directory section, select myaws.com.
10. Choose Choose.

You can now see ad.myaws.com in the Directory field.

After successfully creating the Aurora PostgreSQL cluster, we see that the cluster has joined the AWS Managed Microsoft AD, and the directory status shows as kerberos-enabled.

We can also verify the directory status via the AWS Command Line Interface (AWS CLI). See the following code:

[joedoe@ip-10-11-1-23 ~]$ aws rds describe-db-instances \
> --db-instance-identifier apg-acc-1-kerberos-11-7-instance-1 \
> --query 'DBInstances[*].DomainMemberships' \
> --region ap-southeast-2 \
> --profile aws-acc-1 \
> --output table 
---------------------------------------------------------------------------------------------------
|                                       DescribeDBInstances                                       |
+--------------+---------------+---------------------------------------------+--------------------+
|    Domain    |     FQDN      |                 IAMRoleName                 |      Status        |
+--------------+---------------+---------------------------------------------+--------------------+
|  d-97671c6bae|  ad.myaws.com |  rds-directoryservice-kerberos-access-role  |  kerberos-enabled  |
+--------------+---------------+---------------------------------------------+--------------------+

You can also create the Aurora PostgreSQL cluster via the AWS CLI:

aws rds create-db-cluster \
--db-cluster-identifier apg-acc-1-kerberos-11-7 \
--engine aurora-postgresql \
--port 5432 \
--master-username postgres \
--master-user-password mysecurepassword \
--db-subnet-group-name aws-acc-1-db-subnet-group \
--vpc-security-group-ids sg-0f3e336a3baeab672 \
--domain d-97671c6bae \
--domain-iam-role-name rds-directoryservice-kerberos-access-role

aws rds create-db-instance \
--db-cluster-identifier apg-acc-1-kerberos \
--db-instance-identifier apg-acc-1-kerberos-instance-1 \
--db-instance-class db.r5.large \
--engine aurora-postgresql \
--db-subnet-group-name aws-acc-1-db-subnet-group

aws rds create-db-instance \
--db-cluster-identifier apg-acc-1-kerberos \
--db-instance-identifier apg-acc-1-kerberos-instance-2 \
--db-instance-class db.r5.large \
--engine aurora-postgresql \
--db-subnet-group-name aws-acc-1-db-subnet-group

For Aurora PostgreSQL to call AWS Directory Service for you, you need an AWS Identity and Access Management (IAM) role that uses the managed IAM policy AmazonRDSDirectoryServiceAccess. When you create a DB instance on the AWS Management Console and the console user has the iam:CreateRole permission, the console creates this role automatically. For this use case, the role name is rds-directoryservice-kerberos-access-role.

For instructions on creating the role manually, see Create an IAM Role for Amazon Aurora to Access the AWS Directory Service. When you create this role, choose Directory Service, and attach the AWS managed policy AmazonRDSDirectoryServiceAccess to it.

For existing Aurora PostgreSQL clusters, we can modify the cluster to join the AWS Managed Microsoft AD from the Amazon RDS console, or the AWS CLI:

aws rds modify-db-cluster \
--db-cluster-identifier apg-acc-1-kerberos-11-7 \
--domain d-97671c6bae \
--domain-iam-role-name rds-directoryservice-kerberos-access-role \
--apply-immediately

11. Repeat these steps in aws-acc-2 to create an Aurora PostgreSQL cluster and join the cluster to the shared AWS Managed Microsoft AD, with following details:

• • Cluster name – apg-acc-2-kerberos-11-7
  • DB subnet groups – aws-acc-2-db-subnet-group
  • Security group – aws-acc-2-rds-vpc-allow-connection
  • Database authentication options – Password and Kerberos authentication
  • Directory – myaws.com (d-97671c1f8c), shared by aws-acc-1

12. On the Amazon RDS console, make sure the Aurora PostgreSQL cluster in aws-acc-2 has joined the shared AWS Managed Microsoft AD successfully.

We can also verify the status via the AWS CLI:

[joedoe@ip-10-11-1-23 ~]$ aws rds describe-db-instances \
> --db-instance-identifier apg-acc-2-kerberos-11-7-instance-1 \
> --query 'DBInstances[*].DomainMemberships' \
> --region ap-southeast-2 \
> --profile aws-acc-2 \
> --output table
---------------------------------------------------------------------------------------------------
|                                       DescribeDBInstances                                       |
+--------------+---------------+---------------------------------------------+--------------------+
|    Domain    |     FQDN      |                 IAMRoleName                 |      Status        |
+--------------+---------------+---------------------------------------------+--------------------+
|  d-97671c1f8c|  ad.myaws.com |  rds-directoryservice-kerberos-access-role  |  kerberos-enabled  |
+--------------+---------------+---------------------------------------------+--------------------+

13. In the on-prem-account, log in to the Windows client and install the pgAdmin and psql utility.
14. Log in to the newly created Aurora PostgreSQL cluster in aws-acc-1 with the primary user account.
15. Create the DB user joedoe@ONPREM.LOCAL and grant the rds_ad role to this user.

The username in PostgreSQL is case-sensitive. Kerberos authentication requires that the domain suffix of the username be in uppercase; the case of user account name joedoe needs to match the case of User logon name (pre-Windows 2000) in the AD, as shown in the following screenshot.

16. Log in to the newly created Aurora PostgreSQL cluster in aws-acc-2 with the primary user account.
17. Create the DB user joedoe@ONPREM.LOCAL and grant the rds_ad role to this user.

Logging in to Aurora PostgreSQL clusters

To use external Kerberos and Microsoft AD to log in to the Aurora PostgreSQL clusters on a Windows platform, we need to use a special endpoint. Instead of using the Amazon domain rds.amazonaws.com in the endpoint, use the domain name of the AWS Managed Microsoft AD.

For example, to connect to the Aurora PostgreSQL cluster apg-acc-1-kerberos-11-7 created in aws-acc-1, instead of using apg-acc-1-kerberos-11-7.cluster-cz54v71h2eg5.ap-southeast-2.rds.amazonaws.com as the endpoint, use the special endpoint apg-acc-1-kerberos-11-7.cluster-cz54v71h2eg5.ap-southeast-2.ad.myaws.com, where ad.myaws.com is the domain name of the AWS Managed Microsoft AD.

For more information, see Connecting to PostgreSQL with Kerberos Authentication.

1. Log in as AD user joedoe@onprem to the Windows client that is joined to the on-premises domain local.
2. Log in to the Aurora PostgreSQL cluster created in aws-acc-1 with the special cluster endpoint as DB user joedoe@ONPREM.LOCAL (no password is required for psql).

Kerberos authentication also works when connections are made to the special cluster reader endpoint and special instance endpoint as joedoe@ONPREM.LOCAL.

3. Log in to the Aurora PostgreSQL cluster instance created in aws-acc-2.

This cluster joined the AWS Managed Microsoft AD that is shared by aws-acc-1. The connections to the special endpoints can be established successfully without a password.

We can also connect to the Aurora PostgreSQL cluster with the special endpoint without a password from the pgAdmin application. See the following screenshots.

The following screenshot shows that external Kerberos authentication works for the special cluster writer endpoint, special cluster reader endpoint, and special instance endpoint in pgAdmin for Aurora PostgreSQL created in aws-acc-1 and aws-acc-2.

Logging in to RDS for PostgreSQL instances on a Linux platform

To use external Kerberos and Microsoft AD to log in to the Aurora PostgreSQL clusters and instances on a Linux platform, you don’t need to use the special endpoint. Both the original endpoint (ending with rds.amazonrds.com) and special endpoint (ending with ad.myaws.com) work.

1. Install the psql client on Linux, which you created and configured in the post Preparing on-premises and AWS environments for external Kerberos authentication for Amazon RDS.
2. Log in to the Linux client as AD user joedoe@onprem.
3. Use the kinit command to get a Kerberos ticket from prem.local.
4. Log in to the Aurora PostgreSQL cluster in aws-acc-1; with both the original and special endpoint, no password is required.

5. Log in to the Aurora PostgreSQL cluster in aws-acc-2; with both the original and special endpoint, no password is required.

Summary

In this post, we demonstrated DB user authentication for Aurora PostgreSQL using external Kerberos and Microsoft AD, based on a one-way forest trust between an on-premises AD domain and an AWS Managed Microsoft AD domain. For instructions on creating this trust environment, see Preparing on-premises and AWS environments for external Kerberos authentication for Amazon RDS.

About the Author

Zhen Wang is an RDS Cloud Support Engineer at Amazon Web Services.