With Amazon RDS, there are many options to manage authentication.  The first is to use IAM authentication, which is the preferred method.  A second way is to integrate with AWS secrets, allowing for password management in a centralized manner.  Another approach is Active Directory integration, as this supports resources that are both in AWS and outside, to support the entire organization.  Normally, this is done via Kerberos integration. However, this requires your database instances to be pre-configured with users and roles, increasing IT management burden.

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

 

Benefits of Active Directory Authentication

Active Directory is the leading identity management solution for enterprise organizations.  Within Active Directory, not only is the user authentication information kept, but group membership, i.e. you could have users flagged as being part of the HR team vs. Operations group.  It is group membership that is often neglected in database offerings. AD is often used to verify authentication, but not authorization.

The benefit of full integration with Active Directory is to not only allow authentication of users, but to also remove the burden to manage users on the databases.  Instead of pre-configuring users, the Heimdall Proxy synchronizes the user information as needed out of Active Directory into the database so that access control is maintained.  This benefits IT teams by:

1. Integrating data access control with existing user management processes;
2. Immediate of access termination when a user leaves the group;
3. Password reset is automated without additional Help Desk personnel;
4. The synchronization routine creates an audit log who accessed what data, and when.

 

Solution Overview

 Figure 1: Heimdall Proxy for Authorization and Authentication Architecture Diagram

 

The solution is operationally simple:  As users connect to the proxy: 

1. Credentials are presented to Active Directory or an LDAP server.
2. If the credentials pass, a second LDAP query determines their group membership.
3. 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.
4. Finally, the user’s credentials are used to connect to the database, and the database will use 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.  

 

Prerequisites

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

1. Amazon Virtual Private Cloud (VPC);
2. Postgres compatible database and with the administrator credentials (e.g. Amazon RDS Postgres);
3. Postgres client installed on an EC2 instance; psql is sufficient, but could include DBeaver or other tools;
4. Active Directory server, either standalone or via AWS Directory Server, and have management control;
5. Heimdall Proxy instance.

 

Network Architecture

Since Heimdall will be used as a security perimeter device, it is advised that it be configured in such a way so that applications can not directly connect to the database, and instead they must go through Heimdall.  One way this can be done is by placing the database in one subnet, the Heimdall Proxy in another and applications in the third, and only allowing access to the DB subnet from the Heimdall subnet.  For example, the topology can be setup as follows:

 

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”:

 

Note, 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:

 

 

 

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):

 

 

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 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 is 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) Setup the basic virtual database and 2) Verify a connection using the administrator credentials, setup the authentication as shown:

 

 

Key points on this configuration: 

1. If using LDAP, in the LDAP url, specify ldaps:// vs. ldap://
2. The LDAP security principal needs to be able to read the group membership of any other user;
3. 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;
4. If using nested groups, select “Use nested group filter”;
5. If using a self-signed TLS certificate, or one that the Heimdall TLS subsystem does not recognize, select “Ignore LDAP”;
6. 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 can be found 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 blog, we showed you how to set up Active Directory authentication WITH authorization for Postgres.  The Heimdall Proxy provides synchronization scripts for all Amazon RDS and Redshift instance types.  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:

• Automated Query Caching
• Splitting Query Reads and Writes
• Advanced Connection Pooling
• Heimdall Proxy for Amazon Redshift
• Contact: info@heimdalldata.com

 

 

 

About Heimdall Data

Heimdall Data is an AWS Advanced ISV partner. They have Amazon Service Ready designations for Amazon RDS and Amazon Redshift. Heimdall Data offers a database proxy that offloads SQL improving database scale while securing database access. Proxy deployment does not require code changes.