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

Hi Stephen,

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.

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