Permissions for information_schema

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Permissions for information_schema

Susan Hurst
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: [hidden email]
Mobile: 314-486-3261


Reply | Threaded
Open this post in threaded view
|

Re: Permissions for information_schema

David G Johnston
On Thu, May 16, 2019 at 9:50 AM Susan Hurst <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Permissions for information_schema

Susan Hurst

The objects are granted SELECT to PUBLIC.

 

---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Susan E Hurst Principal Consultant Brookhurst Data LLC Email: [hidden email] 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 <[hidden email]> 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.
 
Reply | Threaded
Open this post in threaded view
|

Re: Permissions for information_schema

Adrian Klaver-4
In reply to this post by Susan Hurst
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
[hidden email]