Re: Permissions for information_schema

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(dot)klaver(at)aklaver(dot)com