Integrating PostgreSQL with a Centralized LDAP Directory

Series: Centralized DB Authentication in the Wild | Part 3 of 9

Introduction

• In Part 2, we built a centralized OpenLDAP directory containing users, service accounts and groups. The directory is now ready to authenticate enterprise users, but PostgreSQL still relies on local authentication.
• In this article, we'll integrate PostgreSQL with the LDAP directory and configure it to authenticate users centrally. PostgreSQL continues to manage authorization through its native roles and privileges, keeping authentication and authorization as separate responsibilities.
• We'll also see why successful LDAP authentication alone is not enough to grant database access and build a complete solution that automates role management.

Note

• PostgreSQL 18.4 runs in Docker on db1.dbacraft.com. The OpenLDAP directory created in Part 2 runs on iam.dbacraft.com.
• Always test authentication changes in a non-production environment before applying them to a live system.

Lab environment

Component	Host	Detail
OpenLDAP	iam.dbacraft.com	osixia/openldap:1.5.0, port 389
PostgreSQL	db1.dbacraft.com	postgres:18, port 5432, container: dbacraft_pg_5432
ldap2pg	db1.dbacraft.com	v6.5.1, installed in /opt/auth/ldap2pg/
Network	db1.dbacraft.com	Update pg_hba.conf to match your network topology

Step 1: Deploy PostgreSQL

For this demo, I prefer creating postgres on docker container which is lightweight.

# Run on: db1.dbacraft.com
version: "3.8"
services:
  postgres:
    image: postgres:18
    container_name: dbacraft_pg_5432
    hostname: db1.dbacraft.com
    restart: unless-stopped
    environment:
      POSTGRES_PASSWORD: "********"
      POSTGRES_DB: lab
    ports:
      - "5432:5432"
    volumes:
      - /data/pg/pgdata:/var/lib/postgresql
      - /data/pg/conf/postgresql.conf:/etc/postgresql-custom/postgresql.conf
      - /data/pg/conf/pg_hba.conf:/etc/postgresql-custom/pg_hba.conf
      - /log/pg:/var/log/postgresql
      - /audit/pg:/var/log/postgresql-audit
    command: ["postgres", "-c", "config_file=/etc/postgresql-custom/postgresql.conf"]
    logging:
      driver: json-file
      options:
        max-size: "5m"
        max-file: "2"

root@db1:/script/pg# docker compose up -d

root@db1:/script/pg# docker ps -a | grep dbacraft_pg_5432
320e460d6dea   postgres:18   "docker-entrypoint.s…"   2 weeks ago   Up 2 minutes   0.0.0.0:5432->5432/tcp   dbacraft_pg_5432

Step 2: Configure postgresql.conf

Update the main pg settings to startup the db and validate connections.

# Path: /data/pg/conf/postgresql.conf
# Run on: db1.dbacraft.com
listen_addresses = '*'
port = 5432

logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d.log'
log_destination = 'stderr'
log_rotation_age = 1d
log_rotation_size = 100MB
log_min_duration_statement = 0
log_connections = on
log_disconnections = on

password_encryption = scram-sha-256
hba_file = '/etc/postgresql-custom/pg_hba.conf'

Step 3: Configure pg_hba.conf for LDAP authentication

PostgreSQL uses pg_hba.conf to determine how clients authenticate. Rules are evaluated from top to bottom and the first matching rule is applied.

# Path: /data/pg/conf/pg_hba.conf
local   all       all                        trust
host    all       postgres  127.0.0.1/32     trust

host    all       all  YOUR_NETWORK/MASK  ldap \
  ldapserver=iam.dbacraft.com \
  ldapport=389 \
  ldapbasedn="ou=people,dc=dbacraft,dc=com" \
  ldapbinddn="uid=svc_ldap_bind_ro,ou=people,dc=dbacraft,dc=com" \
  ldapbindpasswd="********" \
  ldapsearchattribute="uid"

host    all       postgres  172.16.0.0/12    trust

The LDAP rule uses the svc_ldap_bind_ro account created in Part 2. This account only searches the directory and should have ldap directory read-only permissions.

Parameter	Value	Purpose
ldapserver	iam.dbacraft.com	LDAP server hostname
ldapport	389	Standard LDAP port, unencrypted.
ldapbasedn	ou=people,dc=dbacraft,dc=com	Location of user accounts
ldapbinddn	uid=svc_ldap_bind_ro	Read-only bind account
ldapbindpasswd	********	Password for the bind account
ldapsearchattribute	uid	LDAP attribute matched to the PostgreSQL login

Reload the configuration after updating pg_hba.conf.

root@db1:~# docker exec dbacraft_pg_5432 \
  psql -U postgres -c "SELECT pg_reload_conf();"

Step 4: Verify LDAP authentication

Connect using an LDAP user.

root@db1:~# psql -h db1.dbacraft.com -U user_ahmad_dba -d postgres
Password for user user_ahmad_dba:
psql (16.14 (Ubuntu 16.14-0ubuntu0.24.04.1), server 18.4 (Debian 18.4-1.pgdg13+1))
WARNING: psql major version 16, server major version 18.
         Some psql features might not work.
Type "help" for help.

postgres=> \conninfo
You are connected to database "postgres" as user "user_ahmad_dba" on host "db1.dbacraft.com" at port "5432".
postgres=> SELECT current_user, session_user;
  current_user  |  session_user
----------------+----------------
 user_ahmad_dba | user_ahmad_dba
(1 row)

Authentication succeeds. The session is open. Now check the user's privileges.

postgres=> SELECT table_schema, table_name, privilege_type
           FROM information_schema.role_table_grants
           WHERE grantee = current_user
           LIMIT 10;
 table_schema | table_name | privilege_type
--------------+------------+----------------
(0 rows)

The user authenticated successfully, but has no permissions inside PostgreSQL.

The authentication gap

Authentication and authorization are separate. PostgreSQL validates the user's identity through LDAP, but it still requires local roles and privileges before the user can access database objects.

This is the gap we'll solve in the next step using ldap2pg.

Step 5: Why ldap2pg is needed

• LDAP authentication verifies a user's identity, but it does not create or manage PostgreSQL roles. Without those roles, authenticated users still have no permissions inside the database.
• One option is to manage roles manually. Every time a user joins, changes teams or leaves the organization, PostgreSQL roles and memberships must be updated by hand. As the number of users and databases grows, that process quickly becomes difficult to maintain.
• ldap2pg solves this by synchronizing LDAP users and groups with PostgreSQL roles. It compares the directory with the current PostgreSQL role state, creates missing roles, removes obsolete ones and updates group memberships. LDAP becomes the source of truth, while PostgreSQL automatically reflects those changes.

Without ldap2pg	With ldap2pg
New LDAP user requires manual role creation	Roles are created automatically during the next synchronization
User removal requires manual cleanup	Roles and memberships are updated automatically
Role assignments gradually drift from LDAP	PostgreSQL remains synchronized with LDAP
Access changes depend on manual intervention	Access changes are applied automatically after synchronization

This approach keeps authentication centralized in LDAP while allowing PostgreSQL to continue managing authorization through its native role system.

Step 6: Create the parent group roles in PostgreSQL

ldap2pg creates individual PostgreSQL user roles from LDAP users and assigns them to PostgreSQL group roles. These parent group roles must exist before the first synchronization because they are excluded from ldap2pg management using the roles_blacklist_query setting.

Create the parent roles once and grant privileges to them rather than to individual users.

CREATE ROLE "db-admins"        NOLOGIN;
CREATE ROLE "developers"       NOLOGIN;
CREATE ROLE "bi-users"         NOLOGIN;
CREATE ROLE "service-accounts"  NOLOGIN;

-- Example: allow DBAs to access monitoring views
GRANT pg_monitor TO "db-admins";

These roles do not allow direct logins. They act as permission containers. When ldap2pg synchronizes the directory, it creates user roles from LDAP and grants them membership in the appropriate parent role, allowing users to inherit privileges through PostgreSQL role inheritance.

Step 7: Configure ldap2pg

ldap2pg consists of two configuration files.

• sync.sh connects to LDAP and PostgreSQL and runs the synchronization.
• ldap2pg.yml defines how LDAP groups map to PostgreSQL roles.

sync.sh

#!/usr/bin/env bash
# Run on: db1.dbacraft.com
# Path: /opt/auth/ldap2pg/sync.sh
# Usage:
#   ./sync.sh           -> dry run (default, safe)
#   ./sync.sh --real    -> applies changes
set -euo pipefail

export LDAPURI="ldap://iam.dbacraft.com:389"
export LDAPBINDDN="uid=svc_ldap_bind_ro,ou=people,dc=dbacraft,dc=com"
export LDAPPASSWORD="********"

PG_DSN="postgresql://postgres@127.0.0.1:5432/authlab?sslmode=disable"

cd /opt/auth/ldap2pg
ldap2pg -c ldap2pg.yml "$PG_DSN" "$@" -vv

Running the script without --real performs a dry run. No changes are made, making it safe to validate the configuration before applying it.

ldap2pg.yml

# Path: /opt/auth/ldap2pg/ldap2pg.yml
version: 6

ldap:
  uri: ldap://iam.dbacraft.com:389
  bind_dn: uid=svc_ldap_bind_ro,ou=people,dc=dbacraft,dc=com
  password: "********"

postgres:
  dsn: "postgresql://postgres@127.0.0.1:5432/authlab?sslmode=disable"
  roles_blacklist_query:
    - postgres
    - "pg_*"
    - "db-admins"
    - "server-admins"
    - "developers"
    - "bi-users"
    - "service-accounts"

rules:
  - description: "Sync db-admins group from LDAP"
    ldapsearch:
      base: "cn=db-admins,ou=groups,dc=dbacraft,dc=com"
    role:
      name: "{member.uid}"
      options: LOGIN
      parent: "db-admins"

  - description: "Sync developers group from LDAP"
    ldapsearch:
      base: "cn=developers,ou=groups,dc=dbacraft,dc=com"
    role:
      name: "{member.uid}"
      options: LOGIN
      parent: "developers"

  - description: "Sync bi-users group from LDAP"
    ldapsearch:
      base: "cn=bi-users,ou=groups,dc=dbacraft,dc=com"
    role:
      name: "{member.uid}"
      options: LOGIN
      parent: "bi-users"

  - description: "Sync service-accounts group from LDAP"
    ldapsearch:
      base: "cn=service-accounts,ou=groups,dc=dbacraft,dc=com"
    role:
      name: "{member.uid}"
      options: LOGIN
      parent: "service-accounts"

Each rule maps an LDAP group to a PostgreSQL group role. User roles are created from the LDAP uid attribute and added to the appropriate parent role.

The roles_blacklist_query prevents ldap2pg from modifying PostgreSQL system roles or the parent group roles.

Step 8: Run the synchronization

Run a dry run first.

root@db1:/opt/auth/ldap2pg# ./sync.sh

Review the proposed changes. If everything looks correct, apply them.

root@db1:/opt/auth/ldap2pg# ./sync.sh --real

A successful run ends with a summary similar to:

08:33:07 INFO   Synchronisation complete.   searches=5 roles=29 queries=6 grants=0 elapsed=140.690396ms

Step 9: Verify roles and group membership

List the PostgreSQL roles.

root@db1:~# psql -h 127.0.0.1 -U postgres -d postgres -c "\du"

                                   List of roles
      Role name       |                         Attributes
----------------------+------------------------------------------------------------
 bi-users             | Cannot login
 db-admins            | Cannot login
 developers           | Cannot login
 postgres             | Superuser, Create role, Create DB, Replication, Bypass RLS
 service-accounts     | Cannot login
 svc_analytics_ro     |
 svc_inventory_rw     |
 user_ahmad_dba       |
 user_aisyah_sysadmin |
 user_anita_bi        |
 user_arif_dev        |

Confirm group membership is correctly assigned.

root@db1:~# psql -h 127.0.0.1 -U postgres -d postgres -c "
SELECT r.rolname AS member, g.rolname AS group_role
FROM pg_roles r
JOIN pg_auth_members m ON r.oid = m.member
JOIN pg_roles g ON m.roleid = g.oid
WHERE g.rolname NOT LIKE 'pg_%'
ORDER BY g.rolname, r.rolname;"

        member        |    group_role
----------------------+------------------
 user_anita_bi        | bi-users
 user_chong_bi        | bi-users
 user_faiz_bi         | bi-users
 user_preeti_bi       | bi-users
 user_zainab_bi       | bi-users
 user_ahmad_dba       | db-admins
 user_hafiz_dba       | db-admins
 user_priya_dba       | db-admins
 user_siti_dba        | db-admins
 user_wei_dba         | db-admins
 user_arif_dev        | developers
 user_daniel_dev      | developers
 user_lily_dev        | developers
 user_mei_dev         | developers
 user_ravi_dev        | developers
 svc_analytics_ro     | service-accounts
 svc_auth_ro          | service-accounts
 svc_billing_ro       | service-accounts
 svc_catalog_ro       | service-accounts
 svc_inventory_rw     | service-accounts
 svc_notifications_ro | service-accounts
 svc_orders_ro        | service-accounts
 svc_payments_ro      | service-accounts
 svc_reporting_ro     | service-accounts
 svc_search_rw        | service-accounts
(30 rows)

Step 10: Verify the complete lifecycle

Scenario 1: Offboarding

Remove user_hafiz_dba from the db-admins group in LDAP.

root@iam:/opt/auth/ldap# ldapmodify -x -H ldap://localhost:389 \
  -D "cn=admin,dc=dbacraft,dc=com" -W << 'EOF'
dn: cn=db-admins,ou=groups,dc=dbacraft,dc=com
changetype: modify
delete: member
member: uid=user_hafiz_dba,ou=people,dc=dbacraft,dc=com
EOF
Enter LDAP Password:
modifying entry "cn=db-admins,ou=groups,dc=dbacraft,dc=com"

Synchronize PostgreSQL with LDAP.

root@db1:/opt/auth/ldap2pg# ./sync.sh --real

08:33:07 CHANGE Terminate running sessions.      role=user_hafiz_dba database=postgres
08:33:07 CHANGE Reassign objects and purge ACL.  role=user_hafiz_dba owner=postgres database=postgres
08:33:07 CHANGE Reassign objects and purge ACL.  role=user_hafiz_dba owner=postgres database=template1
08:33:07 CHANGE Reassign objects and purge ACL.  role=user_hafiz_dba owner=postgres database=authlab
08:33:07 CHANGE Drop LOGIN.                      role=user_hafiz_dba database=authlab
08:33:07 CHANGE Drop role.                       role=user_hafiz_dba database=authlab
08:33:07 INFO   Synchronisation complete.        searches=5 roles=29 queries=6 grants=0 elapsed=140ms

ldap2pg detects that the user is no longer a member of the LDAP group. Before removing the role, it safely terminates active sessions, reassigns any owned objects, cleans up privileges and then drops the PostgreSQL role.

Attempt to connect using same account.

root@db1:~# psql -h db1.dbacraft.com -U user_hafiz_dba -d postgres
Password for user user_hafiz_dba:
psql: error: connection to server at "db1.dbacraft.com", port 5432 failed: FATAL:  role "user_hafiz_dba" does not exist

Confirm the role is gone from PostgreSQL.

root@db1:~# psql -h 127.0.0.1 -U postgres -d postgres -c \
  "SELECT rolname FROM pg_roles WHERE rolname = 'user_hafiz_dba';"
 rolname
---------
(0 rows)

Production considerations

Consideration	Detail
Bind account credentials	Store bind account credentials in a secrets manager or use another secure method in production.
Transport security	This demo uses port 389 without encryption. In production use LDAPS on port 636 or StartTLS. Credentials passed over plain LDAP are visible on the wire protocol.
Synchronization schedule	Run sync.sh --real on a schedule using cron or a systemd timer. The synchronization interval determines how quickly onboarding and offboarding changes take effect.
Privilege management	ldap2pg creates user roles and manages role membership. Database privileges remain the responsibility of PostgreSQL and should be granted to the parent group roles.
LDAP HA	If LDAP is unavailable, new LDAP-authenticated connections will fail. Existing database sessions continue unaffected. Keep a local postgres account as a break-glass administrative path and implement LDAP HA to avoid a single point of failure.
Role removal	Before dropping a role, ldap2pg terminates active sessions, reassigns owned objects and cleans up privileges. Review this behavior carefully before using automatic synchronization in production.

Wrapping Up

• LDAP now manages authentication, while PostgreSQL continues to manage authorization through its native roles and privileges. By introducing ldap2pg, PostgreSQL roles are synchronized automatically from LDAP group membership, turning onboarding and offboarding into a simple directory update instead of manual role administration.
• Source files are available on GitHub.

What is next

In Part 4, we'll integrate MongoDB Enterprise with the same OpenLDAP directory. Although MongoDB supports LDAP authentication natively (supported in v8 still but deprecated), its authentication and role mapping model differs significantly from PostgreSQL. We'll configure the integration and examine the differences between the two platforms.

Credits and References

• osixia:openldap Docker container
• ldap2pg by Dimitri Fontaine and contributors

Series: Centralized DB Authentication in the Wild

• Part 1: Why Local Database Authentication Creates Operational Chaos
• Part 2: Building an Enterprise LDAP Directory with OpenLDAP
• Part 3: Integrating PostgreSQL with a Centralized LDAP Directory
• Part 4: MongoDB Enterprise LDAP Integration (coming soon)
• Part 5: MySQL Enterprise LDAP Integration (coming soon)
• Part 6: MariaDB LDAP Authentication using PAM and POSIX Groups (coming soon)
• Part 7: Why LDAP Is Not Enough - Introducing Kerberos (coming soon)
• Part 8: Certificate-Based Authentication Across PostgreSQL, MySQL, MariaDB and MongoDB (coming soon)
• Part 9: Production Best Practices and Cross Platform Comparison (coming soon)