can't get psql authentication against Active Directory working

On 18/02/2023 15:02 CET Tomas Pospisek <tpo2@sourcepole.ch> wrote:

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@EXAMPLE.LAN
gssencmode=require

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

On Windows the service file is not read from $home/.pg_service.conf but
$env:appdata/postgresql/.pg_service.conf (or %appdata%/postgresql/.pg_service.conf
when using cmd.exe.)

--
Erik

On 18.02.23 17:16, Erik Wienhold wrote:

On 18/02/2023 15:02 CET Tomas Pospisek <tpo2@sourcepole.ch> wrote:

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@EXAMPLE.LAN
gssencmode=require

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

On Windows the service file is not read from $home/.pg_service.conf but
$env:appdata/postgresql/.pg_service.conf (or %appdata%/postgresql/.pg_service.conf
when using cmd.exe.)

Thank you Erik,

the location of the .pg_service.conf file is not the problem, as I am
seeing that psql.exe is reacting to its contents, that is authenticating
differently depending on whether gssencmode is set or not (maybe I noted
its location wrongly, since i've written the post from my notes, as I do
not have access to the machines in question all the time).

Do you have authentication from psql.exe against Active Directory working?
*t

Greetings,

* Tomas Pospisek (tpo2@sourcepole.ch) 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..?

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

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 .pg_service.conf file in the users $HOME directory looks like this:

[the_db]
host=dbserver.example.lan
port=5432
user=user@EXAMPLE.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.example.lan@EXAMPLE.LAN
[...]
This will list the ticket

Is this using MIT klist or Windows klist though?

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.

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.

* 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.

Thanks,

Stephen

Hi Stephen,

first: thanks a lot for replying!!!!!

On 20.02.23 15:17, Stephen Frost wrote:

* Tomas Pospisek (tpo2@sourcepole.ch) 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@EXAMPLE.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.example.lan@EXAMPLE.LAN
[...]
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

Greetings,

* Tomas Pospisek (tpo2@sourcepole.ch) wrote:

On 20.02.23 15:17, Stephen Frost wrote:

* Tomas Pospisek (tpo2@sourcepole.ch) 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.

I mean ... https://www.postgresql.org/docs/current/sspi-auth.html

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...).

Sure, here's a blog post that I wrote about doing exactly that:

https://www.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication

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?

gssencmode indeed requires a GSS encrypted connection. Unfortunately,
there isn't a way to force GSS or SSPI be used for authentication today
(outside of GSS with encryption) from the client side, though that's
something that's been discussed and I think most people feel would be
good to have. That said, if the server is configured in pg_hba for gss
(or sspi), then the client will try to authenticate that way.

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).

The server's hba needs to be configured for gss (if the server is Linux)
or sspi (if the server is on Windows) and then the client will be
informed that's what the server wants when it connects and it'll try to
authenticate that way. It's not possible currently to force the client
to only try GSS or SSPI, what it will try is mostly driven by the server
side saying what the server wants the client to authenticate with.

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?

PostgreSQL, when built on Windows (including libpq), is always built
with SSPI support. That said, if it's also built with GSSAPI, then SSPI
is preferred but you can set 'gsslib' to 'gssapi' to prefer gssapi be
used instead. Now, if you're setting gssencmode to prefer or require
and the library is built with GSSAPI then we're going to try GSSAPI
first to try to set up the encrypted connection.

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?

Your libpq is almost certainly also built with SSPI.

Or am I completely misunderstanding everything?

Don't think you're completely misunderstanding everything, just a few
missing bits here and there that I've hopefully helped clear up. :)

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?

I'd suggest looking over the blog post that I reference above as it
depends on how the service is configured in AD. You could also look at
the keytab file to see what the service principal is. You do that
using: klist -k /path/to/keytab

What's necessary is for everything to agree. The klist -k /keytab
will tell you what's in the keytab file on the PG server and that's
probably where to start from- if it's 'postgres' then you probably don't
need to configure krbsrvname on anything, if it's 'POSTGRES' then you'll
need to set krbsrvname in the connection string or service config on the
client side and then on the server set krb_srvname also to 'POSTGRES'.

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

Yup, that does match up with what I'd expect.

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

[the_db]
host=dbserver.example.lan
port=5432
user=user@EXAMPLE.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.example.lan@EXAMPLE.LAN
[...]
This will list the ticket

Is this using MIT klist or Windows klist though?

It's using Windows klist.

That's using SSPI then, just to be clear.

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:

Yes, using SSPI when coming from Windows is almost certainly the simpler
approach and gets you the benefit of strong authentication. You'll
probably want to also use TLS/SSL for encryption of the connection and
then use verify-full to make sure that the server is who you are trying
to connect to (the Kerberos authentication also does this, but it
doesn't hurt to also make sure the certificate presented by the server
matches who you are trying to connect to and that it's trusted by one of
your CAs).

* how can I verify that the libpq/psql.exe I have (from osgeo4w) have been
built with SSPI support?

It almost certainly was built with SSPI. SSPI support was added to
PostgreSQL in version 8.3 which was released in 2008. Hopefully you're
not using a library older than that ...

* 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).

See the blog post.

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?

Yes, it should work just fine without TLS/SSL and the authentication
process will be encrypted and secure, but after the authentication is
complete the rest of the traffic will be transmitted in the clear over
the network (unless you've got IPSec or a VPN or something) which is
generally not ideal.

* 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" :-(

There's major codes and minor codes returned from the GSS APIs, the
major code being "it didn't work" and the minor code being "here's why".
In the above case, the major code would have been the "could not
initiate GSSAPI security context" while the minor code is what gets you
the "Internal credentials cache error" bit. Of course, you're hitting
that very early on which is why you just keep hitting that error instead
of something more informative.

Hopefully the blog post and this discussion helps you make some
progress. I wonder how close you are to everything working if you'd
just drop the 'gssencmode' setting, or maybe explicitly set it to
'disable'...

Thanks,

Stephen

Hi Stephen,

again thanks a lot for trying to help me!

On 21.02.23 16:29, Stephen Frost wrote:

* Tomas Pospisek (tpo2@sourcepole.ch) wrote:

On 20.02.23 15:17, Stephen Frost wrote:

* Tomas Pospisek (tpo2@sourcepole.ch) 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.

I mean ... https://www.postgresql.org/docs/current/sspi-auth.html

Were should the settings that are discussed in that document be
configured? In pg_hba.conf?

That document says:

"SSPI authentication only works when both server and client are running
Windows, or, on non-Windows platforms, when GSSAPI is available."

I interpret that phrase like this:

* there's a case where both server and client are running Windows
(doesn't apply to me)
* there's a case where both are running non-Windows (doesn't apply to me
either - server is Linux, client is Windows)

So that's an extremely terse docu that is not clear at all to me.

I'd suggest to change that phrase into:

"SSPI authentication works when both server and client are running
Windows. When server or client are on non-Windows platforms then those
need to use GSSAPI."

- assuming that my interpretation of that super terse docu is correct of
course.

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...).

Sure, here's a blog post that I wrote about doing exactly that:

https://www.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication

Note that that document doesn't mention SSPI a single time.

The ultra terse Postgres docu is using terms such as "SAM", "negotiate
mode", "UPN", without a reference to their definition, which leaves to
Windows noobs like me (and equaly Windows non-noobs) without a clue.
Searching "kerberos SAM" or "SSPI negotiate mode" on duckduckgo doesn't
seem to lead to useful hits. So the documentation seems to be geared to
experts of both Kerberos and Active Directory and Windows technology
alike which as far as I can tell is a very rare specimen.

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?

gssencmode indeed requires a GSS encrypted connection. Unfortunately,
there isn't a way to force GSS or SSPI be used for authentication today
(outside of GSS with encryption) from the client side, though that's
something that's been discussed and I think most people feel would be
good to have. That said, if the server is configured in pg_hba for gss
(or sspi), then the client will try to authenticate that way.

As far as I can see it is *not possible* to configure the server for
SSPI ("or sspi")? It's only possible to configure it for GSS *and* SSPI
at the same time (?) assuming the server is running on a non-Windows
platform?

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).

The server's hba needs to be configured for gss (if the server is Linux)
or sspi (if the server is on Windows) and then the client will be
informed that's what the server wants when it connects and it'll try to
authenticate that way. It's not possible currently to force the client
to only try GSS or SSPI, what it will try is mostly driven by the server
side saying what the server wants the client to authenticate with.

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?

PostgreSQL, when built on Windows (including libpq), is always built
with SSPI support. That said, if it's also built with GSSAPI, then SSPI
is preferred but you can set 'gsslib' to 'gssapi' to prefer gssapi be
used instead. Now, if you're setting gssencmode to prefer or require
and the library is built with GSSAPI then we're going to try GSSAPI
first to try to set up the encrypted connection.

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?

Your libpq is almost certainly also built with SSPI.

Or am I completely misunderstanding everything?

Don't think you're completely misunderstanding everything, just a few
missing bits here and there that I've hopefully helped clear up. :)

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?

I'd suggest looking over the blog post that I reference above as it
depends on how the service is configured in AD. You could also look at
the keytab file to see what the service principal is. You do that
using: klist -k /path/to/keytab

What's necessary is for everything to agree. The klist -k /keytab
will tell you what's in the keytab file on the PG server and that's
probably where to start from- if it's 'postgres' then you probably don't
need to configure krbsrvname on anything, if it's 'POSTGRES' then you'll
need to set krbsrvname in the connection string or service config on the
client side and then on the server set krb_srvname also to 'POSTGRES'.

Ack, thanks a lot!

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

Yup, that does match up with what I'd expect.

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

[the_db]
host=dbserver.example.lan
port=5432
user=user@EXAMPLE.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.example.lan@EXAMPLE.LAN
[...]
This will list the ticket

Is this using MIT klist or Windows klist though?

It's using Windows klist.

That's using SSPI then, just to be clear.

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:

Yes, using SSPI when coming from Windows is almost certainly the simpler
approach and gets you the benefit of strong authentication. You'll
probably want to also use TLS/SSL for encryption of the connection and
then use verify-full to make sure that the server is who you are trying
to connect to (the Kerberos authentication also does this, but it
doesn't hurt to also make sure the certificate presented by the server
matches who you are trying to connect to and that it's trusted by one of
your CAs).

* how can I verify that the libpq/psql.exe I have (from osgeo4w) have been
built with SSPI support?

It almost certainly was built with SSPI. SSPI support was added to
PostgreSQL in version 8.3 which was released in 2008. Hopefully you're
not using a library older than that ...

* 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).

See the blog post.

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?

Yes, it should work just fine without TLS/SSL and the authentication
process will be encrypted and secure, but after the authentication is
complete the rest of the traffic will be transmitted in the clear over
the network (unless you've got IPSec or a VPN or something) which is
generally not ideal.

* 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" :-(

There's major codes and minor codes returned from the GSS APIs, the
major code being "it didn't work" and the minor code being "here's why".
In the above case, the major code would have been the "could not
initiate GSSAPI security context" while the minor code is what gets you
the "Internal credentials cache error" bit. Of course, you're hitting
that very early on which is why you just keep hitting that error instead
of something more informative.

Hopefully the blog post and this discussion helps you make some
progress. I wonder how close you are to everything working if you'd
just drop the 'gssencmode' setting, or maybe explicitly set it to
'disable'...

We dropped the gssencmode setting and were still getting the "Internal
credentials cache error" error. That said, I'll have another session on
probably Monday and I'll try to start from scratch, use the psql.exe
that is coming with vanilla QGIS (without GSS support) (and not the one
from OSGeo with compiled in GSS support) so that it absolutely should
use SSPI and not use GSS at all.

Hopefully that will work. Wish me luck :-D! Thanks a lot for helping
Stephen!!!
*t

Greetings,

* Tomas Pospisek (tpo2@sourcepole.ch) wrote:

again thanks a lot for trying to help me!

Sure, would like to see it work for you.

On 21.02.23 16:29, Stephen Frost wrote:

* Tomas Pospisek (tpo2@sourcepole.ch) wrote:

On 20.02.23 15:17, Stephen Frost wrote:

* Tomas Pospisek (tpo2@sourcepole.ch) 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.

I mean ... https://www.postgresql.org/docs/current/sspi-auth.html

Were should the settings that are discussed in that document be configured?
In pg_hba.conf?

Yes, it's in that part of the documentation. Note that you may not need
to actually configure any of those options..

That document says:

"SSPI authentication only works when both server and client are running
Windows, or, on non-Windows platforms, when GSSAPI is available."

I interpret that phrase like this:

* there's a case where both server and client are running Windows (doesn't
apply to me)
* there's a case where both are running non-Windows (doesn't apply to me
either - server is Linux, client is Windows)

So that's an extremely terse docu that is not clear at all to me.

I'd suggest to change that phrase into:

"SSPI authentication works when both server and client are running Windows.
When server or client are on non-Windows platforms then those need to use
GSSAPI."

- assuming that my interpretation of that super terse docu is correct of
course.

No, that's not correct. The blog post that I referenced is actually
using SSPI on the client and GSS on the server and it works because
they're compatible with each other on the wire. Perhaps we could
improve the documentation you reference above but it's not actually
wrong as-is. Perhaps this would be clearer:

SSPI and GSS are wireline compatible and can be mixed and matched
between clients and servers (where support for GSS is built into the
client library or the server), provided that there is an encryption
method which both will accept.

The encryption method bit is more of a historical artifact at this
point as modern systems have compatible AES-based encryption methods,
but that wasn't always the case. It's also possible that it'll become
an issue in the future but at least for the moment most installations
have an AES-based compatible encryption method.

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...).

Sure, here's a blog post that I wrote about doing exactly that:

https://www.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication

Note that that document doesn't mention SSPI a single time.

It doesn't actually need to because SSPI is the default and it all just
works.. I suppose I could have added in somewhere "SSPI is used on the
client for this", perhaps, but the blog post was more focused on "this
is what you do to make it work" and you don't really need to know that
the API that the client is using in this case happens to be called SSPI.

The ultra terse Postgres docu is using terms such as "SAM", "negotiate
mode", "UPN", without a reference to their definition, which leaves to
Windows noobs like me (and equaly Windows non-noobs) without a clue.

I'm certainly all for improving the documentation. Not sure that just
spelling those out would really be enough and I'm rather confident that
trying to explain all of how Kerberos and AD work wouldn't really be
maintainable in our documentation. Still, specific suggestion on how we
can improve would be great.

Searching "kerberos SAM" or "SSPI negotiate mode" on duckduckgo doesn't seem
to lead to useful hits. So the documentation seems to be geared to experts
of both Kerberos and Active Directory and Windows technology alike which as
far as I can tell is a very rare specimen.

Active Directory experts certainly should be generally familiar with
Kerberos (and the terms you reference above are actually all AD ones,
really..), so I'd argue that the documentation is mainly focused on
helping those individuals. I do generally agree that it would be good
if there were more folks out there who understood this stuff. I'm not
sure how to go about making that happen though.

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?

gssencmode indeed requires a GSS encrypted connection. Unfortunately,
there isn't a way to force GSS or SSPI be used for authentication today
(outside of GSS with encryption) from the client side, though that's
something that's been discussed and I think most people feel would be
good to have. That said, if the server is configured in pg_hba for gss
(or sspi), then the client will try to authenticate that way.

As far as I can see it is *not possible* to configure the server for SSPI
("or sspi")? It's only possible to configure it for GSS *and* SSPI at the
same time (?) assuming the server is running on a non-Windows platform?

As I mention above, on the wire GSS and SSPI are compatible, so a server
on a non-Windows platform configured with GSS is also going to work for
clients using SSPI (and, indeed, the server isn't really going to even
directly be able to tell that the client is using SSPI..).

Maybe I can explain this a bit better-

GSS and SSPI are *APIs* into the respective Kerberos libraries on those
systems. You can consider what's spoken on the actual wire between the
systems to be 'generic Kerberos'.

Hopefully the blog post and this discussion helps you make some
progress. I wonder how close you are to everything working if you'd
just drop the 'gssencmode' setting, or maybe explicitly set it to
'disable'...

We dropped the gssencmode setting and were still getting the "Internal
credentials cache error" error. That said, I'll have another session on
probably Monday and I'll try to start from scratch, use the psql.exe that is
coming with vanilla QGIS (without GSS support) (and not the one from OSGeo
with compiled in GSS support) so that it absolutely should use SSPI and not
use GSS at all.

Hrmpf. That's certainly curious that it still gave you that error..
Was it also still trying to call out to the MIT Kerberos For Windows
services? I wonder if maybe somewhere else the gssencmode was still
getting picked up as being set..

Hopefully that will work. Wish me luck :-D! Thanks a lot for helping
Stephen!!!

Sure, looking forward to hearing what happens next.

Thanks!

Stephen

Hi Stephen,

On 25.02.23 00:52, Stephen Frost wrote:

* Tomas Pospisek (tpo2@sourcepole.ch) wrote:

On 21.02.23 16:29, Stephen Frost wrote:

* Tomas Pospisek (tpo2@sourcepole.ch) wrote:

On 20.02.23 15:17, Stephen Frost wrote:

* Tomas Pospisek (tpo2@sourcepole.ch) wrote:

That [SSPI] document says:

"SSPI authentication only works when both server and client are running
Windows, or, on non-Windows platforms, when GSSAPI is available."

I interpret that phrase like this:

* there's a case where both server and client are running Windows (doesn't
apply to me)
* there's a case where both are running non-Windows (doesn't apply to me
either - server is Linux, client is Windows)

So that's an extremely terse docu that is not clear at all to me.

I'd suggest to change that phrase into:

"SSPI authentication works when both server and client are running Windows.
When server or client are on non-Windows platforms then those need to use
GSSAPI."

- assuming that my interpretation of that super terse docu is correct of
course.

No, that's not correct. The blog post that I referenced is actually
using SSPI on the client and GSS on the server and it works because
they're compatible with each other on the wire. Perhaps we could
improve the documentation you reference above but it's not actually
wrong as-is. Perhaps this would be clearer:

SSPI and GSS are wireline compatible and can be mixed and matched
between clients and servers (where support for GSS is built into the
client library or the server), provided that there is an encryption
method which both will accept.

The encryption method bit is more of a historical artifact at this
point as modern systems have compatible AES-based encryption methods,
but that wasn't always the case. It's also possible that it'll become
an issue in the future but at least for the moment most installations
have an AES-based compatible encryption method.

I have posted a suggestion for an improvement (via the form) to the
pgsql-docs mailing list. Lets see what comes out of that.

I like the idea to document the *why* behind SSPI and GSS, however I
don't understand enough about it to propose a documentation improvement.

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...).

Sure, here's a blog post that I wrote about doing exactly that:

https://www.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication

Note that that document doesn't mention SSPI a single time.

It doesn't actually need to because SSPI is the default and it all just
works..

I can confirm the "just works" part since my setup now indeed *does* work.

However the existing documentation on GSS/Kerberos/SSPI/Active directory
is extremely terse and sparse. So being more explicit and being clearer
would improve the situation I think. Are you able and interested to
change the Blog article?

I'd suggest to add a paragraph like this:

Using a Postgresql client on Windows

If you use a Postgresql client on Windows then that client should
be able to automatically use Windows' SSPI API, which will do the
authentication via Active Directory without any further
configuration necessary.

I suppose I could have added in somewhere "SSPI is used on the
client for this", perhaps, but the blog post was more focused on "this
is what you do to make it work" and you don't really need to know that
the API that the client is using in this case happens to be called SSPI.

The ultra terse Postgres docu is using terms such as "SAM", "negotiate
mode", "UPN", without a reference to their definition, which leaves to
Windows noobs like me (and equaly Windows non-noobs) without a clue.

I'm certainly all for improving the documentation. Not sure that just
spelling those out would really be enough and I'm rather confident that
trying to explain all of how Kerberos and AD work wouldn't really be
maintainable in our documentation. Still, specific suggestion on how we
can improve would be great.

+1 - see my post to the pgsql-docs mailing list for a starter.

Searching "kerberos SAM" or "SSPI negotiate mode" on duckduckgo doesn't seem
to lead to useful hits. So the documentation seems to be geared to experts
of both Kerberos and Active Directory and Windows technology alike which as
far as I can tell is a very rare specimen.

Active Directory experts certainly should be generally familiar with
Kerberos (and the terms you reference above are actually all AD ones,
really..), so I'd argue that the documentation is mainly focused on
helping those individuals. I do generally agree that it would be good
if there were more folks out there who understood this stuff. I'm not
sure how to go about making that happen though.

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?

gssencmode indeed requires a GSS encrypted connection. Unfortunately,
there isn't a way to force GSS or SSPI be used for authentication today
(outside of GSS with encryption) from the client side, though that's
something that's been discussed and I think most people feel would be
good to have. That said, if the server is configured in pg_hba for gss
(or sspi), then the client will try to authenticate that way.

As far as I can see it is *not possible* to configure the server for SSPI
("or sspi")? It's only possible to configure it for GSS *and* SSPI at the
same time (?) assuming the server is running on a non-Windows platform?

As I mention above, on the wire GSS and SSPI are compatible, so a server
on a non-Windows platform configured with GSS is also going to work for
clients using SSPI (and, indeed, the server isn't really going to even
directly be able to tell that the client is using SSPI..).

Maybe I can explain this a bit better-

GSS and SSPI are *APIs* into the respective Kerberos libraries on those
systems. You can consider what's spoken on the actual wire between the
systems to be 'generic Kerberos'.

AFAI understand AD is using public key (asymmetric) encryption as
default where as out of the box MIT Kerberos doesn't? (As opposed to
FreeIPA that *does* use asymmetric encryption), so I'd expect that there
*are* difference between vanilla Kerberos and AD "on the wire"?

Hopefully the blog post and this discussion helps you make some
progress. I wonder how close you are to everything working if you'd
just drop the 'gssencmode' setting, or maybe explicitly set it to
'disable'...

We dropped the gssencmode setting and were still getting the "Internal
credentials cache error" error. That said, I'll have another session on
probably Monday and I'll try to start from scratch, use the psql.exe that is
coming with vanilla QGIS (without GSS support) (and not the one from OSGeo
with compiled in GSS support) so that it absolutely should use SSPI and not
use GSS at all.

Hrmpf. That's certainly curious that it still gave you that error..
Was it also still trying to call out to the MIT Kerberos For Windows
services? I wonder if maybe somewhere else the gssencmode was still
getting picked up as being set..

I guess this would be worth verifying for all the poor souls up there
that get stuck in the same morass. However I do not have direct access
to AD systems and so testing and doublechecking that is procedurally
really involved :-(

Hopefully that will work. Wish me luck :-D! Thanks a lot for helping
Stephen!!!

Sure, looking forward to hearing what happens next.

So it's working and I very grateful that you helped!!! Many, many, many
thanks. If we ever meet in real life, then the beers are on me!
*t

Greetings,

* Tomas Pospisek (tpo2@sourcepole.ch) wrote:

On 25.02.23 00:52, Stephen Frost wrote:

* Tomas Pospisek (tpo2@sourcepole.ch) wrote:

On 21.02.23 16:29, Stephen Frost wrote:

* Tomas Pospisek (tpo2@sourcepole.ch) wrote:

On 20.02.23 15:17, Stephen Frost wrote:

* Tomas Pospisek (tpo2@sourcepole.ch) wrote:

That [SSPI] document says:

"SSPI authentication only works when both server and client are running
Windows, or, on non-Windows platforms, when GSSAPI is available."

I interpret that phrase like this:

* there's a case where both server and client are running Windows (doesn't
apply to me)
* there's a case where both are running non-Windows (doesn't apply to me
either - server is Linux, client is Windows)

So that's an extremely terse docu that is not clear at all to me.

I'd suggest to change that phrase into:

"SSPI authentication works when both server and client are running Windows.
When server or client are on non-Windows platforms then those need to use
GSSAPI."

- assuming that my interpretation of that super terse docu is correct of
course.

No, that's not correct. The blog post that I referenced is actually
using SSPI on the client and GSS on the server and it works because
they're compatible with each other on the wire. Perhaps we could
improve the documentation you reference above but it's not actually
wrong as-is. Perhaps this would be clearer:

SSPI and GSS are wireline compatible and can be mixed and matched
between clients and servers (where support for GSS is built into the
client library or the server), provided that there is an encryption
method which both will accept.

The encryption method bit is more of a historical artifact at this
point as modern systems have compatible AES-based encryption methods,
but that wasn't always the case. It's also possible that it'll become
an issue in the future but at least for the moment most installations
have an AES-based compatible encryption method.

I have posted a suggestion for an improvement (via the form) to the
pgsql-docs mailing list. Lets see what comes out of that.

Great, I've replied with my own suggestion. Hopefully we can work
towards something that works for both of us (and everyone else) and then
I can commit the change.

I like the idea to document the *why* behind SSPI and GSS, however I don't
understand enough about it to propose a documentation improvement.

You did propose a documentation improvement, so I'm a bit confused by
this statement. ;)

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...).

Sure, here's a blog post that I wrote about doing exactly that:

https://www.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication

Note that that document doesn't mention SSPI a single time.

It doesn't actually need to because SSPI is the default and it all just
works..

I can confirm the "just works" part since my setup now indeed *does* work.

Hah! Great, glad to hear that.

However the existing documentation on GSS/Kerberos/SSPI/Active directory is
extremely terse and sparse. So being more explicit and being clearer would
improve the situation I think. Are you able and interested to change the
Blog article?

I'd suggest to add a paragraph like this:

Using a Postgresql client on Windows

If you use a Postgresql client on Windows then that client should
be able to automatically use Windows' SSPI API, which will do the
authentication via Active Directory without any further
configuration necessary.

I'm probably able to make a change to it, given that I wrote it. I'll
see what I can do to improve on it.

I suppose I could have added in somewhere "SSPI is used on the
client for this", perhaps, but the blog post was more focused on "this
is what you do to make it work" and you don't really need to know that
the API that the client is using in this case happens to be called SSPI.

The ultra terse Postgres docu is using terms such as "SAM", "negotiate
mode", "UPN", without a reference to their definition, which leaves to
Windows noobs like me (and equaly Windows non-noobs) without a clue.

I'm certainly all for improving the documentation. Not sure that just
spelling those out would really be enough and I'm rather confident that
trying to explain all of how Kerberos and AD work wouldn't really be
maintainable in our documentation. Still, specific suggestion on how we
can improve would be great.

+1 - see my post to the pgsql-docs mailing list for a starter.

Thanks again, hopefully my response makes sense.

Searching "kerberos SAM" or "SSPI negotiate mode" on duckduckgo doesn't seem
to lead to useful hits. So the documentation seems to be geared to experts
of both Kerberos and Active Directory and Windows technology alike which as
far as I can tell is a very rare specimen.

Active Directory experts certainly should be generally familiar with
Kerberos (and the terms you reference above are actually all AD ones,
really..), so I'd argue that the documentation is mainly focused on
helping those individuals. I do generally agree that it would be good
if there were more folks out there who understood this stuff. I'm not
sure how to go about making that happen though.

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?

gssencmode indeed requires a GSS encrypted connection. Unfortunately,
there isn't a way to force GSS or SSPI be used for authentication today
(outside of GSS with encryption) from the client side, though that's
something that's been discussed and I think most people feel would be
good to have. That said, if the server is configured in pg_hba for gss
(or sspi), then the client will try to authenticate that way.

As far as I can see it is *not possible* to configure the server for SSPI
("or sspi")? It's only possible to configure it for GSS *and* SSPI at the
same time (?) assuming the server is running on a non-Windows platform?

As I mention above, on the wire GSS and SSPI are compatible, so a server
on a non-Windows platform configured with GSS is also going to work for
clients using SSPI (and, indeed, the server isn't really going to even
directly be able to tell that the client is using SSPI..).

Maybe I can explain this a bit better-

GSS and SSPI are *APIs* into the respective Kerberos libraries on those
systems. You can consider what's spoken on the actual wire between the
systems to be 'generic Kerberos'.

AFAI understand AD is using public key (asymmetric) encryption as default
where as out of the box MIT Kerberos doesn't? (As opposed to FreeIPA that
*does* use asymmetric encryption), so I'd expect that there *are* difference
between vanilla Kerberos and AD "on the wire"?

Err, no, Kerberos and all of this is pre-asymmetric encryption and
therefore all uses symmetric encryption. Now, there's been some hacks
to allow asymmetric encryption to be used to acquire the initial ticket
granting ticket (TGT) but that's really just the very beginning of the
Kerberos world and after that it's still all symmetric encryption. Not
that any of this really matters- most users shouldn't have to worry
about this.

Hopefully the blog post and this discussion helps you make some
progress. I wonder how close you are to everything working if you'd
just drop the 'gssencmode' setting, or maybe explicitly set it to
'disable'...

We dropped the gssencmode setting and were still getting the "Internal
credentials cache error" error. That said, I'll have another session on
probably Monday and I'll try to start from scratch, use the psql.exe that is
coming with vanilla QGIS (without GSS support) (and not the one from OSGeo
with compiled in GSS support) so that it absolutely should use SSPI and not
use GSS at all.

Hrmpf. That's certainly curious that it still gave you that error..
Was it also still trying to call out to the MIT Kerberos For Windows
services? I wonder if maybe somewhere else the gssencmode was still
getting picked up as being set..

I guess this would be worth verifying for all the poor souls up there that
get stuck in the same morass. However I do not have direct access to AD
systems and so testing and doublechecking that is procedurally really
involved :-(

I have to say that I strongly suspect that gssencmode was still being
picked up as getting set somewhere. Perhaps in the future someone will
have the cycles to work on supporting transport encryption with SSPI.

Hopefully that will work. Wish me luck :-D! Thanks a lot for helping
Stephen!!!

Sure, looking forward to hearing what happens next.

So it's working and I very grateful that you helped!!! Many, many, many
thanks. If we ever meet in real life, then the beers are on me!

Glad to hear that it's working!

Thanks,

Stephen