Postgresql gss
From Asenjo
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 ...