Обсуждение: 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.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

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

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

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

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

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

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