Thread: Users/Roles do not align.
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/12/ddl-schemas.html Description: According to Section 5.9. Schemas: https://www.postgresql.org/docs/12/ddl-schemas.html `A PostgreSQL database cluster contains one or more named databases. Users and groups of users are shared across the entire cluster, but no other data is shared across databases. Any given client connection to the server can access only the data in a single database, the one specified in the connection request.` According to Chapter 21. Database Roles: https://www.postgresql.org/docs/12/user-manag.html `In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles. Any role can act as a user, a group, or both.` Based on this, I believe Section 5.9 should read: `A PostgreSQL database cluster contains one or more named databases. Roles are shared across the entire cluster, but no other data is shared across databases. Any given client connection to the server can access only the data in a single database, the one specified in the connection request.`
Based on this, I believe Section 5.9 should read: `A PostgreSQL database cluster contains one or more named databases. Roles are shared across the entire cluster, but no other data is shared across databases. Any given client connection to the server can access only the data in a single database, the one specified in the connection request.`
imo the following is a more precise wording:
'A cluster contains three or more named databases ('template0', 'template1', 'postgres', ...). Roles, which are users or groups, see Chapter 21, - as well as database names and tablespace definitions - are shared across the entire cluster. No other data is shared across databases or schemas. Any given client connection to the server can access only the data in a single database, the one specified in the connection request. If it has the necessary privileges, the connection can access all schemas within this database.'
And the last sentence of the paragraph behind 'Note' shall be extended, because schemas are rigidly separated from each other - only the access to different schemas is easily done from a single connection.
'Unlike access to databases, access to schemas is not rigidly separated: a connection can access objects in any of the schemas in the database they are connected to, if they have privileges to do so.'
J. Purtz
Greetings,
* Jürgen Purtz (juergen@purtz.de) wrote:
> >Based on this, I believe Section 5.9 should read:
> >`A PostgreSQL database cluster contains one or more named databases. Roles
> >are shared across the entire cluster, but no other data is shared across
> >databases. Any given client connection to the server can access only the
> >data in a single database, the one specified in the connection request.`
>
> imo the following is a more precise wording:
>
> 'A cluster contains three or more named databases ('template0', 'template1',
> 'postgres', ...). Roles, which are users or groups, see Chapter 21, - as
Roles aren't 'users or groups', they're roles, and we don't actually
have users or groups today.
> well as database names and tablespace definitions - are shared across the
> entire cluster. No other data is shared across databases or schemas. Any
> given client connection to the server can access only the data in a single
> database, the one specified in the connection request. If it has the
> necessary privileges, the connection can access all schemas within this
> database.'
There's a few things wrong about this part anyway- namely that we've got
FDWs now, and there's certainly other cluster-wide things that exist
beyond the specific items listed, so I wonder if perhaps we should just
stop trying to list everything here. The description given in 22.1
seems like it's a lot better since it talks about the hierarchy in a
general sense.
The minimalistic approach suggested initially seems like it might be the
best answer to this right now.
Thanks,
Stephen
Attachment
> There's a few things wrong about this part anyway- namely that we've got > FDWs now, and there's certainly other cluster-wide things that exist > beyond the specific items listed, so I wonder if perhaps we should just > stop trying to list everything here. Inspiring answer! After some inquiry I became aware, that we do not have only 2 levels of 'belong-to' but 3: tables, views, operators, and much more objects belong to a schema; schemata, extensions (e.g. FDW), and more(?) belong to a database; databases, roles, tablespaces, and more belong to a cluster. Two aspects of 'belong-to' are: object names are unique within their level, and objects are automatically known everywhere within their level. Information about such dependencies and their consequences is spread across different chapters of the documentation and the System Catalog. Of course the chapter about roles/users is not suitable to explain the details. But it's important to know the hierarchy, it shut be summarized somewhere. Kind regards, J. Purtz
On Thu, Feb 6, 2020 at 11:06:44AM +0100, Jürgen Purtz wrote:
>
> > There's a few things wrong about this part anyway- namely that we've got
> > FDWs now, and there's certainly other cluster-wide things that exist
> > beyond the specific items listed, so I wonder if perhaps we should just
> > stop trying to list everything here.
>
> Inspiring answer! After some inquiry I became aware, that we do not have
> only 2 levels of 'belong-to' but 3: tables, views, operators, and much more
> objects belong to a schema; schemata, extensions (e.g. FDW), and more(?)
> belong to a database; databases, roles, tablespaces, and more belong to a
> cluster. Two aspects of 'belong-to' are: object names are unique within
> their level, and objects are automatically known everywhere within their
> level.
>
> Information about such dependencies and their consequences is spread across
> different chapters of the documentation and the System Catalog. Of course
> the chapter about roles/users is not suitable to explain the details. But
> it's important to know the hierarchy, it shut be summarized somewhere.
I developed the attached patch to address this suggestion. FYI, you can
list global objects using this query:
SELECT relname
FROM pg_class JOIN pg_tablespace ON (reltablespace = pg_tablespace.oid)
WHERE relkind = 'r' and spcname = 'pg_global';
relname
-----------------------
pg_authid
pg_subscription
pg_database
pg_db_role_setting
pg_tablespace
pg_auth_members
pg_shdepend
pg_shdescription
pg_replication_origin
pg_shseclabel
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
Attachment
Patch applied through 9.5, thanks. --------------------------------------------------------------------------- On Mon, Mar 9, 2020 at 10:50:26PM -0400, Bruce Momjian wrote: > On Thu, Feb 6, 2020 at 11:06:44AM +0100, Jürgen Purtz wrote: > > > > > There's a few things wrong about this part anyway- namely that we've got > > > FDWs now, and there's certainly other cluster-wide things that exist > > > beyond the specific items listed, so I wonder if perhaps we should just > > > stop trying to list everything here. > > > > Inspiring answer! After some inquiry I became aware, that we do not have > > only 2 levels of 'belong-to' but 3: tables, views, operators, and much more > > objects belong to a schema; schemata, extensions (e.g. FDW), and more(?) > > belong to a database; databases, roles, tablespaces, and more belong to a > > cluster. Two aspects of 'belong-to' are: object names are unique within > > their level, and objects are automatically known everywhere within their > > level. > > > > Information about such dependencies and their consequences is spread across > > different chapters of the documentation and the System Catalog. Of course > > the chapter about roles/users is not suitable to explain the details. But > > it's important to know the hierarchy, it shut be summarized somewhere. > > I developed the attached patch to address this suggestion. FYI, you can > list global objects using this query: > > SELECT relname > FROM pg_class JOIN pg_tablespace ON (reltablespace = pg_tablespace.oid) > WHERE relkind = 'r' and spcname = 'pg_global'; > relname > ----------------------- > pg_authid > pg_subscription > pg_database > pg_db_role_setting > pg_tablespace > pg_auth_members > pg_shdepend > pg_shdescription > pg_replication_origin > pg_shseclabel > > -- > Bruce Momjian <bruce@momjian.us> https://momjian.us > EnterpriseDB https://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + > diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml > index 8d3a0d1c22..fe5e81cd65 100644 > --- a/doc/src/sgml/ddl.sgml > +++ b/doc/src/sgml/ddl.sgml > @@ -2625,19 +2625,18 @@ SELECT * FROM information WHERE group_id = 2 FOR UPDATE; > </indexterm> > > <para> > - A <productname>PostgreSQL</productname> database cluster > - contains one or more named databases. Users and groups of users are > - shared across the entire cluster, but no other data is shared across > - databases. Any given client connection to the server can access > - only the data in a single database, the one specified in the connection > - request. > + A <productname>PostgreSQL</productname> database cluster contains > + one or more named databases. Roles and a few other object types are > + shared across the entire cluster. A client connection to the server > + can only access data in a single database, the one specified in the > + connection request. > </para> > > <note> > <para> > Users of a cluster do not necessarily have the privilege to access every > - database in the cluster. Sharing of user names means that there > - cannot be different users named, say, <literal>joe</literal> in two databases > + database in the cluster. Sharing of role names means that there > + cannot be different roles named, say, <literal>joe</literal> in two databases > in the same cluster; but the system can be configured to allow > <literal>joe</literal> access to only some of the databases. > </para> > diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml > index b1b8539fb3..0510afd818 100644 > --- a/doc/src/sgml/manage-ag.sgml > +++ b/doc/src/sgml/manage-ag.sgml > @@ -22,16 +22,13 @@ > </indexterm> > > <para> > - A database is a named collection of <acronym>SQL</acronym> objects > - (<quote>database objects</quote>). Generally, every database > - object (tables, functions, etc.) belongs to one and only one > - database. (However there are a few system catalogs, for example > - <literal>pg_database</literal>, that belong to a whole cluster and > - are accessible from each database within the cluster.) More > - accurately, a database is a collection of schemas and the schemas > - contain the tables, functions, etc. So the full hierarchy is: > - server, database, schema, table (or some other kind of object, > - such as a function). > + A small number of objects, like role, database, and tablespace names, > + are stored at the cluster level and use the <literal>pg_global</literal> > + tablespace. Inside the cluster are multiple databases, which > + are isolated from each other but can access cluster-level objects. > + Inside each database are multiple schemas, which contain objects like > + tables and functions. So the full hierarchy is: cluster, database, > + schema, table (or some other kind of object, such as a function). > </para> > > <para> -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +