DEV Community

Dmitry Romanoff
Dmitry Romanoff

Posted on

3 1

Kerberos Authenticated Setup with PostgreSQL on Linux

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

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up