Installing kerberized PostgreSQL (on Centos 6.5)

By Harikrishna Doredla

Kerberos is a network authentication protocol. It allows client / server communication over non-secure network using secret- key-cryptography ( “tickets”) to identity one another  (client and server). Once the server and the client mutually authenticate their identity , the privacy and data integrity of communications between client and server are assured. No hassle of hacking passwords in the network traffic.

Here is how I installed  on Centos 6.5 , this highly secure solution from MIT ( Michigan Institute of Technology) to achieve kerberized PostgresSQL. ..

The steps  to connect PostgreSQL client to PostgreSQL server using MIT kerberos authentication.

Step: 1

Launch  3 virtual machines (in VMware workstation) with OS as Centos 6.5

Connect the 3 machines in NAT network. (establishes inter connectivity among the three machines )

VM 1: 192.168.1.83   (PostgreSQL client)

VM 2:  192.168.1.84   (Kerberos server)

VM 3:  192.168.1.85   (PostgreSQL server)

Step: 2

Assign domain names to all the 3 virtual machines with entries in  /etc/hosts  as :

192.168.1.83    pclient.keauth.com

192.168.1.84    keauth.com kserver.keauth.com

192.168.1.85    pserver.keauth.com
These domain names are used in Kerberos server configuration.

Set hostname as  kserver.keauth.com in Kerberos server virtual machine.

Set hostname as  pserver.keauth.com in PostgreSQL server virtual machine:

Set hostname as  pclient.keauth.com in PostgreSQL client virtual machine:

Edit  the file : /etc/sysconfig/network in all the three virtual machines to reflect the hostname.

Lines to edit :

NETWORKING=yes

HOSTNAME=[hostname]

service network restart

Check and ensure time synchronization and DNS functions correctly on the 3 machines.

Install and configure Kerberos server (one of the virtual machines VM 2 above) .

Execute in this machine the two commands  :

yum install ntp

yum install krb5-server krb5-libs krb5-workstation

Edit the files  /etc/krb5.conf and /var/kerberos/krb5kdc/kdc.conf files to reflect the realm name and domain-to-realm mappings.

[realms]

KEAUTH.COM  = {

kdc = kserver.keauth.com:88

admin_server = kserver.keauth.com:749

default_domain = keauth.com

}

[domain_realm]

.keauth.com = KEAUTH.COM

keauth.com = KEAUTH.COM

 Please note realm and domain-to-realm names are case sensitive By convention, all realm names are uppercase and all DNS hostnames and domain names are lowercase.

Step: 3

Create the database using the kdb5_util utility from a shell prompt:

[root@kerberos-server krb5kdc]# kdb5_util create -r KEAUTH.COM -s

Loading random data

Initializing database ‘/var/kerberos/krb5kdc/principal’ for realm ‘KEAUTH.COM’,

master key name ‘K/M@KEAUTH.COM’

You will be prompted for the database Master Password.

Enter KDC database master key:

Re-enter KDC database master key to verify:

[root@kerberos-server krb5kdc]#

Edit the /var/kerberos/krb5kdc/kadm5.acl file

*/admin@KEAUTH.COM  *

Type ‘kadmin.local’ in the KDC terminal to create the first principal:

kadmin.local -q “addprinc adminuser/admin”

Authenticating as principal root/admin@KEAUTH.COM with password.

WARNING: no policy specified for adminuser/admin@KEAUTH.COM; defaulting to no policy

Enter password for principal “adminuser/admin@KEAUTH.COM”:

Re-enter password for principal “adminuser/admin@KEAUTH.COM”:

Principal “adminuser/admin@KEAUTH.COM” created.

Starting Kerberos server daemons:

/sbin/service krb5kdc start

/sbin/service kadmin start

chkconfig kadmin on

chkconfig krb5kdc on

chkconfig –list | grep -i k

Step: 4

Installing and configuring Kerberos client in PostgreSQL server and PostgreSQL client:

Make changes in the 2 VMs as below :

Edit  the file  : /etc/yum.repos.d/CentOS-Base.repo –  [base] and [updates] sections

to append additional line:

exclude=postgresql*

Execute the command below in both machines:

rpm -Uvh http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm

 Execute the following commands in  PostgreSQL Server VM:

yum list postgres*

yum install postgresql93-server postgresql93

service postgresql-9.3 initdb

service postgresql-9.3 status

chkconfig –add postgresql-9.3

chkconfig postgresql-9.3 on

service postgresql-9.3 start

su – postgres

psql –version

psql (PostgreSQL) 9.3.3

Setting password for the first time login after installation:

su – postgres

psql

ALTER USER postgres with encrypted password ‘postgres';

Enabling remote access:

Edit the file :  /var/lib/pgsql/9.3/data/postgresql.conf

listen_addresses = ‘*’ (to allow all IP addresses)

Also edit the file : /var/lib/pgsql/data/pg_hba.conf

local   all         all                               ident

host    all         all         0.0.0.0/0             md5

Execute the following commands in  Postgres Client VM:

yum install postgresql93

Now execute steps below both in PostgresSQL Server and client  to configure Kerberos client:

yum install ntp

yum install krb5-libs krb5-workstation

Install the krb5-libs and krb5-workstation packages on all of the client machines. Supply a valid /etc/krb5.conf file for each client (usually this can be the same krb5.conf file used by the KDC at server side).

 Enabling the Kerberos authentication Method in the PostgreSQL server:

Execute the below mentioned commands in Kerberos server to Create a service principal for the PostgreSQL server and extract the key for this service principal into a key tab.

kadmin.local

addprinc -randkey host/pserver.keauth.com

addprinc -randkey postgres/pserver.keauth.com

ktadd -k krb5.keytab host/pserver.keauth.com

ktadd -k krb5.keytab postgres/pserver.keauth.com

copy this to PostgreSQL server

scp krb5.keytab root@192.168.1.85:/etc/

In PostgreSQL server:

Go to /etc folder and change owner ship of krb5.keytab file to give read access to PostgreSQL user.

chown root:postgres krb5.keytab

chmod g+r krb5.keytab

Edit Kerberos related parameters in” /var/lib/pgsql/9.3/data/postgresql.conf”

# Kerberos and GSSAPI

krb_server_keyfile = ‘/etc/krb5.keytab’

krb_srvname = ‘postgres’                # (Kerberos only)

krb_caseins_users = off

To enable Kerberos  authentication method, add below line to  /var/lib/pgsql/9.3/data/pg_hba.conf

host    all             all             0.0.0.0/0               krb5

To enable GSS authentication method, add below line to  /var/lib/pgsql/9.3/data/ pg_hba.conf

host    all             all             0.0.0.0/0               gss include_realm=0 krb_realm=KEAUTH.COM

Step: 5

Create a database user to connect to the server using Kerberos authentication:

su – postgres

psql# CREATE USER psqltesting;# \du# \q

Create a client principal for the PostgreSQL user to get ticket from Kerberos server:

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.

In Kerberos server:

# kadmin.local

addprinc  psqltesting

Remember password given here, the same has to be used in client machine.

In PostgreSQL Client:

As user “psqltesting” connect to PostgreSQL server with Kerberos authentication:

[root@pclient ~]# kinit psqltesting

Password for psqltesting@KEAUTH.COM:
[root@pclient ~]# klist
Ticket cache: FILE:/tmp/krb5cc_0
Default principal: psqltesting@KEAUTH.COM
Valid starting     Expires            Service principal
03/12/14 23:24:53  03/13/14 23:24:53  krbtgt/KEAUTH.COM@KEAUTH.COM
renew until 03/12/14 23:24:53
[root@pclient ~]# psql -h pserver.keauth.com -U psqltesting -d postgres
psql (9.3.3)

Type “help” for help.
postgres=>