Re: view to get all role privileges

Hi,
May be you will have to explore these views...

test=# select schemaname,viewname from pg_views where viewname like
'%priv%';
schemaname | viewname
--------------------+----------------------
information_schema | column_privileges
information_schema | routine_privileges
information_schema | table_privileges
information_schema | udt_privileges
information_schema | usage_privileges
information_schema | data_type_privileges
(6 rows)

test=# select schemaname,viewname from pg_views where viewname like '%rol%';
schemaname | viewname
--------------------+-----------------------------------
pg_catalog | pg_roles
information_schema | applicable_roles
information_schema | administrable_role_authorizations
information_schema | enabled_roles
information_schema | role_column_grants
information_schema | role_routine_grants
information_schema | role_table_grants
information_schema | role_udt_grants
information_schema | role_usage_grants
(9 rows)

On Thu, Nov 7, 2013 at 10:25 AM, Huang, Suya <Suya(dot)Huang(at)au(dot)experian(dot)com>wrote:

> Thanks Ram, but those command doesn’t help here… The goal is to dump all
> privileges in a database and execute it on the new database to make sure we
> have exactly the same environment.
>
>
>
> The privilege of creating schema cannot be listed by \dg or \dp as well.
>
>
>
> Thanks,
>
> Suya
>
>
>
> *From:* Raghu Ram [mailto:raghuchennuru(at)gmail(dot)com]
> *Sent:* Thursday, November 07, 2013 3:50 PM
> *To:* Huang, Suya
> *Cc:* pgsql-novice(at)postgresql(dot)org
> *Subject:* Re: [NOVICE] view to get all role privileges
>
>
>
> On Thu, Nov 7, 2013 at 10:05 AM, Huang, Suya <Suya(dot)Huang(at)au(dot)experian(dot)com>
> wrote:
>
> Hello,
>
>
>
> I’m using Greenplum 4.2.3.0 and did a backup/restore using gpcrondump.
> However, after it completed, I found some of the privileges are missing in
> the new database. for example, the privilege of a particular user to create
> schema in the database.
>
>
>
> Can someone tell me which view in Postgresql is used to check all
> privileges granted to a specific user?
>
>
>
>
>
>
>
> According to PostgreSQL Documentation,you can use PSQL Meta Commands to
> verify the existing Privileges:
>
>
>
> \dg[+] [ *pattern*<http://www.postgresql.org/docs/9.1/static/app-psql.html#APP-PSQL-PATTERNS>
> ]
>
> Lists database roles. If *pattern* is specified, only those roles whose
> names match the pattern are listed. (This command is now effectively the
> same as \du). If the form \dg+ is used, additional information is shown
> about each role, including the comment for each role.
>
> Use psql <http://www.postgresql.org/docs/9.1/static/app-psql.html>'s \dp command
> to obtain information about existing privileges for tables and columns. For
> example:
>
>
>
>
>
> => \dp mytable
>
> Access privileges
>
> Schema | Name | Type | Access privileges | Column access privileges
>
> --------+---------+-------+-----------------------+--------------------------
>
> public | mytable | table | miriam=arwdDxt/miriam | col1:
>
> : =r/miriam : miriam_rw=rw/miriam
>
> : admin=arw/miriam
>
> (1 row)
>
> The entries shown by \dp are interpreted thus:
>
>
>
>
>
> rolename=xxxx -- privileges granted to a role
>
> =xxxx -- privileges granted to PUBLIC
>
>
>
> r -- SELECT ("read")
>
> w -- UPDATE ("write")
>
> a -- INSERT ("append")
>
> d -- DELETE
>
> D -- TRUNCATE
>
> x -- REFERENCES
>
> t -- TRIGGER
>
> X -- EXECUTE
>
> U -- USAGE
>
> C -- CREATE
>
> c -- CONNECT
>
> T -- TEMPORARY
>
> arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
>
> * -- grant option for preceding privilege
>
>
>
> /yyyy -- role that granted this privilege
>
>
>
> Thanks & Regards
>
> Raghu Ram
>