Обсуждение: catalog view use to find DATABASE, LANGUAGE, TABLESPACE, SCHEMA, SEQUENCE privileges granted to user or role

"Louis Lam" <louis.lam@guardium.com> writes:
> In PostgreSQL, there are some grant privileges like grant on SEQUENCE,
> DATABASE, LANGUAGE, SCHEMA and DATABASE.  I grant and revoke these
> privleges on my PostgreSQL on Windows and Linux OS.  But once it is
> granted, how do I find out what user and role are granted with these
> privileges?

It's stored in the various "acl" columns of the system catalogs, which
you can look at either directly or via psql's \d commands.  See the
GRANT reference page for an example and a discussion of how to read
the entries.

            regards, tom lane

Hi Tom,

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?

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?

"{=CTc/postgres,postgres=CTc/postgres,louis3=CTc/postgres,qa_test=C*/pos
tgres,louis_role=C*/postgres}"


Again, thank you very much.
Louis Lam.


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, June 02, 2009 4:12 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:
> In PostgreSQL, there are some grant privileges like grant on SEQUENCE,
> DATABASE, LANGUAGE, SCHEMA and DATABASE.  I grant and revoke these
> privleges on my PostgreSQL on Windows and Linux OS.  But once it is
> granted, how do I find out what user and role are granted with these
> privileges?

It's stored in the various "acl" columns of the system catalogs, which
you can look at either directly or via psql's \d commands.  See the
GRANT reference page for an example and a discussion of how to read
the entries.

            regards, tom lane

"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

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

"Louis Lam" <louis.lam@guardium.com> writes:
> 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;

The information_schema views filter out information about objects that
you don't have any privileges for.  This is required by SQL spec.
It's a bit pointless, since anybody can look at the underlying catalogs,
but we make them follow spec anyway.

            regards, tom lane

Tom,

This sound pretty ugly.  Are you saying that there is no way I can
select all the rows for views in the information_schema, unless I am the
database owner or superuser? in the server?  I can't just grant a user
some type of system view privileges to override this feature like other
database type?

So if I need to find out what table, view and function are granted to
user or role.  I should be force to use pg_class and pg_proc?  Unless I
can have superuser access?


Thanks,
Louis.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, June 03, 2009 2:15 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:
> 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;

The information_schema views filter out information about objects that
you don't have any privileges for.  This is required by SQL spec.
It's a bit pointless, since anybody can look at the underlying catalogs,
but we make them follow spec anyway.

            regards, tom lane

On Wed, Jun 03, 2009 at 01:46:00PM -0500, Louis Lam wrote:
> So if I need to find out what table, view and function are granted to
> user or role.  I should be force to use pg_class and pg_proc?  Unless I
> can have superuser access?

Use \dv+ on the information_schema view you want and copy the query.
Take out the permission check and you're done.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Hi Matijn,

Thank you very much for the suggestion.  I was able to figure this out
yesterday by running this query to get the source code from the view
then strip out the permission check and it work great.

select * from pg_views where viewname = 'table_privileges'


Thank you and Tom Lane for the help

Louis.


-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: Friday, June 05, 2009 1:18 PM
To: Louis Lam
Cc: Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] catalog view use to find DATABASE,
LANGUAGE,TABLESPACE, SCHEMA, SEQUENCE privileges granted to user or role

On Wed, Jun 03, 2009 at 01:46:00PM -0500, Louis Lam wrote:
> So if I need to find out what table, view and function are granted to
> user or role.  I should be force to use pg_class and pg_proc?  Unless
> I can have superuser access?

Use \dv+ on the information_schema view you want and copy the query.
Take out the permission check and you're done.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.