Обсуждение: view to get all role privileges

Поиск
Список
Период
Сортировка

view to get all role privileges

От
"Huang, Suya"
Дата:

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?

 

Thanks,

Suya

Re: view to get all role privileges

От
Raghu Ram
Дата:

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

Re: view to get all role privileges

От
"Huang, Suya"
Дата:

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

Re: view to get all role privileges

От
Jayadevan M
Дата:
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.Huang@au.experian.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@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


Re: view to get all role privileges

От
Raghu Ram
Дата:

On Thu, Nov 7, 2013 at 10:25 AM, Huang, Suya <Suya.Huang@au.experian.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.

 


Command to get all privileges from respective Database:

 /opt/PostgreSQL/9.2/bin/pg_dump -s -U postgres -c  ksnp |egrep "${SCHEMAS}\..*OWNER TO" >> /tmp/privilagesddl.sql

Here,ksnp is a database name and then execute  "/tmp/privilagesddl.sql" file to newly created database.

Thanks & Regards
Raghu Ram

Re: view to get all role privileges

От
"Huang, Suya"
Дата:

Hello Ram,

 

The create schema privilege is missed from the dump file.

 

Thanks,

Suya

 

From: Raghu Ram [mailto:raghuchennuru@gmail.com]
Sent: Thursday, November 07, 2013 4:06 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:25 AM, Huang, Suya <Suya.Huang@au.experian.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.

 

 

Command to get all privileges from respective Database:

 

 /opt/PostgreSQL/9.2/bin/pg_dump -s -U postgres -c  ksnp |egrep "${SCHEMAS}\..*OWNER TO" >> /tmp/privilagesddl.sql

 

Here,ksnp is a database name and then execute  "/tmp/privilagesddl.sql" file to newly created database.

 

Thanks & Regards

Raghu Ram

 

Re: view to get all role privileges

От
"Huang, Suya"
Дата:

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.jayadevan@gmail.com]
Sent: Thursday, November 07, 2013 3:59 PM
To: Huang, Suya
Cc: Raghu Ram; pgsql-novice@postgresql.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.Huang@au.experian.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@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

 

Re: view to get all role privileges

От
Raghu Ram
Дата:

On Thu, Nov 7, 2013 at 10:40 AM, Huang, Suya <Suya.Huang@au.experian.com> wrote:

Hello Ram,

 

The create schema privilege is missed from the dump file.

 


Requesting you to share dump command which you have used to take dump of the database ?

Thanks & Regards
Raghu Ram 

Re: view to get all role privileges

От
Raghu Ram
Дата:

On Thu, Nov 7, 2013 at 11:10 AM, Raghu Ram <raghuchennuru@gmail.com> wrote:

On Thu, Nov 7, 2013 at 10:40 AM, Huang, Suya <Suya.Huang@au.experian.com> wrote:

Hello Ram,

 

The create schema privilege is missed from the dump file.

 


You can get Schema privileges using below function:

postgres=# CREATE OR REPLACE FUNCTION schema_privs(text) RETURNS table(username text, schemaname name, privieleges text[])
postgres-# AS
postgres-# $$
postgres$#   SELECT $1, c.nspname, array(select privs from unnest(ARRAY[
postgres$# ( CASE WHEN has_schema_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
postgres$# (CASE WHEN has_schema_privilege($1,c.oid,'USAGE') THEN 'USAGE' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL)
postgres$# FROM pg_namespace c where has_schema_privilege($1,c.oid,'CREATE,USAGE');
postgres$# $$ language sql;
CREATE FUNCTION

                             
postgres=# select  schema_privs('postgres');
                  schema_privs
------------------------------------------------
 (postgres,pg_toast,"{CREATE,USAGE}")
 (postgres,pg_temp_1,"{CREATE,USAGE}")
 (postgres,pg_toast_temp_1,"{CREATE,USAGE}")
 (postgres,pg_catalog,"{CREATE,USAGE}")
 (postgres,information_schema,"{CREATE,USAGE}")
 (postgres,public,"{CREATE,USAGE}")
(6 rows)

Thanks & Regards
Raghu Ram