Search code examples
postgresqldatabase-migrationpostgresql-9.2

Is there an equivalent to Oracle's user_role_privs view in Postgres?

Am trying to fetch information in Postgres equivalent to Oracle's user_role_privs.

select username,granted_role,admin_option from user_role_privs

I tried all the below views in Postgres but couldn't find the desired one 

information_schema.role_table_grants
pg_roles;
pg_class;
pg_user
pg_catalog.pg_auth_members;

Can anyone suggest which view should be used to get username, granted_role and admin_option in Postgres?

Solution

  • You are looking for the pg_auth_members system catalog that contains relationships between roles (which feature as both users and groups in PostgreSQL).

    To get the names of the users and roles, join with the pg_roles system catalog.