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.klaver@aklaver.com