Re: [PoC] Delegating pg_ident to a third party

Greetings,

* Jacob Champion (pchampion(at)vmware(dot)com) wrote:
> On Tue, 2022-01-04 at 22:24 -0500, Stephen Frost wrote:
> > On Tue, Jan 4, 2022 at 18:56 Jacob Champion <pchampion(at)vmware(dot)com> wrote:
> > >
> > > Could you talk more about the use cases for which having the "actual
> > > user" is better? From an auditing perspective I don't see why
> > > "authenticated as jacob(at)example(dot)net, logged in as admin" is any worse
> > > than "logged in as jacob".
> >
> > The above case isn’t what we are talking about, as far as I
> > understand anyway. You’re suggesting “authenticated as
> > jacob(at)example(dot)net, logged in as sales” where the user in the database
> > is “sales”. Consider triggers which only have access to “sales”, or
> > a tool like pgaudit which only has access to “sales”.
>
> Okay. So an additional getter function in miscadmin.h, and surfacing
> that function to trigger languages, are needed to make authn_id more
> generally useful. Any other cases you can think of?

That would help but now you've got two different things that have to be
tracked, potentially, because for some people you might not want to use
their system auth'd-as ID. I don't see that as a great solution and
instead as a workaround. Yes, we should also do this but it's really an
argument for how to deal with such a setup, not a justification for
going down this route.

> > > I was responding more to your statement that "Being able to have roles
> > > and memberships automatically created is much more the direction that
> > > I'd say we should be going in". It's not that one-role-per-user is
> > > inherently wasteful, but forcing role proliferation where it's not
> > > needed is. If all users have the same set of permissions, there doesn't
> > > need to be more than one role. But see below.
> >
> > Just saying it’s wasteful isn’t actually saying what is wasteful about it.
>
> Well, I felt like it was irrelevant; you've already said you have no
> intention to force one-user-per-role.

Forcing one-user-per-role would be breaking things we already support
so, no, I certainly don't have any intention of requiring such a change.
That said, I do feel it's useful to have these discussions.

> But to elaborate: *forcing* one-user-per-role is wasteful, because if I
> have a thousand employees, and I want to give all my employees access
> to a guest role in the database, then I have to administer a thousand
> roles: maintaining them through dump/restores and pg_upgrades, auditing
> them to figure out why Bob in Accounting somehow got a different
> privilege GRANT than the rest of the users, adding new accounts,
> purging old ones, maintaining the inevitable scripts that will result.

pg_upgrade just handles it, no? pg_dumpall -g does too. Having to deal
with roles in general is a pain but the number of them isn't necessarily
an issue. A guest role which doesn't have any auditing requirements
might be a decent use-case for what you're talking about here but I
don't know that we'd implement this for just that case. Part of this
discussion was specifically about addressing the other challenges- like
having automation around the account addition/removal and sync'ing role
membership too. As for auditing privileges, that should be done
regardless and the case you outline isn't somehow different from others
(the same could be as easily said for how the 'guest' account got access
to whatever it did).

> If none of the users need to be "special" in any way, that's all wasted
> overhead. (If they do actually need to be special, then at least some
> of that overhead becomes necessary. Otherwise it's waste.) You may be
> able to mitigate the cost of the waste, or absorb the mitigations into
> Postgres so that the user can't see the waste, or decide that the waste
> is not costly enough to care about. It's still waste.

Except the amount of 'wasted' overhead being claimed here seems to be
hardly any. The biggest complaint levied at this seems to really be
just the issues around the load on the ldap systems from having to deal
with the frequent sync queries, and that's largely a solvable issue in
the majority of environments out there today.

> > > > I'm also not suggesting that we make everyone do the same
> > > > thing, indeed, later on I was supportive of having an external system
> > > > provide the mapping. Here, I'm just making the point that we should
> > > > also be looking at automatic role/membership creation.
> > >
> > > Gotcha. Agreed; that would open up the ability to administer role
> > > privileges externally too, which would be cool. That could be used in
> > > tandem with something like this patchset.
> >
> > Not sure exactly what you’re referring to here by “administer role
> > privileges externally too”..? Curious to hear what you are imagining
> > specifically.
>
> Just that it would be nice to centrally provision role GRANTs as well
> as role membership, that's all. No specifics in mind, and I'm not even
> sure if LDAP would be a helpful place to put that sort of config.

GRANT's on objects, you mean? I agree, that would be interesting to
consider though it would involve custom entries in the LDAP directory,
no? Role membership would be able to be sync'd as part of group
membership and that was something I was thinking would be handled as
part of this in a similar manner to what the 3rd party solutions provide
today using the cron-based approach.

> > I’d also point out though that having to do an ldap lookup on every
> > login to PG is *already* an issue in some environments, having to do
> > multiple amplifies that.
>
> You can't use the LDAP auth method with this patch yet, so this concern
> is based on code that doesn't exist. It's entirely possible that you
> could do the role query as part of the first bound connection. If that
> proves unworkable, then yes, I agree that it's a concern.

Perhaps it could be done as part of the same connection but that then
has an impact on what the configuration of the ident LDAP lookup would
be, no? That seems like an important thing to flesh out before we move
too much farther with this patch, to make sure that, if we want that to
work, that there's a clear way to configure it to avoid the double LDAP
connection. I'm guessing you already have an idea how that'll work
though..?

> > Not to mention that when the ldap servers can’t be reached for some
> > reason, no one can log into the database and that’s rather
> > unfortunate too.
>
> Assuming you have no caches, then yes. That might be a pretty good
> argument for allowing ldapmap and map to be used together, actually, so
> that you can have some critical users who can always log in as
> "themselves" or "admin" or etc. Or maybe it's an argument for allowing
> HBA to handle fallback methods of authentication.

Ok, so now we're talking about a cache that needs to be implemented
which will ... store the user's password for LDAP authentication? Or
what the mapping is for various LDAP IDs to PG roles? And how will that
cache be managed? Would it be handled by dump/restore? What about
pg_upgrade? How will entries in the cache be removed?

And mainly- how is this different from just having all the roles in PG
to begin with..?

> Luckily I think it's pretty easy to communicate to LDAP users that if
> *all* your login infrastructure goes down, you will no longer be able
> to log in. They're probably used to that idea, if they haven't set up
> any availability infra.

Except that most of the rest of the infrastructure may continue to work
just fine except for logging in- which is something most folks only do
once a day. That is, why is the SQL Server system still happily
accepting connections while the AD is being rebooted? Or why can I
still log into the company website even though AD is down, but I can't
get into PG? Not everything in an environment is tied to LDAP being up
and running all the time, so it's not nearly so cut and dry in many,
many cases.

> > These are, of course, arguments for moving away from methods that
> > require checking with some other system synchronously during login-
> > which is another reason why it’s better to have the authentication
> > credentials easily map to the PG role, without the need for external
> > checks at login time. That’s done with today’s pg_ident, but this
> > patch would change that.
>
> There are arguments for moving towards synchronous checks as well.
> Central revocation of credentials (in timeframes shorter than ticket
> expiration) is what comes to mind. Revocation is hard and usually
> conflicts with the desire for availability.

Revocation in less time than ticket lifetime and everything falling over
due to the AD being restarted are very different. The approaches being
discussed are all much shorter than ticket lifetime and so that's hardly
an appropriate comparison to be making. I didn't suggest that waiting
for ticket expiration would be appropriate when it comes to syncing
accounts between AD and PG or that it would be appropriate for
revocation. Regarding the cache'ing proposed above- in such a case,
clearly, revocation wouldn't be syncronous either. Certainly in the
cases today where cronjobs are being used to perform the sync,
revocation also isn't syncronous (unless also using LDAP for
authentication, of course, though that wouldn't do anything for existing
sessions, while removing role memberships does...).

> What's "better" for me or you is not necessarily "better" overall; it's
> all tradeoffs, all the time.

Sure.

> > Consider the approach I continue to advocate- GSSAPI based
> > authentication, where a user only needs to contact the Kerberos
> > server perhaps every 8 hours or so for an updated ticket but
> > otherwise can authorize directly to PG using their existing ticket
> > and credentials, where their role was previously created and their
> > memberships already exist thanks to a background worker whose job it
> > is to handle that and which deals with transient network failures or
> > other issues. In this world, most logins to PG don’t require any
> > other system to be involved besides the client, the PG server, and
> > the networking between them; perhaps DNS if things aren’t cached on
> > the client.
> >
> > On the other hand, to use ldap authentication (which also happens to
> > be demonstrable insecure without any reasonable way to fix that),
> > with an ldap mapping setup, requires two logins to an ldap server
> > every single time a user logs into PG and if the ldap environment is
> > offline or overloaded for whatever reason, the login fails or takes
> > an excessively long amount of time.
>
> The two systems have different architectures, and different security
> properties, and you have me at a disadvantage in that you can see the
> experimental code I have written and I cannot see the hypothetical code
> in your head.

I've barely glanced at the code you've written and it largely hasn't
been driving my comments on this thread- merely the understanding of how
it works. Further, you've stated that you're already familiar with
systems that sync between LDAP and PG and the vast majority of this
discussion has been about that distinction- if we push the mappings into
PG as roles, or if we execute a query out to LDAP on connection to check
the mapping. The above references to tickets and GSSAPI/Kerberos are
all from existing code as well. The only reference to hypothetical code
is the idea of a background or other worker that subscribes to changes
in LDAP and implements those changes in PG instead of having something
cron-based do it, but that doesn't really change anything about the
architectural question of if we cache (either with an explicit cache, as
you've opined us adding above, though which there is no code for today,
or just by using PG's existing role/membership system) or call out to
LDAP for every login.

> It sounds like I'm more concerned with the ability to have an online
> central source of truth for access control, accepting that denial of
> service may cause the system to fail shut; and you're more concerned
> with availability in the face of network failure, accepting that denial
> of service may cause the system to fail open. I think that's a design
> decision that belongs to an end user.

There is more to it than just failing shut/closed. Part of the argument
being used to drive this change was that it would help to reduce the
load on the LDAP servers because there wouldn't be a need to run large
queries on them frequently out of cron to keep PG's understanding of
what the roles are and their mappings is matching what's in LDAP.

> The distributed availability problems you're describing are, in my
> experience, typically solved by caching. With your not-yet-written
> solution, the caching is built into Postgres, and it's on all of the
> time, but may (see below) only actually perform well with Active
> Directory. With my solution, any caching is optional, because it has to
> be implemented/maintained external to Postgres, but because it's just
> generic "LDAP caching" then it should be broadly compatible and we
> don't have to maintain it. I can see arguments for and against both
> approaches.

I'm a bit confused by the this- either you're referring to the cache
being PG's existing system, which certainly has already been written,
and has existed since it was committed and released as part of 8.1, and
is, indeed, on all the time ... or you're talking about something else
which hasn't been written and could therefore be anything, though I'm
generally against the idea of having an independent cache for this, as
described above.

As for optional cacheing with some generic LDAP caching system, that
strikes me as clearly even worse than building something into PG for
this as it requires maintaining yet another system in order to have a
reasonably well working system and that isn't good. While it's good
that we have pgbouncer, it'd certainly be better if we didn't need it
and it's got a bunch of downsides to it. I strongly suspect the same
would be true of some external generic "LDAP cacheing" system as is
referred to above, though as there isn't anything to look at, I can't
say for sure.

Regarding 'performing well', while lots of little queries may be better
in some cases than less frequent larger queries, that's really going to
depend on the frequency of each and therefore really be rather dependent
on the environment and usage. In any case, however, being able to
leverage change modifications instead of fully resyncing will definitely
be better. At the same time, however, if we have the external generic
LDAP cacheing system that's being claimed ... why wouldn't we simply use
that with the cron-based system today to offload those from the main
LDAP systems?

> > > I guess I'd have to see an implementation -- I was under the impression
> > > that persistent search wasn't widely implemented?
> >
> > I mean … let’s talk about the one that really matters here:
> >
> > https://docs.microsoft.com/en-us/windows/win32/ad/change-notifications-in-active-directory-domain-services
>
> That would certainly be a useful thing to implement for deployments
> that can use it. But my personal interest in writing "LDAP" code that
> only works with AD is nil, at least in the short term.
>
> (The continued attitude that Microsoft Active Directory is "the one
> that really matters" is really frustrating. I have users on LDAP
> without Active Directory. Postgres tests are written against OpenLDAP.)

What would you consider the important directories to worry about beyond
AD? I don't consider the PG testing framework to be particularly
indicative of what enterprises are actually running.

> > OpenLDAP has an audit log system which can be used though it’s
> > certainly not as nice and would require code specific to it.
> >
> > This talks a bit about other directories:
> > https://docs.informatica.com/data-integration/powerexchange-adapters-for-powercenter/10-1/powerexchange-for-ldap-user-guide-for-powercenter/ldap-sessions/configuring-change-data-capture/methods-for-tracking-changes-in-different-directories.html
> >
> > I do wish they all supported it cleanly in the same way.
>
> Okay. But the answer to "is persistent search widely implemented?"
> appears to be "No."

I'm curious as to how the large environments that you've worked with
have generally solved this issue. Is there a generic LDAP cacheing
system that's been used? What?

> > > The current patchset here has pieces of what is usually contained in
> > > HBA (the LDAP host/port/base/filter/etc.) effectively moved into
> > > pg_ident, while other pieces (TLS settings) remain in the HBA and the
> > > environment. That's what I'm referring to. If that is workable for you
> > > in the end, that's fine, but for me it'd be much easier to maintain if
> > > the mapping query and the LDAP connection settings for that mapping
> > > query were next to each other.
> >
> > I can agree with the point that it would be nicer to have the ldap
> > host/port/base/filter be in the hba instead, if there is a way to
> > accomplish that reasonably. Did you have a suggestion in mind for how
> > to do that..? If there’s an alternative approach to consider, it’d
> > be useful to see them next to each other and then we could all
> > contemplate which is better.
>
> I didn't say I necessarily wanted it all in the HBA, just that I wanted
> it all in the same spot.
>
> I don't see a good way to push the filter back into the HBA, because it
> may very well depend on the users being mapped (i.e. there may need to
> be multiple lines in the map). Same for the query attributes. In fact
> if I'm already using AD Kerberos or SSPI and I want to be able to
> handle users coming from multiple domains, couldn't I be querying
> entirely different servers depending on the username presented?

Yeah, that's a good point and which argues for putting everything into
the ident. In such a situation as you describe above, we wouldn't
actually have any LDAP configuration in the HBA and I'm entirely fine
with that- we'd just have it all in ident. I don't see how you'd make
that work with, as you suggest above, LDAP-based authentication and the
idea of having only one connection be used for the LDAP-based auth and
the mapping lookup, but I'm also not generally worried about LDAP-based
auth and would rather we rip it out entirely. :)

As such, I'd say that you've largely convinced me that we should just
move all of the LDAP configuration for the lookup into the ident and
discourage people from using LDAP-based authentication and from putting
LDAP configuration into the hba. I'm still a fan of the general idea of
having a way to configure such ldap parameters in one place in whatever
file they go into and then re-using that multiple times on the general
assumption that folks are likely to need to reference a particular LDAP
configuration more than once, wherever it's configured.

> > > > When it comes to the question of "how to connect to an LDAP server for
> > > > $whatever", it seems like it'd be nice to be able to configure that once
> > > > and reuse that configuration. Not sure I have a great suggestion for
> > > > how to do that. The approach this patch takes of adding options to
> > > > pg_hba for that, just like other options in pg_hba do, strikes me as
> > > > pretty reasonable.
> > >
> > > Right. That part seems less reasonable to me, given the current format
> > > of the HBA. YMMV.
> >
> > If the ldap connection info and filters and such could all exist in
> > the hba, then perhaps a way to define those credentials in one place
> > in the hba file and then use them on other lines would be
> > possible..? Seems like that would be easier than having them also in
> > the ident or having the ident refer to something defined elsewhere.
> >
> > Consider in the hba having:
> >
> > LDAPSERVER[ldap1]=“ldaps://whatever other options go here”
> >
> > Then later:
> >
> > hostssl all all ::0/0 ldap ldapserver=ldap1 ldapmapserver=ldap1 map=myldapmap
> >
> > Clearly needs more thought needed due to different requirements for
> > ldap authentication vs. the map, but still, the general idea being to
> > have all of it in the hba and then a way to define ldap server
> > configuration in the hba once and then reused.
>
> You're open to the idea of bolting a new key/value grammar onto the HBA
> parser, but not to the idea of brainstorming a different configuration
> DSL?

Short answer- yes (or, as mentioned just above, into the ident file vs.
the hba). I'd rather we build on the existing configuration systems
that we have rather than invent something new that will then have to
work with the others, as I don't see it as likely that we could just
replace the existing ones with something new and make everyone
change. Having yet another one strikes me as worse than making
improvements to the existing ones (be those 'bolted on' or otherwise).

Thanks,

Stephen