Users/Roles do not align.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Users/Roles do not align.

apt.postgresql.org Repository Update
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.`
Reply | Threaded
Open this post in threaded view
|

Re: Users/Roles do not align.

Jürgen Purtz

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

Reply | Threaded
Open this post in threaded view
|

Re: Users/Roles do not align.

Stephen Frost
Greetings,

* Jürgen Purtz ([hidden email]) 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Users/Roles do not align.

Jürgen Purtz

> 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




Reply | Threaded
Open this post in threaded view
|

Re: Users/Roles do not align.

Bruce Momjian
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  <[hidden email]>        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 +

global.diff (2K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Users/Roles do not align.

Bruce Momjian

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  <[hidden email]>        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  <[hidden email]>        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 +