Select Page
FacebooktwitterlinkedinyoutubeFacebooktwitterlinkedinyoutube

For database deployments in Azure, there are many options to manage authentication.  The first is to use pure SQL based authentication, where the user, password and roles are pre-configured on the database.  The second is to use Microsoft Entra ID (Formerly Azure Active Directory).  In this second case however, user management still ends up being a manual process, as the users still need to be configured on the database.  In this blog, we will discuss an easier way to synchronize authentication and authorization via Active Directory (AD) into Azure managed databases, using the Heimdall Database Proxy. When used per this configuration, users are provisioned on the database at runtime as needed, reducing the overall management of the database, and only requiring those needing access to be provisioned, even if they are entitled to ask for it.

Benefits of Active Directory Authentication

Active Directory (AD) is the leading identity management solution for enterprise organizations.  Within AD, 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. Postgres compatible database and with the administrator credentials (e.g. Azure Database for Postgres);
  2. Postgres client installed on a VM instance; psql is sufficient, but could include DBeaver or other tools;
  3. Active Directory server, either standalone or via  Microsoft Entra ID, and have management control;
  4. Heimdall Proxy instance.

Network Architecture

Since the Heimdall Proxy 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 the proxy.  One way this can be done is by placing the database in one subnet, the proxy in another and applications in the third, and only allowing access to the DB subnet from the proxy subnet.  For example, the topology can be setup as follows:

 Figure 2: Heimdall Proxy Network Architecture Diagram

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

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

Note:  In new versions of Heimdall, the scripts are available to inject into the data source via the data source tab, so no manual configuration is needed. 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”
 

Future Enhancements

As of the writing of this blog, a beta version of Heimdall is available that includes an alternate interface to configure users in the database via a portal.  This provides enhanced controls allowing users to request different levels of access as needed, and includes approval chain support.  This provides enhanced compliance with segregation of duty requirements as well as adhering to the principle of least privilege.  Please contact Heimdall for access to the beta if interested!

Conclusion

In this blog, we showed you how to set up Active Directory authentication WITH authorization for Postgres.  However, the Heimdall Proxy provides synchronization for all Azure managed databases types (e.g. Postgres, MySQL, SQL Server, Azure Database).  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

About Heimdall Data

Heimdall Data, a Microsoft ISV partner, offers a database proxy that improves the scale and security of databases in Azure. Deployment requires no code changes. 

FacebooktwitterlinkedinyoutubeFacebooktwitterlinkedinyoutube