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

From: "Louis Lam" <louis(dot)lam(at)guardium(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: catalog view use to find DATABASE, LANGUAGE, TABLESPACE, SCHEMA, SEQUENCE privileges granted to user or role
Date: 2009-06-02 22:18:32
Message-ID: 6D8540A29236624096E719740FC5C75306958A80@guardium-01-ex.atlarge.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, June 02, 2009 4:12 PM
To: Louis Lam
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] catalog view use to find DATABASE, LANGUAGE,
TABLESPACE, SCHEMA, SEQUENCE privileges granted to user or role

"Louis Lam" <louis(dot)lam(at)guardium(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-06-02 22:42:49 Re: catalog view use to find DATABASE, LANGUAGE, TABLESPACE, SCHEMA, SEQUENCE privileges granted to user or role
Previous Message Ben Chobot 2009-06-02 22:13:40 Re: Really out of memory?