Setup GSSAPI auth for Postgresql with FreeIPA

postgresql-gssapi-freeipa.md

Draft (will need a proper wording)

Register the Server against freeIPA

• Install FreeIPA client
• enroll as client

Register the Postgresql service for the server

# Change to root account 
$: sudo su

# Get a FreeIPA admin ticket
$: kinit <user>@<REALM.TLD>
 
# Register the postgres service
$: ipa service-add postgres/<srv.fqdn.tld>

# Generate the service keytab
$: ipa-getkeytab -s <ipa.server.fqdn.tld> -p postgres/<srv.fqdn.tld>@<REALM.TLD>G  -k /var/lib/pgsql/data/pg.keytab

# Set the proper access rights 
$: chown postgres:postgres /var/lib/pgsql/data/pg.keytab

Configure Postgresql

• Point Postgresql to its keytab in /var/lib/pgsql/data/postgresql.conf:

  # Allow to listen on multiple address:
  listen_addresses = '*'
  
  # Add the path of the keyfile to 'krb_server_keyfile' :
  krb_server_keyfile = '/var/lib/pgsql/data/pg.keytab'
  

• Add GSSAPI auth to the pg_hba.conf :

host   all   all   <0.0.0.0/8>   gss include_realm=0

• Restart postgresql

Create a role for the user who wants to log in postgres :

$: sudo su - postgres
$: psql
postgres=# create user <username> login;
postgres=# \q

Log in postgress

• Clear kerberos tickets : kdestroy -A
• Get a Kerberos ticket for the user who wants to log into posgresql : kinit <username>
• Access postgresql : psql -d <database> -h <srv.fqdn.tld> -U <username>

Request for a TLS certificate :

ipa-getcert request -K postgres/<srv.fqdn.tld> -k /etc/pki/tls/private/postgresql.key -f /etc/pki/tls/certs/postgresql.pem -D <srv.fqdn.tld> -N "<srv.fqdn.tld>"