PostgreSQL Privileges Test 3

TEST 3 : TABLESPACE

세번째 테스트는 테이블스페이스에 관한 테스트입니다. A Tablespace에 권한이 없는 유저는 A 이용이 가능한가에 관해 테스트를 진행해보겠습니다.

- Tablespace 생성

edb=# create tablespace test_tbs2 location '/data/oss_tbs2';      

CREATE TABLESPACE  

edb=# \db+                    

Name	Owner	Location	Access privileges	Description
pg_default	enterprisedb
pg_global	enterprisedb
test_tbs1	ji	/data/oss_tbs1
test_tbs2	enterprisedb	/data/oss_tbs2

(4 rows)

- ji 에 tablespace 권한 부여

edb=# grant create on tablespace test_tbs2 to ji;

GRANT

edb=# \db+

Name	Owner	Location	Access privileges	l Description
pg_default	enterprisedb
pg_global	enterprisedb
test_tbs1	ji	/data/oss_tbs1
test_tbs2	enterprisedb	/data/oss_tbs2	enterprisedb=C/enterprisedb+ji=C/enterprisedb

(4 rows)

 

- ji & sun 유저에  seoul 스키마에 대한 권한 부여

edb=# grant create, usage on schema seoul to ji;

GRANT

edb=# grant create, usage on schema seoul to sun;

GRANT

edb=# \dns+ seoul

Name	Owner	Access privileges	Description
seoul	enterprisedb	enterprisedb=UC/enterprisedb+ ji=UC/enterprisedb          + sun=UC/enterprisedb

 (4 rows)

 

- ji 접속 후 테이블 생성

edb=> \c edb ji

Password for user ji:

You are now connected to database "edb" as user "ji".

edb=> create table seoul.test_03 (age int) tablespace test_tbs2;

CREATE TABLE

edb=> \dp seoul.test_03

Schema	Name	Type	Access privileges	Column access privileges
seoul	Test_03	table

(1 rows)

 

- sun 접속 후 테이블 생성

edb=> \c edb sun

Password for user sun:

You are now connected to database "edb" as user "sun".

edb=> create table seoul.test_04 (age int) tablespace test_tbs2;

ERROR:  permission denied for tablespace test_tbs2

 

에러 발생 이유 : 권한이 없는 sun은 테이블스페이스를 이용 불가

Post by. 김지선(08.18)