Postgresql gss

Kerberize PostgreSQL in a FreeIPA realm (Centos 6.2)

Assumptions

• the Kerberos ipa realm is IPA.DOMAIN.TLD;
• the kdc in the realm is kdc.ipa.domain.tld;
• the server running postgresql is postgres01.ipa.domain.tld and is a member of the realm;

Steps

• create a service principal name for postgresql and hang it to the postgresql server. You can do this from the web interface or from the ipa cli tools:

postgres/postgres01.ipa.domain.tld@IPA.DOMAIN.TLD

• create a keytab for the service principal postgres/postgres01.ipa.domain.tld@IPA.DOMAIN.TLD on postgres01.ipa.domain.tld. First get a kerberos ticket as an admin user of your ipa realm in case you do not have one:

$ kinit admin@IPA.DOMAIN.TLD
<passwd>

$ ipa-getkeytab -p postgres/postgres01.ipa.domain.tld -s kdc.ipa.domain.tld -k postgres.keytab

This creates a keytab for the postgres service principal in the file postgres.keytab. You can verify it like this:

$ klist -k postgres.keytab
Keytab name: WRFILE:postgres.keytab
KVNO Principal
---- --------------------------------------------------------------------------
   1 postgres/postgres01.ipa.domain.tld@IPA.DOMAIN.TLD
   1 postgres/postgres01.ipa.domain.tld@IPA.DOMAIN.TLD
   1 postgres/postgres01.ipa.domain.tld@IPA.DOMAIN.TLD
   1 postgres/postgres01.ipa.domain.tld@IPA.DOMAIN.TLD

I placed this file in /var/lib/pgsql/data/postgres.keytab.

• modify postgresql.conf file (standaard in centos in /var/lib/pgsql/data/postgresql.conf:

krb_server_keyfile = '/var/lib/pgsql/data/postgres.keytab'

• modify pg_hba.conf to allow gssapi connections:

host all all 0.0.0.0/0 gss

In my case I allow any connection as long as it has a kerberos ticket.

• if SELINUX is enabled (and it should), make sure the keytab context is correct or you will permission denied errors:

ls -lZ postgres.keytab
-rw-r-----. postgres postgres unconfined_u:object_r:postgresql_db_t:s0 postgres.keytab

That's it, restart the postgresql service:

# /etc/init.d/postgresql restart

• Test it:
  • get a ticket from a kerberos user:

$ kinit testuser2@IPA.DOMAIN.TLD
Password for testuser2@IPA.DOMAIN.TLD:
$ klist
Ticket cache: FILE:/tmp/krb5cc_500
Default principal: testuser2@IPA.DOMAIN.TLD
Valid starting     Expires            Service principal
06/02/12 23:06:50  06/03/12 23:06:47  krbtgt/IPA.DOMAIN.TLD@IPA.DOMAIN.TLD
    renew until 06/09/12 23:06:47

• • try logging in the postgresql server:

$ psql -h postgres01.ipa.domain.tld -U testuser2 -d template1
psql: FATAL:  role "testuser2" does not exist
$ klist
Ticket cache: FILE:/tmp/krb5cc_500
Default principal: testuser2@IPA.DOMAIN.TLD

Valid starting     Expires            Service principal
06/02/12 23:06:50  06/03/12 23:06:47  krbtgt/IPA.DOMAIN.TLD@IPA.DOMAIN.TLD
    renew until 06/09/12 23:06:47
06/02/12 23:10:32  06/03/12 23:06:47  postgres/ipaclient01.ipa.domain.tld@IPA.DOMAIN.TLD
    renew until 06/09/12 23:06:47

Ok, we get a ticket but we cannot log in because unfortunately postgresql needs roles to login successfully. That is why we get the message "FATAL: role does not exist."

• • on the psql shell create a role for this user:

$ psql
psql (8.4.11)
Type "help" for help.

postgres=# create user testuser2 login;
CREATE ROLE
postgres=# \du
            List of roles
 Role name | Attributes  | Member of
-----------+-------------+-----------
 admin     |             | {}
 postgres  | Superuser   | {}
           : Create role  
           : Create DB    
 testuser2 |             | {}

• • try again:

$ psql -h postgres01.ipa.domain.tld -U testuser2 -d template1
psql (8.4.11)
Type "help" for help.

template1=>

We're in!

What we then need to do is replicate the users to the postgresql role database. To be continued ...