Re: replacing role-level NOINHERIT with a grant-level option

On Fri, Jul 1, 2022 at 9:05 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > So now A has implicit inherited privs for t1 but not for t2.
>
> Yeah, I anticipate that this would work in the way that you postulate here.

And here is a patch that makes it work that way. In this version, you
can GRANT foo TO bar WITH INHERIT { TRUE | FALSE | DEFAULT }, and
DEFAULT means that role-level [NO]INHERIT property is controlling.
Otherwise, the grant-level option overrides the role-level option.

I have some hesitations about this approach. On the positive side, I
believe it's fully backward compatible, and that's something to like.
On the negative side, I've always felt that the role-level properties
- [NO]INHERIT, [NO]CREATEROLE, [NO]SUPERUSER, [NO]CREATEDB were hacky
kludges, and I'd be happier they all went away in favor of more
principled ways of controlling those behaviors. I think that the
previous design moves us in the direction of being able to eventually
remove [NO]INHERIT, whereas this, if anything, entrenches it.

It might even encourage people to add *more* role-level Boolean flags.
For instance, say we add another grant-level property that controls
whether or not you can SET ROLE to the granted role. Well, it somebody
going to then say that, for symmetry with CREATE ROLE .. [NO]INHERIT
we need CREATE ROLE .. [NO]SETROLE? I think such additions would be
actively harmful, not only because they add complexity, but also
because I think they are fundamentally the wrong way to think about
these kinds of properties.

To me, setting a default for whether or not role grants have INHERIT
default by default is a bit like setting a default for the size of
your future credit card transactions. "If I use my credit card and
don't say how much I want to charge, make it $6.82!" This is obviously
nonsense, at least in normal scenarios, because the amount of a credit
card transaction depends fundamentally on the purpose of the credit
card transaction, and you cannot know what the right amount to charge
in future transactions will be unless you already know the purpose of
those transactions. So here: we probably cannot say anything about the
purpose of a future GRANT based on the identity of the role that is
receiving the privileges. You could have a special purpose role that
receives many grants but always with the same general purpose, just as
you could have a credit card holder that only ever buys things that
cost $6.82, and either case, a default could I guess be useful. But
those seem to be corner cases, and even then the benefit of being able
to set the default seems to be modest.

Thoughts?

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