Role membership and DROP

Started by Laurenz Albeabout 6 years ago8 messages

laurenz.albe@cybertec.at

about 6 years ago

I realized only today that if role A is a member of role B,
A can ALTER and DROP objects owned by B.

I don't have a problem with that, but the documentation seems to
suggest otherwise. For example, for DROP TABLE:

Only the table owner, the schema owner, and superuser can drop a table.

Should I compose a doc patch, or is that too much of a corner case
to mention? I wanted to ask before I do the repetetive work.

Yours,
Laurenz Albe

Tom Lane

tgl@sss.pgh.pa.us

about 6 years ago

In reply to: Laurenz Albe (#1)

Re: Role membership and DROP

Laurenz Albe <laurenz.albe@cybertec.at> writes:

I realized only today that if role A is a member of role B,
A can ALTER and DROP objects owned by B.
I don't have a problem with that, but the documentation seems to
suggest otherwise. For example, for DROP TABLE:

Only the table owner, the schema owner, and superuser can drop a table.

Generally, if you are a member of a role, that means you are the role for
privilege-test purposes. I'm not on board with adding "(or a member of
that role)" to every place it could conceivably be added; I think that
would be more annoying than helpful.

It might be worth clarifying this point in section 5.7,

https://www.postgresql.org/docs/devel/ddl-priv.html

but let's not duplicate that in every ref/ page.

regards, tom lane

Laurenz Albe

laurenz.albe@cybertec.at

about 6 years ago

In reply to: Tom Lane (#2)

1 attachment(s)

Re: Role membership and DROP

On Wed, 2019-11-13 at 17:17 -0500, Tom Lane wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

I realized only today that if role A is a member of role B,
A can ALTER and DROP objects owned by B.
I don't have a problem with that, but the documentation seems to
suggest otherwise. For example, for DROP TABLE:
Only the table owner, the schema owner, and superuser can drop a table.

Generally, if you are a member of a role, that means you are the role for
privilege-test purposes. I'm not on board with adding "(or a member of
that role)" to every place it could conceivably be added; I think that
would be more annoying than helpful.

It might be worth clarifying this point in section 5.7,

https://www.postgresql.org/docs/devel/ddl-priv.html

but let's not duplicate that in every ref/ page.

That's much better.

I have attached a proposed patch.

Yours,
Laurenz Albe

Attachments:

0001-Document-that-the-right-to-ALTER-or-DROP-can-be-inhe.patchtext/x-patch; charset=UTF-8; name=0001-Document-that-the-right-to-ALTER-or-DROP-can-be-inhe.patchDownload

From badfe59750dec82dffe18a5a43fb16f72f283a7d Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Fri, 15 Nov 2019 10:28:26 +0100
Subject: [PATCH] Document that the right to ALTER or DROP can be inherited

Discussion: https://postgr.es/m/504497aca66bf34bdcdd90bd0bcebdc3a33f577b.camel@cybertec.at
---
 doc/src/sgml/ddl.sgml | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index d7158c1b03..51e1957f85 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1579,7 +1579,8 @@ ALTER TABLE products RENAME TO items;
 
   <para>
    The right to modify or destroy an object is always the privilege of
-   the owner only.
+   the owner.  Like all privileges, that right can be inherited by members of
+   the owning role.
   </para>
 
   <para>
-- 
2.21.0

Tom Lane

tgl@sss.pgh.pa.us

about 6 years ago

In reply to: Laurenz Albe (#3)

Re: Role membership and DROP

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Wed, 2019-11-13 at 17:17 -0500, Tom Lane wrote:

It might be worth clarifying this point in section 5.7,
https://www.postgresql.org/docs/devel/ddl-priv.html
but let's not duplicate that in every ref/ page.

I have attached a proposed patch.

   <para>
    The right to modify or destroy an object is always the privilege of
-   the owner only.
+   the owner.  Like all privileges, that right can be inherited by members of
+   the owning role.
   </para>

Hm. This is more or less contradicting the original meaning of the
existing sentence, so maybe we need to rewrite a bit more. What do
you think of

The right to modify or destroy an object is inherent in being the
object's owner. Like all privileges, that right can be inherited by
members of the owning role; but there is no way to grant or revoke
it more selectively.

A larger problem (pre-existing, since there's a reference to being a
member of the owning role just a bit further down) is that I don't think
we've defined role membership at this point, so the reader is quite
entitled to come away more confused than they were before. It might not
be advisable to try to cover role membership here, but we should at
least add a cross-reference to where it's explained.

regards, tom lane

Laurenz Albe

laurenz.albe@cybertec.at

about 6 years ago

In reply to: Tom Lane (#4)

1 attachment(s)

Re: Role membership and DROP

On Fri, 2019-11-15 at 13:41 -0500, Tom Lane wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Wed, 2019-11-13 at 17:17 -0500, Tom Lane wrote:

It might be worth clarifying this point in section 5.7,
https://www.postgresql.org/docs/devel/ddl-priv.html
but let's not duplicate that in every ref/ page.

I have attached a proposed patch.
<para>
The right to modify or destroy an object is always the privilege of
-   the owner only.
+   the owner.  Like all privileges, that right can be inherited by members of
+   the owning role.
</para>
Hm. This is more or less contradicting the original meaning of the
existing sentence, so maybe we need to rewrite a bit more. What do
you think of

The right to modify or destroy an object is inherent in being the
object's owner. Like all privileges, that right can be inherited by
members of the owning role; but there is no way to grant or revoke
it more selectively.

A larger problem (pre-existing, since there's a reference to being a
member of the owning role just a bit further down) is that I don't think
we've defined role membership at this point, so the reader is quite
entitled to come away more confused than they were before. It might not
be advisable to try to cover role membership here, but we should at
least add a cross-reference to where it's explained.

I think you are right about the potential confusion; I have added a
cross-reference. That cross-reference is hopefully still in short-term
memory when the reader proceeds to the second reference to role membership
a few sentences later.

I like your second sentence, but I think that "the right ... is inherent
in being the ... owner" is unnecessarily complicated.
Removing the "always" and "only" makes the apparent contradiction between
the sentences less jarring to me.

I won't fight about words though. Attached is my second attempt.

Yours,
Laurenz Albe

Attachments:

0001-Document-that-the-right-to-ALTER-or-DROP-is-hereditary.V2.patchtext/x-patch; charset=UTF-8; name*0=0001-Document-that-the-right-to-ALTER-or-DROP-is-hereditary.V2.pa; name*1=tchDownload

From 2e3abaaa3b0a5deb006d2210c3e66f5b3571bfd2 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Mon, 18 Nov 2019 15:23:10 +0100
Subject: [PATCH] Document that the right to ALTER or DROP is hereditary

Discussion: https://postgr.es/m/504497aca66bf34bdcdd90bd0bcebdc3a33f577b.camel@cybertec.at
---
 doc/src/sgml/ddl.sgml | 6 ++++--
 1 file changed, 4 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 9d6ec2c738..030c896f82 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1578,8 +1578,10 @@ ALTER TABLE products RENAME TO items;
   </para>
 
   <para>
-   The right to modify or destroy an object is always the privilege of
-   the owner only.
+   The right to modify or destroy an object is the privilege of the owner.
+   Like all privileges, that right can be inherited by members of the owning role,
+   but there is no way to grant or revoke it more selectively.
+   See <xref linkend="role-membership"/> for more about role membership.
   </para>
 
   <para>
-- 
2.21.0

Tom Lane

tgl@sss.pgh.pa.us

about 6 years ago

In reply to: Laurenz Albe (#5)

1 attachment(s)

Re: Role membership and DROP

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Fri, 2019-11-15 at 13:41 -0500, Tom Lane wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Wed, 2019-11-13 at 17:17 -0500, Tom Lane wrote:

It might be worth clarifying this point in section 5.7,
https://www.postgresql.org/docs/devel/ddl-priv.html

I like your second sentence, but I think that "the right ... is inherent
in being the ... owner" is unnecessarily complicated.
Removing the "always" and "only" makes the apparent contradiction between
the sentences less jarring to me.

I think it's important to emphasize that this is implicit in object
ownership.

Looking at the page again, I notice that there's a para a little further
down that overlaps quite a bit with what we're discussing here, but it's
about implicit grant options rather than the right to DROP. In the
attached, I reworded that too, and moved it because it's not fully
intelligible until we've explained grant options. Thoughts?

regards, tom lane

Attachments:

doc-owner-privileges-3.patchtext/x-diff; charset=us-ascii; name=doc-owner-privileges-3.patchDownload

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 9d6ec2c..0be0774 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1578,8 +1578,10 @@ ALTER TABLE products RENAME TO items;
   </para>
 
   <para>
-   The right to modify or destroy an object is always the privilege of
-   the owner only.
+   The right to modify or destroy an object is inherent in being the
+   object's owner, and cannot be granted or revoked in itself.
+   (However, like all privileges, that right can be inherited by
+   members of the owning role; see <xref linkend="role-membership"/>.)
   </para>
 
   <para>
@@ -1614,17 +1616,11 @@ GRANT UPDATE ON accounts TO joe;
   </para>
 
   <para>
-   To revoke a privilege, use the fittingly named
+   To revoke a previously-granted privilege, use the fittingly named
    <xref linkend="sql-revoke"/> command:
 <programlisting>
 REVOKE ALL ON accounts FROM PUBLIC;
 </programlisting>
-   The special privileges of the object owner (i.e., the right to do
-   <command>DROP</command>, <command>GRANT</command>, <command>REVOKE</command>, etc.)
-   are always implicit in being the owner,
-   and cannot be granted or revoked.  But the object owner can choose
-   to revoke their own ordinary privileges, for example to make a
-   table read-only for themselves as well as others.
   </para>
 
   <para>
@@ -1639,6 +1635,13 @@ REVOKE ALL ON accounts FROM PUBLIC;
   </para>
 
   <para>
+   An object's owner can choose to revoke their own ordinary privileges,
+   for example to make a table read-only for themselves as well as others.
+   But owners are always treated as holding all grant options, so they
+   can always re-grant their own privileges.
+  </para>
+
+  <para>
    The available privileges are:
 
    <variablelist>

Laurenz Albe

laurenz.albe@cybertec.at

about 6 years ago

In reply to: Tom Lane (#6)

Re: Role membership and DROP

On Tue, 2019-11-19 at 13:21 -0500, Tom Lane wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Fri, 2019-11-15 at 13:41 -0500, Tom Lane wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Wed, 2019-11-13 at 17:17 -0500, Tom Lane wrote:

It might be worth clarifying this point in section 5.7,
https://www.postgresql.org/docs/devel/ddl-priv.html

I like your second sentence, but I think that "the right ... is inherent
in being the ... owner" is unnecessarily complicated.
Removing the "always" and "only" makes the apparent contradiction between
the sentences less jarring to me.

I think it's important to emphasize that this is implicit in object
ownership.

Looking at the page again, I notice that there's a para a little further
down that overlaps quite a bit with what we're discussing here, but it's
about implicit grant options rather than the right to DROP. In the
attached, I reworded that too, and moved it because it's not fully
intelligible until we've explained grant options. Thoughts?

I am fine with that.

Yours,
Laurenz Albe

Tom Lane

tgl@sss.pgh.pa.us

about 6 years ago

In reply to: Laurenz Albe (#7)

Re: Role membership and DROP

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Tue, 2019-11-19 at 13:21 -0500, Tom Lane wrote:

Looking at the page again, I notice that there's a para a little further
down that overlaps quite a bit with what we're discussing here, but it's
about implicit grant options rather than the right to DROP. In the
attached, I reworded that too, and moved it because it's not fully
intelligible until we've explained grant options. Thoughts?

I am fine with that.

OK, pushed.

regards, tom lane