Date: Mon, 9 Mar 2020 22:50:26 -0400
From: Bruce Momjian <bruce@momjian.us>
To: =?iso-8859-1?Q?J=FCrgen?= Purtz <juergen@purtz.de>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: Users/Roles do not align.
Message-ID: <20200310025026.GA27552@momjian.us>
References: <158057750885.1123.2806779262588618988@wrigleys.postgresql.org>
 <2a273cf6-51be-a25b-5ef6-915ca727d4db@purtz.de>
 <20200205183005.GX3195@tamriel.snowman.net>
 <ae68a0ba-9a20-bbab-8964-4b66c459e4c3@purtz.de>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="WIyZ46R2i8wDzkSu"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <ae68a0ba-9a20-bbab-8964-4b66c459e4c3@purtz.de>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


--WIyZ46R2i8wDzkSu
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

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 +

--WIyZ46R2i8wDzkSu
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="global.diff"

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>

--WIyZ46R2i8wDzkSu--