Windows Active Directory/kerberos authentication with postgres

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 ENVIRONMENT

On Kerberos server

SINCE THIS IS A DEMO, WE WILL TURN OFF FIREWAL

systemctl stop firewalld
systemctl disable firewalld
systemctl mask --now firewalld

yum install krb5-server krb5-workstation pam_krb5 -y
vi /var/kerberos/krb5kdc/kdc.conf —-uncomment anything there and add the following
default_principal_flags = +preauth

default_principal_flags
(Flag string.) Specifies the default attributes of principals created in this realm. The format for this string is a comma-separated list of flags, with ‘+’ before each flag that should be enabled and ‘-‘ before each flag that should be disabled. The postdateable, forwardable, tgt-based, renewable, proxiable, dup-skey, allow-tickets, and service flags default to enabled.
preauth
If this flag is enabled on a client principal, then that principal is required to preauthenticate to the KDC before receiving any tickets. On a service principal, enabling this flag means that service tickets for this principal will only be issued to clients with a TGT that has the preauthenticated bit set.
vi /etc/krb5.conf — edit with your realm 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 postgres/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 [email protected]:/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
/var/lib/edb/as12/data/postgres.keytab
systemctl restart edb-as-12.service

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
hostgssenc all benson 192.168.0.0/24 gss include_realm=0 krb_realm=HOPTO.ORG
create role benson with login password ‘postgres’;

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 [email protected]:/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 [email protected]:/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.

LDAP WITH KERBEROS AUTHENTICATION TO AN ALREADY RUNNING POSTGRES SERVER.

• FIRST WE WILL NEED TO CREATE AN ADMINISTRATOR USER WHO WILL BE USED TO RUN THE KEYTAB GENERATION.
  Open PowerShell as administrator to run the below
• cd \Desktop\
• setspn -A POSTGRES/[email protected] bensonyerima
• setspn -A HOST/[email protected] bensonyerima
• setspn -L bensonyerima
• ktpass /out benyerkbr5.keytab /princ POSTGRES/[email protected] /mapuser [email protected] /pass Postgres12 /ptype KRB5_NT_PRINCIPAL

Next I willhave to transfer the keytab file to ldapepas.hopto.org. in this case I will use winscp. Preferably put this file in the data directory.

After the file has been transferred, we need to go to the epas database server, we need to change the permissions to 0600 and also the ownership to be owned by postgres or enterprisedb
Replace this in the postgresql.conf parameter as below
Krb_server_keyfile
edb#create user “[email protected]” superuser createdb createrole
create user “[email protected]” superuser createdb createrole
host all all 0.0.0/0 gss include_realm=1 krb_ream=HOPTO.ORG –In pg_hba.con file

Now I will log into a separate windows machine kerbwinclient.hopto.org running windows 10 as the user bensonyerima but authenticated from ldap

With a successful login, my credentials are verified by the ldap server running on 192.168.0.169
At this point, I can attempt to connect to the ldapepas server database running on 192.168.0.182
Now I am able to establish a connection to my database running on linux from windows after being authenticated by ldap server.
The klist command below shows be the tgt obtained

We will perform another test by creating another normal user in ldap and test connection again

Create a user and have the properties edited to have the below

On epasdatabase server

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

HOW CONNECT FROM A LINUX MACHINE BUT AUTHENTICATED BY THE WINDOWS SERVER
in this test case, i will attempt to establish a connection from another linux server running on 192.168.0.206 to connect to ldapepas.hopto.org running on 192.168.0.182. But this connection has to first go through the windows ldap server running on 192.168.0.169 to obtain the tgt and service ticket

On the 192.168.0.206, I did two things, first I installed krb5 packages using the below command
yum install krb5-server krb5-workstation pam_krb5

The above file edited to look like below
scp [email protected]:/etc/krb5.conf /etc/krb5.conf
ldapwinserver.hopto.org

Note that this krb5 file is pointing to my windows ldap server.
Make sure the /etc/hosts file is edited on all servers

Now let’s test the connection..

kinit [email protected]
klist
psql -h epasdatabase.hopto.org -d edb -U [email protected]

If we run klist again, we will see our tgt and service ticket

If we want to attempt connection as another user, we will need to clear the cached tickets
Kdestroy -A