Re: view to get all role privileges
От | Jayadevan M |
---|---|
Тема | Re: view to get all role privileges |
Дата | |
Msg-id | CAFS1N4j-kvW7vtMujUXOzmpwFgyPJHRUwF9eRX-6ehz-dPD66Q@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: view to get all role privileges ("Huang, Suya" <Suya.Huang@au.experian.com>) |
Ответы |
Re: view to get all role privileges
|
Список | pgsql-novice |
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)
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 по дате отправления: