PostgreSQL | 역할(사용자) ROLE 생성 | 역할 테이블, 뷰 등에 대한 권한을 추가(GRANT)
GRANT 명령을 사용하여 역할에 권한을 추가할 수 있다. 권한은 테이블이나 스키마 작성 권한이나 테이블과 컬럼에서 데이터를 검색하거나 데이터를 추가할 수 있는 권한 등이다. 여기에서는 GRANT 명령을 사용하여 권한을 추가하는 방법에 대해 설명한다.
GRANT 명령을 사용하여 권한 추가하기
GRANT 명령을 사용하여 역할에 권한을 추가할 수 있다. 목적에 따라 여러 형식이 준비되어 있다.
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE }
routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
여러 형식이 있지만, 기본은 아래와 같다.
GRANT 권한 ON 대상 TO 누구
“권한“은 대상에 따라 다른데 정리하자면 다음과 같다.
| 권한 | 설명 |
|---|---|
| SELECT | 테이블이나 뷰에 데이터를 조회할 수 있다. UPDATE와 DELETE를하기 위해서도 필요하다. |
| INSERT | 테이블에 새로운 데이터를 추가할 수 있다. |
| UPDATE | 테이블의 데이터를 업데이트 할 수 있다. |
| DELETE | 테이블에서 데이터를 삭제할 수 있다. |
| TRUNCATE | 테이블의 데이터를 비울 수 있다. |
| REFERENCES | 테이블과 컬럼에 외래 키 제약 조건을 만들 수 있다. |
| TRIGGER | 테이블에 트리거를 만들 수 있다. |
| CREATE | 대상이 데이터베이스의 경우 스키마를 만들 수 있다. 대상 스키마의 경우, 테이블 등의 개체를 만들 수 있다. |
| CONNECT | 지정된 데이터베이스에 연결할 수 있다. |
| TEMPORARY | 데이터베이스에 임시 테이블을 만들 수 있다. |
| EXECUTE | 함수 또는 프로 시저 연산자의 사용을 허용한다. |
| USAGE | 대상이 스키마의 경우는 객체에 대한 액세스를 허용한다. 대상이 스키가 아니면, 대상에 따라 권한이 부여 된다. |
| ALL PRIVILEGES | 사용할 수 있는 권한을 정리해 허용한다. |
“대상“은 테이블, 테이블의 컬럼 데이터베이스 스키마 등이다.
“누구“는 역할 이름을 지정한다. 현재 사용자를 나타내는 CURRENT_USER와 현재 세션 사용자를 나타내는 SESSION_USER도 지정할 수 있다. 또한 PUBLIC으로 지정한 경우에는 모든 역할(현재 그리고 향후 추가되는 역할의 전부)에 대한 권한이 추가된다.
권한을 다른 역할에 추가는 대상의 객체의 소유자 및 수퍼 유저가 할수 있다. 또한 GRANT 명령을 실행할 때에는 WITH GRANT OPTION을 지정하면 권한이 부여된 역할은 동일한 권한을 다른 역할에 추가할 수 있게 한다. (단, PUBLIC을 지정한 경우는 넣을 수 없다).
그러면 보다 구체적으로 살펴 보도록 하겠다.
테이블에 대한 권한 추가
먼저 테이블에 대한 권한을 추가하는 방법에 대해서 설명하겠다.
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
예를 들어, role_name 역할에 테이블 table_name을 대상으로 SELECT와 INSERT 권한을 추가하려면 다음과 같이 실행한다.
GRANT SELECT, INSERT ON table_name To role_name;
또한, role_name 역할에 테이블 table_name을 대상으로 모든 권한을 추가하려면 다음과 같이 실행한다. (모든 권한이라는 것은 이 형식으로 지정할 수 있는 SELECT, INSERT, UPDATE, DELETE TRUNCATE, REFERENCES, TRIGGER이다).
GRANT ALL PRIVILEGES ON table_name To role_name;
role_name 역할에 지정된 스키마 schema_name에 포함된 모든 테이블을 대상으로 SELECT 권한을 추가하려면 다음과 같이 실행한다.
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name To role_name;
role_name 역할에 테이블 table_name을 대상으로 SELECT와 INSERT 권한을 추가하고 또한 다른 사용자에게 동일한 권한을 부여할 수 있도록하려면 다음과 같이 실행한다.
GRANT SELECT, INSERT ON table_name To role_name WITH GRANT OPTION;
–
그러면 실제로 해보도록 하자. 현재 devkuma 데이터베이스에는 public 스키마에 memo 테이블과 myschema 스키마 book 테이블이 작성되어 있다. 테이블과 컬럼에 대한 권한 정보를 얻으려면 psql 메타 명령 \dp를 사용한다. (\dp는 테이블, 뷰, 시퀀스 목록을 액세스 권한과 함께 표시하는 명령이다.)
devkuma=# \dp
액세스 권한
스키마 | 이름 | 종류 | 액세스 권한 | 칼럼 접근권한 | 정책
--------+------+--------+-------------+---------------+------
public | memo | 테이블 | | |
(1개 행)
devkuma=# \dp myschema.*
액세스 권한
스키마 | 이름 | 종류 | 액세스 권한 | 칼럼 접근권한 | 정책
----------+------+--------+-------------+---------------+------
myschema | book | 테이블 | | |
(1개 행)
devkuma=#
여기서 수퍼 유저가 아닌 mykuma 역할에 SELECT 권한을 추가 할 수 있다. 권한을 추가하기 전에 데이터를 취득하면 어떻게되는지를 확인하기 위해 mykuma에서 PostgreSQL에 접속 후 memo 테이블에서 데이터를 조회해 본다.
devkuma=> select * from memo;
오류: memo 테이블에 대한 접근 권한 없음
devkuma=>
memo 테이블에 대한 SELECT 권한이 없기에 “오류: memo 테이블에 대한 접근 권한 없음“라고 표시되어 데이터를 가져 오는데 실패한다.
그러면 mykuma 역할에 memo 테이블 및 myshema.book 테이블에 SELECT 권한을 추가한다. 수퍼 유저로 접속하여 다음과 같이 실행한다.
devkuma=# grant select on memo to mykuma;
GRANT
devkuma=# grant select on myschema.book to mykuma;
GRANT
devkuma=#
권한이 추가되었다. 그러면 확인을 위해 다시 \dp를 실행해 보자.
devkuma=# \dp
액세스 권한
스키마 | 이름 | 종류 | 액세스 권한 | 칼럼 접근권한 | 정책
--------+------+--------+---------------------------+---------------+------
public | memo | 테이블 | postgres=arwdDxt/postgres+| |
| | | mykuma=r/postgres | |
(1개 행)
devkuma=# \dp myschema.*
액세스 권한
스키마 | 이름 | 종류 | 액세스 권한 | 칼럼 접근권한 | 정책
----------+------+--------+---------------------------+---------------+------
myschema | book | 테이블 | postgres=arwdDxt/postgres+| |
| | | mykuma=r/postgres | |
(1개 행)
devkuma=#
각 테이블의 액세스 권한 곳에 다음과 같은 값이 설정되어 있다.
postgres=arwdDxt/postgres+
mykuma=r/postgres
값은 다음과 같다. mykuma 역할에 대해서는 postgres 역할이 r(SELECT)의 권한이 추가되어 있다는 의미이다.
rolename=xxxx -- 역할에 부여된 권한
=xxxx -- PUBLIC에 부여 된 권한
r -- SELECT (읽기 (read))
w -- UPDATE (쓰기 (write))
a -- INSERT (추가 (append))
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- 모든 권한 (테이블용. 다른 객체는 다르다.)
* -- 직전의 권한에 관한 그랜트 옵션
/yyyy -- 이 권한을 부여한 역할
그럼 다시 일반 역할인 mykuma에서 PostgreSQL에 접속 후 memo 테이블에서 데이터를 조회해 본다.
devkuma=> select * from memo;
id | memo
----+------
(0개 행)
devkuma=>
memo 테이블에서 데이터를 검색할 수 있게 되었다.
마찬가지로 mysheme.book 테이블에 데이터를 조회해 본다.
devkuma=> select * from myschema.book;
오류: myschema 스키마(schema) 접근 권한 없음
줄 1: select * from myschema.book;
^
devkuma=>
이번에는”오류: myschema 스키마(schema) 접근 권한 없음“라고 표시되어 데이터를 조회할 수 없다. 이것은 public 스키마이면 기본적으로 스키마의 테이블 등의 객체에 대한 액세스가 허용되지만만, public 이외의 스키마의 경우 명시적으로 USAGE 권한을 추가하지 않으면 스키마의 객체에 액세스 할 수 없다.
스키마 객체에 대한 권한을 추가하는 방법에 대한 자세한 내용은 나중에 설명하기로 하고, 우선 수퍼 유저로 접속한 뒤 다음과 같이 실행한다.
devkuma=# grant usage on schema myschema to mykuma;
GRANT
devkuma=#
myshema 스키마의 개체에 대한 액세스가 허용되었다.
그럼 다시 mysheme.book 테이블에서 데이터를 조회해 보겠다.
devkuma=> select * from myschema.book;
id
----
(0개 행)
devkuma=>
myschema.book 테이블에서 데이터를 조회할 수 있게 되었다.
테이블의 컬럼에 대한 권한 추가
다음 테이블의 컬럼에 대한 권한을 추가하는 방법에 대해서 설명하겠다.
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
예를 들어 role_name 역할에 테이블 table_name의 column_name 컬럼을 대상으로 SELECT 권한을 추가하려면 다음과 같이 실행한다.
GRANT SELECT (column_name) ON table_name To role_name;
그러면 실제로 해보도록 하자. psql 메타 명령 \dp를 사용하여 테이블과 컬럼에 대한 권한을 확인 해 보면, mykuma 아무것도 권한이 추가되지 않은 상태이다.
devkuma=# \dp
액세스 권한
스키마 | 이름 | 종류 | 액세스 권한 | 칼럼 접근권한 | 정책
--------+------+--------+---------------------------+---------------+------
public | memo | 테이블 | postgres=arwdDxt/postgres | |
(1개 행)
devkuma=#
그러면 mykuma 역할에 memo 테이블의 memo 컬럼에 대한 SELECT 권한을 추가한다. 수퍼 유저로 접속하여 다음과 같이 실행한다.
devkuma=# grant select (memo) on memo to mykuma;
GRANT
devkuma=#
권한이 추가되었다. 그러면 확인을 위해 재차 \dp를 실행해 본다.
devkuma=# \dp
액세스 권한
스키마 | 이름 | 종류 | 액세스 권한 | 칼럼 접근권한 | 정책
--------+------+--------+---------------------------+---------------------+------
public | memo | 테이블 | postgres=arwdDxt/postgres | memo: +|
| | | | mykuma=r/postgres |
(1개 행)
devkuma=#
열 권한 곳에 다음과 같은 값이 설정되어 있다.
memo: +
mykuma=r/postgres
설정되어 있는 값은 접근 권한의 경우와 동일한다. mykuma 역할에 대해 postgres 역할에 의해 r(SELECT)의 권한이 추가되었다는 의미이다.
그럼 다시 일반 역할인 momo에서 PostgreSQL에 접속하여 memo 테이블에서 데이터를 조회한다.
devkuma=> select * from memo;
오류: memo 테이블에 대한 접근 권한 없음
devkuma=>
“오류: memo 테이블에 대한 접근 권한 없음“라고 표시되면서 데이터 조회에 실패한다. 이는 memo 테이블에는 id 열과 memo 컬럼의 두 컬럼이 있고 mykuma 역할은 name 컬럼의 SELECT 권한 한 추가되지 않기 때문이다.
그러면 memo 테이블의 memo 컬럼 만 데이터를 조회해 본다.
devkuma=> select memo from memo;
memo
------
(0개 행)
devkuma=>
이번에는 데이터의 조회에 성공하였다.
또한 public 스키마 이외의 스키마에 작성되는 테이블의 컬럼에서 데이터를 검색하거나하려면 대상 스키마의 USAGE 권한이 필요하다.
스키마를 만들 수 있는 권한 추가하기
다음 지정된 데이터베이스에 스키마를 만들 수있는 권한을 추가하는 방법에 대해서 설명하겠다.
GRANT {{CREATE | CONNECT | TEMPORARY | TEMP} [...] | ALL [PRIVILEGES]}
ON DATABASE database_name [...]
TO role_specification [...] [WITH GRANT OPTION]
예를 들어 role_name 역할에 데이터베이스 database_name에서 스키마를 만들 수있는 권한을 추가하려면 다음과 같이 실행한다.
GRANT CREATE ON DATABASE database_name To role_name;
그러면 실제로 해보도록 하자. psql 메타 명령 \l을 사용하여 devkuma 데이터베이스에 대한 권한을 확인해 보면, memo는 devkuma 데이터베이스에 대해 아무런 권한이 없다.
devkuma=# \l devkuma
데이터베이스 목록
이름 | 소유주 | 인코딩 | Collate | Ctype | 액세스 권한
---------+----------+--------+------------------+------------------+-------------
devkuma | postgres | UTF8 | Korean_Korea.949 | Korean_Korea.949 |
(1개 행)
devkuma=#
그러면 momo 역할에 devkuma 데이터베이스에 스키마를 만들 수 있는 권한을 추가한다. 수퍼 유저로 접속하여 다음과 같이 실행한다.
devkuma=# grant create on database devkuma to mykuma;
GRANT
devkuma=#
권한이 추가되었다. 그러면 확인을 위해 다시 \l을 실행해 본다.
devkuma=# \l devkuma
데이터베이스 목록
이름 | 소유주 | 인코딩 | Collate | Ctype | 액세스 권한
---------+----------+--------+------------------+------------------+-----------------------
devkuma | postgres | UTF8 | Korean_Korea.949 | Korean_Korea.949 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | mykuma=C/postgres
(1개 행)
devkuma=#
액세스 권한 있는 곳에 다음과 같은 값이 설정되어 있다.
=Tc/postgres +
postgres=CTc/postgres+
mykuma=C/postgres
설정되어 있는 값은 접근 권한의 경우와 동일한다. mykuma 역할에 대해서는 postgres 역할에 의해 C(CREATE)의 권한이 추가되었다라는 의미이다.
그러면 일반 역할인 mykuma에서 PostgreSQL에 접속 후 devkuma 데이터베이스에 mykuma 스키마를 만들려고 한다.
devkuma=> create schema mykuma;
CREATE SCHEMA
devkuma=>
mydb 데이터베이스에 devkuma 스키마를 만들 수 있게 되었다. 확인을 위해 psql 메타 명령 \dn을 실행해 본다.
``
devkuma=> \dn
스키마(schema) 목록
이름 | 소유주
———-+———-
mykuma | mykuma
myschema | postgres
public | postgres
(3개 행)
devkuma=> ``
mykuma 스키마가 생성되어있는 것을 확인할 수 있다.
스키마에 테이블 같은 객체를 만들 수 있는 권한 추가하기
마지막으로 지정된 스키마에 테이블 같은 개체를 만들 수 있는 권한을 추가하는 방법에 대해서 설명하겠다.
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
예를 들어 role_name 역할에 schema_name 스키마에서 테이블 등을 만들 수있는 권한을 추가하려면 다음과 같이 실행한다.
GRANT CREATE ON SCHEMA schema_name To role_name;
또한 role_name 역할에 schema_name 스키마에서 테이블 등에 대한 액세스 권한을 추가하려면 다음과 같이 실행한다.
GRANT USAGE ON SCHEMA schema_name To role_name;
그러면 실제로 해보록 하자. psql 메타 명령 \dn+를 사용하여 myschema 스키마에 대한 권한을 확인 해 보면, momo는 myschema 스키마에 대한 권한이 아무것도 없습니다.
devkuma=# \dn+
스키마(schema) 목록
이름 | 소유주 | 액세스 권한 | 설명
----------+----------+----------------------+------------------------
mykuma | mykuma | |
myschema | postgres | postgres=UC/postgres+|
| | mykuma=UC/postgres |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(3개 행)
devkuma=#
그러면 mykuma 역할에 myschema 스키마에 개체를 만들 수 있는 권한을 추가한다. 수퍼 유저로 접속하여 다음과 같이 실행한다.
devkuma=# grant create on schema myschema to mykuma;
GRANT
devkuma=#
권한이 추가되었다. 그러면 확인을 위해 다시 \dn+를 실행해 본다.
devkuma=# \dn+
스키마(schema) 목록
이름 | 소유주 | 액세스 권한 | 설명
----------+----------+----------------------+------------------------
mykuma | mykuma | |
myschema | postgres | postgres=UC/postgres+|
| | mykuma=UC/postgres |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(3개 행)
devkuma=#
액세스 권한 곳에 다음과 같은 값이 설정되어 있습니다.
postgres=UC/postgres+
mykuma=C/postgres
설정되어 있는 값은 접근 권한의 경우와 동일한다. mykuma 역할에 대해서는 postgres 역할에 의해 C(CREATE)의 권한이 추가되었다는 의미이다.
그러면 일반 역할인 mykuma으로 PostgreSQL에 접속하여, devkuma 데이터베이스의 myschema 스키마에 product 테이블을 만들려고 한다.
devkuma=> create table myschema.product (id integer, name varchar (10));
CREATE TABLE
devkuma=>
myschema 스키마에 product 테이블을 만들었다. 확인을 위해 psql 메타 명령 \dp를 실행해 본다.
devkuma=> \dp myschema.*
액세스 권한
스키마 | 이름 | 종류 | 액세스 권한 | 칼럼 접근권한 | 정책
----------+---------+--------+---------------------------+---------------+------
myschema | book | 테이블 | postgres=arwdDxt/postgres+| |
| | | mykuma=r/postgres | |
myschema | product | 테이블 | | |
(2개 행)
devkuma=>
myschema.product 테이블이 생성되어 있는 것을 확인할 수 있다.
–
여기까지 GRANT 명령을 사용하여 권한을 추가하는 방법에 대해 설명하였다.