Hi Stephen,
first: thanks a lot for replying!!!!!
On 20.02.23 15:17, Stephen Frost wrote:
* Tomas Pospisek (tpo2@xxxxxxxxxxxxx) wrote:
so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) via
Active Directory.
Looks like you're trying to do a bit more than that as you're using
GSSAPI (not SSPI, which would be the more typical method on Windows..)
and you're asking for an encrypted connection. Is there a reason you're
going down this particular route..?
The reason I'm using the GSSAPI is it's the only documented way to do
authentication from psql.exe against Active Directory that was able to
find. Even now I am unable to find *any* documentation on how to go
about doing auth from psql.exe to AD with SSPI. Would you have any
pointers to documentation or a howto (or a blog post or a stackoverflow
answer or...).
The reason I am apparently asking for encrypted communications is that I
apparently misinterpreted what `gssencmode`: I thought that it would
force the use of GSS but apparently it's forcing a connection encryption?
How do I force psql.exe to use GSS and/or SSPI then (instead of
username/password authentication that it seems to be doing by default).
Maybe the reason I'm using GSS is because what I really want to achieve
is to authenticate from QGIS with AD. QGIS (from osgeo4w) is shipping a
libpq that is linked against the kerberos library, so that's what I
have. I have no idea how libpq needs to be compiled so as to be able to
do SSPI?
Also, just in case I would find out how to get my hand (or build) a
psql/libpsql that *can* do SSPI - would I be able to swap out the
existing libpq for the libpq with SSPI support and dependend executable
would still be supposed to work?
Or am I completely misunderstanding everything?
psql (Linux) -> postgres (Linux) with authentication against Active
Directory does work.
That's good. One thing to know about Linux vs. Windows when it comes to
Kerberos (and GSSAPI/SSPI) is that the default service name is different
between them. See the libpq parameter krbsrvname documentation here:
https://www.postgresql.org/docs/current/libpq-connect.html
I quote that document:
"krbsrvname
Kerberos service name to use when authenticating with GSSAPI. This
must match the service name specified in the server configuration for
Kerberos authentication to succeed. (See also Section 21.6.) The default
value is normally postgres, but that can be changed when building
PostgreSQL via the --with-krb-srvnam option of configure. In most
environments, this parameter never needs to be changed. Some Kerberos
implementations might require a different service name, such as
Microsoft Active Directory which requires the service name to be in
upper case (POSTGRES).
"
I'm using the postgres server from apt.postgresql.org. I do not know
whether it has been built with `--with-krb-srvnam` but as far as I can
find out it was not.
Could you please give me a working example? Assuming I need to set
`krbsrvname=POSTGRES` then where would I have to set that? In
pg_service.conf? Or in krb5.conf? Or somewhere else?
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
That's a bit of an odd message to get if krbsrvname is this issue
though.
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).
Providing the specific messages would possibly be helpful here..
The message I get when libpq is not compiled against GSS is:
gssencmode value "require" invalid when GSSAPI support is not
compiled in
The .pg_service.conf file in the users $HOME directory looks like this:
[the_db]
host=dbserver.example.lan
port=5432
user=user@xxxxxxxxxxx
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.example.lan@xxxxxxxxxxx
[...]
This will list the ticket
Is this using MIT klist or Windows klist though?
It's using Windows klist.
But when using psql.exe it will not get a ticket for the service nor will it
apparently use the existing service ticket.
As mentioned above, GSSAPI and SSPI aren't the same thing and what I
suspect you're seeing here is that the Windows klist is using SSPI but
the psql you have is built with GSSAPI. There used to be a Kerberos For
Windows utility that would make the bridge between those two, as I
recall, put out by MIT but I'm not sure what the current state of it is.
It hasn't been needed for a very long time as SSPI generally works fine-
though we don't yet have SSPI support for encryption.
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.
psql wouldn't be accessing an actual keytab, it would be trying to
access a credential cache and it is failing on that, though if it was
just non-existant, I'd expect an error along those lines. Accessing the
Windows kerberos service would require either using SSPI, which psql can
be built to do, or using a bridge tool like KfW as mentioned above.
What you are saying seems to imply to me that I should be using SSPI. So
that's what I want to do. So:
* how can I verify that the libpq/psql.exe I have (from osgeo4w) have
been built with SSPI support?
* do you have some complete working config for psql.exe to access a
postgres server with authentication again AD? (I mean only the client
side as what is needed by psql.exe exclusively on a vanilla Windows box
in a AD domain - nothing else).
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?
That's part of KfW and it's the Kerberos libraries, not directly psql,
that's trying to access it. If you want to get this working, probably
the next step would be to look into KfW.
* has anybody ever succeeded in authenticating with psql.exe against Active
Directory?
Yes, many times, thuogh more typically using SSPI support in psql
instead of GSSAPI and using TLS/SSL for encryption.
Do I need to force SSL? Should it work without?
* can you maybe tell me what's wrong from the error message above?
* how can I proceed from here?
Either switch to using SSPI and TLS/SSL, or you could give KfW a try.
If you'd like encryption support with SSPI, that's a bit of a project.
PS: Any way to make GSS more talkative? At this moment all that I can get as
logs is the above "computer says no".
Well, you're hitting the same issue over and over, it looks like.
Usually it's more informative in the minor code.
I unfortunately do not understand the phrase "Usually it's more
informative in the minor code" :-(
*t
