On Thu, Jun 2, 2022 at 12:26 PM Robert Haas <robertmh...@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
