Re: catalog view use to find DATABASE, LANGUAGE, TABLESPACE, SCHEMA, SEQUENCE privileges granted to user or role

Tom,

Since you know this are well.  Do you know if there is some kind of
records filtering use by PostgreSQL when selecting system objects?  For
example, I run this query by PostgreSQL user.

select count(*) from information_schema.table_privileges;

I get 445 rows return.

I ran the same query by a new user that I created without any additional
grant.  I get 123 rows return.  I tried granting this user select
privileges and still same thing.  Of course this view has already been
granted to PUBLIC.

I there some kind of system grant I can issue to by user for selecting
system views without making that user a superuser?


Thanks again,
Louis Lam.


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, June 02, 2009 6:43 PM
To: Louis Lam
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] catalog view use to find DATABASE, LANGUAGE,
TABLESPACE, SCHEMA, SEQUENCE privileges granted to user or role

"Louis Lam" <louis.lam@guardium.com> writes:
> Thank you very much for the quick response.  That was very helpful.  I
> was able to find the privilege on pg_language, pg_database and
> pg_tablespace.  I am looking for privileges granted to SCHEMA and
> SEQUENCE.  Do you by any change know what view or table I can queries
to
> get privileges granted on these two?

pg_namespace, pg_class (sequences are just tables).

> Also when I did a select datacl from pg_database.  The privilege
column
> look like this.  Do you know if there are some system function to
decode
> this column?  Or do I have to write code to interpret this myself?

Nope, there's no pretty-printer for it.

            regards, tom lane