trio.so
How-Tos

How to Connect PostgreSQL to Active Directory (LDAP and Kerberos)

Authenticate PostgreSQL users against Active Directory with LDAP or Kerberos (GSSAPI): pg_hba.conf examples, testing and common errors.

Mountain landscape representing leadership perspective and vision
Written by
Atena Ladchartabi

Content writer, trio.so

Published on
Modified on

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.

Which method to choose

 LDAP simple bindLDAP search+bindGSSAPI (Kerberos)
User types a passwordYesYesNo, uses the Kerberos ticket
Needs a service accountNoYes, for the lookupYes, for the service principal and keytab
Login nameMust map directly to the AD nameAny attribute, such as sAMAccountNameKerberos principal
Setup effortLowestLowHighest

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.

LDAP simple bind

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.

LDAP search+bind

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.
  • To allow only members of a group, use ldapsearchfilter with a filter on memberOf instead of ldapsearchattribute.
  • Give the service account read access only, and keep 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.

GSSAPI (Kerberos) for single sign-on

  1. Create a service account in Active Directory for the database server. The 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.
  2. Create a keytab on a domain controller and copy it to the database server:
    ktpass -princ postgres/[email protected] -mapuser EXAMPLE\svc-postgres -pass * -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPAL -out postgres.keytab
  3. Point PostgreSQL at the keytab in postgresql.conf, and make the file readable by the postgres user only:
    krb_server_keyfile = '/etc/postgresql/postgres.keytab'
  4. Add the rule to pg_hba.conf:
    hostgssenc  all  all  10.0.0.0/8  gss include_realm=0 krb_realm=EXAMPLE.COM

include_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.

Create the roles

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.

Reload and test

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.

Common errors

  • "LDAP authentication failed for user": wrong password, wrong bind name, or the user is outside ldapbasedn. The server log has the detail.
  • "could not perform initial LDAP bind": the search+bind service account or its password is wrong.
  • "could not start LDAP TLS session": the server does not trust the domain controller's certificate. Install your CA certificate on the database server.
  • "role does not exist": authentication worked; create the role.
  • Kerberos "Server not found in Kerberos database": the client used a host name that does not match the service principal. Connect with the exact name in the SPN.
  • Kerberos failures after a while: check clock skew between the server, the client and the domain controller, and that the keytab matches the current service account password.

Related reading: Active Directory migration and Active Directory Certificate Services.

Ready-to-use Templates

Must-have Template Toolkit for IT Admins

Explore All
Template Toolkit

Start your free trial

No credit card required
Full access to all features

Manage Every Device From One Console

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.

14-day free trial. No credit card required.

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.

Smiling womanAbstract geometric patternAbstract geometric patternSmiling womanSmiling woman

Frequently Asked Questions (FAQ)

Have questions? We've got answers. This section covers some of the most commonly asked questions related to this topic.

Yes, with the ldap method or the gss (Kerberos) method in pg_hba.conf. The role must still exist in PostgreSQL.

Simple bind builds the user's bind name from a prefix and suffix and binds directly. Search+bind first looks the user up with a service account, so users can log in with any attribute, such as sAMAccountName.

Register a service principal in Active Directory, create a keytab, set krb_server_keyfile in postgresql.conf, and add a gss line to pg_hba.conf.

Related

From the blog

The related industry news, interviews, technologies, and resources.