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.
- Source files for this article are available on GitHub.
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 |
|
||
| Network | db1.dbacraft.com | Update pg_hba.conf to match your network topology |
Step 1: Deploy PostgreSQL
PostgreSQL runs on db1.dbacraft.com in a Docker container. The configuration files(pg_hba.conf and postgresql.conf) are mounted from the host, allowing changes without rebuilding the container.
# Path: /script/pg/docker-compose.yml # 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 key 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 read-only permissions.
| Parameter | Value | Purpose | ||
|---|---|---|---|---|
| ldapserver | iam.dbacraft.com |
|
||
| ldapport | 389 | Standard LDAP port, unencrypted. | ||
| ldapbasedn | ou=people,dc=dbacraft,dc=com | Location of user accounts | ||
| ldapbinddn | uid=svc_ldap_bind_ro |
|
||
| 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:
LDAP answers one question:
Who are you?
PostgreSQL answers another:
What are you allowed to do?
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 "server-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 server-admins group from LDAP" ldapsearch: base: "cn=server-admins,ou=groups,dc=dbacraft,dc=com" role: name: "{member.uid}" options: LOGIN parent: "server-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 reads a specific LDAP group, creates a PostgreSQL role for every member using their uid as the role name, and grants membership in the parent group role. The roles_blacklist_query prevents ldap2pg from touching the postgres superuser, any built-in pg_* roles, or the group roles themselves.
Step 8: Run the synchronization
Always run the dry run first. It shows exactly what ldap2pg will create or remove without making any changes.
root@db1:/opt/auth/ldap2pg# ./sync.sh
The dry run output shows ldap2pg connecting to both LDAP and PostgreSQL, reading each group, and reporting what it wants the state to look like. The key lines are the Wants role entries, one per LDAP group member.
{{IMAGE_4}}Once the output looks correct, apply the changes.
root@db1:/opt/auth/ldap2pg# ./sync.sh --real
The real run summary line confirms what happened.
08:33:07 INFO Synchronisation complete. searches=5 roles=29 queries=6 grants=0 elapsed=140.690396ms
Step 9: Verify roles and group membership
Check that all roles were created and assigned to the correct parent groups.
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
server-admins | Cannot login
service-accounts | Cannot login
svc_analytics_ro |
svc_auth_ro |
svc_billing_ro |
svc_catalog_ro |
svc_inventory_rw |
svc_notifications_ro |
svc_orders_ro |
svc_payments_ro |
svc_reporting_ro |
svc_search_rw |
user_ahmad_dba |
user_aisyah_sysadmin |
user_anita_bi |
user_arif_dev |
user_chong_bi |
user_daniel_dev |
user_faiz_bi |
user_farah_sysadmin |
user_hafiz_dba |
user_jason_sysadmin |
user_kumar_sysadmin |
user_lily_dev |
user_mei_dev |
user_nadia_sysadmin |
user_preeti_bi |
user_priya_dba |
user_ravi_dev |
user_siti_dba |
user_wei_dba |
user_zainab_bi |
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
user_aisyah_sysadmin | server-admins
user_farah_sysadmin | server-admins
user_jason_sysadmin | server-admins
user_kumar_sysadmin | server-admins
user_nadia_sysadmin | server-admins
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)
{{IMAGE_5}}
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"
Run ldap2pg on db1. The sync detects that user_hafiz_dba is no longer in any LDAP group and drops the role. Notice the sequence: it terminates active sessions first, reassigns any owned objects to postgres, then drops the role cleanly.
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
Attempt to log in as user_hafiz_dba.
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)
The user's LDAP account still exists. The person can still authenticate to other systems that use LDAP. What changed is PostgreSQL access only, and it happened automatically the moment the group membership changed.
{{IMAGE_6}}Production considerations
| Consideration | Detail |
|---|---|
| Bind account credentials | ldapbindpasswd in pg_hba.conf is in plain text. Move it to a secrets manager in production. The same applies to the password in ldap2pg.yml. |
| TLS | This lab uses port 389 without encryption. In production use LDAPS on port 636 or StartTLS. Credentials passed over plain LDAP are visible on the wire. Part 8 covers this. |
| ldap2pg scheduling | Schedule sync.sh --real via cron or a systemd timer. Every 15 minutes is reasonable for most environments. Access revocation after an offboarding event is only as fast as this interval. |
| Group role privileges | ldap2pg creates user roles and assigns group membership. Schema-level GRANT statements on the group roles are still managed inside PostgreSQL separately. |
| LDAP availability | If LDAP is unreachable, new connections covered by the LDAP rule will fail authentication. Existing sessions are not affected. Keep a local trust rule for the postgres superuser on loopback as the break-glass path. |
| ldap2pg drop behaviour | When ldap2pg drops a role it terminates active sessions, reassigns owned objects to postgres and purges ACLs before dropping. This is the correct sequence but review it carefully before running in production for the first time. |
Wrapping Up
- LDAP now owns authentication. PostgreSQL no longer maintains local passwords for users on the internal network. The directory is the single source of truth for who can attempt a connection.
- PostgreSQL continues to own authorization. LDAP confirms identity. What a user can actually do inside the database is still controlled by PostgreSQL roles and GRANT statements.
- ldap2pg bridges the two. It reads LDAP group membership and creates or drops the corresponding PostgreSQL roles automatically. Onboarding and offboarding are now a group membership change followed by a sync run, not a series of manual role commands repeated across multiple databases.
- Source files are available on GitHub.
What is next
PostgreSQL is now connected to the centralized directory. The next database to integrate is MongoDB Enterprise. MongoDB supports LDAP natively but the configuration model is different from PostgreSQL. The group-to-role mapping works differently, and there are a few behaviors that will catch you off guard if you assume it works the same way as pg_hba.conf.
Part 4 connects MongoDB Enterprise to the same OpenLDAP directory.
Credits
- PostgreSQL Global Development Group
- OpenLDAP Project
- ldap2pg by Dimitri Fontaine and contributors
- Docker
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 (this post)
- 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)
No comments:
Post a Comment