Re: view to get all role privileges - Mailing list pgsql-novice
| From | Huang, Suya |
|---|---|
| Subject | Re: view to get all role privileges |
| Date | |
| Msg-id | D83E55F5F4D99B4A9B4C4E259E6227CDA03578@AUX1EXC02.apac.experian.local Whole thread Raw |
| In response to | Re: view to get all role privileges (Raghu Ram <raghuchennuru@gmail.com>) |
| Responses |
Re: view to get all role privileges
Re: view to get all role privileges |
| List | pgsql-novice |
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@gmail.com]
Sent: Thursday, November 07, 2013 3:50 PM
To: Huang, Suya
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] view to get all role privileges
On Thu, Nov 7, 2013 at 10:05 AM, Huang, Suya <Suya.Huang@au.experian.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 ]
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'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
pgsql-novice by date: