Re: Users/Roles do not align. - Mailing list pgsql-docs

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 +