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