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.
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 email@example.com@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."
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.
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
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:
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
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.
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.LOCALinstead (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.
March 4, 2019 •More by this author