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

Greetings Thomas,

* Thomas Munro (thomas(dot)munro(at)enterprisedb(dot)com) wrote:
> Many people use custom scripts, ldap2pg or other similar tools to
> synchronise or manage their PostgreSQL roles from an LDAP server.

While I don't have any particular opinion on the proposed patch, I
wanted to share a thought about a possibly better approach to this kind
of integration with AD:

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.

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).

Thanks!

Stephen