Re: use has_privs_of_role() for pg_hba.conf - Mailing list pgsql-hackers

On Sat, Oct 8, 2022 at 11:14 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Joe Conway <mail@joeconway.com> writes:
> > Thanks -- looks good to me. If there are no other comments or concerns,
> > I will commit/push by the end of the weekend.
>
> Robert seems to think that this patch might be completely misguided,
> so I'm not sure we have real consensus.  I think he may have a point.
>
> An angle that he didn't bring up is that we've had proposals, and
> even I think a patch, for inventing database-local privileges.
> If that were to become a thing, it would interact very badly with
> this idea, because it would often not be clear which set of privileges
> to consider.  As long as HBA checks consider membership, and we don't
> invent database-local role membership, there's no problem.

This argument feels a little bit thin to me, because (1) one could
equally well postulate that we'd want to invent database-local role
membership and (2) presumably the relevant set of privileges would be
those for the database to which the user wishes to authenticate.

I think what is bothering me is a feeling that a privilege is
something that you get because you've authenticated. If you haven't
authenticated yet, you have no privileges. So why should it matter
whether the role to which you could hypothetically authenticate would
inherit the privileges of some other role or not?

Or to put it another way, I don't have any intuition for why someone
would want the system to behave in this way rather than in the way
that it does now. In general, what role inheritance does is actually
pretty easy to understand: either you just have the ability to access
the privileges of some other role at need, or you have those
privileges all the time even without activating them explicitly. I
think in most cases people will expect membership in a predefined role
or a role used as a group to behave in the second way, and membership
in a login role to be used in the first way, but I think there will
likely be some exceptions in both directions, which is fine, because
we can support that.

But the usage where you mention a group in pg_hba.conf feels
orthogonal to all of that to me. In that case, it's not really about
privileges at all, or at least I don't think so. It's about letting
one group of people log into the system from, say, a certain IP
address, and others not (or maybe the reverse). It seems reasonably
likely that you wouldn't want the role you used for grouping purposes
in a case like this to hold any privileges at all, or that if it did
have any privileges you wouldn't want them accessible in any way to
the group members, because if you create a group called
people_who_can_log_in_from_the_modem_pool, you do not therefore want
to end up with tables owned by
people_who_can_log_in_from_the_modem_pool. Under that theory, this
patch is going in the wrong direction.

Now there may be some other scenario in which the patch is going in
exactly the right direction, and if I knew what it was, maybe I'd
agree that the patch was a great idea. But I haven't seen anything
like that on the thread. Basically, the argument is just that the
change would make things more consistent. However, it might be an
abuse of the term. If you go out and buy blue curtains because you
have a blue couch, that's consistent interior decor. If you go out and
buy a blue car because you have a blue couch, that's not really
consistent anything, it's just two fairly-unrelated things that are
both blue.

-- 
Robert Haas
EDB: http://www.enterprisedb.com