[ 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 <sfr...@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