Re: LDAP Authentication

On Fri, Aug 25, 2023 at 3:09 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

Hi Stephen. Thanks for taking the time to make such a detailed response.

* Dominique Devienne (ddevienne(at)gmail(dot)com) wrote:
> > could you please provide more info on [...]
>
>
> https://www.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication

Thanks. We'll study that.

The "pass-the-password" LDAP-based auth method is just about exclusively
> used for 3rd-party non-Kerberos-speaking services. When you have the
> option to use Kerberos, you absolutely should.
>

OK.

> > What about SSO? Can the local creds / token from the already-AD-connected
> > local OS user be extracted,
> > so the user doesn't need to supply any password, not even the AD-one?
>
> Kerberos *is* the true SSO that you're looking for- when you sign into
> any AD connected system (eg: your laptop), you get Kerberos credentials
> (run 'klist' at a cmd prompt sometime..) and you don't have to provide
> your password again for any Kerberos authentication until/unless your
> Kerberos tickets (acquired when you logged into the laptop) expire.
>

Oh, `klist` even works on my Win10 and Win11 boxes, and also on RH 7.5.
Interesting. Didn't about that one.

With the LDAP auth method, the user has to provide their AD password on
> every connection to authenticate, or worse, save their AD password for
> connection in some local file so they don't have to type it in every
> time. That password is then also sent to the PG server on every
> connection, meaning that if the PG server is compromised, the account of
> every user who logs into that PG server is then also compromised- their
> full AD account.
>

Ouch. Indeed.

> > Regarding your second point about availability of the LDAP server, isn't
> > that normal to fail connecting
> > when Auth cannot be ascertained / verified? Kerberos/GSSAPI somehow main
> > some cache to avoid that?
>
> Kerberos tickets are acquired from the AD when you log into your laptop.
> Those tickets will have some lifetime (which you can configure, but
> tends to be 8-10 hours or so). When you connect to a Kerberos service,
> the system will automatically reach out to the AD to obtain a ticket for
> that service (eg, a PostgreSQL ticket, for your account) which will also
> have a specific lifetime. When you connect to PostgreSQL, the PG server
> is able to verify your identity based on the ticket you present- the PG
> server never has to reach out to the AD system for user authentication.
> For as long as the PostgreSQL ticket that you acquired when you first
> connected to the PG server is valid (again, typically 8-10 hours, but
> you can configure it), the AD server is no longer involved and doesn't
> need to be contacted, no matter how many subsequent connections you make
> to the PG server.
>

I get it. Very insightful, again, thanks.
I'm familiar with JWT tokens, and the concepts seem similar.

> Note that Kerberos is all about authentication- not about authorization.
> There's a few different tools out there for sync'ing users from AD into
> PostgreSQL and those can be used to manage who is authorized for a given
> PG server, including removing their authorization in a relatively short
> period of time if needed (more-or-less how often you feel like running
> the sync'ing cronjob or such). I do think it'd be pretty cool to
> improve on that with a way for PG to reach out to an AD system and run a
> query and then track the changes to that query (LDAP has support for
> this kind of thing) which would make changes to, eg, group membership in
> AD propagate to the PG server in pretty close to real-time. Sadly, I'm
> not aware of anyone actively working on that though.
>

Yes, having worked with Oracle in the past, I've read about such
"enterprise" tools.
Having the manually add AD users to the cluster is indeed a bit of a PITA,
and also something I had wondered about.

> Given that caching is often more trouble than it's worth, how is that
> > better? Naïve question, really. --DD
>
> This one I find pretty curious given that cacheing is a very large part
> of what PostgreSQL does and is certainly quite worth it. ;)
>

Sure :). Caching is the easy part. But proper cache invalidation is tough!
I've seen too many faulty caching, to avoid talking to the DB for example,
often from using "naive" DB access/SQL/etc... that I tend to be weary of
such caching :)
Not all organizations have the high degree of code quality of the
PostgreSQL project...

For the N'th time, thanks a bunch Stephen. Cheers, --DD