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

Greetings,

* Tomas Pospisek (tpo2(at)sourcepole(dot)ch) wrote:
> On 25.02.23 00:52, Stephen Frost wrote:
> > * Tomas Pospisek (tpo2(at)sourcepole(dot)ch) wrote:
> > > On 21.02.23 16:29, Stephen Frost wrote:
> > > > * 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:
> >>
> > > 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