Re: [PoC] Delegating pg_ident to a third party

On Mon, 2022-01-03 at 12:36 -0500, Stephen Frost wrote:
> * Jacob Champion (pchampion(at)vmware(dot)com) wrote:
> > On Fri, 2021-12-17 at 10:06 +0100, Peter Eisentraut wrote:
> > > On 17.12.21 00:48, Jacob Champion wrote:
> > > > WDYT? (My responses here will be slower than usual. Hope you all have a
> > > > great end to the year!)
> > >
> > > Looks interesting. I wonder whether putting this into pg_ident.conf is
> > > sensible. I suspect people will want to eventually add more features
> > > around this, like automatically creating roles or role memberships, at
> > > which point pg_ident.conf doesn't seem appropriate anymore.
>
> This is the part that I really wonder about also ... I've always viewed
> pg_ident as being intended mainly for one-to-one kind of mappings and
> not the "map a bunch of different users into the same role" that this
> advocated for. Being able to have roles and memberships automatically
> created is much more the direction that I'd say we should be going in,
> so that in-database auditing has an actual user to go on and not some
> generic role that could be any number of people.

That last point was my motivation for the authn_id patch [1] -- so that
auditing could see the actual user _and_ the generic role. The
information is already there to be used, it's just not exposed to the
stats framework yet.

Forcing one role per individual end user is wasteful and isn't really
making good use of the role-based system that you already have.
Generally speaking, when administering hundreds or thousands of users,
people start dividing them up into groups as opposed to dealing with
them individually. So I don't think new features should be taking away
flexibility in this area -- if one role per user already works well for
you, great, but don't make everyone do the same.

> I'd go a step further and suggest that the way to do this is with a
> background worker that's started up and connects to an LDAP
> infrastructure and listens for changes, allowing the system to pick up
> on new roles/memberships as soon as they're created in the LDAP
> environment. That would then be controlled by appropriate settings in
> postgresql.conf/.auto.conf.

This is roughly what you can already do with existing (third-party)
tools, and that approach isn't scaling out in practice for some of our
existing customers. The load on the central server, for thousands of
idle databases dialing in just to see if there are any new users, is
huge.

> All that said, I do see how having the ability to call out to another
> system for mappings may be useful, so I'm not sure that we shouldn't
> consider this specific change and have it be specifically just for
> mappings, in which case pg_ident seems appropriate.

Yeah, this PoC was mostly an increment on the functionality that
already existed. The division between what goes in pg_hba and what goes
in pg_ident is starting to blur with this patchset, though, and I think
Peter's point is sound.

> I certainly don't think we should have this be limited to LDAP auth-
> such an external mapping ability is suitable for any authentication
> method that supports a mapping (thinking specifically of GSSAPI, of
> course..). Not sure if that's what was meant above but did want to
> make sure that was clear.

You can't use usermaps with LDAP auth yet, so no, that's not what I
meant. (I have another patch for that feature in commitfest, which
would allow these two things to be used together.)

Thanks,
--Jacob

[1] https://www.postgresql.org/message-id/flat/E1lTwp4-0002l4-L9%40gemulon.postgresql.org