Permissions for information_schema

On Thu, May 16, 2019 at 9:50 AM Susan Hurst <susan.hurst@brookhurstdata.com>
wrote:

What are the correct permissions to give to a role so that all objects
in the information_schema (and pg_catalog) are visible to a user?

Have you determined that using the underlying pg_catalog schema is not
viable.

David J.

The objects are granted SELECT to PUBLIC.

---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Susan E Hurst
Principal Consultant
Brookhurst Data LLC
Email: susan.hurst@brookhurstdata.com
Mobile: 314-486-3261

On 2019-05-16 11:53, David G. Johnston wrote:

On 5/16/19 9:50 AM, Susan Hurst wrote:

What are the correct permissions to give to a role so that all objects
in the information_schema (and pg_catalog) are visible to a user?

As example:
https://www.postgresql.org/docs/11/infoschema-tables.html

"... Only those tables and views are shown that the current user has
access to (by way of being the owner or having some privilege)."

If you do:

\d+ information_schema.tables

at the end of the view definition you will see:

... AND (pg_has_role(c.relowner, 'USAGE'::text) OR
has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE,
REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT,
INSERT, UPDATE, REFERENCES'::text));

So the permissions check is baked into the view definition. That means
the role doing the query has to meet the above criteria. Either you have
to create a role that creates all objects and then let that role use the
information_schema(or grant it to other roles) or you need to use a
superuser role.

Permissions seem to make a difference but I don't know which adjustments
to make without causing unintended consequences. We revoked select on
all tables and functions from public, if that makes a difference.  We
don't use the public schema but it appears that postgres does.

Should I be looking at something other than permissions to make
information_schema more visible?  We are particularly interested in
using the comments on everything to create views of our database
structures that we can use for our team's training documentation.  Of
course, the comments/descriptions can't be selected in isolation so we
need full visibility.

Below are samples of select statements with outputs that disagree based
upon the database and presumably, the permissions.

Thanks for your help!

Sue

Production db logged in as admin:

CREATE ROLE admin LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT read TO admin;
GRANT write TO admin;

select * from information_schema.table_constraints;         -- 206 rows
select * from information_schema.constraint_column_usage;   -- 0 rows

sandbox db logged in as postgres:

CREATE ROLE postgres LOGIN
  ENCRYPTED PASSWORD 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
  SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;
select * from information_schema.table_constraints;        -- 621 rows
select * from information_schema.constraint_column_usage;  -- 127 rows

--
Adrian Klaver
adrian.klaver@aklaver.com