Обсуждение: Permissions for information_schema

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?  
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


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

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 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.

 

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