Hey,
In a nearby user complaint email [1] some missing information regarding
ownership reassignment came to light. I took that and went a bit further
to add what I felt was further missing information and context for how the
privilege system is designed. I've tried to formalize and label existing
concepts a bit and updated the glossary accordingly.
The attached is a partial rewrite of the patch on the linked post after
those comments were taken into consideration.
The new glossary entry for privileges defines various qualifications of the
term that are used in the new prose. I've marked up each of the variants
and point them all back to the main entry. I didn't try to incorporate
those terms, or even really look, anywhere else in the documentation. If
the general idea is accepted that kind of work can be done as a follow-up.
David J.
[1]
https://www.postgresql.org/message-id/d294818d12280f6223ddf169ab5454927f5186b6.camel%40cybertec.at
From a4d6a599a0b5d6b8f280e3d8489e7f4a4a555383 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <david.g.johns...@gmail.com>
Date: Thu, 25 Jan 2024 13:41:48 -0700
Subject: [PATCH] v1-improvements-to-ddl-priv Section
---
doc/src/sgml/ddl.sgml | 109 ++++++++++++++++++++++---------------
doc/src/sgml/glossary.sgml | 40 ++++++++++++++
2 files changed, 105 insertions(+), 44 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index fc03a349f0..7c9c9d0dd1 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1855,12 +1855,33 @@ ALTER TABLE products RENAME TO items;
</indexterm>
<para>
- When an object is created, it is assigned an owner. The
- owner is normally the role that executed the creation statement.
- For most kinds of objects, the initial state is that only the owner
- (or a superuser) can do anything with the object. To allow
- other roles to use it, <firstterm>privileges</firstterm> must be
- granted.
+ The permissions needed to interact with an object are split between
+ <firstterm><link linkend="glossary-privilege">privileges</link></firstterm> and
+ <firstterm><link linkend="glossary-owner-rights">owner rights</link></firstterm>.
+ The owner has the right to modify the object, including dropping it, as well as
+ the right to grant and revoke privileges on the object to any role in the system.
+ Aside from a brief description, at the end, of what happens when you reassign the
+ owner of an object to some other role, this section describes privileges.
+ </para>
+
+ <para>
+ For most databases there are many roles and many objects, and thus a large amount
+ of privileges that need to be defined. Two features exist to make management of
+ privileges easier: role membership (i.e., group roles, see <xref linkend="user-manag"/>)
+ including the <literal>PUBLIC</literal> pseudo-role (which is a group role consisting
+ of all roles in the system) is one, while
+ <firstterm><link linkend="glossary-privilege">default privileges</link></firstterm>
+ (see <xref linkend="sql-alterdefaultprivileges"/>) is the other.
+ </para>
+
+ <para>
+ The fundamental design of the privilege system is to disallow by default. A role
+ must, directly or indirectly (via group role inheritance), hold the correct privilege
+ on the object to peform the corresponding action.
+ Furthermore, inheritance is strictly additive, there is no mechanism to block an
+ <firstterm><link linkend="glossary-privilege">indirect privilege</link></firstterm>.
+ Revoking a privilege only removes
+ <firstterm><link linkend="glossary-privilege">direct privileges</link></firstterm>.
</para>
<para>
@@ -1878,21 +1899,14 @@ ALTER TABLE products RENAME TO items;
</para>
<para>
- 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>
- An object can be assigned to a new owner with an <command>ALTER</command>
- command of the appropriate kind for the object, for example
-<programlisting>
-ALTER TABLE <replaceable>table_name</replaceable> OWNER TO <replaceable>new_owner</replaceable>;
-</programlisting>
- Superusers can always do this; ordinary roles can only do it if they are
- both the current owner of the object (or inherit the privileges of the
- owning role) and able to <literal>SET ROLE</literal> to the new owning role.
+ Upon object creation, two sets of
+ <firstterm><link linkend="glossary-privilege">implicit privileges</link></firstterm>
+ are established.
+ The owner is granted all applicable privileges on the object. All other roles,
+ including the <literal>PUBLIC</literal> pseudo-role, get their default privileges.
+ From this point onward only
+ <firstterm><link linkend="glossary-privilege">explicit priviliege</link></firstterm>
+ modification is possible, and is described next.
</para>
<para>
@@ -1904,15 +1918,9 @@ ALTER TABLE <replaceable>table_name</replaceable> OWNER TO <replaceable>new_owne
GRANT UPDATE ON accounts TO joe;
</programlisting>
Writing <literal>ALL</literal> in place of a specific privilege grants all
- privileges that are relevant for the object type.
- </para>
-
- <para>
- The special <quote>role</quote> name <literal>PUBLIC</literal> can
- be used to grant a privilege to every role on the system. Also,
- <quote>group</quote> roles can be set up to help manage privileges when
- there are many users of a database — for details see
- <xref linkend="user-manag"/>.
+ privileges that are relevant for the object type. The pseudo-role <literal>
+ PUBLIC</literal> is a valid role for this purpose. All roles in the system
+ will immediately inherit the indirect privilege(s) named in the command.
</para>
<para>
@@ -1936,9 +1944,11 @@ REVOKE ALL ON accounts FROM PUBLIC;
<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.
+ for example to make a table read-only for themselves. Their owner's
+ right to grant privileges on the owned object, unlike the grant option,
+ does not require them to have the corresponding privilege, and so they
+ are still capable of granting the select privilege back to themselves
+ in the future.
</para>
<para>
@@ -2476,8 +2486,9 @@ GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
<para>
If the <quote>Access privileges</quote> column is empty for a given
- object, it means the object has default privileges (that is, its
- privileges entry in the relevant system catalog is null). Default
+ object, it means the object has
+ <firstterm><link linkend="glossary-privilege">default privileges</link></firstterm>
+ (that is, its privileges entry in the relevant system catalog is null). Default
privileges always include all privileges for the owner, and can include
some privileges for <literal>PUBLIC</literal> depending on the object
type, as explained above. The first <command>GRANT</command>
@@ -2494,20 +2505,30 @@ GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
the <command>ALTER</command>.)
</para>
- <para>
- Notice that the owner's implicit grant options are not marked in the
- access privileges display. A <literal>*</literal> will appear only when
- grant options have been explicitly granted to someone.
- </para>
-
<para>
The <quote>Access privileges</quote> column
shows <literal>(none)</literal> when the object's privileges entry is
non-null but empty. This means that no privileges are granted at all,
- even to the object's owner — a rare situation. (The owner still
- has implicit grant options in this case, and so could re-grant her own
- privileges; but she has none at the moment.)
+ even to the object's owner — a rare situation. The owner can
+ exercise their right to grant privileges to make the object usable to
+ non-superusers again, or their right to drop the object.
</para>
+
+ <para>
+ An object can be assigned to a new owner with an <command>ALTER</command>
+ command of the appropriate kind for the object, for example
+<programlisting>
+ALTER TABLE <replaceable>table_name</replaceable> OWNER TO <replaceable>new_owner</replaceable>;
+</programlisting>
+ Superusers can always do this; ordinary roles can only do it if they are
+ both the current owner of the object (or inherit the privileges of the
+ owning role) and able to <literal>SET ROLE</literal> to the new owning role.
+ The reassignment process involves changing the recorded owner of the object,
+ as well as changing all <literal>current_owner</literal> role references
+ (grantor and grantee) in the Access Control List to <literal>new_owner</literal>;
+ leaving the old role without any direct privileges on the object.
+ </para>
+
</sect1>
<sect1 id="ddl-rowsecurity">
diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml
index 8c2f11480d..799d57a9ab 100644
--- a/doc/src/sgml/glossary.sgml
+++ b/doc/src/sgml/glossary.sgml
@@ -1189,6 +1189,18 @@
</glossdef>
</glossentry>
+ <glossentry id="glossary-owner-rights">
+ <glossterm>Owner rights</glossterm>
+ <glossdef>
+ <para>
+ For objects that have an owner, which is nearly all of them, the owner
+ has certain rights to interact with the object that cannot be revoked:
+ Altering the object, dropping it, and granting privileges on it. See
+ <xref linkend="ddl-priv"/> for more detail on object ownership and privileges.
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry>
<glossterm>Optimizer</glossterm>
<glosssee otherterm="glossary-planner" />
@@ -1298,6 +1310,34 @@
</glossdef>
</glossentry>
+ <glossentry id="glossary-privilege">
+ <glossterm>Privilege</glossterm>
+ <glossdef>
+ <para>
+ The permission, given by bob, for alice to select data from the widget table, is a privilege.
+ Documented in detail in <xref linkend="ddl-priv"/> it consists of a grantor (bob), grantee (alice),
+ action (select) and target object (widget table). This is a direct privilege.
+ </para>
+ <para>
+ In addition to the direct privileges held by a role by virtue of them being named
+ the grantee, a role may also exercise indirect privileges by virtue of being
+ a member of a membership chain with a parent group having the direct privilege.
+ See <xref linkend="role-membership"/>.
+ </para>
+ <para>
+ Direct privileges come about in two ways: explicit privileges arise from usage of
+ the <command>GRANT</command> command; while implicit privileges arise either from
+ being the owner of an object or the object being subject to default privileges
+ when the object was created.
+ </para>
+ <para>
+ The system has a concept of built-in default privileges which are represented as
+ NULL. These defaults are still explicit privileges, and are only implicitly defined.
+ See <xref linkend="ddl-priv"/> for details.
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry id="glossary-procedure">
<glossterm>Procedure (routine)</glossterm>
<glossdef>
--
2.34.1
