Kerberos Authenticated Setup with PostgreSQL on Linux
Tags: kerberos, postgres, linux, setup
Published: 2022-09-21 21:51:32

### 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
```

### 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';
```

### Step #3

```
vi /var/lib/pgsql/14/data/pg_hba.conf
```
add line:

```
host all all 0.0.0.0/0 md5
```

### Step #4

```
vi /var/lib/pgsql/14/data/postgresql.conf
listen_addresses = '*'
```

### Step #5
Restart postgres

```
systemctl restart postgresql-14
systemctl status postgresql-14
```
### Step #6

```
[root@infra krb5kdc]# psql -h infra.labs.local -U postgres -d postgres -W
Password:
psql (14.2)
Type "help" for help.
postgres=#
```
### 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:
```
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
```
### 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/
```
### 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
```
### 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 ~]$
```
### 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=#
```
### 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'
```
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
```
make sure this line is commented:

```
# host all all 0.0.0.0/0 md5
```
### Step #13
Now we should reload parameters on the servers, restarting Postgres:

```
systemctl restart postgresql-14
systemctl status postgresql-14
```
### 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=#
```
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=#
```
* 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
```
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
```