Re: CREATE ROLE inheritance details
On Wed, Jan 17, 2024 at 02:32:47PM -0700, David G. Johnston wrote: > I had a go at this. > > I went with a more "bullet item" approach with my wording for INHERIT/ > NOINHERIT. I tried to address that. > The entire paragraph regarding how the INHERIT "option" works, as opposed to > the attribute, seems out of place where it was and the material is already > covered in the GRANT page. We should either improve that page or extract this > level of detail somewhere else, not try to clutter up the CREATE ROLE page > with > it. Because the CREATE ROLE is the way to create roles, and GRANT does much more than just grant membership, I ended up moving the details from the GRANT page to the CREATE ROLE page. > We consistently say what the default is for these attribute pairs, do so here > as well. I added that, I hope in all the right places. > Turn the parenthetical in the IN ROLE section into actual assertive > documentation of what the clause does. Tweak ROLE and ADMIN as well to fit in > better. Yes, that needed help. > Reword the discussion regarding non-inheritance to be more direct. > > I added mention of the grantee aspect of privileges as a soft way of further > pointing out that the IN ROLE, ROLE, ADMIN clauses are limited in what they > can > control in the resulting membership grants. > > I choose to use the phrasing "giving ... roles" for both parts of the sentence > instead of switching to "... roles are given" for the second half. More of a > style choice but I didn't think switching really added much and just makes it > a > bit wordier and possibly a bit more effort to mentally parse. I adjusted that working, but in a different way. Patch attached. I also found we didn't document that GRANT can be used to modify a membership's attributes even after it is created; I added that. Just a reminder, this is for PG 16 and master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml index 8dd2a6395c..f2dc841b85 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -133,24 +133,33 @@ in sync when changing the above synopsis! NOINHERIT -When the GRANT statement is used to confer -membership in one role to another role, the GRANT -may use the WITH INHERIT clause to specify whether -the privileges of the granted role should be inherited -by the new member. If the GRANT statement does not -specify either inheritance behavior, the new GRANT -will be created WITH INHERIT TRUE if the member -role is set to INHERIT and to -WITH INHERIT FALSE if it is set to -NOINHERIT. +This affects the membership inheritance status when this +role is added as a member of another role, both in this and +future commands. Specifically, it controls the inheritance +status of memberships added with this command using the +IN ROLE clause, and in later commands using +the ROLE clause. It is also used as the +default inheritance status when adding this role as a member +using the GRANT command. If not specified, +INHERIT is the default. + + + +Role membership with the inherit attribute can automatically use +whatever database privileges have been granted to all roles it +is directly or indirectly a member of, though the chain stops +at memberships lacking the inherit attribute. Without role +inheritance, the only other value of membership is the use of +SET ROLE, assuming the membership chain has +SET attributes. In PostgreSQL versions before 16, -the GRANT statement did not support -WITH INHERIT. Therefore, changing this role-level -property would also change the behavior of already-existing grants. -This is no longer the case. +inheritance was a role-level attribute. It could not be specified +during role addition with GRANT, and changing +this role-level property would also change the inheritance behavior +of all existing memberships. This is no longer the case. @@ -285,9 +294,13 @@ in sync when changing the above synopsis! The IN ROLE clause causes the new role to be automatically added as a member of the specified existing -roles. (Note that there is no option to add the new role as an -administrator; use a separate GRANT command -to do that.) +roles. The new membership will have the SET +option enabled and the ADMIN option disabled. +The INHERIT option will be enabled unless the +NOINHERIT attribute is specified. See the command, which has
Re: create table explicitly mention that unique|primary key constraint will create an
On Fri, 2024-01-26 at 19:01 +0530, vignesh C wrote: > CFBot shows that the patch does not apply anymore as in [1]: There was a conflict with 46a0cd4cefb. Updated version attached. Yours, Laurenz Albe From 193d6d6c20f0c2976e0b63f1896978545def3fe8 Mon Sep 17 00:00:00 2001 From: Laurenz Albe Date: Fri, 26 Jan 2024 16:50:11 +0100 Subject: [PATCH v4] Doc: All relations share a namespace This was already documented in the CREATE INDEX reference, but not in the introductory "Data Definition" chapter. Also, document that the index that implements a constraint has the same name as the constraint. Author: Laurenz Albe Reviewed-by: Jian He, Peter Eisentraut, David G. Johnston Discussion: https://postgr.es/m/CACJufxFG682tYcP9aH_F-jrqq5End8MHZR77zcp1%3DDUrEsSu1Q%40mail.gmail.com --- doc/src/sgml/ddl.sgml | 6 +- doc/src/sgml/ref/create_table.sgml | 8 +--- 2 files changed, 10 insertions(+), 4 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index fc03a349f0..434e074df1 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3003,7 +3003,11 @@ SELECT * FROM information WHERE group_id = 2 FOR UPDATE; A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named - objects, including data types, functions, and operators. The same + objects, including data types, functions, and operators. Within one schema, + two objects of the same type cannot have the same name. All relations + (tables, sequences, indexes, views, materialized views, and foreign tables) + share one name space, so they need to have different names if they are in + a single schema. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 079af9126a..bdc3541930 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1027,7 +1027,8 @@ WITH ( MODULUS numeric_literal, REM Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns used in the constraint. But if the constraint includes a WITHOUT OVERLAPS clause, it - will use a GiST index. + will use a GiST index. The created index has the same name as the unique + constraint. @@ -1082,6 +1083,7 @@ WITH ( MODULUS numeric_literal, REM PRIMARY KEY constraint will automatically create a unique B-tree index, or GiST if WITHOUT OVERLAPS was specified, on the column or group of columns used in the constraint. + That index has the same name as the primary key constraint. @@ -1118,8 +1120,8 @@ WITH ( MODULUS numeric_literal, REM Exclusion constraints are implemented using - an index, so each specified operator must be associated with an - appropriate operator class + an index that has the same name as the constraint, so each specified + operator must be associated with an appropriate operator class (see ) for the index access method index_method. The operators are required to be commutative. -- 2.43.0
Re: create table explicitly mention that unique|primary key constraint will create an
On Fri, Jan 19, 2024 at 3:46 AM Laurenz Albe wrote: > In the attached patch, I have copied the enumeration of relations from > the CREATE INDEX page. I think this small redundance is alright, but I > wouldn't mind if this gets removed from CREATE INDEX. > > Tweaking the main paragraph a little. We use examples elsewhere, it seems one for this makes the point very clear with less description. I removed it altogether but namespace is a word unto itself, not "name space". diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index e103eddd40..25db985a56 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3025,10 +3025,11 @@ SELECT * FROM information WHERE group_id = 2 FOR UPDATE; A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. Within one schema, - two objects of the same type cannot have the same name. All relations - (tables, sequences, indexes, views, materialized views, and foreign tables) - share one name space, so they need to have different names if they are in - a single schema. The same + two objects of the same type cannot have the same name. The object type + of relations encompasses all of the following: + tables, sequences, indexes, views, materialized views, and foreign tables. + Thus, for example, an index and a table must have different names if they + are in the same schema. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, David J.
Re: create table explicitly mention that unique|primary key constraint will create an
On Fri, 19 Jan 2024 at 16:16, Laurenz Albe wrote: > > On Thu, 2024-01-18 at 15:54 +0100, Peter Eisentraut wrote: > > On 27.11.23 03:30, Laurenz Albe wrote: > > > True; I don't find it documented that all objects in pg_class share a > > > namespace and that constraints are implemented by indexes of the same > > > name. But I think that the first part is a property of schemas and had > > > better be documented there. > > > > It is documented prominently on the CREATE INDEX reference page. We > > could document it in more places, of course. I find the specific change > > proposal for ddl.sgml a bit weird, though, because this is a very > > introductory section, and you are referring people to pg_class (what is > > that?!?) for details. If we want to put something there, it should > > respect the order in which that chapter introduces concepts. > > > > The changes on create_table.sgml seem ok. Although I had actually > > expected that the system applies the find-a-unique-name routine rather > > than taking the constraint name for the index name unaltered. > > > > Perhaps taking the create_table.sgml changes and combination with the > > existing text on CREATE INDEX is sufficient. > > Ah, I didn't see the CREATE INDEX page. (As an aside: too much > conceptual stuff is documented in our reference pages, but that's a > different issue.) > > For me, the intuitive place to look for information like that is the > "Data Definition" chapter, so I think we should mention it there. > I agree that "pg_class" is too advanced for that chapter, even though > there is an earlier reference to it under "System Columns". > > In the attached patch, I have copied the enumeration of relations from > the CREATE INDEX page. I think this small redundance is alright, but I > wouldn't mind if this gets removed from CREATE INDEX. > > The rest is unmodified. CFBot shows that the patch does not apply anymore as in [1]: === Applying patches on top of PostgreSQL commit ID d282e88e50521a457fa1b36e55f43bac02a3167f === === applying patch ./v3-0001-Doc-All-relations-share-a-namespace.patch ... patching file doc/src/sgml/ref/create_table.sgml Hunk #1 FAILED at 1001. Hunk #2 FAILED at 1054. Hunk #3 succeeded at 1118 (offset 27 lines). 2 out of 3 hunks FAILED -- saving rejects to file doc/src/sgml/ref/create_table.sgml.rej Please post an updated version for the same. [1] - http://cfbot.cputube.org/patch_46_4747.log Regards, Vignesh
Re: SQL command : ALTER DATABASE OWNER TO
On Wed, 2024-01-24 at 15:26 +0100, Daniel Gustafsson wrote: > > On 24 Jan 2024, at 15:23, Laurenz Albe wrote: > > > > On Wed, 2024-01-24 at 11:08 +0100, gp...@free.fr wrote: > > > for this "ALTER DATABASE" form, it should be mentioned that after > > > execution of the command, > > > the old database owner loses all his privileges on it (even connection) > > > although it might > > > still owns schemas or objects (tables, index,...) inside it. > > > > > > Thanks in advance to add this important precision. > > > > How about this: > > > > diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml > > index 4044f0908f..44042f863c 100644 > > --- a/doc/src/sgml/ddl.sgml > > +++ b/doc/src/sgml/ddl.sgml > > @@ -1891,6 +1891,8 @@ ALTER TABLE table_name > > OWNER TO new_owne > > 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 SET ROLE to the new owning > > role. > > + All object privileges of the old owner are transferred to the new owner > > + along with the ownership. > > > > Doesn't seem unreasonable to me, it won't make the docs harder to read and use > for experienced users while it may make them easier to follow for new users. Here is a patch for this change. Yours, Laurenz Albe From 3685b2ce9d921857d629bd20d49b1acfd5f01576 Mon Sep 17 00:00:00 2001 From: Laurenz Albe Date: Fri, 26 Jan 2024 12:01:37 +0100 Subject: [PATCH v1] Document effects of ownership change on privileges Privileges have always been transferred along with the ownership, but it is a good idea to document that. Per complaint by Gilles Parc. Author: Laurenz Albe Reviewed-by: Daniel Gustafsson, David G. Johnston Discussion: https://postgr.es/m/2023185982.281851219.1646733038464.JavaMail.root%40zimbra15-e2.priv.proxad.net --- doc/src/sgml/ddl.sgml | 2 ++ 1 file changed, 2 insertions(+) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index fc03a349f0..835ebd5a67 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1893,6 +1893,8 @@ ALTER TABLE table_name OWNER TO new_owne 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 SET ROLE to the new owning role. + All object privileges of the old owner are transferred to the new owner + along with the ownership. -- 2.43.0
