Active Directory Authentication and Authorization with Amazon RDS

By Roneel Kumar, Sr. Database Specialist Solutions Architect – AWS
By Erik Brandsberg, Chief Architect – Heimdall Data

Heimdall Data

With Amazon Relational Database Service (Amazon RDS), there are many options to manage authentication. The first is to use AWS Identity and Access Management (IAM) authentication, which is the preferred method. A second way is to integrate with Amazon Web Services (AWS) secrets, allowing for password management in a centralized manner.

Another approach is Active Directory (AD) integration, which supports resources that are both in AWS and outside, to centralize user authentication for the entire organization. This is usually done via Kerberos integration, but this requires your database instances to be preconfigured with users and roles, increasing IT management burden.

In this post, we will discuss an easier way to synchronize authentication and authorization via Active Directory into Amazon Relational Database Service (Amazon RDS) using the Heimdall Database Proxy.

Heimdall Data is an AWS Partner with the Amazon RDS Ready designation. The Heimdall Proxy is designed for Amazon RDS, Amazon Aurora, and Amazon Redshift and is transparently deployed to intelligently manage backend connections for improved database scale.

Benefits of Active Directory Authentication

Active Directory is a leading identity management solution for enterprise organizations. In Active Directory, group membership data is also stored in addition to user authentication data. For example, users may be marked as belonging to the HR team vs. the Operations group.

It is group membership that’s sometimes neglected in database offerings. AD is often used to verify authentication but not authorization.

The benefit of full integration with AD is to allow authentication of users and remove the burden to manage users on the databases. Instead of preconfiguring users, the Heimdall Proxy synchronizes user information as needed out of Active Directory into the database so that access control is maintained.

This benefits IT teams by:

Integrating data access control with existing user management processes.
Immediate access termination when a user leaves the group.
Password reset is automated without additional help desk personnel.
Synchronization routine creates an audit log of who accessed what data and when.

Solution Overview

As shown in Figure 1 below, the solution is operationally simple. As users connect to the proxy:

Credentials are presented to Active Directory or an LDAP server.
If the credentials pass, a second LDAP query determines their group membership.
Once one or more groups are extracted, the username, password, and group memberships are passed to the database via a privileged user. This is done via a stored procedure on the database, which then performs the role synchronization internally.
Finally, the user’s credentials are used to connect to the database, and the database uses the configured roles to provide access control as normal.

When a user’s credentials are synchronized with the database, the credentials are cached in the proxy, avoiding load on the directory server and avoiding constant thrashing of credentials on the database.

Figure 1 – Heimdall Proxy for authorization and authentication architecture diagram.

Prerequisites

For this solution, we assume you have the following infrastructure:

Amazon Virtual Private Cloud (Amazon VPC).
Postgres-compatible database with the administrator credentials (such as Amazon RDS for Postgres SQL).
Postgres client installed on an Amazon Elastic Compute Cloud (Amazon EC2) instance; psql (which is the interactive terminal for working with Postgres) is sufficient, but could include DBeaver or other tools.
Active Directory server, either standalone or via AWS Directory Service, and have management control.
Heimdall Proxy instance.

Active Directory Preparation

The first step in configuring the solution is to prepare Active Directory groups to filter at the Heimdall Proxy level. We created a group prefixed “heimdall-” with the rest of the name representing the access control desired. In this case we used “hr-data”.

Figure 2 – Active Directory group.

Note that these names are chosen just for the sake of simplicity—existing groups could be filtered. These names made the configuration simple.

Second, we will create a user and make them a member of this group.

Figure 3 – Adding users to Active Directory groups.

Optionally, you can create a read-only user to act as a security principle for performing queries against Active Directory (we will follow the example with this).

Figure 4 – Adding a read-only user.

Database Preparation

In order to prepare the database, first log in as the database administrator. Next, create a stored procedure to synchronize the credentials by the Heimdall Proxy.

The script to execute for Postgres and compatible databases is documented here.

Please note that you may need to create the “heimdall” schema before creating the stored procedure, although it may exist already if replication lag detection is enabled.

As a second step, it’s the administrator’s responsibility to associate group roles with the appropriate user permissions.

Heimdall Proxy Configuration

After using the Heimdall Central Console Wizard to 1) set up the basic virtual database and 2) verify a connection using the administrator credentials, set up the authentication as shown:

Figure 5 – Heimdall Proxy configuration; specifying the AD users.

Key points on this configuration:

If using LDAP, in the LDAP URL specify ldaps:// vs. ldap://
The LDAP security principal needs to be able to read the group membership of any other user.
If the LDAP group filter returns no records, then the user will be denied. The result must contain at least one group for access to be permitted.
If using nested groups, select “Use nested group filter”.
If using a self-signed TLS certificate, or one the Heimdall TLS subsystem does not recognize, select “Ignore LDAP”.
The admin user is the database administrator user needed to synchronize user information into the database. The script referenced in the sync command performs the synchronization. Examples of the scripts for various environments are documented here.

Testing Authentication

At this point, everything should be configured. We can use psql to check the status of the database configuration. Before triggering a new user, check the state of the users:

# PGPASSWORD=*** psql --host=127.0.0.1 --user=postgres -p 5432
psql (10.19 (Ubuntu 10.19-0ubuntu0.18.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> show users;
 Role name |         Attributes         
-----------+----------------------------
 postgres  | superuser, create database


Connect with the “testuser” via the Heimdall Proxy for the first time on port 5050:

# PGPASSWORD=*** psql --host=127.0.0.1 --user=testuser -p 5050 postgres
psql (10.19 (Ubuntu 10.19-0ubuntu0.18.04.1))
Type "help" for help.

odoo=> show users;
 Role name |         Attributes         
-----------+----------------------------
 postgres  | superuser, create database
 testuser  | 
(4 rows)

=> WITH RECURSIVE cte AS (
   SELECT oid FROM pg_roles WHERE rolname = 'testuser'
   UNION ALL
   SELECT m.roleid
   FROM   cte
   JOIN   pg_auth_members m ON m.member = cte.oid
   )
SELECT oid, oid::regrole::text AS rolename FROM cte;  -- oid & name
  oid   |      rolename      
--------+--------------------
 195158 | testuser
 195159 | "heimdall-hr-data"

Conclusion

In this post, we showed you how to set up Active Directory authentication with authorization for Postgres. Heimdall Data provides synchronization scripts for other databases as well, allowing all Amazon RDS instance types to be supported in a similar way.

Using Active Directory authentication allows enterprise organizations to standardize their password and authorization management via a globally available authentication store, reducing management overhead and improving security and auditing capabilities.

Reference Material

AWS Security Blogs:

Heimdall Proxy Blogs:

.

.

Heimdall Data – AWS Partner Spotlight

Heimdall Data is an AWS Partner with Service Ready designations for Amazon RDS and Amazon Redshift. Heimdall Data offers a database proxy that offloads SQL and improves database scale while securing database access. Proxy deployment does not require code changes.

Contact Heimdall Data | Partner Overview | AWS Marketplace