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
