Re: [PoC] Delegating pg_ident to a third party

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?

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

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.

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.

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

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

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

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.

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

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

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

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.

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

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

> > 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?

> > > 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?

> > I can take a look at the Cyrus requirements for the GSSAPI mechanism.
> > Might be tricky to add tests for it, though. Any others you're
> > interested in?
>
> GSSAPI is the main one … I suppose client side certificates would be
> nice too if that’s possible. I suspect some would like a way to have
> username/pw ldap credentials in some other file besides the hba, but
> that isn’t as interesting to me, at least.

Certificate auth is already there in the patch. See the end of
t/001_ldap.t.

Thanks,
--Jacob