
Learn the key differences between SAML and SCIM, two essential identity management technologies, and how they provide seamless authentication.
Authenticate PostgreSQL users against Active Directory with LDAP or Kerberos (GSSAPI): pg_hba.conf examples, testing and common errors.


Content writer, trio.so
PostgreSQL can check passwords against Active Directory in two ways, both set in pg_hba.conf. LDAP sends the user's password to a domain controller to verify it, either directly (simple bind) or after looking the user up with a service account (search+bind). GSSAPI uses Kerberos, so users who are already signed in to the domain connect without typing a password. In both cases the database role must still exist in PostgreSQL: Active Directory only checks who the user is.
| LDAP simple bind | LDAP search+bind | GSSAPI (Kerberos) | |
|---|---|---|---|
| User types a password | Yes | Yes | No, uses the Kerberos ticket |
| Needs a service account | No | Yes, for the lookup | Yes, for the service principal and keytab |
| Login name | Must map directly to the AD name | Any attribute, such as sAMAccountName | Kerberos principal |
| Setup effort | Lowest | Low | Highest |
Use simple bind for a quick setup, search+bind when users log in with their short name, and GSSAPI when you want single sign-on from domain-joined machines.
PostgreSQL builds the bind name from a prefix, the login name and a suffix, then binds as that user with the password. With Active Directory, the user principal name works well:
# pg_hba.conf
hostssl all all 10.0.0.0/8 ldap ldapserver=dc1.example.com ldapscheme=ldaps ldapprefix="" ldapsuffix="@example.com"
User alice is then checked as [email protected]. ldapscheme=ldaps encrypts the connection to the domain controller; without it, the password crosses the network in clear text. Use ldaptls=1 instead if your domain controllers use StartTLS on port 389.
PostgreSQL first binds with a service account, searches for the user, then binds as the user it found:
# pg_hba.conf
hostssl all all 10.0.0.0/8 ldap ldapserver=dc1.example.com ldapscheme=ldaps ldapbasedn="OU=Staff,DC=example,DC=com" ldapbinddn="CN=svc-postgres,OU=Service Accounts,DC=example,DC=com" ldapbindpasswd="change-me" ldapsearchattribute=sAMAccountName
ldapbasedn is where the search starts.ldapsearchattribute is the attribute that holds the login name. For Active Directory, sAMAccountName matches the short user name.ldapsearchfilter with a filter on memberOf instead of ldapsearchattribute.pg_hba.conf readable by the postgres user alone, because the password is stored there.The simple bind options (prefix and suffix) and the search+bind options cannot be mixed on one line.
ktpass command in the next step maps the service principal name postgres/db.example.com to it, so you do not register the SPN separately.ktpass -princ postgres/[email protected] -mapuser EXAMPLE\svc-postgres -pass * -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPAL -out postgres.keytabpostgresql.conf, and make the file readable by the postgres user only:
krb_server_keyfile = '/etc/postgresql/postgres.keytab'pg_hba.conf:
hostgssenc all all 10.0.0.0/8 gss include_realm=0 krb_realm=EXAMPLE.COMinclude_realm=0 strips @EXAMPLE.COM, so the principal [email protected] logs in as the role alice. For other mappings, use a map= option and pg_ident.conf. hostgssenc (PostgreSQL 12 and later) also requires GSSAPI encryption for the connection.
Every method needs a matching role in PostgreSQL:
CREATE ROLE alice LOGIN;
GRANT app_readonly TO alice;
Do not give these roles a PostgreSQL password; Active Directory checks it. To keep roles in step with AD groups, use a sync tool or script; PostgreSQL does not create roles from LDAP by itself.
After editing pg_hba.conf, reload the configuration in psql:
SELECT pg_reload_conf();
Then test from a client in a shell:
# LDAP test
psql "host=db.example.com dbname=postgres user=alice sslmode=require"
# Kerberos test from a domain machine
kinit [email protected]
psql "host=db.example.com dbname=postgres user=alice"
pg_hba.conf is read top to bottom and the first matching line wins, so put the AD rules above any broader rule that would match first.
ldapbasedn. The server log has the detail.Related reading: Active Directory migration and Active Directory Certificate Services.
trio.so brings device management, patching and security policies for Windows, macOS, Linux, iOS/iPadOS and Android into one console. Enroll a device and see it in the console within minutes.
trio.so brings device management, patching and security policies for Windows, macOS, Linux, iOS/iPadOS and Android into one console. Enroll a device and see it in the console within minutes.





Have questions? We've got answers. This section covers some of the most commonly asked questions related to this topic.
Related
The related industry news, interviews, technologies, and resources.

Learn the key differences between SAML and SCIM, two essential identity management technologies, and how they provide seamless authentication.

Learn how SCIM security enhances identity management with encryption, authentication, and access control.

Explore credential management software for IT managers. Learn key features, benefits, and how to choose the right solution for your organization.

LDAP vs Active Directory: how the protocol and the directory service differ, how SAML fits in, and which suits your organization.

Unlock enhanced security and efficiency with Role-Based Access Control implementation, ensuring access control and streamlined operations.

User provisioning and deprovisioning explained: joiner, mover and leaver steps, SCIM vs directory sync, and how to automate accounts from your directory.

This blog explores the benefits of integrating SCIM (System for Cross-domain Identity Management) Device Management solutions.

Many organizations throughout different industries use LDAP. But how does LDAP work and what are its benefits for your organization?
14-day free trial. No credit card required.
trio.so for MSPs: one console, separate clients