DB/PostgreSQL

PostgreSQL 계정 생성, Role 추가하기(psql 이용) - 2

심플블루 2020. 6. 6. 00:28
반응형

# PostgreSQL 데이터베이스 Role

  • PostgreSQL은 Role 이라는 개념을 사용하여 데이터베이스 엑세스 권한을 관리함

  • Role 의 개념은 "사용자" 및 "그룹" 개념을 포함함

  • 오라클처럼 user와 role이 분리된 개념이 아니라, user=role 느낌으로 사용됨

 

# 먼저 기존 계정들의 Role을 조회해보자

  • psql에 수퍼유저(postgres)로 접속
1
2
3
4
5
6
postgres=# \du
                                롤 목록
 롤 이름  |                      속성                      | 소속 그룹:
----------+------------------------------------------------+------------
 postgres | 슈퍼유저, 롤 만들기, DB 만들기, 복제, RLS 통과   | {}
 red      | DB 만들기, 복제                                | {}
 cs
  • \du 입력
  • 관리자계정(postgres)으로 접속하여야 권한 생성과 Role 추가가 가능하므로 postgres로 접속한다.
  • 현재 postgres, red 계정이 있다.


1
2
3
4
5
postgres=# SELECT * FROM pg_shadow;
 
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd                | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | md53175bce1d3201d16594cebf9d7eb3f9d |          |
 red      |    97387 | t           | f        | t       | f            | md542c3a4a0281e61ee592e36a3eda020f2 |          |
 cs
  • SELECT * FROM pg_shadow; 
  • \du 명령어 외에 쿼리문을 입력하여 pg_shadow 테이블에서 계정정보를 읽어올 수 있다.

# CREATE USER 문에 옵션을 추가해 계정 생성, Role 추가를 동시에 하자

  • CREATE USER 계정이름 WITH  Role_1 Role_2 Role_3 ; 
    형태로 지정 계정 생성과 계정에 Role 추가를 동시에 할 수 있다. 

 

 

1
 
postgres=# CREATE USER yellow WITH CREATEDB REPLICATION PASSWORD 'yellow';
 
 cs

 

 

 

 

 

 

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE USER name [ [ WITH ] option [ ... ] ]
 
where option can be:
 
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
 cs

 

 

 

 

 

 

  • 기본 형태는 CREATE USER name [ [ WITH ] option [ ... ] ] 이다. 계정을 생성할 때 해당 계정에 옵션을 추가할 때 WITH 문을 사용하여 '옵션항목'을 추가해 준다.



# 생성한 yellow 계정의 Role 확인하기

1
2
3
4
5
6
7
postgres=# \du
                                롤 목록
 롤 이름  |                      속성                      | 소속 그룹:
----------+------------------------------------------------+------------
 postgres | 슈퍼유저, 롤 만들기, DB 만들기, 복제, RLS 통과   | {}
 red      | DB 만들기, 복제                                | {}
 yellow   | DB 만들기, 복제                                | {}
 cs
  • \du 입력
  • 새로 생성한 yellow 계정에 DB 만들기(CREATEDB), 복제(REPLICATION) Role 이 추가되어 생성된 것을 확인 할 수 있다.
1
2
3
4
5
6
postgres=# SELECT * FROM pg_shadow;
 
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd                | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | md53175bce1d3201d16594cebf9d7eb3f9d |          |
 red      |    97387 | t           | f        | t       | f            | md542c3a4a0281e61ee592e36a3eda020f2 |          |
 yellow   |    97389 | t           | f        | t       | f            | md5d3499d1fde70bb924b0201cc8faa598d |          |
 cs
  • SELECT * FROM pg_shadow;
  • 해당 쿼리문으로 조회를 하면 더 자세한 정보를 알 수 있다.



# CREATE USER 문에 사용가능한 옵션에 대해 알아보자

 

    • 옵션의 종류는 16가지이다.

    • 옵션을 여러개 쓸 경우, 옵션 사이에 콤마(,)를 넣지 않고 공백만을 둔다.
      ex)  WITH CREATEDB CREATEROLE PASSWORD 'yellow'; 

    • 옵션을 지정하지 않았을 경우 의미가 각각 다르므로 확인하도록 하자.

    • 옵션의 종류와 의미는 다음과 같다.

   옵션명 설명 
수퍼
유저		  SUPERUSER 
  • 새 계정이 '수퍼 유저' 인지 판별하여 데이터베이스에 제한없이 접근할 수 있는지 정한다.
  • 수퍼 유저상태는 위험하므로 실제로 필요한 경우에만 사용해야 한다.
  • 새 수퍼 유저를 만들려면 자신이 수퍼 유저여야 한다.
 NOSUPERUSER
  • 계정을 생성할 때 지정하지 않으면 수퍼 유저가 아니다
    (즉 NOSUPERUSER 와 마찬가지 의미이다.)
데이터
 베이스 
생성		  CREATEDB 
  •  CREATEDB를 지정하면 해당 계정은 새 데이터베이스를 만들 수 있다.
 NOCREATEDB
  • 지정하지 않으면 데이터베이스 작성 기능이 거부되고, 그게 기본 값이다.
    (마찬가지로 NOCREATEDB 라고 지정해도 같다.)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
postgres=> \c
접속정보: 데이터베이스="postgres", 사용자="test2".
postgres=> \du
                                롤 목록
 롤 이름  |                      속성                       | 소속 그룹:
----------+------------------------------------------------+------------
 postgres | 슈퍼유저, 롤 만들기, DB 만들기, 복제, RLS 통과    | {}
 red      | DB 만들기, 복제                                 | {}
 test     |                                                | {}
 test2    |                                                | {}
 
 
postgres=> CREATE DATABASE study;
오류:  데이터베이스를 만들 권한이 없음
 
Role
생성		  CREATEROLE 
  •  CREATEROLE을 지정하면 해당 계정은 새로운 Role을 만들 수 있다.
    (즉, CREATE ROLE 실행가능)
  • 또한 이 Role이 있는 계정은 다른 Role을 변경하거나 삭제할 수 있다.
 NOCREATEROLE
  • 지정하지 않으면 NOCREATEROLE 이 기본값이다.
권한
상속
여부		  INHERIT 
  • 데이터베이스의 권한을 다른 구성원들에게 상속할 수 있는지 여부를 결정한다.
  • 지정하지 않으면 INHERIT 가 기본값이다.
 NOINHERIT
  •  INHERIT 이 기본값이므로 이 권한을 적용하지 않으려면 명시적으로 써줘야 한다.
 로그인
가능		  LOGIN 
  • 해당 계정의 로그인 허용 여부를 결정한다.
    즉, 클라이언트 연결 중에 이 계정이 처음 세션에 접속할 수 있는 권한을 부여할지를 결정한다.
  • LOGIN 속성이 있는 계정은 이 데이터베이스의 사용자라고 생각할 수 있다.
    (처음 세션에 접속할 수 있는 권한을 부여하고 싶다면 CREATE USER 명령어를 사용하여 계정을 만들면 이 옵션을 지정하지 않아도 세션에 접속할 수 있다.
    즉, CREATE USER 명령어는 CREATE ROLE 에 login 권한이 추가된 것이다.) 
 NOLOGIN
  • CREATE USER 명령어를 사용하여 계정을 생성하는 경우를 제외하고 지정하지 않으면 NOLOGIN 가 기본값이다.
복제   REPLICATION
  • 해당 계정이 복제 권한을 가질지 여부를 결정한다. 
  • 복제모드(물리적 또는 논리적)에서 서버에 연결하고 복제 슬롯을 만들거나 삭제하려면 이 역할이 그러한 권한이 있거나 혹은 수퍼유저여야 합니다. 
  • 이 속성이 이는 계정은 매우 권한이 높은 역할이며 따라서 실제로 복제에 사용되는 역할에만 사용해야 한다. 
 NOREPLICATION
  • 지정하지 않으면 NOREPLICATION 이 기본값이다.
 RLS
적용		  BYPASSRLS
  • 해당 계정의 RLS 로우 단위 보안 정책(Row-Level Security)을 무시하는지 여부를 결정한다.
 NOBYPASSRLS
  • 지정하지 않으면 NOBYPASSRLS 이 기본값이다.
 동시 
접속자 수 
제한		  CONNECTION   LIMIT connlimit
  • 해당 계정으로 접속할 수 있는 최대 동시 접속 수를 지정한다.
  • 기본 값은 -1(제한 없음)이다.
    (단, 미리 준비된 트랜젝션, 백그라운드 작업자 프로세스는 이 제한에서 제외된다.)
패스워드
사용여부		  [ENCRYPTED] 
PASSWORD password
  • 계정의 비밀번호를 설정한다. 
  • 따라서 로그인 옵션을 가지지 않는 계정은 이 옵션이 의미가 없다.
  • 비밀번호 인증을 사용하는데,  그 값이 null(공백)이면, 항상 인증 실패로 간주한다.
  • 명시적으로 글자 'null' 로 지정할 때는 PASSWORD NULL 구문을 사용한다.
    (Ver.10 이상만 가능)
  • 비밀번호는 항상 시스템 카탈로그에 암호화해서 저장되며, ENCRYPTED 옵션은 하위 호환성 때문에 지원하고 있다.
  • 암호화 방법은 password_encryption 환경 설정 매개 변수 값으로 지정할 수 있으며, 여기서 지정한 비밀번호 값이 이미 MD5-암호화, SCRAM-암호화 형식으로 암호화된 문자열이라면 그대로 시스템 카탈로그에 저장된다.
패스워드
유효기간		  VALID UNTIL timestamp
  • 이 옵션을 생략하면 암호는 항상 유효함
  • 이 암호의 유효 날짜와 시간을 설정할 수 있음 




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




miriam 계정은 jw8s0F4라는 비밀번호를 2005년 01월 01일까지 사용할 수 있고 
그 기간이 지나면 이 비밀번호는 사용할 수 없음


ROLE		  IN ROLE role_name
  • 새로 만들어지는 롤이 여기서 지정한 롤의 구성원이 된다.
  • 추가될 롤을 하나이상 나열하여 지정할 수 있다.


  • 이 작업으로 하는 만드는 계정이 롤을 수정, 변경할 권리가 없으면 이 작업을 수행할 수 없다.
    따라서 그런 권한이 있는 계정이나 수퍼유저로 GRANT 명령을 이용해야 한다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
postgres=# CREATE USER user01 WITH CREATEDB PASSWORD 'user01';
CREATE ROLE
postgres=# CREATE USER user02 WITH PASSWORD 'user02';
CREATE ROLE
postgres=# \du
                                롤 목록
 롤 이름  |                      속성                       | 소속 그룹:
----------+------------------------------------------------+------------
 postgres | 슈퍼유저, 롤 만들기, DB 만들기, 복제, RLS 통과    | {}
 red      | DB 만들기, 복제                                 | {}
 user01   | DB 만들기                                       | {}
 user02   |                                                | {}
 
 
 cs
계정 user01은 DB를 만들 수 있는 역할을 가지고 있고, 
계정 user02는 아무 역할을 가지고 있지 않다.
 
 






1
2
3
4
5
6
7
8
9
10
postgres=# GRANT user01 to user02;
GRANT ROLE
postgres=# \du
                                롤 목록
 롤 이름  |                      속성                      | 소속 그룹:
----------+------------------------------------------------+------------
 postgres | 슈퍼유저, 롤 만들기, DB 만들기, 복제, RLS 통과   | {}
 red      | DB 만들기, 복제                                | {}
 user01   | DB 만들기                                      | {}
 user02   |                                               | {user01}
 cs




GRANT 문을 이용해 계정 user01 의 권한을 계정 user02에게 부여했다.
그럴 경우 계정user02는 user01의 ROLE그룹 멤버가 된다.


(그룹 ROLE이 존재하는 경우 GRANT 및 REVOKE 명령을 사용하여 
멤버를 추가 및 삭제할 수 있다.)
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Database [postgres]:
Port [5432]:
Username [postgres]: user02
user02 사용자의 암호:
psql (10.13)
도움말을 보려면 "help"를 입력하십시오.
 
postgres=> \c
접속정보: 데이터베이스="postgres", 사용자="user02".
postgres=> \du
                                롤 목록
 롤 이름  |                      속성                      | 소속 그룹:
----------+------------------------------------------------+------------
 postgres | 슈퍼유저, 롤 만들기, DB 만들기, 복제, RLS 통과   | {}
 red      | DB 만들기, 복제                                | {}
 user01   | DB 만들기                                      | {}
 user02   |                                               | {user01}
 yellow   | DB 만들기, 복제                                | {}
 
 
postgres=> CREATE DATABASE t1;
오류:  데이터베이스를 만들 권한이 없음
postgres=> SET ROLE user01;
SET
postgres=> CREATE DATABASE t1;
CREATE DATABASE
 
 cs
그룹 ROLE의 멤버는 명시적으로 SET ROLE 구문을 실행하여 ROLE의 권한을 사용할 수 있다.
일부러 기존 세션창을 닫고 새로운 창에서 계정 user02로 접속하였다.
현재 계정 user02로는 새로운 데이터베이스를 만들 수 없다.
부여받은 user01의 권한을 사용하기 위해 SET ROLE user01; 을 실행하였다.
그 뒤에 데이터베이스를 만드는 데 성공하였다.
 IN GROUP role_name
  • IN ROLE과 같음, 호환을 위해 예전 옵션을 남겨둠
 ROLE
 role_name
  • 새 계정을 기존에 설정해 놓은 롤을 부여할 수 있음
 ADMIN role_name
  • ROLE 옵션과 같으며, 다른 점은 이 옵션이 지정된 롤에 WITH ADMIN OPTION 옵션이 추가 된 것이다.
  • WITH ADMIN OPTION 권한이 적용된 롤은 다른 계정을 그룹에 추가할 권한을  가진다.
 USER 
role_name
  • ROLE 옵션과 같은 것으로, 옛날 구문이다.
 UID  SYSID uid
  • 이 옵션은 무시된다. 하위 버전 호환성 때문에 남겨둠

 

# CREATE USER   VS.  CREATE ROLE

  • 두 구문의 차이점은 무엇일까?
1
2
3
postgres=> CREATE USER user01 WITH PASSWORD 'user01';
 
postgres=> CREATE ROLE user01 WITH LOGIN PASSWORD 'user01';
 cs

위의 두 구문은 같은 의미이다. 

즉,  CREATE USER 는 CREATE ROLE 구문에 로그인(LOGIN) 역할이 추가 된 것이다. 

 

반응형
저작자표시 비영리 변경금지

'DB > PostgreSQL' 카테고리의 다른 글

shp2pgsql로 PostgreSQL에 shp 파일 import 하기  (10) 2020.06.16
윈도우서버에서 PostgreSQL 외부접속 가능하게 설정하는 방법  (0) 2020.06.14
PostgreSQL 계정 생성, Role 추가하기(psql 이용) - 1  (0) 2020.06.04
PostgreSQL 처음 접속하기(shell 스크립트 사용 vs. pgAdmin4 사용)  (0) 2020.06.04
PostgreSQL(Ver.10.13) 설치하는 방법  (0) 2020.05.19

'DB/PostgreSQL'의 다른글

  • 현재글PostgreSQL 계정 생성, Role 추가하기(psql 이용) - 2

관련글

티스토리툴바