Re: can't get psql authentication against Active Directory working

Greetings,

* Tomas Pospisek (tpo2(at)sourcepole(dot)ch) wrote:
> On 20.02.23 15:17, Stephen Frost wrote:
> > * Tomas Pospisek (tpo2(at)sourcepole(dot)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(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
> >
> > 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