Installation of kerberized PostgreSQL

This post describes how to install PostgreSQL on a Debian GNU/Linux system with support for Kerberos 5 authentication. This document considers two kinds of hosts: the server is called server, an example client is called client. Both must be configured as Kerberos client hosts, which is out of the scope of this document.

On the server host, install the postgresql packages:

root@server> apt-get install libpq3 postgresql postgresql-client

On the client host, install the postgresql client package:

root@server> apt-get install libpq3 postgresql-client

During installation, I choose:

  • the standard location for the database files: /var/lib/postgres/data,
  • to purge the existing files,
  • en_US.UTF-8 as the default encoding (if it is not available in the list, first run dpkg-reconfigure locales),
  • US order for dates.

PostgreSQL is configured to user Kerberos as an authentication mechanism, by editing the /etc/postgresql/pg_hba.conf file to contain only the following non-commented lines:

local   all         postgres                                        ident sameuser
host    all         all           krb5

That configuration allows the local admin user postgres (the user that is created automatically created when installing the postgresql package, and that is used to run the server) to connect locally through UNIX sockets, and any other user can connect using TCP/IP connection from any host provided they have a sufficient Kerberos v.5 credential. The ident method allows the postgres user to connect without providing a password, only based on the uid of the psql process.

The server must be restarted to take the changes into account:

root@server> /etc/init.d/postgresql restart

To check that this configuration works, locally on the server:

root@server> su - postgres
postgres@server> psql template1
template1=# \q

For authenticating users through Kerberos, the principal for the service must be created. The principal must be in the form postgresql/serverfqdn, where serverfqdn is the server host name including the DNS domain name. For instance, when logging using the principal lenglet/admin that has the priviledge to create principals, it can be created with the following commands:

lenglet@client> /usr/sbin/kadmin -p lenglet/admin
kadmin: add_principal -randkey host/serverfqdn
kadmin: add_principal -randkey postgres/serverfqdn
kadmin: quit

The automatically generated Kerberos key is a shared secret between the Kerberos server and the PostgreSQL server (it is automatically generated with the -randkey option). The key must therefore be stored in a “keytab” file in the server, readable by the postgres user. However, the Kerberos standard keytab file (/etc/krb5.keytab) is owned by root:rootand has the rw------- rights. A new keytab must therefore be created, for instance as /etc/postgres/krb5.keytab, and the Kerberos keys must be exported into it:

root@server> rm -Rf /etc/postgresql/krb5.keytab
root@server> /usr/sbin/kadmin -p lenglet/admin
kadmin: ktadd -k /etc/postgresql/krb5.keytab host/branwen
kadmin: ktadd -k /etc/postgresql/krb5.keytab postgres/branwen
kadmin: quit
root@server> chown root:postgres /etc/postgresql/krb5.keytab
root@server> chmod g+r /etc/postgresql/krb5.keytab

The path of this file is not the PostgreSQL default location for this file (which would probably violate the Debian policy, unlike/etc/postgres/krb5.keytab), so that path must be configured by adding the following line into the/etc/postgresql/postgresql.conf file on the host of the PostgreSQL server:

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

The database users must be created in the PostgreSQL server as usual using the CREATE USER command. The Kerberos principal must have the PostgreSQL user name as their first component (i.e. before the slash), and the secondary component is ignored by PostgreSQL. For instance, to log in as PostgreSQL user foo, foo@kerberosrealm and foo/bar@kerberosrealm are both valid authentication principals.

For instance, to create a PostgreSQL user that can be used when logging using Kerberos principal lenglet@kerberosrealm or lenglet/admin@kerberosrealm:

postgres@server> psql template1
template1=# CREATE USER lenglet;
template1=# \du
template1=# \q

Check that user lenglet can connect without password from the client host:

lenglet@client> kinit
lenglet@client> psql -h serverfqdn template1
template1=# \q