Permissions for information_schema

From: Susan Hurst <susan(dot)hurst(at)brookhurstdata(dot)com>
To: Discuss List Postgres <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Permissions for information_schema
Date: 2019-05-16 16:50:39
Message-ID: f85051722ca704d022c89dbc908338d5@mail.brookhurstdata.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(dot)hurst(at)brookhurstdata(dot)com
Mobile: 314-486-3261

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tony Shelver 2019-05-16 16:51:34 Re: bigint out of range
Previous Message Steve Atkins 2019-05-16 16:50:01 Re: schema change tracking