[PostgreSQL] ROLE-User의 생성 변경 삭제

PostgreSQL에서 사용자 계정에 대한 생성, 권한을 담당하는 ROLE에 대해 알아봅니다.

PosgreSQL에서 Role은 다른 의미로 사용자도 됩니다. 즉 로그인 유저이자 권한을 동시에 가집니다.

create user를 호출하면 내부적으로 create role을 호출합니다.

 

■ Role의 종류와 생성 방법

postgresql에서 제공되는 데이터베이스 권한들을 특정 이름으로 모아 만드는 것을 role이라고 합니다. 예를 들어 databse를 생성할 수 있는 권한, 접속할 수 있는 권한만을 이용하여 특정 이름을 붙여 만드는것을 role이라고 합니다. 그리고 이 role은 사용자 혹은 그룹으로 간주될 수 있습니다.

이 role의 명령어를 사용하기 위해서는 CREATEROL 권한이 있거나 데이터베이스 슈퍼 계정이여야 합니다.

Role은 데이터베이스 클러스터 수준에서 작성되므로 클러스터의 모든 데이터베이에 적용됩니다.

 

사용법 :

CREATE ROLE name [ [ WITH ] option [ ... ] ]

 

옵션

SUPERUSER | NOSUPERUSER :

최고 권한을 가지는 속성입니다. role을 생성하면 기본적으로는 NOSUPERUSER로 생성이 되며 SUPERUSER로 지정을 해줘야 최고 권한을 가지는 ROLE이 됩니다.

DBA나 기타 최고 권한을 가져야만 하는 ROLE에게 할당합니다.

 

CREATEDB | NOCREATEDB

Database를 생성할 수 있는 권한을 나타냅니다. 기본적으로는 NOCREATEDB이며 권한을 부여하려면 Database 생성 권한을 부여하려면 CREATEDB를 부여합니다.

 

CREATEROLE | NOCREATEROLE

ROLE 생성 권한을 나타냅니다. 기본은 NOCREATEROLE입니다. Role 생성 권한을 부여하려면 명시적으로 CREATEROLE을 부여합니다.

 

INHERIT | NOINHERIT

ROLE에 부여된 특정 권한에 대해 상속할지 여부를 결정합니다. INHERIT는 ROLE구성원 모두에게 부여됩니다. NOINHERIT는 아무것도 상속하지 않습니다. 옵션을 지정하지 않으면 상속(INHERIT)이 기본값입니다.상속을 사용하지 않으면 다른 역할의 구성원 자격은 해당 다른 역할에 대한 역할 설정 기능만 부여합니다.

 

LOGIN | NOLOGIN

PostgreSQL에 로그인 할 수 있는 권한을 권한을 나타냅니다. LOGIN을 표시한다는것은 사용자를 나태낼 수도 있습니다. 이 LOGIN이 없다면 보통 ROLE을 나타내며 사용자는 아닌것으로 해석될 수 있습니다. NOLOGIN이 기본값입니다. 참고로 CREATE USER 명령어를 호출하면 CREATE ROLE이 자동으로 호출됩니다.

 

REPLICATION | NO REPLICATION

복제를 할 수 있는 role을 부여합니다. 마스터 서버에서 로그를 읽어와서 슬레이브 서버에 해당 로그를 적용하는 역활을 합니다. DDL과 DML을 적용하는 만큼 꽤 많은 권한을 가지고 있는 Role이라 할 수 있습니다. Role을 지정하지 않으면 NO REPLICATION이 기본값이며 슈퍼 유저만이 이 role을 부여할 수 있습니다.

 

 

BYPASSRLS | NOBYPASSRLS

모든 row-level security(RLS) 정책을 우회하여 적용하지 않고 통과시킬지 여부를 결정합니다. 슈퍼유저만이 이 역활을 부여할 수 있습니다.

pg_dump는 테이블의 모든 내용이 덤프되도록 기본적으로 row_security를 ​​OFF로 설정합니다. pg_dump를 실행하는 사용자에게 적절한 권한이 없으면 오류가 반환됩니다. 그러나 수퍼 유저와 덤프되는 테이블의 소유자는 항상 RLS를 우회합니다.

 

 

CONNECTION LIMIT connlimit

Role이 로그인할 수 있는 경우 role이 만들 수있는 동시 연결 수를 지정합니다. -1 (기본값)은 제한이 없음을 의미합니다. 정상적인 연결 만이 접속제한에 포함됩니다. Prepared 트랜잭션이나 background worker connection(백그라운드 작업자 연결) 모두 이 제한에 포함되지 않습니다.

 

[ENCRYPTED] PASSWORD 'passowrd' | password NULL

Role의 암호를 설정합니다. (비밀번호는 LOGIN 특성이 있는 역할에만 사용되지만, 비밀번호가 없는 역할에 대해 비밀번호를 정의할 수 있습니다.) 비밀번호 인증을 사용하지 않으려면 이 옵션을 생략 할 수 있습니다. 암호를 지정하지 않으면 암호가 null로 설정되고 해당 사용자에 대한 암호 인증이 항상 실패합니다. null 암호는 선택적으로 PASSWORD NULL로 명시적으로 작성할 수 있습니다.

 

암호는 항상 시스템 카탈로그에 암호화되어 저장됩니다. ENCRYPTED 키워드는 효과가 없지만 이전 버전과의 호환성을 위해 허용됩니다. 암호화 방법은 구성 매개 변수 password_encryption에 의해 결정됩니다. 제시된 암호 문자열이 이미 MD5 암호화 또는 SCRAM 암호화 형식 인 경우 password_encryption에 관계없이 있는 그대로 저장됩니다 (시스템이 지정된 암호화 된 암호 문자열을 해독 할 수 없으므로 다른 형식으로 암호화). 이렇게하면 덤프 / 복원 중에 암호화된 암호를 다시 로드 할 수 있습니다.

 

VALID UNTIL 'timestamp'

VALID UNTIL 절은 role의 암호가 더 이상 유효하지 않은 날짜와 시간을 설정합니다. 이 절을 생략하면 암호는 항상 유효합니다.

 

IN ROLE role_name

IN ROLE 절은 새 role이 새 구성원으로 즉시 추가될 하나 이상의 기존 역할을 나열합니다. (관리자로 새 역할을 추가하는 옵션이 없습니다. 별도의 GRANT 명령을 사용하여 수행합니다..)

 

IN GROUP role_name

IN GROUP은 IN ROLE의 오래전 사용방법입니다.

 

ROLE role_name

ROLE 절은 새 role의 구성원으로 자동 추가되는 하나 이상의 기존 role을 나열합니다. (이것은 사실상 새 role을 "group"으로 만듭니다.)

 

ADMIN role_name

ADMIN 절은 ROLE과 비슷하지만 명명 된 역할이 새 역할 WITH ADMIN OPTION에 추가되어이 역할의 멤버 자격을 다른 사용자에게 부여 할 수있는 권한을 부여합니다.

 

USER role_name

USER 절은 ROLE 절의 오래전 사용방법입니다.

 

SYSID uid

SYSID 절은 무시되지만 이전 버전과의 호환성을 위해 허용됩니다.

 

사용 예제 :

로그인을 할 수 있는 Role을 만들지만 암호는 주지 않는 경우 :

CREATE ROLE jonathan LOGIN;

 

Role을 만들면서 암호를 제공하는 경우 :

CREATE USER는 로그인을 암시한다는 점을 제외하고는 CREATE ROLE과 동일합니다.

CREATE USER davide WITH PASSWORD 'jw8s0F4';

 

2004년 말까지 유효한 암호를 사용하여 역할을 생성합니다. 2005년이 시작되면 더이상 암호는 유효하지 않고 로그인이 거부됩니다.

CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';

 

데이터베이스를 만들고 role을 생성할 수 있는 role을 만듭니다.

CREATE ROLE admin WITH CREATEDB CREATEROLE;

 

 

 

 

■ Role의 변경

ALTER ROLE을 이용해서 기존 ROLE을 변경합니다. 명령문은 CREATE 대신 ALTER를 이용하여 변경을 합니다.

ALTER ROLE role_specification [ WITH ] option [ ... ]

 

사용 예제

Role의 암호를 변경하기 :

ALTER ROLE davide WITH PASSWORD 'hu8jmn3';

 

Role암호 제거 :

ALTER ROLE davide WITH PASSWORD NULL;

 

UTC보다 1시간 빠른 시간대를 사용하여 2015년 5월 4일 정오에 암호가 만료되도록 암호 만료 날짜를 변경.

ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';

 

암호를 유요한 암호로 계속 사용.

ALTER ROLE fred VALID UNTIL 'infinity';

 

역할에 다른 역할 및 새 데이터베이스를 만들 수 있는 기능을 부여.

ALTER ROLE miriam CREATEROLE CREATEDB;

 

maintenance_work_mem 매개 변수의 기본값이 아닌 설정을 역할에 지정.

ALTER ROLE worker_bee SET maintenance_work_mem = 100000;

 

역할에 client_min_messages 매개 변수의 데이터베이스별 기본 설정이 아닌 설정을 지정

ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;

 

■ ROLE의 삭제

DROP ROLE을 이용해서 기존 Role을 삭제합니다.

 

사용예제:

DROP ROLE jonathan;

 

■ ROLE 조회 방법

1. psql의 메타조회를 이용하는 방법

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 devadmin  | Create DB                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 rpladmin  | Create role, Create DB, Cannot login                       | {}
 spadmin   | Superuser, Create DB, Cannot login                         | {}

* role을 생성할 때 login을 붙이지 않으면 Cannot login이라는 role이 붙여진다.

 

2. PG Catalog를 조회하는 방법.

postgres=# select * from pg_roles;
          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid  
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
 pg_signal_backend         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4200
 spadmin                   | t        | t          | f             | t           | f           | f              |           -1 | ********    |               | f            |           | 16525
 pg_read_server_files      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4569
 postgres                  | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |    10
 rpladmin                  | f        | t          | t             | t           | f           | f              |           -1 | ********    |               | f            |           | 16527
 pg_write_server_files     | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4570
 pg_execute_server_program | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4571
 pg_read_all_stats         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3375
 devadmin                  | f        | t          | f             | t           | t           | f              |           -1 | ********    |               | f            |           | 16526
 pg_monitor                | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3373
 pg_read_all_settings      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3374
 pg_stat_scan_tables       | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3377
(12 rows)

* t는 true, f는 false를 나타냅니다.

* pg_roles 테이블과 비슷합니다.

 

■ ROLE의 멤버쉽 및 상속 관계

role을 만들때 상속여부를 지정하지 않으면 기본적으로 상속이 기본 옵션이라고 옵션 설명에 설명되어 있습니다.

role을 만들어보며 상관관계에 대해 알아보겠습니다.

참고로 권한 테스트는 다시 jhlee role로 다시 로그인하여 테스트를 하였습니다.

 

1. 상속 관계

postgres=# create role jhlee login;
postgres=# create role crtdb createdb;
postgres=# create role crtrole CREATEROLE;

 

postgres=# \du;
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 crtdb     | Create DB, Cannot login                                    | {}
 crtrole   | Create role, Cannot login                                  | {}
 jhlee     |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 

postgres=# grant crtrole to jhlee;
GRANT ROLE
postgres=# \du;
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 crtdb     | Create DB, Cannot login                                    | {}
 crtrole   | Create role, Cannot login                                  | {}
 jhlee     |                                                            | {crtrole}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 

postgres=# grant crtdb to crtrole;
GRANT ROLE
postgres=# \du;
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 crtdb     | Create DB, Cannot login                                    | {}
 crtrole   | Create role, Cannot login                                  | {crtdb}
 jhlee     |                                                            | {crtrole}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 

 

2. 상속 테스트

최종적으로 jhlee는 직접적으로 crtrole(createrole), 간접적으로 crtdb(createdb) 권한을 상속받을 수 있습니다.

해당 Role에 직간접으로 포함되어 있기 때문입니다.

간접권한인 database 생성을 시도해봅니다.

postgres=> create database testdb;
ERROR:  permission denied to create database

 

해당 Role이 직간접적으로 포함되어 있기 때문에 생성이 될거라 생각하지만(저같은 경우) 예상과 달리 위와 같이 권한에러가 발생하면서 사용할 수 없게 됩니다.

상속받은 role을 사용하려면 권한을 활성화시켜주어야 합니다.

set을 이용하여 권한을 활성화 시킵니다. 참고로 자신의 계정에서 실행해 주어야 하며 접속한 현재 세션에서만 유효합니다. 즉 일시적으로 부여됩니다.

해당 세션을 종료하게 되면 상속되었던 권한은 모두 없어지게 됩니다.

postgres=> set role crtrole;
SET
postgres=> create role testrole;
CREATE ROLE
postgres=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 crtdb     | Create DB, Cannot login                                    | {}
 crtrole   | Create role, Cannot login                                  | {crtdb}
 jhlee     |                                                            | {crtrole}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testrole  | Cannot login                                               | {}

 

 

간접권한인 Database 생성을 시도합니다.

postgres=> create database testdb;
ERROR:  permission denied to create database

 

에러가 발생하는것을 확인할 수 있다.

간접권한인 crtdb를 활성화해보고 다시 Database생성을 시도합니다.

postgres=> set role crtdb;
SET
postgres=> create database testdb;
CREATE DATABASE
postgres=> select oid, datname from pg_catalog.pg_database;
  oid  |  datname  
-------+-----------
 13593 | postgres
 16384 | sbtest
     1 | template1
 13592 | template0
 16550 | testdb       <-생성된 Database 확인.
(5 rows)

 

■ 상속권한 종료

다음과 같은 명령어로 일시적으로 상속받은 권한을 해제할 수 있습니다.

postgres=> set role jhlee;
postgres=> set role none;
postgres=> reset role;