Re: view to get all role privileges

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<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