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-
/messages/by-id/837cc50a-532a-85f5-a231-9d68f2184e52@dunslane.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
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-
/messages/by-id/837cc50a-532a-85f5-a231-9d68f2184e52@dunslane.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?
Greetings,
* Noah Misch (noah@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-
/messages/by-id/837cc50a-532a-85f5-a231-9d68f2184e52@dunslane.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 <revoke statement>
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 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?
In my reading, yes, it's much the same. I invite others to try and read
through it and see if they agree with my conclusions. Again, this is
really all on the 'revoke statement' side and isn't really covered on
the 'grant' side.
Thanks,
Stephen
On Mon, Oct 4, 2021 at 10:57 PM Stephen Frost <sfrost@snowman.net> 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
On Oct 5, 2021, at 9:23 AM, Robert Haas <robertmhaas@gmail.com> 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
+---> bobIt 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
Greetings,
On Tue, Oct 5, 2021 at 12:23 Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Oct 4, 2021 at 10:57 PM Stephen Frost <sfrost@snowman.net> 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
Show quoted text
On Tue, Oct 5, 2021 at 12:38 PM Mark Dilger
<mark.dilger@enterprisedb.com> 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
Greetings,
On Tue, Oct 5, 2021 at 12:38 Mark Dilger <mark.dilger@enterprisedb.com>
wrote:
On Oct 5, 2021, at 9:23 AM, Robert Haas <robertmhaas@gmail.com> 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 +---> bobIt 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
Show quoted text
Greetings,
On Tue, Oct 5, 2021 at 13:09 Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Oct 5, 2021 at 12:38 PM Mark Dilger
<mark.dilger@enterprisedb.com> 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
Show quoted text
On Oct 5, 2021, at 10:14 AM, Stephen Frost <sfrost@snowman.net> wrote:
What does the “ownership” concept actually buy us then?
DROP ... CASCADE.
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Greetings,
On Tue, Oct 5, 2021 at 13:17 Mark Dilger <mark.dilger@enterprisedb.com>
wrote:
On Oct 5, 2021, at 10:14 AM, Stephen Frost <sfrost@snowman.net> 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
Show quoted text
On Oct 5, 2021, at 10:20 AM, Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
On Tue, Oct 5, 2021 at 13:17 Mark Dilger <mark.dilger@enterprisedb.com> wrote:
On Oct 5, 2021, at 10:14 AM, Stephen Frost <sfrost@snowman.net> 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
On Tue, Oct 5, 2021 at 3:41 PM Mark Dilger <mark.dilger@enterprisedb.com> 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
Greetings,
* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
On Oct 5, 2021, at 10:20 AM, Stephen Frost <sfrost@snowman.net> wrote:
On Tue, Oct 5, 2021 at 13:17 Mark Dilger <mark.dilger@enterprisedb.com> wrote:On Oct 5, 2021, at 10:14 AM, Stephen Frost <sfrost@snowman.net> 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
On Oct 6, 2021, at 9:01 AM, Stephen Frost <sfrost@snowman.net> 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
Greetings,
* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
On Oct 6, 2021, at 9:01 AM, Stephen Frost <sfrost@snowman.net> 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
On Oct 6, 2021, at 10:20 AM, Stephen Frost <sfrost@snowman.net> 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
Greetings,
* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
On Oct 6, 2021, at 10:20 AM, Stephen Frost <sfrost@snowman.net> 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
On Oct 6, 2021, at 11:09 AM, Stephen Frost <sfrost@snowman.net> 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
Greetings,
* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
On Oct 6, 2021, at 11:09 AM, Stephen Frost <sfrost@snowman.net> 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 <drop role statement>, 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
