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
