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

[ trimming various comments that broadly make sense to me and which
don't seem to require further comment in this moment ]

On Mon, Jun 6, 2022 at 7:21 PM Stephen Frost <sfrost@snowman.net> wrote:
> > To revoke a grant entirely, you just say REVOKE foo FROM bar, as now.
> > To change an option for an existing grant, you can re-execute the
> > grant statement with a different WITH clause. Any options that are
> > explicitly mentioned will be changed to have the associated values;
> > unmentioned options will retain their existing values. If you want to
> > change the value of a Boolean option to false, you have a second
> > option, which is to write "REVOKE option_name OPTION FOR foo FROM
> > bar," which means exactly the same thing as "GRANT foo TO bar WITH
> > option_name FALSE".
>
> I'm a bit concerned about this because, iiuc, it would mean:
>
> GRANT foo TO bar WITH FRUIT KIWI, SARDINES;
> GRANT foo TO bar WITH FRUIT STRAWBERRY;
>
> would mean that the GRANT of FRUIT would then *only* have STRAWBERRY,
> right?

I think that you are misunderstanding what kind of option I intended
FRUIT to be. Here, I was imagining FRUIT as a property that every
grant has. Any given grant is either strawberry, or it's kiwi, or it's
banana. It cannot be more than one of those things, nor can it be none
of those things. It follows that if you execute GRANT without
specifying a FRUIT, there's some default - hopefully banana, but
that's a matter of taste. Later, you can change the fruit associated
with a grant, but you cannot remove it, because there's no such thing
as a fruitless grant. Imagine that the catalog representation is a
"char" that is either 's', 'k', or 'b'.

Now you could certainly question whether it's a good idea for us to
have an option that works like this. I don't really know. For a while
I thought that it might make sense to propose something like ACCESS {
EXPLICIT | IMPLICIT | NONE }, where ACCESS IMPLICIT would mean that
the grantee implicitly has the permissions of the role, ACCESS
EXPLICIT means that they do not implicitly have those permissions but
can access them via SET ROLE, and ACCESS NONE means that they can't
even do that. The default would I suppose be ACCESS IMPLICIT but you
could change it to one of the other two. However, I then thought that
it made more sense to keep it as two separate Booleans because
actually all four combinations are sensible: you could want to have a
setup where you're allowed to implicitly access the permissions of the
role but you CANNOT SET ROLE to it. For instance, this might make
sense for a predefined role, so that you don't end up with tables
owned by pg_monitor or whatever.

Anyway, if the hypothetical FRUIT property works as I describe here -
there's always a single value - then the second GRANT leaves the
SARDINES property set, but changes the FRUIT property from strawberry
to kiwi. Since the property is single-valued, you cannot add a second
fruit, nor can you remove the fruit altogether, because those just
aren't sensible ideas with an option of this kind. As alonger example,
it's like the FORMAT property of EXPLAIN: it always has to be TEXT or
XML or JSON. You can choose not to explicitly specify the option, but
then you get a default. Your EXPLAIN output always has to have some
format.

> In your proposal, does:
>
> GRANT foo TO bar WITH FRUIT STRAWBERRY;
>
> mean that 'foo' is grant'd to 'bar' too?  Seems to be closest to current
> usage and the spec's ideas on these things.  I'm thinking that could be
> dealt with by having a MEMBERSHIP option (which would be a separate
> column in pg_auth_members and default would be 'true') but otherwise
> using exactly what you have here, eg:

Currently, GRANT always creates an entry in pg_auth_members, or
modifies an existing one, or does nothing because the one that's there
is the same as the one it would have created. I think we should stick
with that idea.

That's why I proposed the name SET, not MEMBERSHIP. You would still
get a catalog entry in pg_auth_members, so you are still a member in
some loose sense even if your grant has INHERIT FALSE and SET FALSE,
but in such a case the fact that you are a member isn't really doing
anything for you in terms of getting you access to privileges because
you're neither allowed to exercise them implicitly nor SET ROLE to the
role.

I find that idea - that GRANT always grants membership but membership
by itself doesn't really do anything for you unless you've got some
other options enabled somewhere - more appealing than the design you
seem to have in mind, which seems to me that membership is the same
thing as the ability to SET ROLE and thus, if the ability to SET ROLE
has not been granted, you have a grant that didn't confirm membership
in any sense. I'm not saying we couldn't make that work, but I think
it's awkward to make that work. Among other problems, what happens
with the actual catalog representation? You could for example still
create a role in pg_auth_members and then have a Boolean column
membership = false, but that's a bit odd. Or you could add a new
catalog or you could rename the existing catalog, but that's more
complicated for not much benefit. I think there's some fuzziness at
the semantic level with this kind of thing too: if I do a GRANT with
MEMBERSHIP FALSE, what exactly is it that I am granting? I like the
conceptual simplicity of being able to say that a GRANT always confers
membership, but membership does not intrinsically include the ability
to SET ROLE -- that's a Boolean property of membership, not membership
itself.

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