Integrating PostgreSQL with Active Directory for LDAP Authentication

3 min readMay 17, 2024

Introduction

PostgreSQL, one of the most popular open-source relational databases, can be configured to authenticate users via LDAP. When combined with Active Directory (AD), this setup allows centralized management of credentials and access control. In this article, we will discuss the process of integrating PostgreSQL with AD for LDAP authentication.

Understanding Active Directory and Its Structure

Active Directory (AD) is a directory service developed by Microsoft for Windows domain networks. It plays a key role in user and resource management within a network. AD uses a hierarchical structure with domains, trees, and forests, and it organizes resources into containers like organizational units (OUs).

A typical AD distinguished name (DN) might look like this:

CN=ITU,OU=Users,OU=ITUUSERS,DC=ITU,DC=gov,DC=tr

CN: Common Name
OU: Organizational Unit
DC: Domain Component

Configuring PostgreSQL for LDAP Authentication

Step 1: Create Roles in PostgreSQL

Roles in PostgreSQL can represent both individual users and groups from OU ldap prefix . Here’s how to create the necessary roles:

CREATE ROLE "ITUUSERS";
CREATE ROLE "11-CAMPUS" IN ROLE "ITUUSERS";
CREATE ROLE "12-FACULTY" IN ROLE "11-CAMPUS";
CREATE ROLE "13-DEPARTMANT" IN ROLE "12-FACULTY";
CREATE ROLE "14-CLASS" IN ROLE "13-DEPARTMANT";
CREATE ROLE "kemal.oz" IN ROLE "14-CLASS";
ALTER USER "kemal.oz" LOGIN;

Step 2: Edit the pg_hba.conf File

The pg_hba.conf file is where you define client authentication. Configure it for LDAP as follows:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             10.102.120.0/32         ldap        ldapurl="ldaps://ldap.itu.gov.tr:636" ldapbasedn="OU=ITUUSERS,DC=ITU,DC=gov,DC=tr" ldapbinddn="CN=User1,CN=Users,DC=ITU,DC=gov,DC=tr" ldapbindpasswd="test123" ldapsearchattribute="sAMAccountName"
host    all             all             10.102.120.0/32         ldap         ldapserver="ldap.itu.gov.tr"     ldapport=636     ldapscheme="ldaps"     ldapbasedn="OU=ITUUSERS,dc=itu,dc=gov,dc=tr"     ldapbinddn="CN=User 000001,OU=SERVICE ACCOUNTS,OU=Information Technologies,OU=MED Bınası,OU=BIRIMLER,OU=IT Domain,DC=ibb,DC=gov,DC=tr"     ldapbindpasswd="test123"     ldapsearchattribute="sAMAccountName"
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

Step 3: Reload Postgres Config

After editing the configuration file, reload configuration file to apply the changes:

pg_ctl reload

SELECT pg_reload_conf();

Detailed Explanation

ldapurl: The URL for the LDAP server, using LDAPS for secure communication.

ldaps://ldap.itu.gov.tr:636

ldabasedn: The base DN for LDAP searches.

ldapsearch -v -x -H ldaps://ldap.***.gov.tr:636 -D "User1@***.gov.tr" -W -b "dc=ibb,dc=gov,dc=tr" "(sAMAccountName=User1)"
Enter LDAP Password:



filter: (sAMAccountName=user1)
requesting: All userApplication attributes
# extended LDIF
#
# LDAPv3
# More information ....

or 


ldapsearch -x -H "laps://ldap.***.gov.tr:636" -D "User1@***.gov.tr" -W -b "DC=ibb,DC=gov,DC=tr" "(objectClass=*)" > a.txt

ldapbinddn: The DN of the user PostgreSQL will bind as.

CN=User1,CN=Users,DC=ITU,DC=gov,DC=tr

ldapbindpasswd: The password for the bind DN.

test123

ldapsearchattribute: The LDAP attribute used to search for the username.

sAMAccountName

Conclusion

Integrating PostgreSQL with Active Directory for LDAP authentication enhances security and centralizes user management. By following the steps outlined in this guide, you can set up a robust authentication mechanism for your PostgreSQL databases. This integration not only simplifies user management but also leverages the existing infrastructure of your enterprise’s Active Directory.

For more detailed and technical articles like this, keep following our blog on Medium. If you have any questions or need further assistance, feel free to reach out in the comments below and directly.