How to setup Windows Active Directory with PostgreSQL GSSAPI Kerberos Authentication

PostgreSQL provides a bevy of authentication methods to allow you to pick the one that makes the most sense for your environment. One desired implementation that I have found customers wanting is to use Windows Active Directory with PostgreSQL's GSSAPI authentication interface using Kerberos. I've put together this guide to help you take advantage of this setup in your own environment.

Setting up Windows Active Directory

The first step in setting up a Windows Active Directory is to create a regular user account. The password can be anything but shouldn't expire and it needs to be unique in the environment. In this instance, we'll use pg1postgres.

Once the user account exists, we have to create a mapping between that user account and the service principal and create a keytab file. These steps can be combined using the Windows ktpass command, like so:

ktpass /out pg1.keytab /princ postgres@pg1.domain.local@DOMAIN.LOCAL /mapuser pg1postgres /crypto AES256-SHA1 +rndpass /target DOMAIN.LOCAL -ptype KRB5_NT_PRINCIPAL

This should create a pg1.keytab file which has to then be copied to the PostgreSQL server on Ubuntu.

Lastly, in the Windows system, go into the User account, under Properties for the pg1postgres user, on the 'Account' tab, be sure to check the box that says "This account supports Kerberos AES 256 bit encryption."

Setting up PostgreSQL on Ubuntu

On the Ubuntu PostgreSQL server, move the pg1.keytab file into /etc/postgresql/, change the ownership to be postgres:postgres and the file mode to be 600.

On both the client and servers, the krb5-user package should be installed. In an Active Directory environment, that's likely all that will be required since the rest of the information is available in DNS.

In postgresql.conf, configure krb_server_keyfile to point to the keytab file, like so:

krb_server_keyfile = '/etc/postgresql/pg1.keytab'

In pg_hba.conf, configure the appropriate rows to use the gss authentication mechanism, like so:

host all all 0.0.0.0/0 gss

Once these steps are done, PostgreSQL is ready to accept Kerberos (aka GSSAPI) based authentication from clients.

Creating Kerberos users in PostgreSQL

When Kerberos / GSSAPI authentication is used, the "authentication system" user authenticated to PostgreSQL will be user@DOMAIN. In our example, this will be sfrost@DOMAIN.LOCAL. In order for a user to authenticate with Kerberos and log in, that user needs to exist in PostgreSQL, or a mapping needs to exist to map to a user in PostgreSQL. For instance, here is what things look like without a mapping:

As a user who can create roles, run:

postgres=# create user "sfrost@DOMAIN.LOCAL"; CREATE ROLE

Then, to log in using Kerberos as that user, run psql like so:

(if you do not have a ticket already, run: kinit sfrost@DOMAIN.LOCAL)

psql -U sfrost@DOMAIN.LOCAL -h pg1.domain.local -d postgres

Note that in Kerberos, a user is always logging into a server and we have to specify what that server is- in this case "-h pg1.domain.local" is telling psql that we want to log into the pg1.domain.local server, even though that's actually the local system. Further, psql, by default, will try to log into PostgreSQL using the current Unix username, which is "sfrost" in this case, but there is no "sfrost" PostgreSQL user, so we have to use -U sfrost@DOMAIN.LOCAL to tell psql to use that username to log in. Alternatively, the user created in PostgreSQL could be "sfrost" and a mapping created to allow the Kerberos user sfrost@DOMAIN.LOCAL to log in as that user. See the PostgreSQL documentation of pg_ident.conf for details.

Requirements for Kerberos Authentication

There's a number of things which Kerberos depends on for proper authentication:

• Reverse DNS must be set up and returning the correct result. This is what Kerberos uses to find the service in Active Directory.
• The clocks on all of the systems need to be reasonably close to each other (within about 5 minutes)
• The reverse DNS result for the IP that the server is answering on needs to match the service principal used in the ktpass command.
• If running psql on Windows, it may be necessary to deal with case differences- specifically, the service principal might have to be specified to psql in the connection string, or created in active directory as POSTGRES/pg1.domain.local@DOMAIN.LOCAL instead (though psql on unix systems would then have to use the connection string option).
• There are additional complications when it comes to integration with Web servers, such as when running pgAdmin4 as an independent web server, because the web server needs to be configured to authenticate with its own service account using SPNEGO, and that service account needs to be configured in Active Directory to allow delegation, and the web client needs to be able to authenticate and delegate authentication to the web server to allow it to log into PostgreSQL. (This would be good to have a dedicated article on).
• The PostgreSQL server needs to live in the Active Directory domain and in its DNS, or in a domain with a cross-realm trust with the AD server. For a larger environment, with many PostgreSQL servers, it may make sense to have a Unix-based KDC, such as the MIT KDC, and then have a cross-realm trust between the Active Directory environment and the Unix/PostgreSQL environment.