From: Bruce Momjian <bruce@momjian.us>
To: Jürgen Purtz <juergen@purtz.de>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: Users/Roles do not align.
Date: Mon, 9 Mar 2020 22:50:26 -0400
Message-ID: <20200310025026.GA27552@momjian.us> (raw)
In-Reply-To: <ae68a0ba-9a20-bbab-8964-4b66c459e4c3@purtz.de>
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>
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 +
Attachments:global.diff (text/x-diff, 2.8K) downloaddiff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgmlindex 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.sgmlindex 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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, juergen@purtz.de, pgsql-docs@lists.postgresql.org
Subject: Re: Users/Roles do not align.
In-Reply-To: <20200310025026.GA27552@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox