PostgreSQL의 사용자 관리

오픈소스 데이터베이스인 PostgreSQL의 사용자를 관리하는 방법에 대해 정리한다.

 

PostgreSQL의 psql을 사용하는 방법은 아래 포스팅을 참고한다.

PostgreSQL의 psql 사용법

 

PostgreSQL 소개와 Docker를 사용하여 설정하는 방법은 아래 포스팅을 참고한다.

Docker로 PostgreSQL 사용하기

 

 

PostgreSQL 공식 Docker 이미지에 한글을 설정한 Docker 이미지 생성 방법은 아래 포스팅을 참고한다.

공식 PostgreSQL Docker 이미지에 한글 적용하기

 

GitHub과 DockerHub 사이트는 다음과 같다.

• DockerHub - https://hub.docker.com/_/postgres
• GitHub - https://github.com/docker-library/postgres

 

PostgreSQL은 다음과 같은 구조를 가지고 있다.

각 기능은 다음과 같다.

• Users/Groups - 사용자 정보 관리
• Databases - 실제 레코드가 저장되는 Database 관리
• Tablespaces - Database의 Object가 저장 된 파일시스템의 경로 관리
• Schemas - PostgreSQL 내부에서 Database를 논리적으로 구분하여 관리
• Tables - Row와 Column으로 구성 된 실제 레코드가 저장되는 공간
• Views - 읽기 전용의 가상 테이블로 제한 된 정보만 제공하기 위한 공간

 

 

사용자 관리

PostgreSQL을 사용할 계정을 관리한다.

 

앞서 다룬 PostgreSQL 구조의 Users/Groups 기능에 해당한다.

 

USER 조회

다음 명령으로 조회한다. 실행하는 계정에 SUPERUSER 권한이 있어야 한다.

# SQL
SELECT * FROM PG_SHADOW;

 

다음과 같이 출력된다. postgres 계정 하나만 존재하는 것을 확인할 수 있다.

postgres=# SELECT * FROM PG_SHADOW;
usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |                passwd                  | valuntil | useconfig
---------+----------+-------------+----------+---------+--------------+----------------------------------------+----------+-----------
postgres |      10  | t           | t        | t       | t            | SCRAM-SHA-256$40...중략.../ShlbveV/S8= |          |
(1 row)
postgres=#

 

사용자 권한 (Role)은 아래 명령을 사용한다.

# psql 명령
postgres=# \du

 

다음과 같이 출력된다.

권한 (Role) 이름과 권한 속성 (Attributes)으로 구분 되는 것을 확인할 수 있다.

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

 

각 권한별 설명은 다음과 같다.

ROLE	기능	비고
Superuser	USER 생성과 권한 부여 가능	관리자
Create role	새로운 ROLE 정의 가능	관리자
Create DB	DB 생성 권한 부여	관리자, 개발자
Replication	DB를 실시간으로 복사하는 가능	관리자, 개발자
Bypass RLS	모든 row-level security 우회 가능 (pg_dump 실행)	관리자

 

 

USER 생성

다음 명령으로 생성한다.

CREATE USER username [[ WITH ] option [ ... ]];

 

대표적인 option은 다음과 같다.

Option	Default	Description
SUPERUSER | NOSUPERUSER	NOSUPERUSER	SUPERUSER 권한 여부
CREATEDB | NOCREATEDB	NOCREATEDB	DATABASE 생성 가능 여부
CREATEUSER | NOCREATEUSER	NOCREATEUSER	사용자 생성 가능 여부
INHERIT | NOINHERIT	INHERIT	DATABASE의 권한 상속 가능 여부
PASSWORD 'password'’	null	USER의 비밀번호 생성
VALID UNTIL 'timestamp'		비밀번호 유효기간 (생략하면 제한없음)

 

다음과 같이 USER를 생성할 수 있다.

# CREATE USER 계정명 PASSWORD '암호' 권한; 형태
# 일반 사용자인 경우 권한 부분을 생략하면 Default 값으로 지정
postgres=# CREATE USER brown PASSWORD 'brown' SUPERUSER;
CREATE ROLE

postgres=# SELECT * FROM PG_SHADOW;
usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |                passwd                | valuntil | useconfig
---------+----------+-------------+----------+---------+--------------+--------------------------------------+----------+-----------
postgres |       10 | t           | t        | t       | t            | SCRAM-SHA-256$40...생략...hlbveV/S8= |          |
brown    |    16384 | f           | t        | f       | f            | SCRAM-SHA-256$40...생략...8vpSTisGE= |          |
(2 rows)

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

 

관리자 계정은 postgres 계정만 사용하거나, 추가 계정을 생성하고 postgres와 동일한 Role을 부여한다.

일반적인 개발자 계정은 PASSWORD와 SUPERUSER 옵션만 부여한다.

자동화 계정이나 특정 시스템에서 조회 목적으로 사용하는 계정은 PASSWORD 옵션만 부여한다.

 

 

USER 수정

다음 명령으로 수정한다.

ALTER USER name [ [ WITH ] option [ ... ] ];

 

권한과 Option은 USER 조회와 생성 항목을 참고한다.

 

다음과 같이 수정할 수 있다.

# 이름 변경
# ALTER USER 기존계정명 RENAME TO 변경계정명;
postgres=# ALTER USER test3 RENAME TO test4;

# 비밀번호 변경
# ALTER USER 계정명 WITH PASSWORD '신규비밀번호';
postgres=# ALTER USER test2 WITH PASSWORD 'testpass';

# 권한 변경 / 부여
# ALTER USER 계정명 WITH 권한(옵션) ... ;
postgres=# ALTER USER test1 WITH CREATEUSER REPLICATION;

 

USER 삭제

다음 명령으로 삭제한다.

DROP USER name;

 

다음과 같이 삭제할 수 있다.

# 계정 삭제
# DROP USER 계정명;
postgres=# DROP USER test4;