How to Set Up Kerberos Authentication Using Active Directory with PostgreSQL Database

Kerberos is one of the leading network security authentication protocols, recommended by EDB in the Postgres Vision 2021 Conference, and preferred and implemented in many large organizations. Kerberos is a ticket-based authentication system that authenticates between trusted hosts using strong encryption algorithms. Kerberos excels at single-sign-on (SSO). With SSO, identity is proven once to Kerberos for the specified amount of time, and then Kerberos passes this information as a Ticket Granting Ticket (TGT) to other services or machines as a proof of your identity.

Kerberos introduces third party authentication between client and server. Therefore, in our implementation, there will be one server running Postgres Client, a second server running PostgreSQL DB Server, and the third will be Kerberos Server which is also called Key Distribution Center (KDC) server. These three servers are also referred to as the three heads of Kerberos taken from Greek mythology – Cerberus.
KDC can be divided into three major components: 

1. Database of all principals (user accounts & services)
2. Authentication Server (AS) 
3. Ticket Granting Server (TGS)

The Authentication Server is responsible for initial authentication requests from users, triggered through the kinit command. TGS grants the service ticket to the users, which is used to request access to the database server in our case.

The first component of KDC is a database of all principals. MIT Kerberos provides the kdb5_util command to create its own database and then allows you to create and manage principals and create a keytab file. However, when we integrate Kerberos with Active Directory, this database is replaced with Active Directory Domain Controller Database. Therefore, this solution integrates the power of Active Directory Centralized user management with strong Kerberos authentication.

KDC => AD Domain Controller + Authentication Server (AS) + Ticket Granting Server (TGS)

In our implementation, below are the names of the servers:
AD Domain Controller running KDC (Microsoft Windows Server):     ad_kdc.ad.mydomain.qa
PostgreSQL Client (Running on RedHat Linux):                         pgclient.myenterprise.net
PostgreSQL DB Server (Running on RedHat Linux):                pgdbserver.myenterprise.net

Let’s go over a few key terms here.

• REALM– is equivalent to a domain. Here our realm is: @AD.MYDOMAIN.QA.
• Principal – could be Service Principal or User Principal. Service Principals are in the format of servicename/hostname@REALM. User principals are in the format of username@REALM.

Following are the examples of service principals.

• Default principal Service: POSTGRES/pgdbserver.myenterprise.net@AD.MYDOMAIN.QA. Check the postgresql documentation: 20.6 GSSAPI Authentication for the default service name and whether it should be small or capital letters.
• mongodb/<hostname>@REALM => default service for MongoDB.
• ORACLE/<hostname>@REALM => default service for Oracle DB.
• HTTP/pgadmin.myenterprise.net@AD.MYDOMAIN.QA  -> Web Service for PGADMIN. For detail – look at the article: Kerberos Support in pgAdmin 4, by Khushboo Vashi.
• USER Principal Examples: appuser1@AD.MYDOMAIN.QA, testuser@AD.MYDOMAIN.QA.
• TGT – The Ticket Granting Ticket is a message used to confirm the identity of the principals and to deliver session keys.
• Keytab: A file extracted from the KDC database containing principal, and associated encryption key.

Figure 1: High level steps of Kerberos Authentication.

As shown in Figure 1, in Kerberos, when the kinit command is executed, a client sends an encrypted password to Authentication Server (AS) along with the principal and receives an encrypted ticket granting ticket (TGT). By default, TGT expires in 10 hours and is renewable within 24 hours. The period can be changed in the krb5.conf file. Once a client has TGT, the password is never sent over the network to KDC. When a client initiates a connection request to the database, it uses the TGT to get a service ticket from the TGS. This service ticket is sent to Postgres DB Server where service: POSTGRES allows a connection after verification from the KDC.

 

Steps to make Kerberos authentication work with active directory

1. Ensure that krb5-workstation or krb5-server is installed on both PostgreSQL client and server machines. If not, get it installed.
2. Update /etc/krb5.conf on both client and server machines (Only root can do it).

A sample /etc/krb5.conf file is presented below. Normally, the KDC is managed by a security team in a large organization. You may want to discuss the values of krb5.conf with a member of your security personnel.

[logging]
default = /tmp/krb5libs.log
kdc = /tmp/krb5kdc.log
admin_server = /tmp/kadmind.log

[libdefaults]
default_realm = AD.MYDOMAIN.QA
dns_lookup_realm = false
#ticket_lifetime = 24h
#renew_lifetime = 7d
forwardable = true
udp_preference_limit = 1

[realms]
AD.MYDOMAIN.QA = {
kdc = ad_kdc.ad.mydomain.qa:636
admin_server = ad_kdc.ad.mydomain.qa:636
}
[domain_realm]
.ad.mydomain.qa = ADMYDOMAIN.QA
ad.mydomain.qa + AD.MYDOMAIN.QA

3. Ensure PATH is set up for kinit and ktutil Kerberos commands in .profile.

export PATH=$PATH:/opt/boksm/bin:/opt/boksm/sbin

4. Keytab Generation for the service principal: POSTGRES

You may have to request security personnel to generate a keytab file. If you are responsible for it, you need to create an active directory service account first. Service Principal Name must be uniquely identifiable and must be registered against the service account. That service account cannot be used for other Service Principals. The Service Principal is always in the form of service/hostname@REALM.

• You will create an AD account (e.g. acc1_pgdbserver) in the realm @AD.MYDOMAIN.QA. You also need to set supported encryption types to that service account. You can do so using the set-aduser command.
• Use the KTPass command to generate a keytab file for service: POSTGRES. This keytab file will then need to be moved to PostgreSQL DB Server.

KTPass –princ POSTGRES/pgdbserver.myenterprise.net@AD.MYDOMAIN.QA – Pass XXXXXXXXYYYYYYYYZZZZZZZZ –mapuser acc1_pgdbserver -cypto ALL –ptype KRB5_NT_Principal –out postgres.pgdbserver.myenterprise.net.keytab

 

PostgreSQL DB server setup (pgdbserver.myenterprise.net)

5. After copying the keytab file to the database server (pgdbserver.myenterprise.net), verify the contents of keytab with the ktutil command.

kutil
ktutil: rkt postgres.pgdbserver.myenterprise.net.keytab
ktutil: list
slot KVNO Principal
1    3         POSTGRES/pgdbserver.myenterprise.net@AD.MYDOMAIN.QA
          	……….

6. Set the keytab file in postgresql.conf file param: krb_server_keyfile.

krb_server_keyfile=/myhome/…/postgres.pgdbserver.myenterprise.net.keytab

7. Set the pg_hba.conf file for Kerberos authentication.

host	all         	all                 	samenet     	gss include_realm=1 krb_realm=AD.MYDOMAIN.QA

8. Create a user in PostgreSQL. That should match the User Principal in Active Directory.

edb=# create user "testuser@AD.MYDOMAIN.QA" superuser ;
CREATE ROLE
          	Reload the updated configurations -> pg_ctl reload

 

PostgreSQL client setup (pgclient.myenterprise.net)

9. Ensure that Steps 1, 2 and 3 above were followed on client machine.

10. Get the user principal “testuser” created in Active Directory. You can connect to the database either through password or using keytab file for user principal: testuser.

kinit testuser
>> Enter Password:
klist
Ticket Cache: FILE:/tmp/krb5cc_123455
Default principal: testuser@AD.MYDOMAIN.QA
Valid starting   	        	Expires         	                       	Service principal
08/28/2021 12:11:14  08/28/2021 22:11:14 krbtgt/AD.MYDOMAIN.QA@AD.MYDOMAIN.QA
    	renew until 08/29/2021 12:11:14

………………………………………………………………………………………………………

Note=> If you have a keytab file for a testuser. You can initialize as shown below:

kinit -k -t testuser.keytab testuser

Note=> The keytab file for the testuser will be generated as shown below on AD

Domain Controller Server running the KDC.

KTPass –princ testuser@AD.MYDOMAIN.QA – Pass XXXXXXXXYYYYYYYYZZZZZZZZ –mapuser testuser -cypto ALL –ptype KRB5_NT_Principal –out testuser.keytab

 

Now you can test the connection on the client server

psql -h pgdbserver.myenterprise.net -d edb -U testuser@AD.MYDOMAIN.QA
psql (13.3.7, server 13.3.7)
GSSAPI-encrypted connection

Type "help" for help.

Read more: Video: Applying PostgreSQL Security to the AAA Framework