Grant user priv to access schema

Post date: Apr 12, 2013 2:25:54 PM

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;

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