Kerberos Authenticated Setup with PostgreSQL on Linux

Dmitry Romanoff - Sep 21 '22 - - Dev Community

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
The Main Issue With Car Spare Key And What You Can Do To Fix It
Image
Indisputable Proof You Need Asbestos Government Compensation
Image
How to Train Your Cat to Use a Cat Flap
Image
react-dom.createRoot
Image
Cat Flap Cost Near Me
Image
It's Time To Extend Your How Much Is A Spare Car Key Options
Image
15 Terms That Everyone Within The Chesterfield 2 Seater Sofa Industry Should Know
Image
Then You've Found Your Chesterfield Leather Couch ... Now What?
Image
10 Quick Tips About Asbestos Cancer Law Lawyer Mesothelioma
Image
Bi-Fold Door Repair Isn't As Difficult As You Think
Image
Why No One Cares About Asbestos Mesothelioma Life Expectancy
Image
Check Valve Manufacturers: Ensuring Efficiency and Reliability in Fluid Control Systems
Image
10 Tell-Tale Signs You Need To Get A New Remote Key Repair
Image
5 Killer Quora Answers On Mesothelioma From Asbestos
Image
How To Create An Awesome Instagram Video About Keys Repair
Image
The Role of Data Science in Shaping Modern Economics

datascience, ai
Image
Atomic Design in Frontend (english)

frontend, architecture, react, typescript
Image
The Asbestos Lawyer Mesothelioma Case Study You'll Never Forget
Image
20 Trailblazers Lead The Way In Car Key Repair Cost
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player