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:
Changing this configuration to enable Kerberos principals to access their databases is relatively easy:
- I create a service principal for the PostgreSQL server, naming it
postgres/hostname
. (I can change the service name (postgres
) if I configurekrb_srvname
inpostgresql.conf
accordingly, but this is typically not necessary.) - 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
:
I then configure access to the PostgreSQL server by modifying pg_hba.conf
:
Clients with valid Kerberos tickets can now connect to the database server and access databases:
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:
and the PostgreSQL server log shows
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
:
I then define that map in pg_ident.conf
:
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. :-)