Re: Cache invalidation after authentication (on-the-fly role creation)

On Fri, Jul 6, 2018 at 1:50 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> What if we (optionally, of course) had an always-running background
> worker which connects to AD and streams down the changes happening by
> using AD's Change Notification system:
>
> https://docs.microsoft.com/en-us/windows/desktop/AD/change-notifications-in-active-directory-domain-services
>
> Then integrate those changes into PG as they come in, avoiding the need
> for a cronjob.

Hi Stephen,

Yeah, that's a good idea. There are several change tracking
techniques available[1], some of which might also be useful for the
just-in-time sync technique I'm talking about (a cheap way to know if
anything interesting changed since last time at the same time as you
authenticate, so you can avoid doing any work at all most of the
time). Maybe other LDAP vendors can do this type of stuff too (it
looks like there was an attempt to standardise it, but it appears to
be resting[2]...)

Another idea would be for someone to take a tool like ldap2pg and give
it a --daemon mode where it does that, and then package it up with
rc.d/systemd/whatever glue so it's easy to deploy.

Another idea, somewhere between that and your idea, is to guess that
the main reason you really want to put this stuff in a background
worker is because you want a long running process and you can't be
bothered with the daemon management for an external thing... so... we
could make a generic background worker extension that'll run arbitrary
external long running programs while the database it up, and then
something like ldap2pg could have a --stream-changes mode that'd do it
until asked to shut down.

I'm assuming it'd be better to keep the actual messy synchronisation
problem outside the core PostgreSQL project, where a thousand tools
can bloom. We just need the right hooks and cache invalidation logic
so they can, and we're pretty close.

> Again, I'm not expressing an opinion for or against the change you
> propose, just mentioning another approach to the general problem. I can
> see some advantages to waiting until an actual connection attempt to go
> create the role (you don't end up with roles in the system which never
> actually log into it) and advantages to using a background worker (the
> role will already be created, avoiding possible delay during the
> authentication and setup work of the role; more clear what roles have
> access on the system; ability to GRANT access to roles which haven't
> logged in yet or to set other attributes on the role prior to login).

Another advantage to asynchronous schemes is that you can use group
role names in pg_hba.conf (whereas that is checked before the auth
module is invoked, so won't work with my synchronous just-in-time
scheme; you need to use "all" in that case, though there is an easy
workaround using LDAP search_filter).

A disadvantage of your specific scheme is that it doesn't exist yet
and probably needs to be written in C, and my scheme (despite its
disadvantages) probably only requires slinging a few lines of
Python/fooscript/barscript around.

[1] https://docs.microsoft.com/en-us/windows/desktop/AD/overview-of-change-tracking-techniques
[2] https://datatracker.ietf.org/doc/draft-dawkins-ldapext-subnot/

--
Thomas Munro
http://www.enterprisedb.com