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

Top comments (0)