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