PostgreSQL Kerberos SSO with Active Directory

By Tahir Baloch | Information Technology Dynamics Inc. (ITDI)

Kerberos single sign-on lets users connect to PostgreSQL using their enterprise identity without entering a separate database password.

This guide walks through configuring PostgreSQL on Linux with Microsoft Active Directory, including service principal registration, keytab generation, user mapping, client testing and troubleshooting.

The guide is based on my implementation experience. All organization-specific names, server details, accounts and network addresses have been replaced with fictional examples.

Example environment

Component	Example value
PostgreSQL server	pg-db01.example.com
Kerberos realm	EXAMPLE.COM
Domain controllers	dc01.example.com and dc02.example.com
Linux database service account	enterprisedb
AD service account	svc_pg_db01
Service principal	postgres/pg-db01.example.com@EXAMPLE.COM
Database	appdb
Port	5444
Keytab location	/var/lib/edb/postgres.keytab
Example user	alex.reader

Community PostgreSQL installations commonly use the postgres operating system account, port 5432 and different configuration paths.

---

1 Install Kerberos and time synchronization tools

Run these commands on the Linux PostgreSQL server with the required operating system privileges.

# SYSTEM CHANGE
sudo dnf install -y krb5-workstation krb5-libs chrony
sudo systemctl enable --now chronyd

Verify the installed packages, time synchronization and hostname resolution.

# READ-ONLY
rpm -q krb5-workstation krb5-libs chrony
chronyc tracking
chronyc sources -v
hostname -f
getent hosts pg-db01.example.com dc01.example.com dc02.example.com

2 Configure the Linux Kerberos client

Edit /etc/krb5.conf on the PostgreSQL server. Active Directory provides the Kerberos KDC in this example. Merge these settings with the existing configuration, retaining the distribution’s crypto-policy settings.

# CONFIGURATION CHANGE
includedir /etc/krb5.conf.d/

[logging]
    default = FILE:/var/log/krb5libs.log
    kdc = FILE:/var/log/krb5kdc.log
    admin_server = FILE:/var/log/kadmind.log

[libdefaults]
    default_realm = EXAMPLE.COM
    dns_lookup_realm = false
    dns_lookup_kdc = true
    ticket_lifetime = 24h
    renew_lifetime = 7d
    forwardable = true
    rdns = false
    pkinit_anchors = FILE:/etc/pki/tls/certs/ca-bundle.crt
    spake_preauth_groups = edwards25519
    dns_canonicalize_hostname = fallback
    qualify_shortname = ""
    default_ccache_name = KEYRING:persistent:%{uid}
    udp_preference_limit = 0

[realms]
    EXAMPLE.COM = {
        kdc = dc01.example.com
        kdc = dc02.example.com
        admin_server = dc01.example.com
    }

[domain_realm]
    .example.com = EXAMPLE.COM
    example.com = EXAMPLE.COM

Ticket lifetimes, credential-cache settings and forwarding should follow your organization’s policy. Forwardable tickets are not required for a direct client connection to PostgreSQL.

3 Create the AD service account and register the SPN

Ask the Active Directory administrator to create a dedicated service account named svc_pg_db01. Use this same account for SPN registration and keytab generation. Run the following commands in an elevated Windows command prompt with the required AD permissions.

REM READ-ONLY
setspn -Q postgres/pg-db01.example.com
setspn -L EXAMPLE\svc_pg_db01

If the SPN is absent, register it using the following command. The -S option checks for duplicate SPNs.

REM AD CHANGE
setspn -S postgres/pg-db01.example.com EXAMPLE\svc_pg_db01

If the SPN already belongs to the intended account, no additional registration is needed. If it belongs to another account, resolve ownership with the AD administrator before generating the keytab.

4 Generate the keytab on Windows

The AD administrator generates the keytab using ktpass, mapping the PostgreSQL service principal to the dedicated AD account. Run this command in an elevated Windows command prompt. The caret characters provide line continuation in Command Prompt, not PowerShell.

REM AD CHANGE AND KEYTAB GENERATION
ktpass /out C:\Temp\postgres.keytab ^
       /princ postgres/pg-db01.example.com@EXAMPLE.COM ^
       /mapuser EXAMPLE\svc_pg_db01 ^
       /crypto AES256-SHA1 ^
       /ptype KRB5_NT_PRINCIPAL ^
       /pass *

The /pass * option prompts for the password. Coordinate keytab generation with the AD administrator because ktpass can change account attributes and service keys, potentially invalidating existing keytabs.

Verify the principal spelling and case, encryption type and key version number (KVNO). Do not copy a fixed KVNO from another environment.

Transfer postgres.keytab to the Linux database server through an approved secure channel. The keytab contains credentials and must be protected like a password.

5 Deploy and inspect the keytab on Linux

After securely transferring postgres.keytab to the Linux server, run these commands from the directory containing the file. This example uses the enterprisedb operating system account.

# FILESYSTEM CHANGE
sudo mv postgres.keytab /var/lib/edb/postgres.keytab
sudo chown enterprisedb:enterprisedb /var/lib/edb/postgres.keytab
sudo chmod 600 /var/lib/edb/postgres.keytab

Inspect the keytab as the database service account to verify readability, the principal, key version number and encryption type.

# READ-ONLY
sudo -u enterprisedb klist -kte /var/lib/edb/postgres.keytab

The principal must match postgres/pg-db01.example.com@EXAMPLE.COM exactly. A short hostname and a fully qualified hostname are different principals. The deployment path must also match the krb_server_keyfile setting configured in the next step.

6 Configure PostgreSQL to use the keytab

For this EDB installation, edit /var/lib/edb/as15/data/postgresql.conf and set the keytab location to the same path used in the previous step.

# CONFIGURATION CHANGE
krb_server_keyfile = '/var/lib/edb/postgres.keytab'

Through an authorized DBA connection, check which configuration files the running PostgreSQL instance uses.

-- READ-ONLY
SHOW config_file;
SHOW hba_file;
SHOW ident_file;
SHOW krb_server_keyfile;

SHOW reports the active settings. If the configuration has been edited but not yet applied, the keytab setting may still show its previous value. Configuration reload and validation are covered in a later step.

7 Configure client authentication

Edit /var/lib/edb/as15/data/pg_hba.conf and add a Kerberos authentication rule for the intended database and approved client network. The address below is a documentation example; replace it with your permitted client subnet.

# CONFIGURATION CHANGE
# TYPE  DATABASE  USER  ADDRESS         METHOD  OPTIONS
host    appdb     all   192.0.2.0/24    gss     map=kerberos_map include_realm=1

The gss method authenticates users through Kerberos. Keeping include_realm=1 allows the full authenticated principal to be checked against the mappings configured in the next step.

This rule does not require encrypted database traffic. Where TLS is already configured, use the following alternative to require TLS for matching connections.

# ALTERNATIVE FOR TLS CONNECTIONS
hostssl appdb     all   192.0.2.0/24    gss     map=kerberos_map include_realm=1

Choose the rule that matches your transport configuration. PostgreSQL uses the first matching HBA rule; failed authentication does not fall through to a later password rule. Retain the existing administration, replication, monitoring and application rules required by your environment.

8 Map AD users to PostgreSQL roles

Edit /var/lib/edb/as15/data/pg_ident.conf to map each authenticated Kerberos principal to its PostgreSQL login role. The map name must match kerberos_map in pg_hba.conf.

# CONFIGURATION CHANGE
# MAPNAME       SYSTEM-USERNAME          PG-USERNAME
kerberos_map    alex.reader@EXAMPLE.COM  alex.reader
kerberos_map    sam.analyst@EXAMPLE.COM  sam.analyst

For consistently named accounts, a realm-restricted regular expression can replace individual mappings.

# OPTIONAL ALTERNATIVE TO INDIVIDUAL MAPPINGS
kerberos_map    /^([a-z0-9._-]+)@EXAMPLE\.COM$    \1

This expression maps matching lowercase usernames from EXAMPLE.COM to the same PostgreSQL role name. It does not convert uppercase names to lowercase, create database roles or grant permissions. Check the actual principal presented by the client before choosing the mapping.

9 Create the PostgreSQL login roles

Kerberos authenticates the user, but the corresponding PostgreSQL LOGIN role must already exist. Run the following through an authorized DBA connection for users whose roles have not yet been created.

-- DDL
CREATE ROLE "alex.reader" LOGIN;
CREATE ROLE "sam.analyst" LOGIN;

Grant permission to connect to the example database. Schema and object permissions should be assigned through the appropriate application or reporting roles.

-- DDL / PRIVILEGE CHANGE
GRANT CONNECT ON DATABASE appdb
TO "alex.reader", "sam.analyst";

The AD service account holding PostgreSQL’s Kerberos service keys does not need a PostgreSQL SUPERUSER role for this authentication setup.

10 Apply and validate the configuration

Keep an authorized DBA session open while applying authentication changes. Check the configuration files for parsing errors before reloading.

-- READ-ONLY
SELECT line_number, error
FROM pg_hba_file_rules
WHERE error IS NOT NULL;

SELECT line_number, error
FROM pg_ident_file_mappings
WHERE error IS NOT NULL;

Resolve any reported errors, then reload the EDB service.

# SERVICE CONFIGURATION RELOAD
sudo systemctl reload edb-as-15

# READ-ONLY
sudo systemctl status edb-as-15

Verify the active keytab setting and check whether any relevant settings require a restart.

-- READ-ONLY
SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name IN (
    'krb_server_keyfile',
    'listen_addresses',
    'port'
);

If a changed setting requires a restart, schedule it through the normal operational process. A restart interrupts database connections.

# SERVICE RESTART WHEN REQUIRED
sudo systemctl restart edb-as-15

# READ-ONLY
sudo systemctl status edb-as-15

11 Test Kerberos authentication from clients

On a Linux client, obtain a user ticket and inspect the ticket cache.

# AUTHENTICATION TEST
kinit alex.reader@EXAMPLE.COM
klist

Connect using the server’s fully qualified hostname and the mapped PostgreSQL role.

# CONNECTION TEST
psql "host=pg-db01.example.com port=5444 dbname=appdb user=alex.reader"

On Windows, use a domain user session and a PostgreSQL client supporting integrated authentication. Inspect the Windows ticket cache and request the PostgreSQL service ticket.

REM AUTHENTICATION TEST
klist
klist get postgres/pg-db01.example.com

REM CONNECTION TEST
psql "host=pg-db01.example.com port=5444 dbname=appdb user=alex.reader"

For the TLS-only hostssl alternative, use a trusted CA certificate and require hostname verification. This libpq example disables GSS transport encryption so the connection uses TLS while retaining Kerberos authentication.

psql "host=pg-db01.example.com port=5444 dbname=appdb user=alex.reader gssencmode=disable sslmode=verify-full sslrootcert=/path/to/trusted-ca.crt"

Replace the certificate path for your client operating system. After connecting, confirm the database identity.

-- READ-ONLY
SELECT current_user, session_user, current_database();

A successful connection confirms authentication. Test the intended schema and table permissions separately.

12 Connect from Tableau Desktop

On a Windows client with valid domain credentials, open Tableau Desktop and select the PostgreSQL connector. Use these example connection settings.

Field	Example setting
Server	pg-db01.example.com
Port	5444
Database	appdb
Authentication	Integrated Authentication, where supported by the installed connector and driver
Encryption	Match the PostgreSQL TLS or GSS encryption configuration

The authenticated user must map to an existing PostgreSQL LOGIN role with the required database, schema and object permissions. Connect and verify that the expected authorized objects are visible.

Tableau Server connections and scheduled refreshes require separate service identity and delegation planning. A successful Desktop connection does not confirm that those server workflows are configured.

13 Troubleshoot principal and key version mismatches

If Kerberos authentication fails, inspect the deployed keytab and compare its principal, key version number (KVNO) and encryption type with the requested service ticket.

# READ-ONLY
sudo -u enterprisedb klist -kte /var/lib/edb/postgres.keytab

For example, a keytab might contain this fictional entry:

KVNO  Principal
7     postgres/pg-db01@EXAMPLE.COM

The PostgreSQL log might instead report:

LOG: accepting GSS security context failed
DETAIL: Request ticket server
postgres/pg-db01.example.com@EXAMPLE.COM
not found in keytab (ticket kvno 8)
FATAL: GSSAPI authentication failed for user “alex.reader”

In this example, both the hostname and KVNO differ. Check the following:

1. The client hostname matches the registered service SPN.
2. The SPN belongs to the intended AD account and is unique.
3. The keytab contains the exact requested principal.
4. The keytab contains keys matching the current AD account key version and ticket encryption type.
5. PostgreSQL can read the file specified by krb_server_keyfile.
6. DNS, time synchronization, HBA rules and user mappings are correct.

If the AD account’s keys have changed, coordinate keytab regeneration and deployment with the AD administrator, then obtain fresh client tickets. Changing a KVNO label does not recreate the current secret key.

Reference

• PostgreSQL GSSAPI authentication
• PostgreSQL client authentication rules
• PostgreSQL user name maps
• Microsoft ktpass
• Microsoft setspn
• MIT Kerberos documentation
• MIT Kerberos configuration file — krb5.conf
• Microsoft Kerberos authentication overview