Re: Users/Roles do not align.

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 +