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

On Thu, Jun 2, 2022 at 12:26 PM Robert Haas <robertmhaas@gmail.com> wrote:
> 1. Extend the GRANT role_name TO role_name [ WITH ADMIN OPTION ] with
> a new, optional clause, something like WITH NO INHERIT or WITH
> NOINHERIT or WITHOUT INHERIT.

I just realized that, with ADMIN OPTION, you can change your mind
after the initial grant, and we probably would want to allow the same
kind of thing here. The existing syntax is:

1. GRANT foo TO bar [WITH ADMIN OPTION];
2. REVOKE foo FROM bar;
3. REVOKE ADMIN OPTION FOR foo FROM bar;

To grant the admin option later, you just use (1) again and this time
include WITH ADMIN OPTION. To revoke it without removing the grant
entirely, you use (3). This could easily be generalized to any number
of options all of which are Boolean properties and all of which have a
default value of false, but INHERIT is a Boolean property with a
default value of true, and calling the property NOINHERIT to dodge
that issue seems dumb. I'd like to find a way to extend the syntax
that can accommodate not only INHERIT as an option, but any other
options we might want to add in the future, and maybe even leave room
for non-Boolean properties, just in case. The question of which
options we think it valuable to add is separate from what the syntax
ought to be, so for syntax discussion purposes let's suppose we want
to add three new options: FRUIT, which can be strawberry, banana, or
kiwi; CHOCOLATE, a Boolean whose default value is true; and SARDINES,
another Boolean whose default value is false. Then:

GRANT foo TO bar WITH FRUIT STRAWBERRY;
GRANT foo TO bar WITH CHOCOLATE FALSE;
GRANT foo TO bar WITH SARDINES TRUE;
GRANT foo TO bar WITH SARDINES; -- same as previous
GRANT foo TO bar WITH SARDINES OPTION; -- also same as previous
GRANT foo TO bar WITH FRUIT KIWI, SARDINES; -- multiple options
GRANT foo TO bar WITH CHOCOLATE OPTION, SARDINES OPTION; -- dubious combination

In other words, you write a comma-separated list of option names. Each
option name can be followed by an option value or by the word OPTION.
If it is followed by the word OPTION or by nothing, the option value
is taken to be true. This would mean that, going forward, any of the
following would work: (a) GRANT foo TO bar WITH ADMIN OPTION, (b)
GRANT foo TO BAR WITH ADMIN, (c) GRANT foo TO BAR WITH ADMIN TRUE.

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

In terms of what options to offer, the most obvious idea is to just
add INHERIT as a Boolean option which is true by default. We could go
further and also add a SET option, with the idea that INHERIT OPTION
controls whether you can exercise the privileges of the role without
SET ROLE, and SET OPTION controls whether you can switch to that role
using the SET ROLE command. Those two things together would give us a
way to get to the admin-without-membership concept that we have
previously discussed: GRANT foo TO BAR WITH ADMIN TRUE, INHERIT FALSE,
SET FALSE sounds like it should do the trick.

I briefly considered suggesting that the way to set a Boolean-valued
option to false ought to be to write "NO option_name" rather than
"option_name FALSE", since it reads more naturally, but I proposed
this instead because it's more like what we do for other options lists
(cf. EXPLAIN, VACUUM, COPY).

Thoughts?

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