Kerberos Authentication Setup With Postgres On Linux

According to myth, Kerberos (you might know him as Cerberus) guards the Gates to the Underworld. He’s a big 3 headed dog with a snake for a tail and a really bad temper.

In the modern world, MIT Computer Scientists used the name and visual of Kerberos for their computer network authentication protocol.

What is Kerberos?

Kerberos authentication is currently the default authorization technology used by Microsoft Windows, and implementations of Kerberos exist in Apple OS, FreeBSD, UNIX, and Linux.

Microsoft introduced their version of Kerberos in Windows2000. It has also become a standard for websites and Single-Sign-On implementations across platforms

Kerberos Protocol Flow Overview

ØRealm: Realm is equivalent to a domain or a group that all the users and servers belong to.

ØPrincipal: any users and services are defined as a principal in Kerberos. For example, benson, postgres, postgres/pg.edb.com etc.

ØInstance: Kerberos uses Instance to manage service principals and especially for administrative principals.

ØKDC: Key Distribution Center contains one database of all principals and two components:

     •  AS: Authentication Server is responsible for the initial authentication request from users triggered by kinit.

      •TGS: Ticket Granting Server assigns the requested resource on a Service Server to the users.

ØTGT: Ticket Granting Ticket is a message used to confirm the identity of the principals and to deliver session keys which is used for future secured communication among user, TGS, and SS.

ØKeytab: A file extracted from the KDC principal database and contains the encryption key for a service or host.

ØClient: a workstation needs to access a Service Server. For example, psql running on a Client machine and want to connect to PostgreSQL server

KERBEROS AUTHENTICATION WITH POSTGRES

PostgreSQL supports GSSAPI with Kerberos authentication

The Generic Security Service Application Program Interface (GSSAPI, also GSS-API) is an application programming interface for programs to access security services

GSSAPI is an industry-standard protocol for secure authentication. GSSAPI provides automatic authentication (single sign-on) for systems that support it. The authentication itself is secure, but the data sent over the database connection will be sent unencrypted unless SSL is used.

When GSSAPI uses Kerberos, it uses a standard principal in the format servicename/hostname@realm.

The following configuration options are supported for GSSAPI:

      include_realm

If set to 1, the realm name from the authenticated user principal is included in the system user name that’s passed through user name mapping. This is the recommended configuration as, otherwise, it is impossible to differentiate users with the same username who are from different realms.

map

Allows for mapping between system and database user names. . For a GSSAPI/Kerberos principal, such as username@EXAMPLE.COM (or, less commonly, username/hostbased@EXAMPLE.COM), the default user name used for mapping is username (or , respectively), unless include_realm has been set to 1. username/hostbased

krb_realm Sets the realm to match user principal names against. If this parameter is set, only users of that realm will be accepted. If it is not set, users of any realm can connect, subject to whatever user name mapping is done

Here are some basic kerberos tools need to know.

Økadmin.local: KDC database administration tool used manage principal and policy.

Økinit: used to obtain and cache Kerberos ticket-granting ticket.

Øklist: used to list principal and tickets held in a credentials cache, or the keys held in a keytab file.

Øktutil: used to read, write, or edit entries in a keytab.

Økdb5_util utility enables you to create, dump, load, and destroy the Kerberos V5 database

DEMO FOR KERBEROS SETUP

cat /etc/hosts

On Kerberos server

hostname

ps –ef|grep postgres

systemctl stop firewalld

systemctl disable firewalld

systemctl mask –now firewalld

yum install krb5-server krb5-workstation pam_krb5

vi /var/kerberos/krb5kdc/kdc.conf  —-uncomment anything there and add the following

default_principal_flags = +preauth

vi /etc/krb5.conf  — edit with your real and note the cap and non caps

vi /var/kerberos/krb5kdc/kadm5.acl   —edit with the realm

kdb5_util create -s -r HOPTO.ORG  — create the Kerberos database

 systemctl start krb5kdc kadmin

systemctl enable krb5kdc kadmin

systemctl status krb5kdc kadmin

Once KDC server has been installed, we need to create an admin user to manage principals, and it is recommended to use a different username. In our case, root/admin. Below are the commands used for the setup and also add a principal enterprisedb which is the database user and the Linux login user.

kadmin.local

addprinc root/admin

addprinc enterprisedb

Once Kerberos service is running again, we can perform a quick test. First, try klist to see if any credentials cache exists, then try to see if root/admin can be authenticated. If no error, then try to use klist again to list the principal cached.

Klist

kinit root/admin

klist

Add a principal enterprisedb/epasdatabase.hopto.org as a principle instance for Service server

addprinc postgres/epasdatabase.hopto.org

addprinc benson

Listprincs

Extract the service principal from KDC principal database to a keytab file, which will be used to configure epas 12 Server. The file should be saved to current folder when run below commands.

ktutil

add_entry -password -p postgres/epasdatabase.hopto.org -k 1 -e aes256-cts-hmac-sha1-96

wkt postgres.keytab

exit

Ktutil

List

read_kt postgres.keytab

list

Now we copy this file to the data_directory of our epas server

scp postgres.keytab enterprisedb@epasdatabase.hopto.org:/var/lib/edb/as12/data

ON THE EPASDATABASE, WE ARE GOING TO BE INSTALLING EPAS12. THIS IS BECAUSE EPAS12 SUPPORTS KERBEROS AND GSSAPI. AS SEEN BELOW FROM THE PG_HBA.CONF FILE. THIS SUPPORT STARTS FROM EPAS12

add the location in the as the following in the postgresql.conf file just a reload is ok

show krb_server_keyfile;

This is the minimum changes in postgresql.conf required for GSSAPI user authentication with Kerberos.

Next I edit pg_hba.conf file file

hostgssenc all  enterprisedb     192.168.0.0/24     gss include_realm=0 krb_realm=HOPTO.ORG

gss include_realm=0 means the authentication method gss is used with the option include_realm=0 which indicates the realm name from the authenticated user principal will be stripped off before being passed through the user name mapping.

krb_realm Sets the realm to match user principal names against. If this parameter is set, only users of that realm will be accepted. If it is not set, users of any realm can connect, subject to whatever user name mapping is done.

ON THE KERBCLIENT MACHINE, WE WILL INSTALL THE CLIENT PACKAGES

yum install krb5-workstation pam_krb5 –y

Now copy the /etc/krb5.conf file from the Kerberos server.

scp root@kerbserver.hopto.org:/etc/krb5.conf /etc/krb5.conf

So from the client, we can test the connection to the epasdatabase by first obtaining our ticket from the Kerberos server.

Since we have a user in the Kerberos server called benson, and this user is also in the epas database, we can attempt to connect as that user from the client.

kdestroy –A

kinit benson

klist

psql -h epasdatabase.hopto.org -d edb -U benson

In this case, we just need the psql utility. We can get this by installing the epas binaries.

yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

yum -y install https://yum.enterprisedb.com/edb-repo-rpms/edb-repo-latest.noarch.rpm

scp root@epasdatabase.hopto.org:/etc/yum.repos.d/edb.repo /etc/yum.repos.d/

yum install edb-as12-server -y

SELECT pid, gss_authenticated, encrypted, principal from pg_stat_gssapi where pid = pg_backend_pid();

So in order to use Kerberos with gssapi, or if you will want to add a user,

Even if the user is created in the postgres server and the pg_hba.conf file is set for that user, remote connection by that user cannot be established.

What you will need to do is that the user must be added to Kerberos database and authenticated from there before that connection can be established.

You can follow the attached video for more details on configurations

About the author

Avatar photo

bensonyerima

Hi, I'm Benson Yerima, a database administrator with an obsession for all things tech. This blog is dedicated for helping people learn about database technology.

View all posts