Unable to get PostgreSQL 15 with Kerberos (GSS) working - Mailing list pgsql-general

Hi All

Would really like some help to get this working, I’m not sure where to turn next?

About things:

PostgreSQL 15

Running of RHEL8

Using official repo

Build-in OS version(s) of PostgreSQL are disabled

Postgresql15-server & postgresql15-contrib installed via dnf

PostgreSQL 15 up to date via dnf update

RHEL8

Active directory domain joined, Samba server with SSSD with Winbind (https://access.redhat.com/solutions/3802321)

Active Directory

Running in 2016 compatibility mode

Generally

Apart from not being able to get Kerberos working for PostgreSQL the system is stable and working as expected

Issue:

No matter what I try I don’t seem to be able to get the psl command locally to work using Kerberos.  I receive for following message:

FATAL:  GSSAPI authentication failed for user "postgres"

FATAL:  GSSAPI authentication failed for user myad.username@MYDOMAIN.NET

Using the kinit command I can get a Kerberos ticket both via the created keytab file for the postgres user or for my AD account.  I’ve even intentional let the ticket run out and received the warning on screen that my ‘Ticket expired’.

My Current Config:

/etc/krb5.conf

# To opt out of the system crypto-policies configuration of krb5, remove the

# symlink at /etc/krb5.conf.d/crypto-policies which will not be recreated.

includedir /etc/krb5.conf.d/

[logging]

    default = FILE:/var/log/krb5libs.log

    kdc = FILE:/var/log/krb5kdc.log

    admin_server = FILE:/var/log/kadmind.log

[libdefaults]

    default_realm = MYDOMAIN.NET

#    dns_lookup_realm = true

                dns_lookup_kdc = true

    forwardable = true

                udp_preference_limit = 1

# Values for next three parameters should be used from Default Domain Policy GPO

# Default Domain Policy \ Computer Configuration \ Policies \ Windows Settings \ ...

# ... \ Security Settings Account Policies \ Kerberos Policy

# Maximum lifetime for user ticket

ticket_lifetime = 10h

# Maximum lifetime for user ticket renewal

renew_lifetime = 7d

# Maximum tolerance for computer clock synchronization

clockskew = 300

[realms]

MYDOMAIN.NET  = {

     admin_server = uk-ref1-dc2.mydomain.net

                kdc = uk-ref1-dc2.mydomain.net

                kdc = uk-ref2-dc1.mydomain.net

                kdc = uk-ref3-dc1.mydomain.net

                kdc = uk-ref3-dc2.mydomain.net

}

[domain_realm]

.mydomain.net = MYDOMAIN.NET

 mydomain.net = MYDOMAIN.NET

Active Directory User for the Service Principal Name (SPN)

User created ‘pg_hostname’ (15 characters in length)

User properties changed

Delegation tab: Enable – Trust this user for delegation to any service (Kerberos Only)

Account tab: Enable – This account supports Kerberos AES 128 bit encryption

Account tab: Enable – This account supports Kerberos AES 256 bit encryption

Password reset at this point

Generate the postgres.keytab File

ktpass /out C:\11111\postgres.keytab /princ POSTGRES/hostname.mydomain.net@MYDOMAIN.NET /mapuser pg_hostname /crypto AES256-SHA1 +rndpass /target MYDOMAIN.NET -ptype KRB5_NT_PRINCIPAL

…Confirmed using ‘setspn -L pg_hostname’

Setup the postgres.keytab File on the PostgreSQL Server

Copied to /pgcluster/data

Owner/group set to postgres

Permissions set to 0400 (read)

Update the postgresql.conf to reference the postgres.keytab file

krb_server_keyfile = '/pgcluster/data/postgres.keytab'

Update the pg_hba.conf to Enable Kerberos Authentication

host    all             all             ::1/128                 gss

… only this while testing directly on the server

… place at top of IPv6 section

Created a Domain Test account via psql command

create user " myad.username@MYDOMAIN.NET"; CREATE ROLE

Restart PostgreSQL

systemctl restart postgresql-15

Testing

kinit -kt /pgcluster/data/postgres.keytab POSTGRES/hostname.mydomain.net@MYDOMAIN.NET

klist

Ticket cache: KCM:0:20151

Default principal: POSTGRES/hostname.mydomain.net@MYDOMAIN.NET

Valid starting     Expires            Service principal

23/02/24 10:19:12  23/02/24 20:19:12  krbtgt/MYDOMAIN.NET@MYDOMAIN.NET

        renew until 23/02/24 20:19:12

psql -h localhost -U postgres -d postgres

… logs show: GSSAPI authentication failed for user "postgres"

kinit myad.username@MYDOMAIN.NET

klist

Ticket cache: KCM:0:54648

Default principal: myad.username@MYDOMAIN.NET

Valid starting     Expires            Service principal

23/02/24 11:56:51  23/02/24 21:56:48  krbtgt/MYDOMAIN.NET@ MYDOMAIN.NET

        renew until 23/02/24 21:56:51

psql -h localhost -U myad.username -d postgres

… logs show: GSSAPI authentication failed for user " myad.username "

Tried increasing logging but did not get any additional info related to the failure