can't get psql authentication against Active Directory working

From: Tomas Pospisek <tpo2(at)sourcepole(dot)ch>
To: pgsql-general(at)postgresql(dot)org
Subject: can't get psql authentication against Active Directory working
Date: 2023-02-18 14:02:00
Message-ID: 08b836a7-272a-2309-da45-ac691fccacb8@sourcepole.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello all,

so I'm trying to authenticate psql (on Windows) -> postgres (on Linux)
via Active Directory.

psql (Linux) -> postgres (Linux) with authentication against Active
Directory does work.

However the same with psql.exe on Windows does not. I get:

D:\>C:\OSGeo4W\bin\psql.exe service=the_db
psql: error: connection to server at "dbserver.example.lan
(192.168.4.104), port 5432 failed: could not initiate GSSAPI
security context: No credentials were supplied, or the credentials
were unavailable or inaccessible: Internal credentials cache error

psql.exe from the OSGeo4W QGIS Installer *does* include GSS support. (I
have tried with a different psql.exe without GSS support and it would
tell me that it does not support GSS).

The .pg_service.conf file in the users $HOME directory looks like this:

[the_db]
host=dbserver.example.lan
port=5432
user=user(at)EXAMPLE(dot)LAN
gssencmode=require

This same pg_service.conf does work for psql (Linux).

Also getting a Kerberos ticket for the service on Windows does work:

D:\> klist get postgres/dbserver(dot)example(dot)lan(at)EXAMPLE(dot)LAN
[...]
This will list the ticket

But when using psql.exe it will not get a ticket for the service nor
will it apparently use the existing service ticket.

I have tried to trace psql.exe with Window's Process Monitor and I can't
see it accessing no keytab file (I'm not sure whether a keytab file
exists at all under Windows or if psql.exe doesn't instead need to
access some Windows service). I see that psql.exe will open and close a
TCP connection to dbserver.example.lan, however as far as I can see that
connection is completely irrelevant for the aquisition of a Kerberos
ticket for the service since that is a business purely between psql.exe
and Active Directory or respectively between psql.exe and the
credentials cache. And there is no other TCP connection being opened to
anywhere from psql.exe.

What I find suspicious about the error above is "... the credentials
were unavailable or inaccessible: Internal credentials cache error",
since that looks like either psql.exe can't access the (inexisting)
keytab file, or it can't access Window's Kerberos service.

Also, I see that psql.exe is trying to access a ccapiserver.exe which
does not exist. Should psql.exe be able to access that ccapiserver.exe
file? That means is the OSGeo4W QGIS installer, that also installs all
things necessary for psql missing that ccapiserver.exe executable?

* has anybody ever succeeded in authenticating with psql.exe against
Active Directory?
* can you maybe tell me what's wrong from the error message above?
* how can I proceed from here?

Thanks a lot for any pointers and/or help!!!
*t

PS: Any way to make GSS more talkative? At this moment all that I can
get as logs is the above "computer says no".

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Siddharth Jain 2023-02-18 14:46:59 Does Postgres 14 have a query cache?
Previous Message Ryan MYJ 2023-02-18 00:00:18 Hi All,