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-8as the default encoding (if it is not available in the list, first rundpkg-reconfigure locales),USorder 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 0.0.0.0 0.0.0.0 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