e 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-9d68f21
(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
> 'sel
%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 paraphrasi
On Mon, Oct 4, 2021 at 10:57 PM Stephen Frost wrote:
> - Disallow roles from being able to REVOKE role membership that they
> didn't GRANT in the first place.
I think that's not quite the right test. For example, if alice and bob
are superusers and alice grants pg_monitor to doug, bob should be
> On Oct 5, 2021, at 9:23 AM, Robert Haas wrote:
>
>> - Disallow roles from being able to REVOKE role membership that they
>> didn't GRANT in the first place.
>
> I think that's not quite the right test. For example, if alice and bob
> are superusers and alice grants pg_monitor to doug, bob
Greetings,
On Tue, Oct 5, 2021 at 12:23 Robert Haas wrote:
> On Mon, Oct 4, 2021 at 10:57 PM Stephen Frost wrote:
> > - Disallow roles from being able to REVOKE role membership that they
> > didn't GRANT in the first place.
>
> I think that's not quite the right test. For example, if alice an
On Tue, Oct 5, 2021 at 12:38 PM Mark Dilger
wrote:
> Additionally, role "alice" might not exist anymore, which would leave the
> privilege irrevocable.
I thought that surely this couldn't be right, but apparently we have
absolutely no problem with leaving the "grantor" column in pg_authid
as a d
Greetings,
On Tue, Oct 5, 2021 at 12:38 Mark Dilger
wrote:
>
>
> > On Oct 5, 2021, at 9:23 AM, Robert Haas wrote:
> >
> >> - Disallow roles from being able to REVOKE role membership that they
> >> didn't GRANT in the first place.
> >
> > I think that's not quite the right test. For example, if
Greetings,
On Tue, Oct 5, 2021 at 13:09 Robert Haas wrote:
> On Tue, Oct 5, 2021 at 12:38 PM Mark Dilger
> wrote:
> > Additionally, role "alice" might not exist anymore, which would leave
> the privilege irrevocable.
>
> I thought that surely this couldn't be right, but apparently we have
> abs
> On Oct 5, 2021, at 10:14 AM, Stephen Frost wrote:
>
> What does the “ownership” concept actually buy us then?
DROP ... CASCADE.
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Greetings,
On Tue, Oct 5, 2021 at 13:17 Mark Dilger
wrote:
> > On Oct 5, 2021, at 10:14 AM, Stephen Frost wrote:
> >
> > What does the “ownership” concept actually buy us then?
>
> DROP ... CASCADE
I’m not convinced that we need to invent the concept of ownership in order
to find a sensible w
> On Oct 5, 2021, at 10:20 AM, Stephen Frost wrote:
>
> Greetings,
>
> On Tue, Oct 5, 2021 at 13:17 Mark Dilger wrote:
> > On Oct 5, 2021, at 10:14 AM, Stephen Frost wrote:
> >
> > What does the “ownership” concept actually buy us then?
>
> DROP ... CASCADE
>
> I’m not convinced that we
On Tue, Oct 5, 2021 at 3:41 PM Mark Dilger wrote:
> If roles have owners, then DROP ROLE bob CASCADE drops bob, bob's objects,
> roles owned by bob, their objects and any roles they own, recursively. Roles
> which bob merely has admin rights on are unaffected, excepting that they are
> adminis
Greetings,
* Mark Dilger (mark.dil...@enterprisedb.com) wrote:
> > On Oct 5, 2021, at 10:20 AM, Stephen Frost wrote:
> > On Tue, Oct 5, 2021 at 13:17 Mark Dilger
> > wrote:
> > > On Oct 5, 2021, at 10:14 AM, Stephen Frost wrote:
> > >
> > > What does the “ownership” concept actually buy us th
> On Oct 6, 2021, at 9:01 AM, Stephen Frost wrote:
>
> I can see how what you describe as the behavior you'd like to see of
> DROP ROLE ... CASCADE could be useful... However, at least in the
> latest version of the standard that I'm looking at, when a
> DROP ROLE ... CASCADE is executed, wh
Greetings,
* Mark Dilger (mark.dil...@enterprisedb.com) wrote:
> > On Oct 6, 2021, at 9:01 AM, Stephen Frost wrote:
> > I can see how what you describe as the behavior you'd like to see of
> > DROP ROLE ... CASCADE could be useful... However, at least in the
> > latest version of the standard th
> On Oct 6, 2021, at 10:20 AM, Stephen Frost wrote:
>
> Consistency is not having dangling pointers around to things which no
> longer exist- FK reference kind of things. Object management is about
> actual *removal* of full blown objects like roles, tables, etc. DROP
> TABLE ... CASCADE doe
Greetings,
* Mark Dilger (mark.dil...@enterprisedb.com) wrote:
> > On Oct 6, 2021, at 10:20 AM, Stephen Frost wrote:
> >
> > Consistency is not having dangling pointers around to things which no
> > longer exist- FK reference kind of things. Object management is about
> > actual *removal* of fu
> On Oct 6, 2021, at 11:09 AM, Stephen Frost wrote:
>
> After all, it says
> "REOKVE R FROM A DB", not "DROP ROLE A CASCADE".
Wait, are you arguing what DROP ROLE A CASCADE should do based on what the spec
says REVOKE R FROM A DB should do? If so, I'd say that's irrelevant. I'm not
propo
Greetings,
* Mark Dilger (mark.dil...@enterprisedb.com) wrote:
> > On Oct 6, 2021, at 11:09 AM, Stephen Frost wrote:
> > After all, it says
> > "REOKVE R FROM A DB", not "DROP ROLE A CASCADE".
>
> Wait, are you arguing what DROP ROLE A CASCADE should do based on what the
> spec says REVOKE R F
On Wed, Oct 6, 2021 at 2:48 PM Stephen Frost wrote:
> What I'm saying above is that the command explicitly listed there
> *isn't* 'DROP ROLE A DB', even though that is something which the spec
> *could* have done, had they wished to. Given that they didn't, it seems
> very clear that making such
Greetings,
* Robert Haas (robertmh...@gmail.com) wrote:
> On Wed, Oct 6, 2021 at 2:48 PM Stephen Frost wrote:
> > What I'm saying above is that the command explicitly listed there
> > *isn't* 'DROP ROLE A DB', even though that is something which the spec
> > *could* have done, had they wished to.
> On Oct 6, 2021, at 11:48 AM, Stephen Frost wrote:
>
> In the spec, under , there is a 'General Rules'
> section (as there is with most statements) and in that section it says
> that for every authorization identifier (that is, some privilege, be it
> a GRANT of SELECT rights on an object, or
On Wed, Oct 6, 2021 at 3:29 PM Stephen Frost wrote:
> Does that mean that we also get to change what a specific set of
> commands, which are all well-defined in the standard, do even when that
> goes against what an SQL compliant implementation would do? I really
> don't think so. If this was *n
Greetings,
On Wed, Oct 6, 2021 at 16:01 Mark Dilger
wrote:
> > On Oct 6, 2021, at 11:48 AM, Stephen Frost wrote:
> >
> > In the spec, under , there is a 'General Rules'
> > section (as there is with most statements) and in that section it says
> > that for every authorization identifier (that i
Greetings,
On Wed, Oct 6, 2021 at 16:28 Robert Haas wrote:
> On Wed, Oct 6, 2021 at 3:29 PM Stephen Frost wrote:
> > Does that mean that we also get to change what a specific set of
> > commands, which are all well-defined in the standard, do even when that
> > goes against what an SQL complian
> On Oct 6, 2021, at 1:48 PM, Stephen Frost wrote:
>
> This specific syntax, including the CASCADE bit, has, at minimum, at least
> been contemplate by the SQL folks sufficiently to be described in one
> specific way. I don’t have a copy of 2016 handy, unfortunately, and so I’m
> not sure
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 RO
Greetings,
* Vik Fearing (v...@postgresfriends.org) wrote:
> On 10/6/21 8:48 PM, Stephen Frost wrote:
> > Consider that with what you're proposing, a user could execute the
> > following series of entirely SQL-spec compliant statements, and get
> > very different results depending on if we have th
Greetings,
* Mark Dilger (mark.dil...@enterprisedb.com) wrote:
> > On Oct 6, 2021, at 1:48 PM, Stephen Frost wrote:
> > This specific syntax, including the CASCADE bit, has, at minimum, at least
> > been contemplate by the SQL folks sufficiently to be described in one
> > specific way. I don’t
> On Oct 7, 2021, at 7:43 AM, Stephen Frost wrote:
>
>> Assuming no concept of role ownership exists, but that DROP ROLE bob CASCADE
>> is implemented in a spec compliant way, if there is a role "bob" who owns
>> various objects, what happens when DROP ROLE bob CASCADE is performed? Do
>>
Greetings,
* Mark Dilger (mark.dil...@enterprisedb.com) wrote:
> > On Oct 7, 2021, at 7:43 AM, Stephen Frost wrote:
> >> Assuming no concept of role ownership exists, but that DROP ROLE bob
> >> CASCADE is implemented in a spec compliant way, if there is a role "bob"
> >> who owns various objec
> On Oct 7, 2021, at 9:05 AM, Stephen Frost wrote:
>
>> Hmmph. I think it would be strange if all of the following were true:
>>
>> 1) DROP ROLE bob CASCADE drops all objects owned by bob
>> 2) Roles can own other roles
>> 3) DROP ROLE bob CASCADE never cascades to other roles
>>
>> I'm ass
On 10/7/21 4:21 PM, Stephen Frost wrote:
> Greetings,
>
> * Vik Fearing (v...@postgresfriends.org) wrote:
>> On 10/6/21 8:48 PM, Stephen Frost wrote:
>>> Consider that with what you're proposing, a user could execute the
>>> following series of entirely SQL-spec compliant statements, and get
>>> v
On Thu, Oct 7, 2021 at 12:52 PM Vik Fearing wrote:
> I can agree with you now, but it's certainly not the easiest thing to
> interpret.
That's putting it mildly.
--
Robert Haas
EDB: http://www.enterprisedb.com
Greetings,
* Mark Dilger (mark.dil...@enterprisedb.com) wrote:
> > On Oct 7, 2021, at 9:05 AM, Stephen Frost wrote:
> > I don't think the spec supports any of the three rules you list.
>
> And I'm not seeing that it prohibits any of them.
I don't agree that we can decide to have random statemen
> On Oct 7, 2021, at 10:23 AM, Stephen Frost wrote:
>
>> And I'm not seeing that it prohibits any of them.
>
> I don't agree that we can decide to have random statements which are
> defined explicitly in the standard to do X end up doing X+Y, simply
> because the standard didn't explicitly sa
Greetings,
* Mark Dilger (mark.dil...@enterprisedb.com) wrote:
> > On Oct 7, 2021, at 10:23 AM, Stephen Frost wrote:
> >> And I'm not seeing that it prohibits any of them.
> >
> > I don't agree that we can decide to have random statements which are
> > defined explicitly in the standard to do X
> On Oct 7, 2021, at 11:30 AM, Stephen Frost wrote:
>
>> Because we've already decided how object ownership works. I didn't write
>> any code to have roles get dropped when their owners get dropped. I just
>> put ownership into the system and this is how it naturally works. So you
>> are
Greetings,
* Mark Dilger (mark.dil...@enterprisedb.com) wrote:
> > On Oct 7, 2021, at 11:30 AM, Stephen Frost wrote:
> >> Because we've already decided how object ownership works. I didn't write
> >> any code to have roles get dropped when their owners get dropped. I just
> >> put ownership i
> On Oct 7, 2021, at 12:19 PM, Stephen Frost wrote:
>
> Uh, I didn't say it 'must not fail'.
Ah-hah, right, I misremembered. You were quoting the spec at me, and I went to
read a copy of the spec as a consequence, and saw something like that there.
Let me see if I can find that again.
—
> On Oct 7, 2021, at 12:31 PM, Mark Dilger wrote:
>
> Let me see if I can find that again.
12.6
::=
DROP ROLE
Syntax Rules
1) Let R be the role identified by the specified .
General Rules
1) Let A be any identified by a role authorization
descriptor as having been granted
to R.
2) Th
Greetings,
* Mark Dilger (mark.dil...@enterprisedb.com) wrote:
> > On Oct 7, 2021, at 12:31 PM, Mark Dilger
> > wrote:
> >
> > Let me see if I can find that again.
>
> 12.6
>
> ::=
> DROP ROLE
>
> Syntax Rules
> 1) Let R be the role identified by the specified .
>
> General Rules
> 1) Le
> On Oct 7, 2021, at 7:44 PM, Stephen Frost wrote:
>
> I don't actually think REVOKE ROLE CASCADE must not fail, nor do I see
> that as explicit in anything you quote above.
I don't see that myself, but I thought that you would, given your other
statements about how we shouldn't take a spec
44 matches
Mail list logo