Re: view to get all role privileges

Checked, none of them has the create schema privilege information.

I'm curious, no one ever has this problem before?

Thanks,
Suya

From: Jayadevan M [mailto:maymala(dot)jayadevan(at)gmail(dot)com]
Sent: Thursday, November 07, 2013 3:59 PM
To: Huang, Suya
Cc: Raghu Ram; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] 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<mailto: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<mailto:raghuchennuru(at)gmail(dot)com>]
Sent: Thursday, November 07, 2013 3:50 PM
To: Huang, Suya
Cc: pgsql-novice(at)postgresql(dot)org<mailto: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<mailto: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