DEV Community

Dmitry Romanoff
Dmitry Romanoff

Posted on

Kerberos Authenticated Setup with PostgreSQL on Linux

#kerberos #postgres #linux #setup

Step #1

Add PostrgeSQL to Linux system:

sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

sudo yum install -y postgresql14-server postgresql14
/usr/pgsql-14/bin/postgresql-14-setup initdb
systemctl enable --now postgresql-14
Enter fullscreen mode Exit fullscreen mode

Step #2

Let's examine "regular" connection to Postgres (not Kerberos).
Set password for Postgres user and login

sudo su - postgres
-bash-4.2$ psql
psql (14.2)
Type "help" for help.
postgres=#
alter user postgres with password '123456';
Enter fullscreen mode Exit fullscreen mode

Step #3 

vi /var/lib/pgsql/14/data/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

add line:

host all all 0.0.0.0/0 md5
Enter fullscreen mode Exit fullscreen mode

Step #4 

vi /var/lib/pgsql/14/data/postgresql.conf
listen_addresses = '*'
Enter fullscreen mode Exit fullscreen mode

Step #5

Restart postgres

systemctl restart postgresql-14
systemctl status postgresql-14
Enter fullscreen mode Exit fullscreen mode

Step #6 

[root@infra krb5kdc]# psql -h infra.labs.local -U postgres -d postgres -W
Password:
psql (14.2)
Type "help" for help.
postgres=#
Enter fullscreen mode Exit fullscreen mode

Step #7

Now let's authenticate using Kerberos
For easy configuration I install Postges DB on the same machine that Kerberos Server runs.
On Kerberos Server:

kadmin.local
addprinc postgres/infra.labs.local@LABS.LOCAL
xst -k myrealm.labs.keytab postgres/infra.labs.local@LABS.LOCAL
[root@infra krb5kdc]# kadmin.local
Authenticating as principal dmitry/admin@LABS.LOCAL with password.
kadmin.local: addprinc postgres/infra.labs.local@LABS.LOCAL
WARNING: no policy specified for postgres/infra.labs.local@LABS.LOCAL; defaulting to no policy
Enter password for principal "postgres/infra.labs.local@LABS.LOCAL":
Re-enter password for principal "postgres/infra.labs.local@LABS.LOCAL":
Principal "postgres/infra.labs.local@LABS.LOCAL" created.
kadmin.local: xst -k myrealm.labs.keytab postgres/infra.labs.local@LABS.LOCAL
Entry for principal postgres/infra.labs.local@LABS.LOCAL with kvno 2, encryption type aes256-cts-hmac-sha1-96 added to keytab WRFILE:myrealm.labs.keytab.
Entry for principal postgres/infra.labs.local@LABS.LOCAL with kvno 2, encryption type aes128-cts-hmac-sha1-96 added to keytab WRFILE:myrealm.labs.keytab.
Entry for principal postgres/infra.labs.local@LABS.LOCAL with kvno 2, encryption type des3-cbc-sha1 added to keytab WRFILE:myrealm.labs.keytab.
Entry for principal postgres/infra.labs.local@LABS.LOCAL with kvno 2, encryption type arcfour-hmac added to keytab WRFILE:myrealm.labs.keytab.
Entry for principal postgres/infra.labs.local@LABS.LOCAL with kvno 2, encryption type camellia256-cts-cmac added to keytab WRFILE:myrealm.labs.keytab.
Entry for principal postgres/infra.labs.local@LABS.LOCAL with kvno 2, encryption type camellia128-cts-cmac added to keytab WRFILE:myrealm.labs.keytab.
Entry for principal postgres/infra.labs.local@LABS.LOCAL with kvno 2, encryption type des-hmac-sha1 added to keytab WRFILE:myrealm.labs.keytab.
Entry for principal postgres/infra.labs.local@LABS.LOCAL with kvno 2, encryption type des-cbc-md5 added to keytab WRFILE:myrealm.labs.keytab.
kadmin.local:
Enter fullscreen mode Exit fullscreen mode

This creates a set of entries for this database user that will be used as validation for the PostgreSQL backend.

[root@infra krb5kdc]# ls -rtolga myrealm.labs.keytab
-rw-------. 1 658 Mar 18 12:20 myrealm.labs.keytab
Enter fullscreen mode Exit fullscreen mode

Step #8:

Copy the keytap file that has just been created and give access to it to the client with chown:

scp myrealm.labs.keytab 192.168.0.111:/etc/
Enter fullscreen mode Exit fullscreen mode

Step #9:

On Kerberos Client:

kdestroy
chmod 644 /etc/myrealm.labs.keytab
kinit -k -t /etc/myrealm.labs.keytab postgres/infra.labs.local@LABS.LOCAL
Enter fullscreen mode Exit fullscreen mode

Step #10:

Use klist to display the contents of the Kerberos ticket:

[dmitry@client ~]$ klist
Ticket cache: KEYRING:persistent:1000:1000
Default principal: postgres/infra.labs.local@LABS.LOCAL
Valid starting Expires Service principal
03/18/2022 12:32:06 03/19/2022 12:32:06 krbtgt/LABS.LOCAL@LABS.LOCAL
[dmitry@client ~]$
Enter fullscreen mode Exit fullscreen mode

Step #11

Create the user on the Postgres DB that will be used for Kerberos authentication:

[root@client ~]# su - postgres
-bash-4.2$ psql
psql (14.2)
Type "help" for help.
postgres=# CREATE ROLE "postgres/infra.labs.local@LABS.LOCAL" SUPERUSER LOGIN
postgres-# ;
CREATE ROLE
postgres=#
Enter fullscreen mode Exit fullscreen mode

Step #12

Update postgresql.conf to point to the keytab file previously created:

vi /var/lib/pgsql/14/data/postgresql.conf
krb_server_keyfile='/etc/myrealm.labs.keytab'
Enter fullscreen mode Exit fullscreen mode

And add this entry in pg_hba.conf:

vi /var/lib/pgsql/14/data/pg_hba.conf
host all all 0.0.0.0/0 gss include_realm=1 krb_realm=LABS.LOCAL
Enter fullscreen mode Exit fullscreen mode

make sure this line is commented:

# host all all 0.0.0.0/0 md5
Enter fullscreen mode Exit fullscreen mode

Step #13

Now we should reload parameters on the servers, restarting Postgres:

systemctl restart postgresql-14
systemctl status postgresql-14
Enter fullscreen mode Exit fullscreen mode

Step #14

Now connect to Postgres using Kerberos:

[root@infra krb5kdc]# psql -U "postgres/infra.labs.local@LABS.LOCAL" -h infra.labs.local postgres
psql (14.2)
GSSAPI-encrypted connection
Type "help" for help.
postgres=#
Enter fullscreen mode Exit fullscreen mode

The Connection to Postgres using Kerberos succeeded: "GSSAPI-encrypted connection", and no pwd prompted
Check kerberos authentication from the Kerberos Client Machine:

dmitry@client ~]$ psql -U "postgres/infra.labs.local@LABS.LOCAL" -h infra.labs.local postgres
psql (14.2)
GSSAPI-encrypted connection
Type "help" for help.
postgres=#
Enter fullscreen mode Exit fullscreen mode
  • In case you're getting this error: 
[dmitry@client ~]$ psql -U "postgres/infra.labs.local@LABS.LOCAL" -h infra.labs.local postgres
psql: error: connection to server at "infra.labs.local" (192.168.0.254), port 5432 failed: No route to host
Enter fullscreen mode Exit fullscreen mode

Is the server running on that host and accepting TCP/IP connections?
you should allow Postgres client connection to Postgres server DB machine.
On Postgres DB Server machine:

firewall-cmd --add-service=postgresql --permanent
firewall-cmd --reload
Enter fullscreen mode Exit fullscreen mode

Latest comments (0)

Read next

heiytor profile image

Writing a Window Manager From Scratch Part I

Heitor Danilo -

pramod74 profile image

Guide to using ‘ed’ editor in Linux

pramod74 -

mustafazaimoglu profile image

Creating Package-Specific Local Repository in Rocky Linux 9

Mustafa ZAİMOĞLU -

danielwambo profile image

Exploring Data Visualization with NumPy

danielwambo -