Thread: Unable to get PostgreSQL 15 with Kerberos (GSS) working
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
Regards
Matt Dennison
Greetings, * Matthew Dennison (mail@matty-uk.co.uk) wrote: > 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 followingmessage: > FATAL: GSSAPI authentication failed for user "postgres" > FATAL: GSSAPI authentication failed for user myad.username@MYDOMAIN.NET [...] > kinit -kt /pgcluster/data/postgres.keytab POSTGRES/hostname.mydomain.net@MYDOMAIN.NET<mailto: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 Doesn't look like you're actually getting a PG tickets ... > psql -h localhost -U postgres -d postgres And this might be why. Don't use '-h localhost' because that'll, by default anyway, cause the Kerberos library to try to do reverse DNS on the address you are trying to connect to (::1/128, for example) ... and that possibly just resolves to 'localhost', which isn't the server's name that you're trying to connect to. If the rDNS lookup fails then we'll use what you provided- but again, that's just 'localhost' and isn't the server's name in the AD realm. Try doing: psql -h hostname.mydomain.net -U postgres -d postgres instead, and update your pg_hba.conf to match those connections which are coming into the system's actual IP address instead of only matching loaclhost connection attempts. You're definitely not going to have any succcess until you can do a 'klist' and see a POSTGRES/hostname.mydomain.net ticket listed after doing your psql attempt. Thanks, Stephen
Attachment
I have subsequence discovered that the psql command running from remote Windows (server/client) and RHEL8 works as expectedusing GSS. PGAdmin4 also works via Kerberos (was on my list of things to get working). It's just locally on theserver psql will not work to postgresql running on the same server. I really don't get it, but have decided I can live without it working on the server. R -----Original Message----- From: Stephen Frost <sfrost@snowman.net> Sent: Monday, February 26, 2024 7:33 PM To: Matthew Dennison <mail@matty-uk.co.uk> Cc: pgsql-general@lists.postgresql.org Subject: Re: Unable to get PostgreSQL 15 with Kerberos (GSS) working Greetings, * Matthew Dennison (mail@matty-uk.co.uk) wrote: > 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 followingmessage: > FATAL: GSSAPI authentication failed for user "postgres" > FATAL: GSSAPI authentication failed for user > myad.username@MYDOMAIN.NET [...] > kinit -kt /pgcluster/data/postgres.keytab > POSTGRES/hostname.mydomain.net@MYDOMAIN.NET<mailto:POSTGRES/hostname.m > ydomain.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 Doesn't look like you're actually getting a PG tickets ... > psql -h localhost -U postgres -d postgres And this might be why. Don't use '-h localhost' because that'll, by default anyway, cause the Kerberos library to try todo reverse DNS on the address you are trying to connect to (::1/128, for example) ... and that possibly just resolves to'localhost', which isn't the server's name that you're trying to connect to. If the rDNS lookup fails then we'll use whatyou provided- but again, that's just 'localhost' and isn't the server's name in the AD realm. Try doing: psql -h hostname.mydomain.net -U postgres -d postgres instead, and update your pg_hba.conf to match those connections which are coming into the system's actual IP address insteadof only matching loaclhost connection attempts. You're definitely not going to have any succcess until you can do a 'klist' and see a POSTGRES/hostname.mydomain.net ticketlisted after doing your psql attempt. Thanks, Stephen
Greetings, * Matthew Dennison (mail@matty-uk.co.uk) wrote: > I have subsequence discovered that the psql command running from remote Windows (server/client) and RHEL8 works as expectedusing GSS. PGAdmin4 also works via Kerberos (was on my list of things to get working). It's just locally on theserver psql will not work to postgresql running on the same server. > > I really don't get it, but have decided I can live without it working on the server. Perhaps I wasn't clear- it's not going to work if you do: psql -h localhost Instead, try doing: psql -h hostname.mydomain.net -U postgres -d postgres Basically, Kerberos isn't really set up to work when the host you're connecting to is named 'localhost' because the name of the host is how you figure out what ticket to acquire from the KDC. Thanks, Stephen
Attachment
On 28 Feb 2024, at 17:11, Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
* Matthew Dennison (mail@matty-uk.co.uk) wrote:I have subsequence discovered that the psql command running from remote Windows (server/client) and RHEL8 works as expected using GSS. PGAdmin4 also works via Kerberos (was on my list of things to get working). It's just locally on the server psql will not work to postgresql running on the same server.I really don't get it, but have decided I can live without it working on the server.
Perhaps I wasn't clear- it's not going to work if you do:
psql -h localhost
Instead, try doing:
psql -h hostname.mydomain.net -U postgres -d postgres
Basically, Kerberos isn't really set up to work when the host you're
connecting to is named 'localhost' because the name of the host is how
you figure out what ticket to acquire from the KDC.
Thanks,
Stephen
Attachment
Greetings, * Matthew Dennison (mail@matty-uk.co.uk) wrote: > Sorry, yes I tried that and received the same results. Did you try doing a 'klist' after? Would be interesting to know if you got a Kerberos ticket or not. Thanks, Stephen
Attachment
Here's the results: psql: error: connection to server at "hostname.mydomain.net" (::1), port 5432 failed: GSSAPI continuation error: UnspecifiedGSS failure. Minor code may provide more information: No Kerberos credentials available (default cache: KCM:) If I run kinit and get Kerberos ticket ahead of running the command I then receive: psql: error: connection to server at " hostname.mydomain.net " (::1), port 5432 failed: could not initiate GSSAPI securitycontext: Unspecified GSS failure. Minor code may provide more information: Server not found in Kerberos database connection to server at " hostname.mydomain.net " (::1), port 5432 failed: GSSAPI continuation error: Unspecified GSS failure. Minor code may provide more information: Server not found in Kerberos database R -----Original Message----- From: Stephen Frost <sfrost@snowman.net> Sent: Wednesday, February 28, 2024 6:35 PM To: Matthew Dennison <mail@matty-uk.co.uk> Cc: pgsql-general@lists.postgresql.org Subject: Re: Unable to get PostgreSQL 15 with Kerberos (GSS) working Greetings, * Matthew Dennison (mail@matty-uk.co.uk) wrote: > Sorry, yes I tried that and received the same results. Did you try doing a 'klist' after? Would be interesting to know if you got a Kerberos ticket or not. Thanks, Stephen
Greetings, * Matthew Dennison (mail@matty-uk.co.uk) wrote: > If I run kinit and get Kerberos ticket ahead of running the command I then receive: Right, have to kinit first. > psql: error: connection to server at " hostname.mydomain.net " (::1), port 5432 failed: could not initiate GSSAPI securitycontext: Unspecified GSS failure. Minor code may provide more information: Server not found in Kerberos database Note the minor code info- Server not found in Kerberos database. Also note that it apparently connected to '::1' which would be localhost- by default, the Kerberos library will do a reverse DNS lookup on the IP that it connected to, to get the canonical name of the host. That can be disabled if necessary but really, when you use 'hostname.mydomain.net' that should be returning the host's real IP address and not ::1. I'd suggest looking into fixing that (maybe it's in /etc/hosts that way?) and then this should work. There are options to disable reverse DNS for Kerberos too and then it'll use whatever you pass to '-h' to look the host up in the Kerberos database, but that's really not ideal. Thanks, Stephen
Attachment
Should a FQDN resolve to ::1?Here's the results: psql: error: connection to server at "hostname.mydomain.net" (::1), port 5432 failed: GSSAPI continuation error: Unspecified GSS failure. Minor code may provide more information: No Kerberos credentials available (default cache: KCM:) If I run kinit and get Kerberos ticket ahead of running the command I then receive: psql: error: connection to server at " hostname.mydomain.net " (::1), port 5432 failed: could not initiate GSSAPI security context: Unspecified GSS failure. Minor code may provide more information: Server not found in Kerberos database connection to server at " hostname.mydomain.net " (::1), port 5432 failed: GSSAPI continuation error: Unspecified GSS failure. Minor code may provide more information: Server not found in Kerberos database
Host file looks like:
127.0.0.1 localhost hostname hostname.mydomain.net
::1 localhost hostname hostname.mydomain.net
10.204.50.65 hostname hostname.mydomain.net
I also tried commenting out the ::1 line, but the issue moved to 127.0.0.1, commented that out and the issue moved to the IP. I really don’t get it. However, when I finally realised it was a localised issue (after days) I was able to move forward. No idea why it will not work on the server itself, but it is something I can live with.
Regards
Matt Dennison
From: Rob Sargent <robjsargent@gmail.com>
Sent: Thursday, February 29, 2024 2:56 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: Unable to get PostgreSQL 15 with Kerberos (GSS) working
On 2/29/24 01:18, Matthew Dennison wrote:
Here's the results:psql: error: connection to server at "hostname.mydomain.net" (::1), port 5432 failed: GSSAPI continuation error: Unspecified GSS failure. Minor code may provide more information: No Kerberos credentials available (default cache: KCM:)If I run kinit and get Kerberos ticket ahead of running the command I then receive:psql: error: connection to server at " hostname.mydomain.net " (::1), port 5432 failed: could not initiate GSSAPI security context: Unspecified GSS failure. Minor code may provide more information: Server not found in Kerberos databaseconnection to server at " hostname.mydomain.net " (::1), port 5432 failed: GSSAPI continuation error: Unspecified GSS failure. Minor code may provide more information: Server not found in Kerberos database
Should a FQDN resolve to ::1?
Greetings, * Matthew Dennison (mail@matty-uk.co.uk) wrote: > Host file looks like: > > 127.0.0.1 localhost hostname hostname.mydomain.net > ::1 localhost hostname hostname.mydomain.net > 10.204.50.65 hostname hostname.mydomain.net > > I also tried commenting out the ::1 line, but the issue moved to 127.0.0.1, commented that out and the issue moved to theIP. I really don’t get it. However, when I finally realised it was a localised issue (after days) I was able to moveforward. No idea why it will not work on the server itself, but it is something I can live with. Once you got it to just the IP, it was trying to look up 'hostname' not 'hostname.mydomain.net' in the KDC, I suspect, since that's first. Might be interesting to try it with: 10.204.50.65 hostname.mydomain.net hostname instead. I've no doubt that it can be made to work if we get the DNS bits sorted. Thanks! Stephen