Thread: 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

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?

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-
> > 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 <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
interms 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
beable 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

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

> 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

> 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-
thoughit would be helpful to first get everyone’s idea of just what *would* this command do if run on a role who “owns”
orhas “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
rolesthey own, recursively.  Roles which bob merely has admin rights on are unaffected, excepting that they are
administeredby 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
objectsthey create, because in the end you just drop the one role cascade and all that other stuff is guaranteed to be
cleanedup 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
cascadeto other roles because the concept of "roles that bob owns" doesn't exist.  If bob created other roles, those
willbe 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
rolesthey own, recursively.  Roles which bob merely has admin rights on are unaffected, excepting that they are
administeredby 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
andobjects they create, because in the end you just drop the one role cascade and all that other stuff is guaranteed to
becleaned 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
cascadeto other roles because the concept of "roles that bob owns" doesn't exist.  If bob created other roles, those
willbe 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
rolesthey own, recursively.  Roles which bob merely has admin rights on are unaffected, excepting that they are
administeredby 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
andobjects they create, because in the end you just drop the one role cascade and all that other stuff is guaranteed to
becleaned 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
cascadeto other roles because the concept of "roles that bob owns" doesn't exist.  If bob created other roles, those
willbe 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'mproposing 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
otherroles 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
RFROM A DB is the one and only thing that DROP ROLE .. CASCADE can do.  If they are excluding that it do anything else,
thenyes, 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'mproposing 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
otherroles 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
REVOKER FROM A DB is the one and only thing that DROP ROLE .. CASCADE can do.  If they are excluding that it do
anythingelse, 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
beinganalogous 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
headaround how to interpret the spec (of which i have no copy, mind you.)  I'm trying to distinguish between statements
likeX 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
inthe 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
asbeing 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
headaround how to interpret the spec (of which i have no copy, mind you.)  I'm trying to distinguish between statements
likeX 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
inthe 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?
Ifso, 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

On Wed, Oct 6, 2021 at 2:48 PM Stephen Frost <sfrost@snowman.net> 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

Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Wed, Oct 6, 2021 at 2:48 PM Stephen Frost <sfrost@snowman.net> 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

> On Oct 6, 2021, at 11:48 AM, Stephen Frost <sfrost@snowman.net> wrote:
>
> 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).

I think you are saying that "DROP ROLE bob" implies revoking "bob" from anybody who has membership in role bob.  I
agreewith 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
ownedby 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
sayingwhat DROP ROLE ... CASCADE is supposed to do.  I found some Vertica docs that say what Vertica does.  I found
someEnterprise 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
theconnection 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

On Wed, Oct 6, 2021 at 3:29 PM Stephen Frost <sfrost@snowman.net> 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

> On Oct 6, 2021, at 1:48 PM, Stephen Frost <sfrost@snowman.net> wrote:
>
> This specific syntax, including the CASCADE bit, has, at minimum, at least been contemplate by the SQL folks
sufficientlyto be described in one specific way.  I don’t have a copy of 2016 handy, unfortunately, and so I’m not sure
ifit’s described that way in a “stable” version of the standard or not (it isn’t defined in the 2006 draft I’ve seen),
butultimately 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,
withwhat’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
difficultfor 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
thereis a role "bob" who owns various objects, what happens when DROP ROLE bob CASCADE is performed?  Do bob's objects
getdropped, do they get orphaned, or do they get assigned to some other owner?  I would expect that they get dropped,
butI'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

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

Greetings,

* Vik Fearing (vik@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

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Oct 6, 2021, at 1:48 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > This specific syntax, including the CASCADE bit, has, at minimum, at least been contemplate by the SQL folks
sufficientlyto be described in one specific way.  I don’t have a copy of 2016 handy, unfortunately, and so I’m not sure
ifit’s described that way in a “stable” version of the standard or not (it isn’t defined in the 2006 draft I’ve seen),
butultimately 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,
withwhat’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
difficultfor 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,
ifthere is a role "bob" who owns various objects, what happens when DROP ROLE bob CASCADE is performed?  Do bob's
objectsget 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

> On Oct 7, 2021, at 7:43 AM, Stephen Frost <sfrost@snowman.net> wrote:
>
>> Assuming no concept of role ownership exists, but that DROP ROLE bob CASCADE is implemented in a spec compliant way,
ifthere is a role "bob" who owns various objects, what happens when DROP ROLE bob CASCADE is performed?  Do bob's
objectsget 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
thespec 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

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Oct 7, 2021, at 7:43 AM, Stephen Frost <sfrost@snowman.net> 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
objectsget 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
thatthe 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

> On Oct 7, 2021, at 9:05 AM, Stephen Frost <sfrost@snowman.net> 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
thatthe 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
allowanything that isn't expressly prohibited.  Prohibitive rules prohibit anything that isn't explicitly permitted.
I'mtaking 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
prohibitedif 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.
Sotentatively, 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
thisnext 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
seeinga 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

On 10/7/21 4:21 PM, Stephen Frost wrote:
> Greetings,
> 
> * Vik Fearing (vik@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 <role definition> 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

On Thu, Oct 7, 2021 at 12:52 PM Vik Fearing <vik@postgresfriends.org> 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

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Oct 7, 2021, at 9:05 AM, Stephen Frost <sfrost@snowman.net> 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

> On Oct 7, 2021, at 10:23 AM, Stephen Frost <sfrost@snowman.net> 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
prohibitingwhat I've proposed, after we've already gone and done it.  I'm not as interested in what a bunch of
philosopherswriting a spec think, but if all the other major SQL databases go that direction and we're off in a
differentdirection, 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
didthem 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
ownersget dropped.  I just put ownership into the system and this is how it naturally works.  So you are advocating
thatDROP...CASCADE works one way for every object type save one.  I think that's an incredibly unclean design.  Having
DROP...CASCADEwork the same way for all ownership relations for all object types without exception makes so much more
senseto me. 

What if we go with what you are saying, the spec never resolves in the direction you are predicting, and all the other
databasevendors go the way I'm proposing, and we're the only ones with this ugly wart that you have to use a different
syntaxfor 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

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Oct 7, 2021, at 10:23 AM, Stephen Frost <sfrost@snowman.net> 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
prohibitingwhat I've proposed, after we've already gone and done it.  I'm not as interested in what a bunch of
philosopherswriting a spec think, but if all the other major SQL databases go that direction and we're off in a
differentdirection, 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
wedid 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
theirowners get dropped.  I just put ownership into the system and this is how it naturally works.  So you are
advocatingthat 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
makesso 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
otherdatabase vendors go the way I'm proposing, and we're the only ones with this ugly wart that you have to use a
differentsyntax for roles than for everything else?  We'll be supporting that ugly wart for years and years to come,
andlook 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 that would be to
simply not implement anything for the exact 'DROP ROLE bob CASCADE;'
syntax and instead come up with something else, at least initially.
That way, whenever the standard comes out which has something definitive
to say about how 'DROP ROLE bob CASCADE;' should work, we can implement
whatever it is that they decided upon and hope that other databases do
too.

I find it very unlikely that the standard will come out any time soon
with a concept of role ownership though, making it very unlikely that a
different decision will be made regarding how DROP ROLE ... CASCADE;
works.  That said, the way to avoid such a possibility is to use some
other syntax, which is what I've been advocating for since the start.

Thanks,

Stephen

> On Oct 7, 2021, at 11:30 AM, Stephen Frost <sfrost@snowman.net> wrote:
>
>> Because we've already decided how object ownership works.  I didn't write any code to have roles get dropped when
theirowners get dropped.  I just put ownership into the system and this is how it naturally works.  So you are
advocatingthat 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
makesso 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
ROLEbob 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
argumentagainst 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

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Oct 7, 2021, at 11:30 AM, Stephen Frost <sfrost@snowman.net> wrote:
> >> Because we've already decided how object ownership works.  I didn't write any code to have roles get dropped when
theirowners get dropped.  I just put ownership into the system and this is how it naturally works.  So you are
advocatingthat 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
makesso 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
DROPROLE 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

> On Oct 7, 2021, at 12:19 PM, Stephen Frost <sfrost@snowman.net> 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

> On Oct 7, 2021, at 12:31 PM, Mark Dilger <mark.dilger@enterprisedb.com> wrote:
>
> Let me see if I can find that again.


12.6 <drop role statement>

<drop role statement> ::=
DROP ROLE <role name>

Syntax Rules
1) Let R be the role identified by the specified <role name>.

General Rules
1) Let A be any <authorization identifier> identified by a role authorization descriptor as having been granted
to R.
2) The following <revoke role statement> 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 role statement> ::=
REVOKE [ ADMIN OPTION FOR ] <role revoked> [ { <comma> <role revoked> }... ]
FROM <grantee> [ { <comma> <grantee> }... ]
[ GRANTED BY <grantor> ]
<drop behavior>

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 <revoke statement> is a <revoke privilege statement>, then
        ... SNIP ...
b) If the <revoke statement> is a <revoke role statement>, 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 <table name> of V. The following <drop view
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 <table name> of T. The following <drop table
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
REVOKEROLE CASCADE.  Then interpreting 31's description of how REVOKE ROLE RESTRICT works under the principle Expressio
UniusEst Exclusio Alterius I conclude that REVOKE ROLE CASCADE must not raise an exception.  That leads me to the
conclusionthat 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

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Oct 7, 2021, at 12:31 PM, Mark Dilger <mark.dilger@enterprisedb.com> wrote:
> >
> > Let me see if I can find that again.
>
> 12.6 <drop role statement>
>
> <drop role statement> ::=
> DROP ROLE <role name>
>
> Syntax Rules
> 1) Let R be the role identified by the specified <role name>.
>
> General Rules
> 1) Let A be any <authorization identifier> identified by a role authorization descriptor as having been granted
> to R.
> 2) The following <revoke role statement> 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 role statement> ::=
> REVOKE [ ADMIN OPTION FOR ] <role revoked> [ { <comma> <role revoked> }... ]
> FROM <grantee> [ { <comma> <grantee> }... ]
> [ GRANTED BY <grantor> ]
> <drop behavior>
>
> 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 <revoke statement> is a <revoke privilege statement>, then
>         ... SNIP ...
> b) If the <revoke statement> is a <revoke role statement>, 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 <table name> of V. The following <drop view
> 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 <table name> of T. The following <drop table
> 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
REVOKEROLE CASCADE.  Then interpreting 31's description of how REVOKE ROLE RESTRICT works under the principle Expressio
UniusEst Exclusio Alterius I conclude that REVOKE ROLE CASCADE must not raise an exception.  That leads me to the
conclusionthat 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

> On Oct 7, 2021, at 7:44 PM, Stephen Frost <sfrost@snowman.net> 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
requirementto do X and turn it into doing X+Y, because the user wouldn't be expecting Y.  So I thought that if DROP
ROLEbob was defined in the spec to basically just do REVOKE bob FROM EVERYBODY, and if the CASCADE version of that
wasn'tsupposed to fail, then you'd say that DROP ROLE bob CASCADE wasn't supposed to fail either.  (Failing is the
unexpectedaction Y that I expected your rule to prohibit.) 

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company