Thread: PGSQL: listing db/role and user/role relationships

Hi all,

    How can I retrieve:


1) each role’s privileges on a given DB

For example, if I do: “GRANT ALL PRIVILEGES ON DATABASE thedb TO
therole”, how can I list this grant among others with access to “thedb”?


2) which users have access to a given role

For example, if I do: “GRANT therole TO theuser”, how can I list all of
the users that can take on “therole”?


3) which roles a given user can access

For example, if I do: “GRANT therole TO theuser”, how can I list all of
the roles that “theuser” can take on?


Thanks!


-Felipe Gasper
Houston, TX, USA

On Sat, Nov 16, 2013 at 1:19 AM, Felipe Gasper <felipe@felipegasper.com> wrote:
> Hi all,
>
>         How can I retrieve:
>
>
> 1) each role’s privileges on a given DB
>

Do you mean pg_database.datacl?
http://www.postgresql.org/docs/current/static/catalog-pg-database.html

> 2) which users have access to a given role
> 3) which roles a given user can access

I guess playing with pg_roles and pg_auth_member will provide you all
the information.

Hope this helps.
Luca

On 17.11.13 2:56 AM, Luca Ferrari wrote:
> On Sat, Nov 16, 2013 at 1:19 AM, Felipe Gasper <felipe@felipegasper.com> wrote:
>> Hi all,
>>
>>          How can I retrieve:
>>
>>
>> 1) each role’s privileges on a given DB
>>
>
> Do you mean pg_database.datacl?
> http://www.postgresql.org/docs/current/static/catalog-pg-database.html
>
>> 2) which users have access to a given role
>> 3) which roles a given user can access
>
> I guess playing with pg_roles and pg_auth_member will provide you all
> the information.
>
> Hope this helps.
> Luca
>

Thank you!

One more question: how “stable” are these interfaces? Are they by chance
available via information_schema?

-FG

On Mon, Nov 18, 2013 at 4:00 AM, Felipe Gasper <felipe@felipegasper.com> wrote:

> One more question: how “stable” are these interfaces? Are they by chance
> available via information_schema?

Enough stable that they have not changed so much since 8.3. But I'm
not used to the information_schema, so I don't know which are
exported/available via such schema.

Luca