ROLE
PostgreSQL의 Role은 Oracle의 Role + Oracle의 User의 역할이 같이 수행되는 Object라고 할 수 있습니다.
데이터베이스의 Object를 소유할 수 있으며, 오브젝트에 대한 권한을 다른 Role에 부여하거나 취소하여 제어할 수 있습니다.
PostgreSQL의 default Role은 postgres입니다.
최초 DB 설치 후 접속하여 정보 확인
psql -U postgres -d postgres -h localhost -p 15432
##1
##pg_shadow는 superuser 권한이 있는 role만 수행가능
SELECT * FROM pg_shadow;
usename |usesysid|usecreatedb|usesuper|userepl|usebypassrls|passwd |valuntil|useconfig|
--------+--------+-----------+--------+-------+------------+-------------------------------------------------------------------------------------------------------------------------------------+--------+---------+
postgres| 10|true |true |true |true |SCRAM-SHA-256$4096:5RIbY8T6ApW2XW5JD/Olkw==$JNn9o1q9gXj5GaPxGYUBloJQNKyUt3Zu3g9askj7+Gg=:gclgsblnSvG6JynN8JEonKQKdaaEaOOOvt5VXSgfGbg=| |NULL |
##2
postgres=# \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
**************************
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
##3
SELECT * FROM pg_user
usename |usesysid|usecreatedb|usesuper|userepl|usebypassrls|passwd |valuntil|useconfig|
--------+--------+-----------+--------+-------+------------+--------+--------+---------+
postgres| 10|true |true |true |true |********| |NULL |
##4
select rolname,rolsuper,rolcanlogin from pg_roles;
rolname |rolsuper|rolcanlogin|
-------------------------+--------+-----------+
pg_database_owner |false |false |
pg_read_all_data |false |false |
pg_write_all_data |false |false |
pg_monitor |false |false |
pg_read_all_settings |false |false |
pg_read_all_stats |false |false |
pg_stat_scan_tables |false |false |
pg_read_server_files |false |false |
pg_write_server_files |false |false |
pg_execute_server_program|false |false |
pg_signal_backend |false |false |
pg_checkpoint |false |false |
postgres |true |true |
postgreSQL은 미리 특정 권한을 가지고 있는 기본 Role을 제공합니다. (##4의 pg_roles에 postgres를 제외한 pg_로 시작하는 것들)
기본 Role 설명
pg_read_all_data - 모든 데이터(테이블, 뷰, 시퀀스)오브젝트에 대한 SELECT 할 수 있고, 모든 스키마에 대한 USAGE 권한을 얻습니다.
pg_write_all_data - 모든 데이터(테이블, 뷰, 시퀀스) 오브젝트에 대한 INSERT, UPDATE, DELETE 수행할 수 있고, 모든 스키마에 대한 USAGE 권한을 얻습니다.
pg_read_all_settings - 일반적으로 SUPERUSER 에게만 표시되는 환경 변수를 포함하여 모든 환성 변수를 읽을 수 있습니다.
pg_read_all_stats - 모든 pg_stat으로 시작하는 VIEW들를 조회할 수 있습니다.
일반적으로 SUPERUSER 에게만 표시되는 EXTENSION을 포함한 다양한 통계 관련 EXTENSION을 사용할 수 있습니다.
pg_stat_scan_tables - 긴 시간 동안 테이블에 ACCESS SHARE LOCK 수행하는 모니터링 기능을 실행할 수 있습니다.
pg_monitor - 다양한 모니터링 VIEWS 및 FUNCTIONS 을 읽고 실행합니다.
해당 Role 은 pg_read_all_settings 및 pg_read_all_stats, pg_stat_scan_tables 의 멤버입니다."
pg_database_owner - 암시적으로 현재 데이터베이스 소유자로 구성됩니다.
pg_signal_backend - 쿼리를 취소하거나 세션을 종료하도록 다른 백엔드에 신호를 보낼 수 있습니다.
pg_read_server_files - COPY 및 기타 파일 액세스 기능을 사용하여 데이터베이스가 서버에서 액세스할 수 있는 모든 위치에서 파일 읽기를 허용합니다.
pg_write_server_files- COPY 및 기타 파일 액세스 기능을 사용하여 데이터베이스가 서버에서 액세스할 수 있는 모든 위치의 파일에 쓰기를 허용합니다.
pg_execute_server_program - COPY 및 서버 측 프로그램 실행을 허용하는 기타 기능과 마찬가지로 사용자가 데이터베이스를 실행할 때 데이터베이스 서버에서 프로그램 실행을 허용합니다.
Role 생성
Role 생성을 위해서는 SUPERUSER 속성 또는 CREATEROLE 속성이 필요합니다.
속성을 생략 시 default 옵션으로 생성됩니다.
CREATE USER/ CREATE GROUP 생성문 자체는 모두 CREATE ROLE의 별칭입니다.
CREATE ROLE role_name [[WITH] option [...]]
Role 생성 옵션
SUPERUSER | NOSUPERUSER(default) - SUPERUSER 여부
CREATEDB | NOCREATEDB (default) - DATABASE 생성 가능 여부
CREATEROLE | NOCREATEROLE (default) - USER 생성 가능 여부
PASSWORD ‘password‘ Null(default) - USER 의 비밀번호 생성
LOGIN | NOLOGIN (default) -로그인 가능 여부
REPLICATION | NOREPLICATION (default) - 복제 서버 연결 가능 여부
BYPASSRLS | NOBYPASSRLS (default) - 행 수준 보안 정책 우회 여부
INHERIT (default) | NOINHERIT - ROLE 권한 상속 여부
Role 생성 예시
##1 접속 권한이 없는 role
create role nologin_role;
##2 접속권한이 있는 role
create role login_role with login password '######';
##3 User생성
create user loging_user password '######' CREATEROLE REPLICATION;
postgres=# \du
List of roles
Role name | Attributes | Member of
--------------+------------------------------------------------------------+-----------
login_role | | {}
loging_user | Create role, Replication | {}
nologin_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
select rolname,rolsuper,rolcanlogin from pg_roles;
rolname |rolsuper|rolcanlogin|
-------------------------+--------+-----------+
pg_database_owner |false |false |
pg_read_all_data |false |false |
pg_write_all_data |false |false |
pg_monitor |false |false |
pg_read_all_settings |false |false |
pg_read_all_stats |false |false |
pg_stat_scan_tables |false |false |
pg_read_server_files |false |false |
pg_write_server_files |false |false |
pg_execute_server_program|false |false |
pg_signal_backend |false |false |
pg_checkpoint |false |false |
postgres |true |true |
nologin_role |false |false |
login_role |false |true |
loging_user |false |true |
방금 생성한 3개의 role로 접속 시도해보기
nologin옵션으로 생성한 role 제외하고 나머지는 접속이 가능합니다.
##1
$ psql -U nologin_role -h localhost -p 15432
psql: error: connection to server at "localhost" (::1), port 15432 failed: FATAL: role "nologin_role" is not permitted to log in
##2
$ psql -U login_role -h localhost -p 15432
psql (15.5)
Type "help" for help.
postgres=> select current_user;
current_user
--------------
login_role
(1 row)
##3
$ psql -U loging_user -h localhost -p 15432
psql (15.5)
Type "help" for help.
postgres=> select current_user;
current_user
--------------
loging_user
(1 row)
Role 속성 변경
SUPERUSER는 모든 ROLE의 속성을 변경할 수 있습니다.
CREATEROLE 속성을 가진 ROLE은 SUPERUSER 및 REPLICATION 속성 이외의 ROLE의 속성을 변경할 수 있습니다.
ALTER ROLE role_name [[WITH] option[...]]
postgres=# alter role loging_user superuser;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
--------------+------------------------------------------------------------+-----------
login_role | | {}
loging_user | Superuser, Create role, Replication | {}
nologin_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# alter role loging_user nosuperuser;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
--------------+------------------------------------------------------------+-----------
login_role | | {}
loging_user | Create role, Replication | {}
nologin_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
REPLICATION ROLE을 가지고 있는 ROLE을 변경하려면 SUPERUSER권한을 가져야 합니다.
$ psql -U loging_user -h localhost -p 15432
psql (15.5)
Type "help" for help.
postgres=> select * from current_user;
current_user
--------------
loging_user
(1 row)
postgres=> alter role loging_user superuser;
ERROR: must be superuser to alter superuser roles or change superuser attribute
postgres=> alter role loging_user replication;
ERROR: must be superuser to alter replication roles or change replication attribute
postgres=> alter role loging_user noreplication;
ERROR: must be superuser to alter replication roles or change replication attribute
postgres=> alter role loging_user nologin;
ERROR: must be superuser to alter replication roles or change replication attribute
REPLICATION ROLE이 없는 Role의 login옵션은 변경할 수 있습니다.
postgres=# select current_user;
current_user
--------------
postgres
(1 row)
postgres=# alter role loging_user noreplication;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
--------------+------------------------------------------------------------+-----------
login_role | | {}
loging_user | Create role | {}
nologin_role | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
###replication role이 없는 유저의 login /nologin 변경은 가능.
postgres=> select * from current_user;
current_user
--------------
loging_user
(1 row)
postgres=> alter role loging_user nologin;
ALTER ROLE
Role 이름변경
자신의 이름은 변경할 수 없습니다.
SUPERUSER는 모든 ROLE의 이름을 변경할 수 있습니다.
CREATE ROLE 속성을 가진 ROLE은 SUPERUSER를 제외한 ROLE의 이름을 변경할 수 있습니다.
ALTER ROLE role_name RENAME TO new_name
postgres=# alter role loging_user rename to login_user;
ALTER ROLE
Role 삭제
SUPERUSER를 가진 Role은 SUPERUSER 권한이 있어야 삭제가 가능합니다.
CREATE ROLE 속성을 가진 ROLE은 SUPERUSER를 제외한 ROLE을 삭제할 수 있습니다.
DROP ROLE [IF EXISTS] role_name [...]
postgres=# drop role login_user;
DROP ROLE
권한(Privilege)
Object 생성문을 실행한 Role이 Owner가 됩니다.
권한이 없다면 Owner 또는 SUPERUSER만 Object를 사용할 수 있습니다.
Object에 접근할 수 있는 권한은 Owner에게 내제되어 있습니다.
Object의 Owner는 자신의 권한을 변경할 수 있습니다.
즉, 테이블을 자신을 포함하여 읽기 전용으로 만들 수 있습니다.
Owner는 언제든지 자신의 권한을 다시 부여할 수 있습니다.
테스트테이블 생성
postgres=# create role ysbae login;
CREATE ROLE
postgres=# create table t1 (i int);
CREATE TABLE
postgres-# \dt+ t1
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------+-------+----------+-------------+---------------+---------+-------------
public | t1 | table | postgres | permanent | heap | 0 bytes |
(1 row)
생성한 유저에게 소유권 부여
4번째 컬럼의 Owner가 ysbae로 변경되었습니다.
alter table t1 owner to ysbae;
postgres-# \dt+ t1
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------+-------+-------+-------------+---------------+---------+-------------
public | t1 | table | ysbae | permanent | heap | 0 bytes |
(1 row)
postgres=# select grantor,grantee,table_name,privilege_type from information_schema.table_privileges
postgres-# where table_name='t1';
grantor | grantee | table_name | privilege_type
---------+---------+------------+----------------
ysbae | ysbae | t1 | INSERT
ysbae | ysbae | t1 | SELECT
ysbae | ysbae | t1 | UPDATE
ysbae | ysbae | t1 | DELETE
ysbae | ysbae | t1 | TRUNCATE
ysbae | ysbae | t1 | REFERENCES
ysbae | ysbae | t1 | TRIGGER
(7 rows)
GRANT
GRANT명령으로 두가지 변형을 수행합니다.
1. 데이터 베이스 Object에 대한 GRANT
2.Role에 대한 GRANT
테스트 스키마 생성
postgres=# create schema test_schema;
CREATE SCHEMA
postgres=# \dn
List of schemas
Name | Owner
-------------+-------------------
public | pg_database_owner
test_schema | postgres
(2 rows)
public과 test_schema에 테이블 생성
postgres=# create table t2(i text);
CREATE TABLE
postgres=# create table test_schema.t2(i text);
CREATE TABLE
postgres=# \dp t2
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | t2 | table | | |
(1 row)
postgres=# \dp test_schema.t2;
**************************
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
-------------+------+-------+-------------------+-------------------+----------
test_schema | t2 | table | | |
(1 row)
ysbae유저의 테이블 조회시도
두개의 스키마에 있는 t2 모두 조회가 안됩니다.
postgres=> select current_user;
current_user
--------------
ysbae
(1 row)
postgres=> select * from t2;
ERROR: permission denied for table t2
postgres=> select * from test_schema.t2;
ERROR: permission denied for schema test_schema
LINE 1: select * from test_schema.t2;
SELECT 권한 부여
postgres=# select current_user;
current_user
--------------
postgres
(1 row)
postgres=# grant select on t2 to ysbae;
GRANT
postgres=# grant select on test_schema.t2 to ysbae;
GRANT
\dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
public | t1 | table | | |
public | t2 | table | postgres=arwdDxt/postgres+| |
| | | ysbae=r/postgres | |
(2 rows)
\dp test_schema.*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
-------------+------+-------+---------------------------+-------------------+----------
test_schema | t2 | table | postgres=arwdDxt/postgres+| |
| | | ysbae=r/postgres | |
(1 row)
조회
조회권한이 부여됐음에도 다른 스카미의 테이블은 여전히 조회되지 않습니다.
##ysbae 세션
postgres=# select current_user;
current_user
--------------
ysbae
(1 row)
postgres=> select * from t2;
i
---
(0 rows)
postgres=> select * from test_schema.t2;
ERROR: permission denied for schema test_schema
LINE 1: select * from test_schema.t2;
스키마에 대한 USAGE 권한 부여
usgae on schema 권한 부여 후 정상적으로 조회가 됩니다.
###postgres 세션
postgres=# select current_user;
current_user
--------------
postgres
(1 row)
postgres=# grant usage on schema test_schema to ysbae;
GRANT
##ysbae 세션
postgres=# select current_user;
current_user
--------------
ysbae
(1 row)
postgres=> select * from test_schema.t2;
i
---
(0 rows)
다른 Role에게 Role로 권한 부여
james를 생성하고 james에게 ysbae를 부여합니다.
유저처럼 쓰던 ysbae자체가 권한이 되어 부여해주는 부분이 Oracle과 다른 부분이네요.
postgres=# select current_user;
current_user
--------------
postgres
(1 row)
postgres=# create role james login;
CREATE ROLE
postgres=# grant ysbae to james;
GRANT ROLE
/du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
james | | {ysbae}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | | {}
ysbae | | {}
ysbae가 조회했던 t2가 모두 조회되고 있습니다.
$ psql -U james -d postgres -p 15432
psql (15.5)
Type "help" for help.
postgres=> select * from current_user;
current_user
--------------
james
(1 row)
postgres=> select * from t2;
i
---
(0 rows)
postgres=> select * from test_schema.t2;
i
---
(0 rows)
REVOKE
하나 이상의 Role에서 부여된 권한을 취소합니다.
public 스키마의 t2 조회권한을 회수합니다.
postgres=# revoke select on t2 from ysbae;
REVOKE
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
public | t2 | table | postgres=arwdDxt/postgres | |
(1 row)
권한을 받은 james 또한 같이 권한이 회수됩니다.
postgres=> select current_user;
current_user
--------------
ysbae
(1 row)
postgres=> select * from t2;
ERROR: permission denied for table t2
postgres=> select current_user;
current_user
--------------
james
(1 row)
postgres=> select * from t2;
ERROR: permission denied for table t2
james에게서 ysbae 자체를 회수합니다.
postgres=# revoke ysbae from james;
REVOKE ROLE
/du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
james | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user | | {}
ysbae | | {}
WITH GRANT OPTION을 사용하여 james에게 ysbae를 부여합니다.
grant option이 설정되어 있으면 *로 추가표시됩니다.
postgres=# grant select on t2 to ysbae with grant option;
GRANT
/dp t2
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
public | t2 | table | postgres=arwdDxt/postgres+| |
| | | ysbae=r*/postgres | |
(1 row)
with grant option이 있는 ysbae가 james에게 t2에 대한 select 권한을 부여했고, /dt 로 확인결과 ysbae로 부터 받았다는 표시가 나타납니다.
$ psql -U ysbae -d postgres -p 15432
psql (15.5)
Type "help" for help.
postgres=> select * from current_user;
current_user
--------------
ysbae
(1 row)
postgres=> grant select on t2 to james;
GRANT
postgres=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policie
s
--------+------+-------+---------------------------+-------------------+--------
--
public | t1 | table | | |
public | t2 | table | postgres=arwdDxt/postgres+| |
| | | ysbae=r*/postgres +| |
| | | james=r/ysbae | |
(2 rows)
두 role 다 조회가 잘 됩니다.
postgres=> select * from current_user;
current_user
--------------
ysbae
(1 row)
postgres=> select * from t2;
i
---
(0 rows)
$ psql -U james -d postgres -p 15432
psql (15.5)
Type "help" for help.
postgres=> select current_user;
current_user
--------------
james
(1 row)
postgres=> select * from t2;
i
---
(0 rows)
ysbae를 revoke 시 에러가 발생합니다.
postgres=# revoke select on t2 from ysbae;
ERROR: dependent privileges exist
HINT: Use CASCADE to revoke them too.
with grant option으로 다른 role에게 권한을 주었다면 의존성이 발생하여 cascade 옵션을 사용해서 회수해야 합니다.james의 권한도 같이 회수된 것을 확인할 수 있습니다.
postgres=# revoke select on t2 from ysbae cascade;
REVOKE
\dp t2
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
public | t2 | table | postgres=arwdDxt/postgres | |
(1 row)
출처 : tmax OpenSQl for PostgresSQL 교육자료
'스터디 > PostgreSQL' 카테고리의 다른 글
| Object - 테이블, 테이블파티셔닝, 인덱스 (0) | 2024.03.29 |
|---|---|
| Object - 데이터베이스,테이블스페이스,스키마 (2) | 2024.01.10 |
| PostgresSQL 아키텍쳐 (2) | 2023.11.30 |
댓글