Re: Role Self-Administration
> On Oct 7, 2021, at 7:44 PM, Stephen Frost wrote: > > I don't actually think REVOKE ROLE CASCADE must not fail, nor do I see > that as explicit in anything you quote above. I don't see that myself, but I thought that you would, given your other statements about how we shouldn't take a spec requirement to do X and turn it into doing X+Y, because the user wouldn't be expecting Y. So I thought that if DROP ROLE bob was defined in the spec to basically just do REVOKE bob FROM EVERYBODY, and if the CASCADE version of that wasn't supposed to fail, then you'd say that DROP ROLE bob CASCADE wasn't supposed to fail either. (Failing is the unexpected action Y that I expected your rule to prohibit.) — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Role Self-Administration
Greetings,
* Mark Dilger (mark.dil...@enterprisedb.com) wrote:
> > On Oct 7, 2021, at 12:31 PM, Mark Dilger
> > wrote:
> >
> > Let me see if I can find that again.
>
> 12.6
>
> ::=
> DROP ROLE
>
> Syntax Rules
> 1) Let R be the role identified by the specified .
>
> General Rules
> 1) Let A be any identified by a role authorization
> descriptor as having been granted
> to R.
> 2) The following is effectively executed without
> further Access Rule checking:
> REVOKE R FROM A
> 3) The descriptor of R is destroyed.
>
>
> So DROP ROLE bob is expected to execute the revoke command. Let's see what
> that says
>
> ::=
> REVOKE [ ADMIN OPTION FOR ] [ { }... ]
> FROM [ { }... ]
> [ GRANTED BY ]
>
>
> 31) If RESTRICT is specified, and there exists an abandoned privilege
> descriptor, abandoned view,
> abandoned table constraint, abandoned assertion, abandoned domain constraint,
> lost domain, lost column,
> lost schema, or a descriptor that includes an impacted data type descriptor,
> impacted collation, impacted
> character set, abandoned user-defined type, or abandoned routine descriptor,
> then an exception condition
> is raised: dependent privilege descriptors still exist.
> 33) Case:
> a) If the is a , then
> ... SNIP ...
> b) If the is a , then:
> i) If CASCADE is specified, then all abandoned role authorization descriptors
> are destroyed.
> ii) All abandoned privilege descriptors are destroyed.
> 34) For every abandoned view descriptor V, let S1.VN be the of
> V. The following statement> is effectively executed without further Access Rule checking:
> DROP VIEW S1.VN CASCADE
> 35) For every abandoned table descriptor T, let S1.TN be the of
> T. The following statement> is effectively executed without further Access Rule checking:
> DROP TABLE S1.TN CASCADE
>
> The way I read that, DROP ROLE implies REVOKE ROLE, and I'm inferring that
> DROP ROLE CASCADE would therefore imply REVOKE ROLE CASCADE. Then
> interpreting 31's description of how REVOKE ROLE RESTRICT works under the
> principle Expressio Unius Est Exclusio Alterius I conclude that REVOKE ROLE
> CASCADE must not raise an exception. That leads me to the conclusion that
> DROP ROLE CASCADE must not raise an exception.
I don't actually think REVOKE ROLE CASCADE must not fail, nor do I see
that as explicit in anything you quote above.
What also is missing from the quotes above is what actually defines an
abandoned object. If you read back through how the spec explains when
an object is considered to be 'abandoned', it's more complicated. The
gist of it, however, is that if the role loses access rights to a type,
for example, and that type is used in a table, then a cascade does
remove that table (and various permutations of that for other object
types). There isn't any equivilant for roles and it isn't really about
'ownership' but about USAGE rights. In some cases (such as that of a
VIEW), while we don't explicitly perform the DROP that the spec calls
for, we check the privileges at VIEW access time, making the view not
usable if the owner of the view no longer has access to the underlying
tables.
I do appreciate that this illustrates that you can end up with things
being DROP'd, if you explicitly follow the spec, due to a REVOKE
CASCADE statement, something which I had argued seemed rather dangerous
and counter-intuitive (and still do) but that case isn't quite the same
and is something we've also already deviated from- in the direction of
avoiding having objects get DROP'd in such cases.
> Sorry for misremembering this as something you said.
No worries.
Thanks,
Stephen
signature.asc
Description: PGP signature
Re: Role Self-Administration
> On Oct 7, 2021, at 12:31 PM, Mark Dilger wrote:
>
> Let me see if I can find that again.
12.6
::=
DROP ROLE
Syntax Rules
1) Let R be the role identified by the specified .
General Rules
1) Let A be any identified by a role authorization
descriptor as having been granted
to R.
2) The following is effectively executed without
further Access Rule checking:
REVOKE R FROM A
3) The descriptor of R is destroyed.
So DROP ROLE bob is expected to execute the revoke command. Let's see what
that says
::=
REVOKE [ ADMIN OPTION FOR ] [ { }... ]
FROM [ { }... ]
[ GRANTED BY ]
31) If RESTRICT is specified, and there exists an abandoned privilege
descriptor, abandoned view,
abandoned table constraint, abandoned assertion, abandoned domain constraint,
lost domain, lost column,
lost schema, or a descriptor that includes an impacted data type descriptor,
impacted collation, impacted
character set, abandoned user-defined type, or abandoned routine descriptor,
then an exception condition
is raised: dependent privilege descriptors still exist.
33) Case:
a) If the is a , then
... SNIP ...
b) If the is a , then:
i) If CASCADE is specified, then all abandoned role authorization descriptors
are destroyed.
ii) All abandoned privilege descriptors are destroyed.
34) For every abandoned view descriptor V, let S1.VN be the of V.
The following is effectively executed without further Access Rule checking:
DROP VIEW S1.VN CASCADE
35) For every abandoned table descriptor T, let S1.TN be the of T.
The following is effectively executed without further Access Rule checking:
DROP TABLE S1.TN CASCADE
The way I read that, DROP ROLE implies REVOKE ROLE, and I'm inferring that DROP
ROLE CASCADE would therefore imply REVOKE ROLE CASCADE. Then interpreting 31's
description of how REVOKE ROLE RESTRICT works under the principle Expressio
Unius Est Exclusio Alterius I conclude that REVOKE ROLE CASCADE must not raise
an exception. That leads me to the conclusion that DROP ROLE CASCADE must not
raise an exception.
Sorry for misremembering this as something you said.
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: Role Self-Administration
> On Oct 7, 2021, at 12:19 PM, Stephen Frost wrote: > > Uh, I didn't say it 'must not fail'. Ah-hah, right, I misremembered. You were quoting the spec at me, and I went to read a copy of the spec as a consequence, and saw something like that there. Let me see if I can find that again. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Role Self-Administration
Greetings, * Mark Dilger (mark.dil...@enterprisedb.com) wrote: > > On Oct 7, 2021, at 11:30 AM, Stephen Frost wrote: > >> Because we've already decided how object ownership works. I didn't write > >> any code to have roles get dropped when their owners get dropped. I just > >> put ownership into the system and this is how it naturally works. So you > >> are advocating that DROP...CASCADE works one way for every object type > >> save one. I think that's an incredibly unclean design. Having > >> DROP...CASCADE work the same way for all ownership relations for all > >> object types without exception makes so much more sense to me. > > > > We've decided how object ownership works related to DROP ROLE ... > > CASCADE..? I don't follow how that is the case. What we *do* have is > > dependency handling, but that isn't the same as ownership. > > We have a concept of objects being owned, and we prohibit the owner being > NULL. You've already said upthread that DROP ROLE bob CASCADE must revoke > "bob" from other roles, must remove "bob", and must not fail. How do you > handle this? Uh, I didn't say it 'must not fail'. Thanks, Stephen signature.asc Description: PGP signature
Re: Role Self-Administration
> On Oct 7, 2021, at 11:30 AM, Stephen Frost wrote: > >> Because we've already decided how object ownership works. I didn't write >> any code to have roles get dropped when their owners get dropped. I just >> put ownership into the system and this is how it naturally works. So you >> are advocating that DROP...CASCADE works one way for every object type save >> one. I think that's an incredibly unclean design. Having DROP...CASCADE >> work the same way for all ownership relations for all object types without >> exception makes so much more sense to me. > > We've decided how object ownership works related to DROP ROLE ... > CASCADE..? I don't follow how that is the case. What we *do* have is > dependency handling, but that isn't the same as ownership. We have a concept of objects being owned, and we prohibit the owner being NULL. You've already said upthread that DROP ROLE bob CASCADE must revoke "bob" from other roles, must remove "bob", and must not fail. How do you handle this? CREATE ROLE bob; GRANT CREATE ON DATABASE regression TO bob; SET SESSION AUTHORIZATION bob; CREATE SCHEMA bobs_schema; RESET SESSION AUTHORIZATION; DROP ROLE bob CASCADE; You can't have bobs_schema have a null owner, nor can you refuse to drop bob. Do you just decide that the role dropping "bob" automatically become the new owner of bobs_schema? Do you assign it to the database owner? What do you do? And whatever you say we should do, how is that more spec compliant than what I propose we do? I would expect the argument against X performing X+Y would cut against anything you suggest as much as it cuts against what I suggest. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Role Self-Administration
Greetings, * Mark Dilger (mark.dil...@enterprisedb.com) wrote: > > On Oct 7, 2021, at 10:23 AM, Stephen Frost wrote: > >> And I'm not seeing that it prohibits any of them. > > > > I don't agree that we can decide to have random statements which are > > defined explicitly in the standard to do X end up doing X+Y, simply > > because the standard didn't explicitly say "you can't have Y happen when > > X does". > > I agree that a clean design is important, and I wouldn't want to do this if I > didn't think it was the cleanest way to go. But I am mindful of the problem > you raised upthread about the spec going in some other direction, and > ultimately prohibiting what I've proposed, after we've already gone and done > it. I'm not as interested in what a bunch of philosophers writing a spec > think, but if all the other major SQL databases go that direction and we're > off in a different direction, I can certainly see the problems that would > entail both for community Postgres and for my employer. If we can agree that the proposed spec is, in fact, prohibiting what you've proposed without it having to explicitly spell that out, then that's progress. > > I hate to think what the standard would look like if it was required > > that every possible thing that could happen when a statement is run had > > to be explicitly listed as "don't have this happen when this command > > runs" except for the few things that the standard defines the statement > > to do. > > > > The argument being presented here would allow us to have INSERTs perform > > CREATE ROLEs, or have DELETEs also TRUNCATE other tables that aren't > > even mentioned in the command, and still claim to be in compliance with > > the standard. > > I don't mean to be flippant, but we do allow both of those things to be done > with triggers. It's not the same as if we did them automatically, but there > seems to be some wiggle room concerning what a system can do. ... triggers are defined in the standard. This isn't a trigger. If you'd like to be able to create an EVENT TRIGGER on DROP ROLE to do whatever you want, I wouldn't have any issue with that. > > Extending the language with new syntax and then deciding how that new > > syntax works is one thing, but taking existing, defined, syntax and > > making it do something other than what the standard is saying does, imv > > anyway, go against the standard. Sure, we've gone against the standard > > at times for good reasons, but I don't agree that this is anywhere close > > to a reasonable case for that. > > > > Let's just invent some new syntax for what you're looking for here that > > works the way you want and doesn't have this issue. As I said before, I > > agree with the general usefulness of this idea, and I can even generally > > get behind the idea of role ownership to allow us to do that, but we > > can't make 'DROP ROLE bob CASCADE;' do it, it needs to be something > > more, like 'DROP ROLE bob CASCADE OBJECTS;' or such. > > > > I really don't understand why there's so much push back to go in that > > direction. Why must 'DROP ROLE bob CASCADE;' drop all of bob's objects > > and roles "owned" by bob? > > Because we've already decided how object ownership works. I didn't write any > code to have roles get dropped when their owners get dropped. I just put > ownership into the system and this is how it naturally works. So you are > advocating that DROP...CASCADE works one way for every object type save one. > I think that's an incredibly unclean design. Having DROP...CASCADE work the > same way for all ownership relations for all object types without exception > makes so much more sense to me. We've decided how object ownership works related to DROP ROLE ... CASCADE..? I don't follow how that is the case. What we *do* have is dependency handling, but that isn't the same as ownership. Further, DROP SCHEMA ... CASCADE is also defined in the standard and explicitly says that it cascades down with DROP TABLE for tables, et al. That you don't like that the standard says one thing for DROP SCHEMA ... CASCADE; and something else for DROP ROLE ... CASCADE; is laudable but doesn't change that fact that that's the case, at least today. > What if we go with what you are saying, the spec never resolves in the > direction you are predicting, and all the other database vendors go the way > I'm proposing, and we're the only ones with this ugly wart that you have to > use a different syntax for roles than for everything else? We'll be > supporting that ugly wart for years and years to come, and look ridiculous, > and rightly so. I don't want to invent an ugly wart unless I'm completely > forced to do so. I can't predict the future any better than the next person, I'm afraid, so I don't have any particular insight into when this might become final. If we want to avoid any risk here of conflicting with what the standard might do in this area then the best way to do
Re: Role Self-Administration
> On Oct 7, 2021, at 10:23 AM, Stephen Frost wrote: > >> And I'm not seeing that it prohibits any of them. > > I don't agree that we can decide to have random statements which are > defined explicitly in the standard to do X end up doing X+Y, simply > because the standard didn't explicitly say "you can't have Y happen when > X does". I agree that a clean design is important, and I wouldn't want to do this if I didn't think it was the cleanest way to go. But I am mindful of the problem you raised upthread about the spec going in some other direction, and ultimately prohibiting what I've proposed, after we've already gone and done it. I'm not as interested in what a bunch of philosophers writing a spec think, but if all the other major SQL databases go that direction and we're off in a different direction, I can certainly see the problems that would entail both for community Postgres and for my employer. > I hate to think what the standard would look like if it was required > that every possible thing that could happen when a statement is run had > to be explicitly listed as "don't have this happen when this command > runs" except for the few things that the standard defines the statement > to do. > > The argument being presented here would allow us to have INSERTs perform > CREATE ROLEs, or have DELETEs also TRUNCATE other tables that aren't > even mentioned in the command, and still claim to be in compliance with > the standard. I don't mean to be flippant, but we do allow both of those things to be done with triggers. It's not the same as if we did them automatically, but there seems to be some wiggle room concerning what a system can do. > Extending the language with new syntax and then deciding how that new > syntax works is one thing, but taking existing, defined, syntax and > making it do something other than what the standard is saying does, imv > anyway, go against the standard. Sure, we've gone against the standard > at times for good reasons, but I don't agree that this is anywhere close > to a reasonable case for that. > > Let's just invent some new syntax for what you're looking for here that > works the way you want and doesn't have this issue. As I said before, I > agree with the general usefulness of this idea, and I can even generally > get behind the idea of role ownership to allow us to do that, but we > can't make 'DROP ROLE bob CASCADE;' do it, it needs to be something > more, like 'DROP ROLE bob CASCADE OBJECTS;' or such. > > I really don't understand why there's so much push back to go in that > direction. Why must 'DROP ROLE bob CASCADE;' drop all of bob's objects > and roles "owned" by bob? Because we've already decided how object ownership works. I didn't write any code to have roles get dropped when their owners get dropped. I just put ownership into the system and this is how it naturally works. So you are advocating that DROP...CASCADE works one way for every object type save one. I think that's an incredibly unclean design. Having DROP...CASCADE work the same way for all ownership relations for all object types without exception makes so much more sense to me. What if we go with what you are saying, the spec never resolves in the direction you are predicting, and all the other database vendors go the way I'm proposing, and we're the only ones with this ugly wart that you have to use a different syntax for roles than for everything else? We'll be supporting that ugly wart for years and years to come, and look ridiculous, and rightly so. I don't want to invent an ugly wart unless I'm completely forced to do so. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Role Self-Administration
Greetings, * Mark Dilger (mark.dil...@enterprisedb.com) wrote: > > On Oct 7, 2021, at 9:05 AM, Stephen Frost wrote: > > I don't think the spec supports any of the three rules you list. > > And I'm not seeing that it prohibits any of them. I don't agree that we can decide to have random statements which are defined explicitly in the standard to do X end up doing X+Y, simply because the standard didn't explicitly say "you can't have Y happen when X does". I hate to think what the standard would look like if it was required that every possible thing that could happen when a statement is run had to be explicitly listed as "don't have this happen when this command runs" except for the few things that the standard defines the statement to do. The argument being presented here would allow us to have INSERTs perform CREATE ROLEs, or have DELETEs also TRUNCATE other tables that aren't even mentioned in the command, and still claim to be in compliance with the standard. Extending the language with new syntax and then deciding how that new syntax works is one thing, but taking existing, defined, syntax and making it do something other than what the standard is saying does, imv anyway, go against the standard. Sure, we've gone against the standard at times for good reasons, but I don't agree that this is anywhere close to a reasonable case for that. Let's just invent some new syntax for what you're looking for here that works the way you want and doesn't have this issue. As I said before, I agree with the general usefulness of this idea, and I can even generally get behind the idea of role ownership to allow us to do that, but we can't make 'DROP ROLE bob CASCADE;' do it, it needs to be something more, like 'DROP ROLE bob CASCADE OBJECTS;' or such. I really don't understand why there's so much push back to go in that direction. Why must 'DROP ROLE bob CASCADE;' drop all of bob's objects and roles "owned" by bob? Thanks, Stephen signature.asc Description: PGP signature
Re: Role Self-Administration
On Thu, Oct 7, 2021 at 12:52 PM Vik Fearing wrote: > I can agree with you now, but it's certainly not the easiest thing to > interpret. That's putting it mildly. -- Robert Haas EDB: http://www.enterprisedb.com
Re: Role Self-Administration
On 10/7/21 4:21 PM, Stephen Frost wrote: > Greetings, > > * Vik Fearing (v...@postgresfriends.org) wrote: >> On 10/6/21 8:48 PM, Stephen Frost wrote: >>> Consider that with what you're proposing, a user could execute the >>> following series of entirely SQL-spec compliant statements, and get >>> very different results depending on if we have this 'ownership' concept >>> or not: >>> >>> SET ROLE postgres; >>> CREATE ROLE r1; >>> >>> SET ROLE r1; >>> CREATE ROLE r2; >>> >>> SET ROLE postgres; >>> DROP ROLE r1 CASCADE; >>> >>> With what you're suggesting, the end result would be that r2 no longer >>> exists, whereas with the spec-defined behvaior, r2 *would* still exist. >> >> The way I read the spec, r2 would be destroyed along with its objects. >> >> 12.7 GR 30.b.i says to destroy all abandoned role authorization >> descriptors, and r2 matches that according to my reading of 12.7 GR 7. > > 12.7 refers to the "revoke statement", just so folks are able to follow. > > I concur that 30.b.1 says that. > > What I disagree with, however, is that a 'role authorization descriptor' > equates to a 'role'. Okay. > 12.6 is 'drop role statement' and it's "Function" is "Destroy a role" > > 12.7 is 'revoke statement' and it's "Function" is "Destroy privileges > and role authorizations". > > In other words, my reading is that a "role authorization descriptor" is > the equivilant of a row in pg_auth_members, not one in pg_authid. This > is further substantiated in Framework, 4.4.6 Roles, which makes a clear > distinction between "role" and "role authorization". I was looking for this distinction in Foundation and didn't think to look in Framework (I wish this thing would be just one huge document), so thanks for pointing me to that. I think I got confused by 12.4 putting in the General Rules that a role authorization descriptor is created, but putting that a role descriptor is created in the *Syntax Rules*. And that is in fact the *only* place "role descriptor" appears in Foundation. > I certainly don't think that "REVOKE R FROM A;" should be going around > dropping roles, yet your reading would imply that it should be. I can agree with you now, but it's certainly not the easiest thing to interpret. -- Vik Fearing
Re: Role Self-Administration
> On Oct 7, 2021, at 9:05 AM, Stephen Frost wrote: > >> Hmmph. I think it would be strange if all of the following were true: >> >> 1) DROP ROLE bob CASCADE drops all objects owned by bob >> 2) Roles can own other roles >> 3) DROP ROLE bob CASCADE never cascades to other roles >> >> I'm assuming you see the inconsistency in that set of rules. So, one of >> them must be wrong. You've just replied that the spec is mute on the >> subject of #1. Is there any support in the spec for claiming that #2 is >> wrong? > > Pretty sure I mentioned this before, but the spec doesn't seem to really > say anything about roles owning other roles, so #2 isn't part of the > spec. Regulations and specifications are usually thought about as either "permissive" or "prohibitory". Permissive rules allow anything that isn't expressly prohibited. Prohibitive rules prohibit anything that isn't explicitly permitted. I'm taking the SQL spec to be a permissive set of rules. I'm reasonable enough to concede that even if something is not explicitly prohibited, it is still effectively prohibited if it cannot be done without also doing some other thing that is prohibited. From your statements, I take it that #2 is allowed, at least if it doesn't necessarily lead to some other violation. So tentatively, I conclude that roles may own other roles. > #1 also isn't supported by the spec from what I can see. From that, I tentatively conclude that #1 is allowed, though I am aware that you may argue that it necessarily violates this next thing... > When the statement is: > > DROP ROLE bob; > > or > > DROP ROLE bob RESTRICT; > > then the command "REVOKE bob FROM A RESTRICT;" is supposed to be run BUT > is supposed to throw an exception if there are "any dependencies on the > role." Yeah, I don't think my proposal violates this. > If the statement is: > > DROP ROLE bob CASCADE; > > then the command "REVOKE bob FROM A CASCADE;" is run and shouldn't throw > an exception. Right, and this will be run. It's just that other stuff, like dropping owned objects, will also be run. I'm not seeing a prohibition here, just a mandate, and the proposal fulfills the mandate. > I don't think the spec supports any of the three rules you list. And I'm not seeing that it prohibits any of them. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Role Self-Administration
Greetings, * Mark Dilger (mark.dil...@enterprisedb.com) wrote: > > On Oct 7, 2021, at 7:43 AM, Stephen Frost wrote: > >> Assuming no concept of role ownership exists, but that DROP ROLE bob > >> CASCADE is implemented in a spec compliant way, if there is a role "bob" > >> who owns various objects, what happens when DROP ROLE bob CASCADE is > >> performed? Do bob's objects get dropped, do they get orphaned, or do they > >> get assigned to some other owner? I would expect that they get dropped, > >> but I'd like to know what the spec says about it before going any further > >> with this discussion. > > > > While the spec does talk about roles and how they can own objects, such > > as schemas, the 'drop role statement' doesn't appear to say anything > > about what happens to the objects which that role owns (in any case > > of CASCADE, RESTRICT, or no drop behavior, is specified). > > Hmmph. I think it would be strange if all of the following were true: > > 1) DROP ROLE bob CASCADE drops all objects owned by bob > 2) Roles can own other roles > 3) DROP ROLE bob CASCADE never cascades to other roles > > I'm assuming you see the inconsistency in that set of rules. So, one of them > must be wrong. You've just replied that the spec is mute on the subject of > #1. Is there any support in the spec for claiming that #2 is wrong? Pretty sure I mentioned this before, but the spec doesn't seem to really say anything about roles owning other roles, so #2 isn't part of the spec. #1 also isn't supported by the spec from what I can see. When the statement is: DROP ROLE bob; or DROP ROLE bob RESTRICT; then the command "REVOKE bob FROM A RESTRICT;" is supposed to be run BUT is supposed to throw an exception if there are "any dependencies on the role." If the statement is: DROP ROLE bob CASCADE; then the command "REVOKE bob FROM A CASCADE;" is run and shouldn't throw an exception. I don't think the spec supports any of the three rules you list. Thanks, Stephen signature.asc Description: PGP signature
Re: Role Self-Administration
> On Oct 7, 2021, at 7:43 AM, Stephen Frost wrote: > >> Assuming no concept of role ownership exists, but that DROP ROLE bob CASCADE >> is implemented in a spec compliant way, if there is a role "bob" who owns >> various objects, what happens when DROP ROLE bob CASCADE is performed? Do >> bob's objects get dropped, do they get orphaned, or do they get assigned to >> some other owner? I would expect that they get dropped, but I'd like to >> know what the spec says about it before going any further with this >> discussion. > > While the spec does talk about roles and how they can own objects, such > as schemas, the 'drop role statement' doesn't appear to say anything > about what happens to the objects which that role owns (in any case > of CASCADE, RESTRICT, or no drop behavior, is specified). Hmmph. I think it would be strange if all of the following were true: 1) DROP ROLE bob CASCADE drops all objects owned by bob 2) Roles can own other roles 3) DROP ROLE bob CASCADE never cascades to other roles I'm assuming you see the inconsistency in that set of rules. So, one of them must be wrong. You've just replied that the spec is mute on the subject of #1. Is there any support in the spec for claiming that #2 is wrong? — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Role Self-Administration
Greetings, * Mark Dilger (mark.dil...@enterprisedb.com) wrote: > > On Oct 6, 2021, at 1:48 PM, Stephen Frost wrote: > > This specific syntax, including the CASCADE bit, has, at minimum, at least > > been contemplate by the SQL folks sufficiently to be described in one > > specific way. I don’t have a copy of 2016 handy, unfortunately, and so I’m > > not sure if it’s described that way in a “stable” version of the standard > > or not (it isn’t defined in the 2006 draft I’ve seen), but ultimately I > > don’t think we are really talking about entirely net-new syntax here… > > > > If we were, that would be different and perhaps we would just be guessing > > at what the standard might do in the future, but I don’t think it’s an open > > ended question at this point.. > > > > (Even if it was, I have to say that the direction that they’re going in > > certainly seems consistent to me, anyway, with what’s been done in the past > > and I think it’d be bad of us to go in a different direction from that > > since it’d be difficult for us to change it later when the new spec comes > > out and contradicts what we decided to do..) > > Assuming no concept of role ownership exists, but that DROP ROLE bob CASCADE > is implemented in a spec compliant way, if there is a role "bob" who owns > various objects, what happens when DROP ROLE bob CASCADE is performed? Do > bob's objects get dropped, do they get orphaned, or do they get assigned to > some other owner? I would expect that they get dropped, but I'd like to know > what the spec says about it before going any further with this discussion. While the spec does talk about roles and how they can own objects, such as schemas, the 'drop role statement' doesn't appear to say anything about what happens to the objects which that role owns (in any case of CASCADE, RESTRICT, or no drop behavior, is specified). Thanks, Stephen signature.asc Description: PGP signature
Re: Role Self-Administration
Greetings, * Vik Fearing (v...@postgresfriends.org) wrote: > On 10/6/21 8:48 PM, Stephen Frost wrote: > > Consider that with what you're proposing, a user could execute the > > following series of entirely SQL-spec compliant statements, and get > > very different results depending on if we have this 'ownership' concept > > or not: > > > > SET ROLE postgres; > > CREATE ROLE r1; > > > > SET ROLE r1; > > CREATE ROLE r2; > > > > SET ROLE postgres; > > DROP ROLE r1 CASCADE; > > > > With what you're suggesting, the end result would be that r2 no longer > > exists, whereas with the spec-defined behvaior, r2 *would* still exist. > > The way I read the spec, r2 would be destroyed along with its objects. > > 12.7 GR 30.b.i says to destroy all abandoned role authorization > descriptors, and r2 matches that according to my reading of 12.7 GR 7. 12.7 refers to the "revoke statement", just so folks are able to follow. I concur that 30.b.1 says that. What I disagree with, however, is that a 'role authorization descriptor' equates to a 'role'. 12.6 is 'drop role statement' and it's "Function" is "Destroy a role" 12.7 is 'revoke statement' and it's "Function" is "Destroy privileges and role authorizations". In other words, my reading is that a "role authorization descriptor" is the equivilant of a row in pg_auth_members, not one in pg_authid. This is further substantiated in Framework, 4.4.6 Roles, which makes a clear distinction between "role" and "role authorization". I certainly don't think that "REVOKE R FROM A;" should be going around dropping roles, yet your reading would imply that it should be. Thanks, Stephen signature.asc Description: PGP signature
Re: Role Self-Administration
On 10/6/21 8:48 PM, Stephen Frost wrote: > Consider that with what you're proposing, a user could execute the > following series of entirely SQL-spec compliant statements, and get > very different results depending on if we have this 'ownership' concept > or not: > > SET ROLE postgres; > CREATE ROLE r1; > > SET ROLE r1; > CREATE ROLE r2; > > SET ROLE postgres; > DROP ROLE r1 CASCADE; > > With what you're suggesting, the end result would be that r2 no longer > exists, whereas with the spec-defined behvaior, r2 *would* still exist. The way I read the spec, r2 would be destroyed along with its objects. 12.7 GR 30.b.i says to destroy all abandoned role authorization descriptors, and r2 matches that according to my reading of 12.7 GR 7. -- Vik Fearing
Re: Role Self-Administration
> On Oct 6, 2021, at 1:48 PM, Stephen Frost wrote: > > This specific syntax, including the CASCADE bit, has, at minimum, at least > been contemplate by the SQL folks sufficiently to be described in one > specific way. I don’t have a copy of 2016 handy, unfortunately, and so I’m > not sure if it’s described that way in a “stable” version of the standard or > not (it isn’t defined in the 2006 draft I’ve seen), but ultimately I don’t > think we are really talking about entirely net-new syntax here… > > If we were, that would be different and perhaps we would just be guessing at > what the standard might do in the future, but I don’t think it’s an open > ended question at this point.. > > (Even if it was, I have to say that the direction that they’re going in > certainly seems consistent to me, anyway, with what’s been done in the past > and I think it’d be bad of us to go in a different direction from that since > it’d be difficult for us to change it later when the new spec comes out and > contradicts what we decided to do..) Assuming no concept of role ownership exists, but that DROP ROLE bob CASCADE is implemented in a spec compliant way, if there is a role "bob" who owns various objects, what happens when DROP ROLE bob CASCADE is performed? Do bob's objects get dropped, do they get orphaned, or do they get assigned to some other owner? I would expect that they get dropped, but I'd like to know what the spec says about it before going any further with this discussion. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Role Self-Administration
Greetings, On Wed, Oct 6, 2021 at 16:28 Robert Haas wrote: > On Wed, Oct 6, 2021 at 3:29 PM Stephen Frost wrote: > > Does that mean that we also get to change what a specific set of > > commands, which are all well-defined in the standard, do even when that > > goes against what an SQL compliant implementation would do? I really > > don't think so. If this was *new* syntax to go along with some new > > feature or extension in PG, sure, we can define what that syntax does > > because the standard doesn't. In this case we're talking entirely about > > objects and statements which the standard does define. > > Well, I think what we're talking about is saying something like: > > CREATE USER mybigcustomer CREATEROLE; > > And then having the mybigcustomer role be able to create other roles, > which would be automatically dropped if I later said: > > DROP USER mybigcustomer CASCADE; > > Since AFAIK CREATEROLE is not in the specification, I think we're > perfectly free to say that it alters the behavior of the subsequent > DROP USER command in any way that we judge reasonable. I agree that we > need to have SQL-standard syntax do SQL-standard things, but it > doesn't have to be the case that the whole command goes unmentioned by > the specification. Options that we add to CREATE USER or CREATE TABLE > or any other command can modify the behavior of those objects, and the > spec has nothing to say about it. > > Now that doesn't intrinsically mean that it's a good idea. I think > what I hear you saying is that you find it pretty terrifying that > "DROP USER mybigcustomer CASCADE;" could blow away a lot of users and > a lot of tables and that could be scary. And I agree, but that's a > design question, not a spec question. Today, there is not, in > PostgreSQL, a DROP USER .. CASCADE variant. If there are objects that > depend on the user, DROP USER fails. So we could for example decide > that DROP USER .. CASCADE will cascade to other users, but not to > regular objects. Or maybe that's too inconsistent, and we should do > something like DROP ROLES OWNED BY [role]. Or maybe having both DROP > OWNED BY and DROP ROLES OWNED BY is too weird, and the existing DROP > OWNED BY [role] command should also cascade to roles. Those kinds of > things seem worth discussing to me, to come up with the behavior that > will work best for people. But I do disagree with the idea that we're > not free to innovate here. We make up new SQL syntax and new > configuration variables and all kinds of new things all the time, and > I don't think this is any different. This specific syntax, including the CASCADE bit, has, at minimum, at least been contemplate by the SQL folks sufficiently to be described in one specific way. I don’t have a copy of 2016 handy, unfortunately, and so I’m not sure if it’s described that way in a “stable” version of the standard or not (it isn’t defined in the 2006 draft I’ve seen), but ultimately I don’t think we are really talking about entirely net-new syntax here… If we were, that would be different and perhaps we would just be guessing at what the standard might do in the future, but I don’t think it’s an open ended question at this point.. (Even if it was, I have to say that the direction that they’re going in certainly seems consistent to me, anyway, with what’s been done in the past and I think it’d be bad of us to go in a different direction from that since it’d be difficult for us to change it later when the new spec comes out and contradicts what we decided to do..) Thanks, Stephen >
Re: Role Self-Administration
Greetings, On Wed, Oct 6, 2021 at 16:01 Mark Dilger wrote: > > On Oct 6, 2021, at 11:48 AM, Stephen Frost wrote: > > > > In the spec, under , there is a 'General Rules' > > section (as there is with most statements) and in that section it says > > that for every authorization identifier (that is, some privilege, be it > > a GRANT of SELECT rights on an object, or GRANT of role membership in > > some role) which references the role being dropped, the command: > > > > REVOKE R FROM A DB > > > > is effectively executed (without further access rule checking). > > I think you are saying that "DROP ROLE bob" implies revoking "bob" from > anybody who has membership in role bob. I agree with that entirely, and my > proposal does not change that. (Roles owned by "bob" are not typically > members of role "bob" to begin with.) Yes and no…. Specifically the spec says that “DROP ROLE bob CASCADE” implies revoking memberships that bob is in. The other drop behavior is “RESTRICT”, which, as you might expect, implies throwing an error instead. > What I'm saying above is that the command explicitly listed there > > *isn't* 'DROP ROLE A DB', even though that is something which the spec > > *could* have done, had they wished to. > > Clearly the spec could have said that "DROP ROLE bob" implies "and drop > all roles which are members of bob" and did not. I fullly agree with that > decision, and I'm not trying to change it one iota. I’m not talking about what the spec says for just “DROP ROLE bob”, but rather what the spec says for “DROP ROLE bob CASCADE”. The latest versions add the drop behavior syntax to the end of DROP ROLE and it can be either CASACDE or RESTRICT, and if it’s CASCADE then the rule is to run the REVOKEs that I’ve been talking about. > Given that they didn't, it seems > > very clear that making such a change would very much be a deviation and > > violation of the spec. > > Sure, and I'm not proposing any such change. But.. you are, because what I’ve been talking about has specifically been the spec-defined “CASCADE” case, not bare DROP ROLE. > That we invented some behind-the-scenes concept > > of role ownership where we track who actually created what role and then > > use that info to transform a REVOKE into a DROP doesn't make such a > > transformation OK. > > I think I understand why you say this. You seem to be conflating the idea > of having privileges on role "bob" to being owned by role "bob". That's > not the case. Maybe you are not conflating them, but I can't interpret > what you are saying otherwise. I’m talking specifically about what happens when someone runs a DROP ROLE with CASCADE. > Consider that with what you're proposing, a user could execute the > > following series of entirely SQL-spec compliant statements, and get > > very different results depending on if we have this 'ownership' concept > > or not: > > > > SET ROLE postgres; > > CREATE ROLE r1; > > > > SET ROLE r1; > > CREATE ROLE r2; > > > > SET ROLE postgres; > > DROP ROLE r1 CASCADE; > > > > With what you're suggesting, the end result would be that r2 no longer > > exists, whereas with the spec-defined behvaior, r2 *would* still exist. > > If you try this on postgres 14, you get a syntax error because CASCADE is > not supported in the grammar for DROP ROLE: > > mark.dilger=# drop role bob cascade; > ERROR: syntax error at or near "cascade" > > I don't know if those statements are "entirely SQL-spec compliant" because > I have yet to find a reference to the spec saying what DROP ROLE ... > CASCADE is supposed to do. I found some Vertica docs that say what Vertica > does. I found some Enterprise DB docs about what Advanced Server does (or > course, since I work here.) I don't see much else. They’re valid commands in the version I’m looking at, though I think actually that this is a pre-release as apparently 2016 is the latest when I thought there was something more recent. I’m not sure if the 2016 version included the CASCADE option for DROP ROLE or not. Even if it’s only a preview, sure looks like this is the direction they’re going in and it seems consistent, at least to me, with other things they’ve done in this area… You have quoted me parts of the spec about what REVOKE is supposed to do, > and I have responded about why I don't see the connection to DROP > ROLE...CASCADE. The bits from REVOKE that I quoted were only at the very start of this thread…. This entire sub thread has only been about the DROP ROLE statement.. Are there any other references to either the spec or how other common > databases handle this? Trying to get some more insight into the version of the spec I’m looking at and if I can figure out a way that you’d be able to see what I’m talking about directly. Thanks, Stephen >
Re: Role Self-Administration
On Wed, Oct 6, 2021 at 3:29 PM Stephen Frost wrote: > Does that mean that we also get to change what a specific set of > commands, which are all well-defined in the standard, do even when that > goes against what an SQL compliant implementation would do? I really > don't think so. If this was *new* syntax to go along with some new > feature or extension in PG, sure, we can define what that syntax does > because the standard doesn't. In this case we're talking entirely about > objects and statements which the standard does define. Well, I think what we're talking about is saying something like: CREATE USER mybigcustomer CREATEROLE; And then having the mybigcustomer role be able to create other roles, which would be automatically dropped if I later said: DROP USER mybigcustomer CASCADE; Since AFAIK CREATEROLE is not in the specification, I think we're perfectly free to say that it alters the behavior of the subsequent DROP USER command in any way that we judge reasonable. I agree that we need to have SQL-standard syntax do SQL-standard things, but it doesn't have to be the case that the whole command goes unmentioned by the specification. Options that we add to CREATE USER or CREATE TABLE or any other command can modify the behavior of those objects, and the spec has nothing to say about it. Now that doesn't intrinsically mean that it's a good idea. I think what I hear you saying is that you find it pretty terrifying that "DROP USER mybigcustomer CASCADE;" could blow away a lot of users and a lot of tables and that could be scary. And I agree, but that's a design question, not a spec question. Today, there is not, in PostgreSQL, a DROP USER .. CASCADE variant. If there are objects that depend on the user, DROP USER fails. So we could for example decide that DROP USER .. CASCADE will cascade to other users, but not to regular objects. Or maybe that's too inconsistent, and we should do something like DROP ROLES OWNED BY [role]. Or maybe having both DROP OWNED BY and DROP ROLES OWNED BY is too weird, and the existing DROP OWNED BY [role] command should also cascade to roles. Those kinds of things seem worth discussing to me, to come up with the behavior that will work best for people. But I do disagree with the idea that we're not free to innovate here. We make up new SQL syntax and new configuration variables and all kinds of new things all the time, and I don't think this is any different. -- Robert Haas EDB: http://www.enterprisedb.com
Re: Role Self-Administration
> On Oct 6, 2021, at 11:48 AM, Stephen Frost wrote: > > In the spec, under , there is a 'General Rules' > section (as there is with most statements) and in that section it says > that for every authorization identifier (that is, some privilege, be it > a GRANT of SELECT rights on an object, or GRANT of role membership in > some role) which references the role being dropped, the command: > > REVOKE R FROM A DB > > is effectively executed (without further access rule checking). I think you are saying that "DROP ROLE bob" implies revoking "bob" from anybody who has membership in role bob. I agree with that entirely, and my proposal does not change that. (Roles owned by "bob" are not typically members of role "bob" to begin with.) > What I'm saying above is that the command explicitly listed there > *isn't* 'DROP ROLE A DB', even though that is something which the spec > *could* have done, had they wished to. Clearly the spec could have said that "DROP ROLE bob" implies "and drop all roles which are members of bob" and did not. I fullly agree with that decision, and I'm not trying to change it one iota. > Given that they didn't, it seems > very clear that making such a change would very much be a deviation and > violation of the spec. Sure, and I'm not proposing any such change. > That we invented some behind-the-scenes concept > of role ownership where we track who actually created what role and then > use that info to transform a REVOKE into a DROP doesn't make such a > transformation OK. I think I understand why you say this. You seem to be conflating the idea of having privileges on role "bob" to being owned by role "bob". That's not the case. Maybe you are not conflating them, but I can't interpret what you are saying otherwise. > Consider that with what you're proposing, a user could execute the > following series of entirely SQL-spec compliant statements, and get > very different results depending on if we have this 'ownership' concept > or not: > > SET ROLE postgres; > CREATE ROLE r1; > > SET ROLE r1; > CREATE ROLE r2; > > SET ROLE postgres; > DROP ROLE r1 CASCADE; > > With what you're suggesting, the end result would be that r2 no longer > exists, whereas with the spec-defined behvaior, r2 *would* still exist. If you try this on postgres 14, you get a syntax error because CASCADE is not supported in the grammar for DROP ROLE: mark.dilger=# drop role bob cascade; ERROR: syntax error at or near "cascade" I don't know if those statements are "entirely SQL-spec compliant" because I have yet to find a reference to the spec saying what DROP ROLE ... CASCADE is supposed to do. I found some Vertica docs that say what Vertica does. I found some Enterprise DB docs about what Advanced Server does (or course, since I work here.) I don't see much else. You have quoted me parts of the spec about what REVOKE is supposed to do, and I have responded about why I don't see the connection to DROP ROLE...CASCADE. Are there any other references to either the spec or how other common databases handle this? — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Role Self-Administration
Greetings, * Robert Haas (robertmh...@gmail.com) wrote: > On Wed, Oct 6, 2021 at 2:48 PM Stephen Frost wrote: > > What I'm saying above is that the command explicitly listed there > > *isn't* 'DROP ROLE A DB', even though that is something which the spec > > *could* have done, had they wished to. Given that they didn't, it seems > > very clear that making such a change would very much be a deviation and > > violation of the spec. That we invented some behind-the-scenes concept > > of role ownership where we track who actually created what role and then > > use that info to transform a REVOKE into a DROP doesn't make such a > > transformation OK. > > If PostgreSQL implements extensions to the SQL specification, then we > get to decide how those features interact with the features that are > specified. Does that mean that we also get to change what a specific set of commands, which are all well-defined in the standard, do even when that goes against what an SQL compliant implementation would do? I really don't think so. If this was *new* syntax to go along with some new feature or extension in PG, sure, we can define what that syntax does because the standard doesn't. In this case we're talking entirely about objects and statements which the standard does define. > For example, I presume the spec doesn't say that you can drop a > function by dropping the extension that contains it, but that's just > because extensions as we have them in PostgreSQL are not part of the > SQL standard. It would be silly to have rejected that feature on those > grounds, because nobody is forced to use extensions, and if you don't, > then they do not cause any deviation from spec-mandated behavior. The prior example that I used didn't include *any* non-SQL standard statements, so I don't view this argument as applicable. > In the same way, nobody would be forced to make a role own another > role, and if you don't, then you'll never notice any deviation from > spec-mandated behavior on account of that feature. So you're suggesting that roles created by other roles wouldn't *automatically* by owned by the creating role and that, instead, someone would have to explicitly say something like: ALTER ROLE x OWNED BY y; after the role is created, and only then would a DROP ROLE y CASCADE; turn into DROP ROLE x CASCADE; DROP ROLE y CASCADE; and that, absent that happening, a DROP ROLE y CASCADE; would do what the standard says, and not actually DROP all the associated objects but only run the REVOKE statements? I'll accept that, in such a case, we could argue that we're no longer following the spec because the user has started to use some PG extension to the spec, but, I've got a really hard time seeing how such a massive difference in what DROP ROLE x CASCADE; does would be acceptable or at all reasonable. One could lead to hundreds of tables being dropped out of the database and a massive outage while the other would just mean some role memberships get cleaned up as part of a role being dropped. Having one command that does two vastly different things like that is a massive, loaded, foot-pointed gun. > If the SQL specification says that roles can own other roles, but that > DROP has to have some special behavior in regards to that feature, > then we should probably try to do what the spec says. But if the spec > doesn't think that the concept of roles owning other roles even > exists, but we choose to invent such a concept, then I think we can > make it work however we like without worrying about > spec-compatibility. We've already invented lots of other things like > that, and the project is the better for it. The SQL spec doesn't say that roles can own other roles. I don't think that means we get to rewrite what DROP ROLE ... CASCADE does. Extend DROP ROLE with other parameters which are relevant to our extension of the spec? Sure, perhaps, but not entirely redefine what the base command does to be different from what the SQL spec says it does. Thanks, Stephen signature.asc Description: PGP signature
Re: Role Self-Administration
On Wed, Oct 6, 2021 at 2:48 PM Stephen Frost wrote: > What I'm saying above is that the command explicitly listed there > *isn't* 'DROP ROLE A DB', even though that is something which the spec > *could* have done, had they wished to. Given that they didn't, it seems > very clear that making such a change would very much be a deviation and > violation of the spec. That we invented some behind-the-scenes concept > of role ownership where we track who actually created what role and then > use that info to transform a REVOKE into a DROP doesn't make such a > transformation OK. If PostgreSQL implements extensions to the SQL specification, then we get to decide how those features interact with the features that are specified. For example, I presume the spec doesn't say that you can drop a function by dropping the extension that contains it, but that's just because extensions as we have them in PostgreSQL are not part of the SQL standard. It would be silly to have rejected that feature on those grounds, because nobody is forced to use extensions, and if you don't, then they do not cause any deviation from spec-mandated behavior. In the same way, nobody would be forced to make a role own another role, and if you don't, then you'll never notice any deviation from spec-mandated behavior on account of that feature. If the SQL specification says that roles can own other roles, but that DROP has to have some special behavior in regards to that feature, then we should probably try to do what the spec says. But if the spec doesn't think that the concept of roles owning other roles even exists, but we choose to invent such a concept, then I think we can make it work however we like without worrying about spec-compatibility. We've already invented lots of other things like that, and the project is the better for it. -- Robert Haas EDB: http://www.enterprisedb.com
Re: Role Self-Administration
Greetings, * Mark Dilger (mark.dil...@enterprisedb.com) wrote: > > On Oct 6, 2021, at 11:09 AM, Stephen Frost wrote: > > After all, it says > > "REOKVE R FROM A DB", not "DROP ROLE A CASCADE". > > Wait, are you arguing what DROP ROLE A CASCADE should do based on what the > spec says REVOKE R FROM A DB should do? If so, I'd say that's irrelevant. > I'm not proposing to change what REVOKE does. If not, could you clarify? > Did I misunderstand? No, that's not what I'm saying. In the spec, under , there is a 'General Rules' section (as there is with most statements) and in that section it says that for every authorization identifier (that is, some privilege, be it a GRANT of SELECT rights on an object, or GRANT of role membership in some role) which references the role being dropped, the command: REVOKE R FROM A DB is effectively executed (without further access rule checking). What I'm saying above is that the command explicitly listed there *isn't* 'DROP ROLE A DB', even though that is something which the spec *could* have done, had they wished to. Given that they didn't, it seems very clear that making such a change would very much be a deviation and violation of the spec. That we invented some behind-the-scenes concept of role ownership where we track who actually created what role and then use that info to transform a REVOKE into a DROP doesn't make such a transformation OK. Consider that with what you're proposing, a user could execute the following series of entirely SQL-spec compliant statements, and get very different results depending on if we have this 'ownership' concept or not: SET ROLE postgres; CREATE ROLE r1; SET ROLE r1; CREATE ROLE r2; SET ROLE postgres; DROP ROLE r1 CASCADE; With what you're suggesting, the end result would be that r2 no longer exists, whereas with the spec-defined behvaior, r2 *would* still exist. If that doesn't make it clear enough then I'm afraid you'll just need to either acquire a copy of the spec and point out what I'm misunderstanding in it (or get someone else to who has access to it), or accept that we need to use some other syntax for this capability. I don't think it's unreasonable to have different syntax for this, particularly as it's a concept that doesn't even exist in the standard (as far as I can tell, anyway). Adopting SQL defined syntax to use with a concept that the standard doesn't have sure seems like a violation of the POLA. If you feel really strongly that this must be part of DROP ROLE then maybe we could do something like: DROP ROLE r1 CASCADE OWNED ROLES; or come up with something else, but just changing what DROP ROLE .. CASCADE is defined by the spec to do isn't the right approach, imv. Thanks, Stephen signature.asc Description: PGP signature
Re: Role Self-Administration
> On Oct 6, 2021, at 11:09 AM, Stephen Frost wrote: > > After all, it says > "REOKVE R FROM A DB", not "DROP ROLE A CASCADE". Wait, are you arguing what DROP ROLE A CASCADE should do based on what the spec says REVOKE R FROM A DB should do? If so, I'd say that's irrelevant. I'm not proposing to change what REVOKE does. If not, could you clarify? Did I misunderstand? — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Role Self-Administration
Greetings, * Mark Dilger (mark.dil...@enterprisedb.com) wrote: > > On Oct 6, 2021, at 10:20 AM, Stephen Frost wrote: > > > > Consistency is not having dangling pointers around to things which no > > longer exist- FK reference kind of things. Object management is about > > actual *removal* of full blown objects like roles, tables, etc. DROP > > TABLE ... CASCADE doesn't drop tables which haven an FK dependency on > > the dropped table, the FK is just removed. > > Right, but DROP SCHEMA ... CASCADE does remove the tables within, no? I > would see alice being a member of role bob as being analogous to the foreign > key example, and charlie being owned by bob as being more like the table > within a schema. Objects aren't able to live outside of a schema, so it doesn't seem to be quite the same case there. Further, DROP SCHEMA is defined in the standard as saying: DROP (TABLE, VIEW, DOMAIN, etc) T CASCADE > I'm fine with using a different syntax for this if what i'm proposing > violates the spec. I'm just trying to wrap my head around how to interpret > the spec (of which i have no copy, mind you.) I'm trying to distinguish > between statements like X SHALL DO Y and X SHALL DO NOTHING BUT Y. I don't > know if the spec contains a concept of roles owning other roles, and if not, > does it forbid that concept? I should think that if that concept is a > postgres extension not present in the spec, then we can make it do anything > we want. I do think what you're suggesting is pretty clearly not what the SQL committee imagined DROP ROLE ... CASCADE to do. After all, it says "REOKVE R FROM A DB", not "DROP ROLE A CASCADE". Unfortunately, more recent versions of the spec don't seem to be available very easily and the older draft that I've seen around doesn't have CASCADE on DROP ROLE. Working with roles, which are defined in the spec, it seems pretty important to have access to the spec though to see these things. As far as I can tell, no, there isn't a concept of role 'ownership' in the spec. If there was then perhaps things would be different ... but that's not the case. I disagree quite strongly that adding such an extension would allow us to seriuosly deviate from what the spec says should happen regarding DROP ROLE ... CASCADE though. If that argument held water, we could ignore what the spec says about just about anything because PG has features that aren't in the spec. Thanks, Stephen signature.asc Description: PGP signature
Re: Role Self-Administration
> On Oct 6, 2021, at 10:20 AM, Stephen Frost wrote: > > Consistency is not having dangling pointers around to things which no > longer exist- FK reference kind of things. Object management is about > actual *removal* of full blown objects like roles, tables, etc. DROP > TABLE ... CASCADE doesn't drop tables which haven an FK dependency on > the dropped table, the FK is just removed. Right, but DROP SCHEMA ... CASCADE does remove the tables within, no? I would see alice being a member of role bob as being analogous to the foreign key example, and charlie being owned by bob as being more like the table within a schema. I'm fine with using a different syntax for this if what i'm proposing violates the spec. I'm just trying to wrap my head around how to interpret the spec (of which i have no copy, mind you.) I'm trying to distinguish between statements like X SHALL DO Y and X SHALL DO NOTHING BUT Y. I don't know if the spec contains a concept of roles owning other roles, and if not, does it forbid that concept? I should think that if that concept is a postgres extension not present in the spec, then we can make it do anything we want. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Role Self-Administration
Greetings, * Mark Dilger (mark.dil...@enterprisedb.com) wrote: > > On Oct 6, 2021, at 9:01 AM, Stephen Frost wrote: > > I can see how what you describe as the behavior you'd like to see of > > DROP ROLE ... CASCADE could be useful... However, at least in the > > latest version of the standard that I'm looking at, when a > > DROP ROLE ... CASCADE is executed, what happens for all authorization > > identifiers is: > > > > REVOKE R FROM A DB > > > > Where R is the role being dropped and A is the authoriztaion identifier. > > I'm not proposing that all roles with membership in bob be dropped when role > bob is dropped. I'm proposing that all roles *owned by* role bob also be > dropped. Postgres doesn't currently have a concept of roles owning other > roles, but I'm proposing that we add such a concept. Of course, any role > with membership in role bob would no longer have that membership, and any > role managed by bob would not longer be managed by bob. The CASCADE would > not result drop those other roles merely due to membership or management > relationships. I get all of that ... but you're also talking about changing the behavior of something which is defined pretty clearly in the standard to be something that's very different from what the standard says. > > In other words, the SQL committee seems to disagree with you when it > > comes to what CASCADE on DROP ROLE means (though I can't say I'm too > > surprised- generally speaking, CASCADE is about getting rid of the > > dependency so the system stays consistent, not as a method of object > > management...). > > I'm not sure I understand how what they are saying disagrees with what I am > saying, unless they are saying that REVOKE R FROM A DB is the one and only > thing that DROP ROLE .. CASCADE can do. If they are excluding that it do > anything else, then yes, that would be an incompatibility. That is exactly what DROP ROLE ... CASCADE is defined in the standard to do. That definition covers not just permissions on objects but also permissions on roles. To take that and turn it into a DROP ROLE for roles looks like a *very* clear and serious deviation from the standard. If we were to go down this road, I'd suggest we have some *other* syntax that isn't defined by the standard to do something else. eg: DROP ROLES OWNED BY R; or something along those lines. I'm not saying that your idea is without merit or that it wouldn't be useful, I'm just trying to make it clear that the standard already says what DROP ROLE .. CASCADE means and we should be loath to deviate very far from that. > As far as keeping the system consistent, I think that's what this does. As > soon as a role is defined as owning other stuff, then dropping the role > cascade means dropping the other stuff. > > Could you elaborate more on the difference between object management and > consistency as it applies to this issue? Consistency is not having dangling pointers around to things which no longer exist- FK reference kind of things. Object management is about actual *removal* of full blown objects like roles, tables, etc. DROP TABLE ... CASCADE doesn't drop tables which haven an FK dependency on the dropped table, the FK is just removed. Thanks, Stephen signature.asc Description: PGP signature
Re: Role Self-Administration
> On Oct 6, 2021, at 9:01 AM, Stephen Frost wrote: > > I can see how what you describe as the behavior you'd like to see of > DROP ROLE ... CASCADE could be useful... However, at least in the > latest version of the standard that I'm looking at, when a > DROP ROLE ... CASCADE is executed, what happens for all authorization > identifiers is: > > REVOKE R FROM A DB > > Where R is the role being dropped and A is the authoriztaion identifier. I'm not proposing that all roles with membership in bob be dropped when role bob is dropped. I'm proposing that all roles *owned by* role bob also be dropped. Postgres doesn't currently have a concept of roles owning other roles, but I'm proposing that we add such a concept. Of course, any role with membership in role bob would no longer have that membership, and any role managed by bob would not longer be managed by bob. The CASCADE would not result drop those other roles merely due to membership or management relationships. > In other words, the SQL committee seems to disagree with you when it > comes to what CASCADE on DROP ROLE means (though I can't say I'm too > surprised- generally speaking, CASCADE is about getting rid of the > dependency so the system stays consistent, not as a method of object > management...). I'm not sure I understand how what they are saying disagrees with what I am saying, unless they are saying that REVOKE R FROM A DB is the one and only thing that DROP ROLE .. CASCADE can do. If they are excluding that it do anything else, then yes, that would be an incompatibility. As far as keeping the system consistent, I think that's what this does. As soon as a role is defined as owning other stuff, then dropping the role cascade means dropping the other stuff. Could you elaborate more on the difference between object management and consistency as it applies to this issue? — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Role Self-Administration
Greetings, * Mark Dilger (mark.dil...@enterprisedb.com) wrote: > > On Oct 5, 2021, at 10:20 AM, Stephen Frost wrote: > > On Tue, Oct 5, 2021 at 13:17 Mark Dilger > > wrote: > > > On Oct 5, 2021, at 10:14 AM, Stephen Frost wrote: > > > > > > What does the “ownership” concept actually buy us then? > > > > DROP ... CASCADE > > > > I’m not convinced that we need to invent the concept of ownership in order > > to find a sensible way to make this work- though it would be helpful to > > first get everyone’s idea of just what *would* this command do if run on a > > role who “owns” or has “admin rights” of another role? > > Ok, I'll start. Here is how I envision it: > > If roles have owners, then DROP ROLE bob CASCADE drops bob, bob's objects, > roles owned by bob, their objects and any roles they own, recursively. Roles > which bob merely has admin rights on are unaffected, excepting that they are > administered by one fewer roles once bob is gone. > > This design allows you to delegate to a new role some task, and you don't > have to worry what network of other roles and objects they create, because in > the end you just drop the one role cascade and all that other stuff is > guaranteed to be cleaned up without any leaks. > > If roles do not have owners, then DROP ROLE bob CASCADE drops role bob plus > all objects that bob owns. It doesn't cascade to other roles because the > concept of "roles that bob owns" doesn't exist. If bob created other roles, > those will be left around. Objects that bob created and then transferred to > these other roles are also left around. I can see how what you describe as the behavior you'd like to see of DROP ROLE ... CASCADE could be useful... However, at least in the latest version of the standard that I'm looking at, when a DROP ROLE ... CASCADE is executed, what happens for all authorization identifiers is: REVOKE R FROM A DB Where R is the role being dropped and A is the authoriztaion identifier. In other words, the SQL committee seems to disagree with you when it comes to what CASCADE on DROP ROLE means (though I can't say I'm too surprised- generally speaking, CASCADE is about getting rid of the dependency so the system stays consistent, not as a method of object management...). I'm not against having something that would do what you want, but it seems like we'd have to at least call it something else and maybe we should worry about that later, once we've addressed the bigger issue of making the system handle GRANTORs correctly. Thanks, Stephen signature.asc Description: PGP signature
Re: Role Self-Administration
On Tue, Oct 5, 2021 at 3:41 PM Mark Dilger wrote: > If roles have owners, then DROP ROLE bob CASCADE drops bob, bob's objects, > roles owned by bob, their objects and any roles they own, recursively. Roles > which bob merely has admin rights on are unaffected, excepting that they are > administered by one fewer roles once bob is gone. > > This design allows you to delegate to a new role some task, and you don't > have to worry what network of other roles and objects they create, because in > the end you just drop the one role cascade and all that other stuff is > guaranteed to be cleaned up without any leaks. > > If roles do not have owners, then DROP ROLE bob CASCADE drops role bob plus > all objects that bob owns. It doesn't cascade to other roles because the > concept of "roles that bob owns" doesn't exist. If bob created other roles, > those will be left around. Objects that bob created and then transferred to > these other roles are also left around. I'm not sure that I'm totally on board with the role ownership concept, but I do think it has some potential advantages. For instance, suppose the dba creates a bunch of "master tenant" roles which correspond to particular customers: say, amazon, google, and facebook. Now each of those master tenant rolls creates roles under it which represent the various people or applications from those companies that will be accessing the server: e.g. sbrin and lpage. Now, if Google runs out of money and stops paying the hosting bill, we can just "DROP ROLE google CASCADE" and sbrin and lpage get nuked too. That's kind of cool. What happens if we don't have that? Then we'll need to work harder to make sure all traces of Google are expunged from the system. In fact, how do we do that, exactly? In this particular instance it should be straightforward: if we see that google can administrer sbrin and lpage and nobody else can, then it probably follows that those roles should be nuked when the google role is nuked. But what if we have separate users apple and nextstep either of whom can administer the role sjobs? If nextstep goes away, we had better not remove sjobs because he's still able to be administered by apple, but if apple also goes away, then we'll want to remove sjobs then. That's doable, but complicated, and all the logic that figures this out now lives outside the database. With role ownership, we can enforce that the roles form a tree, and subtrees can be easily lopped off without the user needing to do anything complicated. Without role ownership, we've just got a directed graph of who can administer who, and it need not be connected or acyclic. Now we may be able to cope with that, or we may be able to set things up so that users can cope with that using logic external to the database without anything getting too complicated. But I certainly see the appeal of a system where the lines of control form a DAG rather than a general directed graph. It seems to make it a whole lot easier to reason about what operations should and should not be permitted and how the whole thing should actually work. It's a fairly big change from the status quo, though, and maybe it has disadvantages that make it a suboptimal choice. -- Robert Haas EDB: http://www.enterprisedb.com
Re: Role Self-Administration
> On Oct 5, 2021, at 10:20 AM, Stephen Frost wrote: > > Greetings, > > On Tue, Oct 5, 2021 at 13:17 Mark Dilger wrote: > > On Oct 5, 2021, at 10:14 AM, Stephen Frost wrote: > > > > What does the “ownership” concept actually buy us then? > > DROP ... CASCADE > > I’m not convinced that we need to invent the concept of ownership in order to > find a sensible way to make this work- though it would be helpful to first > get everyone’s idea of just what *would* this command do if run on a role who > “owns” or has “admin rights” of another role? Ok, I'll start. Here is how I envision it: If roles have owners, then DROP ROLE bob CASCADE drops bob, bob's objects, roles owned by bob, their objects and any roles they own, recursively. Roles which bob merely has admin rights on are unaffected, excepting that they are administered by one fewer roles once bob is gone. This design allows you to delegate to a new role some task, and you don't have to worry what network of other roles and objects they create, because in the end you just drop the one role cascade and all that other stuff is guaranteed to be cleaned up without any leaks. If roles do not have owners, then DROP ROLE bob CASCADE drops role bob plus all objects that bob owns. It doesn't cascade to other roles because the concept of "roles that bob owns" doesn't exist. If bob created other roles, those will be left around. Objects that bob created and then transferred to these other roles are also left around. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Role Self-Administration
Greetings, On Tue, Oct 5, 2021 at 13:17 Mark Dilger wrote: > > On Oct 5, 2021, at 10:14 AM, Stephen Frost wrote: > > > > What does the “ownership” concept actually buy us then? > > DROP ... CASCADE I’m not convinced that we need to invent the concept of ownership in order to find a sensible way to make this work- though it would be helpful to first get everyone’s idea of just what *would* this command do if run on a role who “owns” or has “admin rights” of another role? Thanks, Stephen >
Re: Role Self-Administration
> On Oct 5, 2021, at 10:14 AM, Stephen Frost wrote: > > What does the “ownership” concept actually buy us then? DROP ... CASCADE. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Role Self-Administration
Greetings, On Tue, Oct 5, 2021 at 13:09 Robert Haas wrote: > On Tue, Oct 5, 2021 at 12:38 PM Mark Dilger > wrote: > > Additionally, role "alice" might not exist anymore, which would leave > the privilege irrevocable. > > I thought that surely this couldn't be right, but apparently we have > absolutely no problem with leaving the "grantor" column in pg_authid > as a dangling reference to a pg_authid role that no longer exists: > rhaas=# select * from pg_auth_members where grantor not in (select oid > from pg_authid); > roleid | member | grantor | admin_option > ++-+-- >3373 | 16412 | 16410 | f > (1 row) > > Yikes. We'd certainly have to do something about that if we want to > use the grantor field for anything security-sensitive, since otherwise > hilarity would ensue if that OID got recycled for a new role at any > later point in time. Yeah, ew. We should just fix this. This seems weirdly inconsistent with what we do in other cases: > > rhaas=# create table foo (a int, b text); > CREATE TABLE > rhaas=# grant select on table foo to alice with grant option; > GRANT > rhaas=# \c rhaas alice > You are now connected to database "rhaas" as user "alice". > rhaas=> grant select on table foo to bob; > GRANT > rhaas=> \c - rhaas > You are now connected to database "rhaas" as user "rhaas". > rhaas=# drop role alice; > ERROR: role "alice" cannot be dropped because some objects depend on it > DETAIL: privileges for table foo > rhaas=# > > Here, because the ACL on table foo records alice as a grantor, alice > cannot be dropped. But when alice is the grantor of a role, the same > rule doesn't apply. I think the behavior shown in this example, where > alice can't be dropped, is the right behavior, and the behavior for > roles is just plain broken. Agreed. Thanks, Stephen >
Re: Role Self-Administration
Greetings, On Tue, Oct 5, 2021 at 12:38 Mark Dilger wrote: > > > > On Oct 5, 2021, at 9:23 AM, Robert Haas wrote: > > > >> - Disallow roles from being able to REVOKE role membership that they > >> didn't GRANT in the first place. > > > > I think that's not quite the right test. For example, if alice and bob > > are superusers and alice grants pg_monitor to doug, bob should be able > > to revoke that grant even though he is not alice. > > Additionally, role "alice" might not exist anymore, which would leave the > privilege irrevocable. Do we actually allow that case to happen today..? I didn’t think we did and instead there’s a dependency from the grant on to the Alice role. If that doesn’t exist today then I would think we’d need that and therefore this concern isn’t an issue. It's helpful to think in terms of role ownership rather than role creation: > > superuser > +---> alice > +---> charlie > +---> diane > +---> bob > > It makes sense that alice can take ownership of diane and drop charlie, > but not that bob can do so. Nor should charlie be able to transfer > ownership of diane to alice. Nor should charlie be able to drop himself. I dislike moving away from the ADMIN OPTION when it comes to roles as it puts us outside of the SQL standard. Having the ADMIN OPTION for a role seems, at least to me, to basically mean the things you’re suggesting “ownership” to mean- so why have two different things, especially when one doesn’t exist as a concept in the standard..? I agree that Charlie shouldn’t be able to drop themselves in general, but I don’t think we need an “ownership” concept for that. We also prevent loops already which I think is called for in the standard already (would need to go reread and make sure though) which already prevents Charlie from granting Diane to Alice. What does the “ownership” concept actually buy us then? Thanks, Stephen >
Re: Role Self-Administration
On Tue, Oct 5, 2021 at 12:38 PM Mark Dilger wrote: > Additionally, role "alice" might not exist anymore, which would leave the > privilege irrevocable. I thought that surely this couldn't be right, but apparently we have absolutely no problem with leaving the "grantor" column in pg_authid as a dangling reference to a pg_authid role that no longer exists: rhaas=# select * from pg_auth_members where grantor not in (select oid from pg_authid); roleid | member | grantor | admin_option ++-+-- 3373 | 16412 | 16410 | f (1 row) Yikes. We'd certainly have to do something about that if we want to use the grantor field for anything security-sensitive, since otherwise hilarity would ensue if that OID got recycled for a new role at any later point in time. This seems weirdly inconsistent with what we do in other cases: rhaas=# create table foo (a int, b text); CREATE TABLE rhaas=# grant select on table foo to alice with grant option; GRANT rhaas=# \c rhaas alice You are now connected to database "rhaas" as user "alice". rhaas=> grant select on table foo to bob; GRANT rhaas=> \c - rhaas You are now connected to database "rhaas" as user "rhaas". rhaas=# drop role alice; ERROR: role "alice" cannot be dropped because some objects depend on it DETAIL: privileges for table foo rhaas=# Here, because the ACL on table foo records alice as a grantor, alice cannot be dropped. But when alice is the grantor of a role, the same rule doesn't apply. I think the behavior shown in this example, where alice can't be dropped, is the right behavior, and the behavior for roles is just plain broken. -- Robert Haas EDB: http://www.enterprisedb.com
Re: Role Self-Administration
Greetings, On Tue, Oct 5, 2021 at 12:23 Robert Haas wrote: > On Mon, Oct 4, 2021 at 10:57 PM Stephen Frost wrote: > > - Disallow roles from being able to REVOKE role membership that they > > didn't GRANT in the first place. > > I think that's not quite the right test. For example, if alice and bob > are superusers and alice grants pg_monitor to doug, bob should be able > to revoke that grant even though he is not alice. > > I think the rule should be: roles shouldn't be able to REVOKE role > memberships unless they can become the grantor. Yes, role membership still equating to “being” that role still holds with this, even though I didn’t say so explicitly. But I think maybe if it should even be more general than that and > apply to all sorts of grants, rather than just roles and role > memberships: roles shouldn't be able to REVOKE any granted permission > unless they can become the grantor. Right, this was covered towards the end of my email, though again evidently not clearly enough, sorry about that. For example, if bob grants SELECT on one of his tables to alice, he > should be able to revoke the grant, too. But if the superuser performs > the grant, why should bob be able to revoke it? The superuser has > spoken, and bob shouldn't get to interfere ... unless of course he's > also a superuser. Mostly agreed except I’d exclude the explicit “superuser” flag bit and just say if r1 granted the right, r2 shouldn’t be the one who is allowed to revoke it until r2 happens to also be a member of r1. Thanks, Stephen >
Re: Role Self-Administration
> On Oct 5, 2021, at 9:23 AM, Robert Haas wrote: > >> - Disallow roles from being able to REVOKE role membership that they >> didn't GRANT in the first place. > > I think that's not quite the right test. For example, if alice and bob > are superusers and alice grants pg_monitor to doug, bob should be able > to revoke that grant even though he is not alice. Additionally, role "alice" might not exist anymore, which would leave the privilege irrevocable. It's helpful to think in terms of role ownership rather than role creation: superuser +---> alice +---> charlie +---> diane +---> bob It makes sense that alice can take ownership of diane and drop charlie, but not that bob can do so. Nor should charlie be able to transfer ownership of diane to alice. Nor should charlie be able to drop himself. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Role Self-Administration
On Mon, Oct 4, 2021 at 10:57 PM Stephen Frost wrote: > - Disallow roles from being able to REVOKE role membership that they > didn't GRANT in the first place. I think that's not quite the right test. For example, if alice and bob are superusers and alice grants pg_monitor to doug, bob should be able to revoke that grant even though he is not alice. I think the rule should be: roles shouldn't be able to REVOKE role memberships unless they can become the grantor. But I think maybe if it should even be more general than that and apply to all sorts of grants, rather than just roles and role memberships: roles shouldn't be able to REVOKE any granted permission unless they can become the grantor. For example, if bob grants SELECT on one of his tables to alice, he should be able to revoke the grant, too. But if the superuser performs the grant, why should bob be able to revoke it? The superuser has spoken, and bob shouldn't get to interfere ... unless of course he's also a superuser. -- Robert Haas EDB: http://www.enterprisedb.com
Re: Role Self-Administration
Greetings, * Noah Misch (n...@leadboat.com) wrote: > On Mon, Oct 04, 2021 at 10:57:46PM -0400, Stephen Frost wrote: > > "A role is not considered to hold WITH ADMIN OPTION on itself, but it > > may grant or revoke membership in itself from a database session where > > the session user matches the role." > > > Here's the thing - having looked back through the standard, it seems > > we're missing a bit that's included there and that makes a heap of > > difference. Specifically, the SQL standard basically says that to > > revoke a privilege, you need to have been able to grant that privilege > > in the first place (as Andrew Dunstan actually also brought up in a > > recent thread about related CREATEROLE things- > > https://www.postgresql.org/message-id/837cc50a-532a-85f5-a231-9d68f2184e52%40dunslane.net > > ) and that isn't something we've been considering when it comes to role > > 'self administration' thus far, at least as it relates to the particular > > field of the "grantor". > > Which SQL standard clauses are you paraphrasing? (A reference could take the > form of a spec version number, section number, and rule number. Alternately, > a page number and URL to a PDF would suffice.) 12.7 Specifically the bit about how a role authorization is said to be identified if it defines the grant of the role revoked to the grantee *with grantor A*. Reading it again these many years later, that seems to indicate that you need to actually be the grantor or able to be the grantor who performed the original grant in order to revoke it, something that wasn't done in the original implementation of roles. > > We can't possibly make things like EVENT TRIGGERs or CREATEROLE work > > with role trees if a given role can basically just 'opt out' of being > > part of the tree to which they were assigned by the user who created > > them. Therefore, I suggest we contemplate two changes in this area: > > I suspect we'll regret using the GRANT system to modify behaviors other than > whether or not one gets "permission denied". Hence, -1 on using role > membership to control event trigger firing, whether or not $SUBJECT changes. I've not been entirely sure if that's a great idea or not either, but I didn't see any particular holes in Tom's suggestion that we use this as a way to identify a tree of roles, except for this particular issue that a role is currently able to 'opt out', which seems like a mistake in the original role implementation and not an issue with Tom's actual idea to use it in this way. I do think that getting the role management sorted out with just the general concepts of 'tenant' and 'landlord' as discussed in the thread with Mark about changes to CREATEROLE and adding of other predefined roles is a necessary first step, and only after we feel like we've solved that should we come back to the idea of using that for other things, such as event trigger firing. > > - Allow a user who is able to create roles decide if the role created is > > able to 'self administor' (that is- GRANT their own role to someone > > else) itself. > > > > - Disallow roles from being able to REVOKE role membership that they > > didn't GRANT in the first place. > > Either of those could be reasonable. Does the SQL standard take a position > relevant to the decision? A third option is to track each role's creator and > make is_admin_of_role() return true for the creator, whether or not the > creator remains a member. That would also benefit cases where the creator is > rolinherit and wants its ambient privileges to shed the privileges of the role > it's creating. It's a bit dense, but my take on the revoke statement description is that the short answer is "yes, the standard does take a position on this" at least as it relates to role memberships. As for if a role would have the ability to control it for themselves, that seems like a natural extension of the general approach whereby a role can't grant themselves admin role on their own role if they don't already have it, but some other, appropriately privileged role, could. I don't feel it's necessary to track additional information about who created a specific role. Simply having, when that role is created, the creator be automatically granted admin rights on the role created seems like it'd be sufficient. > > We should probably do a more thorough review > > to see if there's other cases where a given role is able to REVOKE > > rights that have been GRANT'd by some other role on a particular object, > > as it seems like we should probably be consistent in this regard across > > everything and not just for roles. That might be a bit of a pain but it > > seems likely to be worth it in the l
Re: Role Self-Administration
On Mon, Oct 04, 2021 at 10:57:46PM -0400, Stephen Frost wrote: > "A role is not considered to hold WITH ADMIN OPTION on itself, but it > may grant or revoke membership in itself from a database session where > the session user matches the role." > Here's the thing - having looked back through the standard, it seems > we're missing a bit that's included there and that makes a heap of > difference. Specifically, the SQL standard basically says that to > revoke a privilege, you need to have been able to grant that privilege > in the first place (as Andrew Dunstan actually also brought up in a > recent thread about related CREATEROLE things- > https://www.postgresql.org/message-id/837cc50a-532a-85f5-a231-9d68f2184e52%40dunslane.net > ) and that isn't something we've been considering when it comes to role > 'self administration' thus far, at least as it relates to the particular > field of the "grantor". Which SQL standard clauses are you paraphrasing? (A reference could take the form of a spec version number, section number, and rule number. Alternately, a page number and URL to a PDF would suffice.) > We can't possibly make things like EVENT TRIGGERs or CREATEROLE work > with role trees if a given role can basically just 'opt out' of being > part of the tree to which they were assigned by the user who created > them. Therefore, I suggest we contemplate two changes in this area: I suspect we'll regret using the GRANT system to modify behaviors other than whether or not one gets "permission denied". Hence, -1 on using role membership to control event trigger firing, whether or not $SUBJECT changes. > - Allow a user who is able to create roles decide if the role created is > able to 'self administor' (that is- GRANT their own role to someone > else) itself. > > - Disallow roles from being able to REVOKE role membership that they > didn't GRANT in the first place. Either of those could be reasonable. Does the SQL standard take a position relevant to the decision? A third option is to track each role's creator and make is_admin_of_role() return true for the creator, whether or not the creator remains a member. That would also benefit cases where the creator is rolinherit and wants its ambient privileges to shed the privileges of the role it's creating. > We should probably do a more thorough review > to see if there's other cases where a given role is able to REVOKE > rights that have been GRANT'd by some other role on a particular object, > as it seems like we should probably be consistent in this regard across > everything and not just for roles. That might be a bit of a pain but it > seems likely to be worth it in the long run and feels like it'd bring us > more in-line with the SQL standard too. Does the SQL standard take a position on whether REVOKE SELECT should work that way?
Role Self-Administration
Greetings, There's been various discussions about CREATEROLE, EVENT TRIGGERs, and other things which hinge around the general idea that we can create a 'tree' of roles where there's some root and then from that root there's a set of created roles, or at least roles which have been GRANT'd other roles as part of an explicit arrangement. The issue with many of these suggestions is that roles, currently, are able to 'administer' themselves. That means that such role memberships aren't suitable for such controls. To wit, this happens: Superuser: =# create user u1; CREATE ROLE =# create user u2; CREATE ROLE =# grant u2 to u1; GRANT ROLE ... Log in as u2: => revoke u2 from u1; REVOKE ROLE ... This is because we allow 'self administration' of roles, meaning that they can decide what other roles they are a member of. This is documented as: "A role is not considered to hold WITH ADMIN OPTION on itself, but it may grant or revoke membership in itself from a database session where the session user matches the role." at: https://www.postgresql.org/docs/current/sql-grant.html Further, we comment this in the code: * A role can admin itself when it matches the session user and we're * outside any security-restricted operation, SECURITY DEFINER or * similar context. SQL-standard roles cannot self-admin. However, * SQL-standard users are distinct from roles, and they are not * grantable like roles: PostgreSQL's role-user duality extends the * standard. Checking for a session user match has the effect of * letting a role self-admin only when it's conspicuously behaving * like a user. Note that allowing self-admin under a mere SET ROLE * would make WITH ADMIN OPTION largely irrelevant; any member could * SET ROLE to issue the otherwise-forbidden command. in src/backend/utils/adt/acl.c Here's the thing - having looked back through the standard, it seems we're missing a bit that's included there and that makes a heap of difference. Specifically, the SQL standard basically says that to revoke a privilege, you need to have been able to grant that privilege in the first place (as Andrew Dunstan actually also brought up in a recent thread about related CREATEROLE things- https://www.postgresql.org/message-id/837cc50a-532a-85f5-a231-9d68f2184e52%40dunslane.net ) and that isn't something we've been considering when it comes to role 'self administration' thus far, at least as it relates to the particular field of the "grantor". We can't possibly make things like EVENT TRIGGERs or CREATEROLE work with role trees if a given role can basically just 'opt out' of being part of the tree to which they were assigned by the user who created them. Therefore, I suggest we contemplate two changes in this area: - Allow a user who is able to create roles decide if the role created is able to 'self administor' (that is- GRANT their own role to someone else) itself. - Disallow roles from being able to REVOKE role membership that they didn't GRANT in the first place. This isn't as big a change as it might seem as we already track which role issued a given GRANT. We should probably do a more thorough review to see if there's other cases where a given role is able to REVOKE rights that have been GRANT'd by some other role on a particular object, as it seems like we should probably be consistent in this regard across everything and not just for roles. That might be a bit of a pain but it seems likely to be worth it in the long run and feels like it'd bring us more in-line with the SQL standard too. So, thoughts? Thanks! Stephen signature.asc Description: PGP signature
