[PostgreSQL] 기본 권한 설정 default privilege(권한 자동 부여)

 

oracle에서는 신규 오브젝트 생성 후 권한을 부여해줘야 합니다.

postgresql은 기본권한을 설정하여 신규생성되는 오브젝트에 자동으로 권한을 부여하는 편리한 기능이 있습니다

 

- 신규 생성되는 오브젝트에 권한을 자동으로 부여하는 방법
- ALTER DEFAULT PRIVILEGES ... 명령으로 수행
- 기존 오브젝트는 적용안되고 기본권한 설정 후 신규 오브젝트에만 적용됨(기본 권한 제거시에도 동일)

 

[목차여기]

명령어

ALTER DEFAULT PRIVILEGES
    [ FOR USER target_user [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    grant_or_revoke_clause

where grant_or_revoke_clause is one of:

GRANT { { SELECT | INSERT | UPDATE | DELETE | DROP | REFERENCES } [,...] | ALL [ PRIVILEGES ] }
	ON TABLES
	TO { user_name [ WITH GRANT OPTION ] | ROLE role_name | GROUP group_name | PUBLIC } [, ...]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
	ON FUNCTIONS
	TO { user_name [ WITH GRANT OPTION ] |  ROLE role_name | GROUP group_name | PUBLIC } [, ...]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
	ON PROCEDURES
	TO { user_name [ WITH GRANT OPTION ] |  ROLE role_name | GROUP group_name | PUBLIC } [, ...]

REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | REFERENCES } [,...] | ALL [ PRIVILEGES ] }
	ON TABLES
	FROM user_name [, ...] [ RESTRICT ]

REVOKE  { { SELECT | INSERT | UPDATE | DELETE | REFERENCES } [,...] | ALL [ PRIVILEGES ] }
	ON TABLES
	FROM { ROLE role_name | GROUP group_name | PUBLIC } [, ...] [ RESTRICT ]

REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL [ PRIVILEGES ] }
	ON FUNCTIONS
	FROM user_name [, ...] [ RESTRICT ]

REVOKE { EXECUTE | ALL [ PRIVILEGES ] }
	ON FUNCTIONS
	FROM { ROLE role_name | GROUP group_name | PUBLIC } [, ...] [ RESTRICT ]

REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL [ PRIVILEGES ] }
	ON PROCEDURES
	FROM user_name [, ...] [ RESTRICT ]

REVOKE { EXECUTE | ALL [ PRIVILEGES ] }
	ON PROCEDURES
	FROM { ROLE role_name | GROUP group_name | PUBLIC } [, ...] [ RESTRICT ]

파라미터

FOR USER target_user
선택 사항. 기본 권한이 정의되는 사용자의 이름입니다. 수퍼유저만이 다른 사용자의 기본 권한을 지정할 수 있습니다. 기본값은 현재 사용자입니다.

IN SCHEMA schema_name
선택 사항. IN SCHEMA 절이 나타나면 지정된 schema_name에 생성되는 새 객체에 지정된 기본 권한이 적용됩니다. 이 경우, ALTER DEFAULT PRIVILEGES의 대상인 사용자 또는 사용자 그룹은 지정된 스키마에 대해 CREATE 권한을 가져야 합니다. 스키마에 특정한 기본 권한이 기존 전역 기본 권한에 추가됩니다. 기본적으로 전체 데이터베이스에 기본 권한이 전역적으로 적용됩니다.

GRANT
지정된 사용자가 생성하는 모든 새로운 테이블, 함수 또는 저장 프로시저에 대해 지정된 사용자나 그룹에게 부여하는 권한 세트입니다. GRANT 명령과 함께 사용할 수 있는 GRANT 절을 이용해 동일한 권한과 옵션을 설정할 수 있습니다.

WITH GRANT OPTION
권한을 받은 사용자가 이번에는 똑같은 권한을 다른 사용자에게 허용할 수 있음을 나타내는 절입니다. 그룹 또는 PUBLIC에는 WITH GRANT OPTION을 허용할 수 없습니다.

TO user_name | ROLE role_name | GROUP group_name
지정된 기본 권한이 적용될 사용자, 역할 또는 사용자 그룹의 이름입니다.

REVOKE
지정된 사용자가 생성하는 모든 새로운 테이블, 함수 또는 저장 프로시저에 대해 지정된 사용자나 그룹에서 취소하는 권한 세트입니다. REVOKE 명령과 함께 사용할 수 있는 REVOKE 절을 이용해 동일한 권한과 옵션을 설정할 수 있습니다.

GRANT OPTION FOR
다른 사용자에게 지정된 권한을 허용하는 옵션만 취소하고 권한 자체를 취소하지는 않는 절입니다. 그룹 또는 PUBLIC에서 GRANT OPTION을 취소할 수 없습니다.

FROM user_name | ROLE role_name | GROUP group_name
지정된 권한이 기본적으로 취소되는 사용자, 역할 또는 사용자 그룹의 이름입니다.

RESTRICT
RESTRICT 옵션은 사용자가 직접 허용한 권한만 취소합니다. 이 값이 기본값입니다.

 

예시

-- sk1 스키마 모든 테이블 SIUD 권한을 sk1에게 부여하는 기본권한 
alter default privileges in schema sk1 grant select,insert,update,delete on tables to sk1 ;
-- sk1 스키마 시퀀스의 모든 권한을 sk1에게 부여하는 기본권한
alter default privileges in schema sk1 grant all on sequences to sk1 ;
-- sk1 스키마의 펑션 실행 권한을 sk1에게 부여하는 기본권한
alter default privileges in schema sk1 grant execute on functions to sk1 ;

-- 기본 권한 제거
ALTER DEFAULT PRIVILEGES IN SCHEMA sk1 REVOKE ALL ON TABLES FROM sk1 ;

 

반응형

테스트

기본 권한 설정 전 테이블 생성

-- 스키마생성
testdb=# create schema sk1 ;
CREATE SCHEMA
-- 유저생성
testdb=# CREATE USER sk1 password 'sk1' ;
CREATE ROLE
-- 스키마 usage 권한 부여
testdb=# GRANT USAGE ON SCHEMA sk1 TO sk1 ;
GRANT
-- 테스트 테이블(sk1.tab1) 생성
testdb=# create table sk1.tab1 as SELECT LEVEL FROM GENERATE_SERIES(1,100) level ;
SELECT 100
testdb=# select count(*) from sk1.tab1 ;
 count 
-------
   100
(1 row)

 

테이블 조회

sk1 유저로 sk1.tab1 테이블 조회

[postgres@svr1:/home/postgres]$ psql -d testdb -U sk1
Password for user sk1: 
psql (14.9)
Type "help" for help.

testdb=> 
testdb=> \dt+ sk1.tab1
                                     List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method |    Size    | Description 
--------+------+-------+----------+-------------+---------------+------------+-------------
 sk1    | tab1 | table | postgres | permanent   | heap          | 8192 bytes | 
(1 row)

-- 조회시 권한에러 발생
testdb=> select count(*) from sk1.tab1 ;
ERROR:  permission denied for table tab1
testdb=>

-- 테이블 권한 확인 시 아무것도 없음 아직 아무권한도 부여하지 않았으므로
testdb=# select * 
testdb-# from information_schema.table_privileges
testdb-# where 1=1
testdb-# AND grantee = 'sk1'
testdb-# ;
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
(0 rows)

testdb=#

 

기본 권한 설정

-- 기본 권한 조회, 아직 아무것도 없음
testdb=#  select
testdb-#  defaclrole::regrole AS owner,
testdb-#  defaclnamespace::regnamespace AS schema,
testdb-#  defaclobjtype AS object_type,
testdb-#  defaclacl AS privileges
testdb-# FROM pg_default_acl
testdb-# order by owner, schema, object_type
testdb-# ;
 owner | schema | object_type | privileges 
-------+--------+-------------+------------
(0 rows)

-- 기본 권한 설정
-- sk1 스키마 모든 테이블 SIUD권한 sk1 유저에게 부여
testdb=# ALTER DEFAULT PRIVILEGES IN SCHEMA sk1 GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO sk1 ;
ALTER DEFAULT PRIVILEGES

-- 기본 권한 확인
testdb=#  select                                                                                       
 defaclrole::regrole AS owner,
 defaclnamespace::regnamespace AS schema,
 defaclobjtype AS object_type,
 defaclacl AS privileges
FROM pg_default_acl
order by owner, schema, object_type
;
  owner   | schema | object_type |     privileges      
----------+--------+-------------+---------------------
 postgres | sk1    | r           | {sk1=arwd/postgres}
(1 row)​

 

기본 권한 설정 후 테이블 생성

-- 테이블(sk1.tab2) 생성
testdb=# create table sk1.tab2 as SELECT LEVEL FROM GENERATE_SERIES(1,100) level ;
SELECT 100
-- 테이블 권한 조회 시 tab2 테이블은 기본 권한이 자동으로 권한 부여되어 있음
testdb=# select *                                                                 
from information_schema.table_privileges
where 1=1
AND grantee = 'sk1'
;
 grantor  | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
----------+---------+---------------+--------------+------------+----------------+--------------+----------------
 postgres | sk1     | testdb          | sk1          | tab2       | INSERT         | NO           | NO
 postgres | sk1     | testdb          | sk1          | tab2       | SELECT         | NO           | YES
 postgres | sk1     | testdb          | sk1          | tab2       | UPDATE         | NO           | NO
 postgres | sk1     | testdb          | sk1          | tab2       | DELETE         | NO           | NO
(4 rows)

testdb=# 

-- sk1 유저로 sk1.tab2 조회
testdb=> select count(*) from sk1.tab2 ;
 count 
-------
   100
(1 row)

testdb=>