Grant user priv to access schema

posted Apr 28, 2017, 4:24 PM by Sachchida Ojha
test=# create role training04 with login;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
test=# grant all on schema testschema to training04;
See also ---> test=#GRANT USAGE ON SCHEMA testschema TO training04;
test=# create schema testschema;
CREATE SCHEMA
test=# grant all on schema testschema to training04;
GRANT
test=# \dn
        List of schemas
        Name        |  Owner   
--------------------+----------
 information_schema | obarrett
 pg_aoseg           | obarrett
 pg_bitmapindex     | obarrett
 pg_catalog         | obarrett
 pg_toast           | obarrett
 public             | obarrett
 testschema         | obarrett
(10 rows)

test=# \q
/Users/obarrett $ psql -d test -U training04
psql (8.2.14)
Type "help" for help.

test=> create table testschema.foo(bar char);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'bar' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
test=> 

test=> alter role training04 set search_path to "$user", public, testschema;
ALTER ROLE
test=> \c
psql (8.2.14)
You are now connected to database "test".
test=> \d
                List of relations
   Schema   | Name | Type  |   Owner    | Storage 
------------+------+-------+------------+---------
 testschema | foo  | table | training04 | heap
(1 row)

DROP ROLE

test=# create role training04 with login;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
test=# grant all on database test to training04;
GRANT
test=# \q

/Users/obarrett $ psql -d test -U training04
psql (8.2.14)
Type "help" for help.

test=> create schema testschema;
CREATE SCHEMA
test=> \dn
         List of schemas
        Name        |   Owner    
--------------------+------------
 gp_toolkit         | obarrett
 information_schema | obarrett
 pg_aoseg           | obarrett
 pg_bitmapindex     | obarrett
 pg_catalog         | obarrett
 pg_toast           | obarrett
 public             | obarrett
 testschema         | training04
(10 rows)

test=>
Comments