The PostgreSQL database server has a number of methods by which it can authenticate users: trust allows connections unconditionally, password, md5, and crypt require a client to provide some form of password, ident uses the Identification Protocol defined in RFC 1413, pluggable modules (pam), LDAP and Kerberos via GSSAPI.

Many of the environments I get to visit use one of the password-based mechanisms, and the pg_hba.conf configuration file, which PostgreSQL uses to verify a client’s access to the server, contains entries like this to authenticate clients, with the file users containing a list of usernames:

# TYPE  DATABASE  USER    CIDR-ADDRESS   METHOD
local   all       all                    md5
host    all       @users  10.0.1.0/24    md5

Changing this configuration to enable Kerberos principals to access their databases is relatively easy:

1. I create a service principal for the PostgreSQL server, naming it postgres/hostname. (I can change the service name (postgres) if I configure krb_srvname in postgresql.conf accordingly, but this is typically not necessary.)
2. I extract the key for this service principal into a keytab and ensure it’s readable by the PostgreSQL user. I configure the keytab name in postgresql.conf:

krb_server_keyfile = '/var/lib/pgsql/data/krb5.keytab' 
krb_srvname = 'postgres'

I then configure access to the PostgreSQL server by modifying pg_hba.conf:

# TYPE  DATABASE  USER    CIDR-ADDRESS   METHOD
host    all       all     10.0.1.0/24    gss include_realm=0 krb_realm=MENS.DE

Clients with valid Kerberos tickets can now connect to the database server and access databases:

$ kinit jpm
Password for jpm@MENS.DE:

$ psql -h hippo.ww.mens.de -d jp 
psql (9.1.3, server 8.4.11)
Type "help" for help.

jp=> \q

$ klist
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: jpm@MENS.DE

Valid starting    Expires           Service principal
23/06/2012 15:15  24/06/2012 15:15  krbtgt/MENS.DE@MENS.DE
23/06/2012 15:16  24/06/2012 15:15  postgres/hippo.ww.mens.de@MENS.DE

This works seamlessly providing a client’s principal name matches the requested database user name. So, as we just saw, a principal jpm@MENS.DE can access a database belonging to Postgres user jpm, but principal f2@MENS.DE cannot:

$ kinit f2
Password for f2@MENS.DE:

$ psql -h hippo.ww.mens.de -d jp -U jpm
psql: FATAL:  Kerberos 5 authentication failed for user "jpm"

and the PostgreSQL server log shows

LOG:  provided username (jpm) and authenticated username (f2) don't match

Newer PostgreSQL versions provide a mapping mechanism between Kerberos principal names used in GSSAPI authentication and database names. First I specify I want mapping by configuring a map name I define in pg_hba.conf:

# TYPE  DATABASE  USER    CIDR-ADDRESS   METHOD
host    all       all     10.0.1.0/24    gss include_realm=1 krb_realm=MENS.DE map=krb

I then define that map in pg_ident.conf:

# MAPNAME  SYSTEM-USERNAME     PG-USERNAME
krb        /^(.*)@MENS\.DE$    \1
krb        f2@MENS.DE          jpm

The first line uses a regular expression (introduced by the /) to convert user@REALM into user. Careful: PostgreSQL detects the regular expression by the first forward slash, but the expression does not end with a slash! In the second example, I map a principal name to a PostgresSQL database-user name.

There we are: another password bites the dust. :-)