DevOps 지식 허브
DevOps 지식 허브

PostgreSQL에서 사용자, 역할 및 권한 관리를 위한 필수 명령어

강력한 PostgreSQL 보안 및 사용자 관리를 위한 필수 SQL 명령어를 마스터하세요. 이 가이드에서는 역할 생성, 수정 및 삭제, 복잡한 속성(LOGIN 및 CREATEDB 등) 설정, 그룹 멤버십 관리에 대한 실용적인 단계를 제공합니다. 강력한 `GRANT` 및 `REVOKE` 명령어를 사용하여 정밀하게 액세스를 제어하고 테이블, 스키마 및 함수에 대한 객체 수준 권한을 정의하는 방법을 알아보세요. 최소 권한 원칙을 자세한 예제로 구현하고 현재 보안 설정을 감사하기 위한 주요 psql 명령어를 찾아보세요.

377 조회수

PostgreSQL에서 사용자, 역할 및 권한 관리를 위한 필수 명령

데이터베이스 보안은 강력한 접근 제어에 달려 있습니다. PostgreSQL에서는 이 제어가 주로 역할(Roles) 및 관련 권한(Permissions) (또는 권한 부여)을 통해 관리됩니다. 이러한 보안 엔터티를 생성, 수정 및 관리하는 방법을 이해하는 것은 모든 데이터베이스 관리자 또는 개발자에게 기본입니다.

이 포괄적인 가이드는 PostgreSQL 환경 내에서 사용자를 안전하게 관리하고, 접근 정책을 정의하며, 최소 권한 원칙을 구현하는 데 필요한 필수 SQL 명령을 탐구합니다. 역할 생성, 속성 설정, 그룹 멤버십, 그리고 GRANT 및 REVOKE를 사용한 상세한 객체 권한 관리를 다룰 것입니다.

PostgreSQL 역할 이해하기

사용자와 그룹을 엄격하게 구분하는 일부 다른 데이터베이스 시스템과 달리, PostgreSQL은 역할(Role)이라는 통합된 개념을 사용합니다. 역할은 다음을 나타낼 수 있습니다:

  1. 데이터베이스 사용자 (로그인할 수 있는 엔터티로, 일반적으로 LOGIN 속성을 가짐).
  2. 사용자 그룹 (권한 그룹화를 위해서만 사용되는 엔터티로, 일반적으로 LOGIN 속성을 가짐).

효과적인 보안은 데이터베이스에 접근하는 개인이나 애플리케이션의 책임을 정확하게 반영하는 역할을 정의하는 것에서 시작됩니다.

1. 역할 관리: 생성, 수정 및 삭제

데이터베이스 주체를 관리하는 기본 명령은 CREATE ROLE, ALTER ROLE, DROP ROLE을 중심으로 이루어집니다.

새 역할 생성

역할을 생성할 때, 해당 속성, 특히 로그인 가능 여부(LOGIN)와 인증 자격 증명(PASSWORD)을 지정해야 합니다.

기본 로그인 사용자 생성

연결을 위해 암호가 필요한 표준 사용자 역할을 생성하려면:

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

특정 속성을 가진 역할 생성

역할은 기능 및 시스템 권한을 정의하는 다양한 속성을 가질 수 있습니다:

속성 설명
LOGIN 역할이 데이터베이스에 연결할 수 있도록 허용합니다.
SUPERUSER 모든 데이터베이스 권한을 부여합니다 (극히 드물게 사용하십시오).
CREATEROLE 역할이 다른 역할을 생성, 변경 및 삭제할 수 있도록 허용합니다.
CREATEDB 역할이 새 데이터베이스를 생성할 수 있도록 허용합니다.
REPLICATION 역할이 스트리밍 복제를 시작할 수 있도록 허용합니다.
INHERIT / NOINHERIT 역할이 멤버인 역할로부터 권한을 자동으로 상속받는지 여부를 제어합니다 (기본값은 INHERIT).

예시: 관리자 역할 생성 (슈퍼유저 아님)

CREATE ROLE db_admin WITH
    LOGIN
    PASSWORD 'admin_secret'
    CREATEROLE
    CREATEDB
    VALID UNTIL '2025-01-01'; -- 선택 사항: 만료일

기존 역할 수정

ALTER ROLE을 사용하여 기존 역할의 속성을 변경하거나 암호를 업데이트합니다.

-- 기존 사용자의 암호 변경
ALTER ROLE app_user WITH PASSWORD 'new_strong_password';

-- 로그인 기능 해제 (사용자를 그룹으로 전환)
ALTER ROLE db_admin NOLOGIN;

-- 사용자를 잠금 처리
ALTER ROLE old_employee NOLOGIN;

역할 삭제

역할을 삭제하기 전에, 해당 역할이 데이터베이스 객체(테이블, 스키마 등)를 소유하고 있지 않은지 확인하십시오. 소유하고 있다면, REASSIGN OWNED를 사용하여 먼저 소유권을 이전해야 합니다.

-- 역할이 소유한 모든 객체를 삭제하고 'postgres'에게 재할당
REASSIGN OWNED BY old_employee TO postgres;

-- 그런 다음 역할을 삭제
DROP ROLE old_employee;

경고: 역할 삭제는 되돌릴 수 없습니다. 특히 많은 객체를 소유한 역할의 경우 주의하십시오.

2. 그룹 멤버십 관리

역할은 종종 권한 관리를 단순화하기 위한 그룹으로 기능합니다. 50명의 개별 사용자에게 권한을 부여하는 대신, 하나의 그룹 역할에 권한을 부여한 다음, 사용자를 해당 그룹의 멤버로 만듭니다.

그룹 역할 생성

그룹은 일반적으로 LOGIN 속성 없이 생성됩니다.

CREATE ROLE data_analysts NOLOGIN;

그룹 멤버십 부여 및 해지

GRANT 명령을 사용하여 그룹 역할에 멤버를 추가하고, REVOKE를 사용하여 제거합니다.

-- 'alice' 및 'bob' 사용자를 'data_analysts' 그룹에 추가
GRANT data_analysts TO alice, bob;

-- 'bob'을 'data_analysts' 그룹에서 제거
REVOKE data_analysts FROM bob;

WITH ADMIN OPTION

멤버십 부여 시 WITH ADMIN OPTION을 포함하면, 수신자 역할은 해당 그룹의 멤버십을 다른 사람에게 부여할 수 있으며, 그룹 역할을 DROP할 수도 있습니다.

GRANT data_analysts TO supervisor WITH ADMIN OPTION;

3. 객체 권한 (Privileges) 관리

권한은 역할이 어떤 데이터베이스 객체(테이블, 뷰, 함수, 스키마 등)에서 어떤 작업을 수행할 수 있는지를 정의합니다. 이는 데이터베이스 보안의 핵심입니다.

GRANT 명령 구문

GRANT privilege_list ON object_type object_name TO role_name [WITH GRANT OPTION];

일반적인 권한 및 예시

테이블 권한

권한 허용되는 작업
SELECT 테이블에서 데이터를 읽습니다.
INSERT 새 행을 추가합니다.
UPDATE 기존 행을 수정합니다.
DELETE 기존 행을 제거합니다.
TRUNCATE 테이블을 완전히 비웁니다.
REFERENCES 외래 키 제약 조건을 생성합니다.

예시: 특정 테이블에 대한 읽기 전용 접근 권한 부여.

GRANT SELECT ON TABLE production.orders TO data_analysts;

-- 테이블에 대한 모든 DML 작업 권한 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE staging.temp_data TO app_user;

데이터베이스 및 스키마 권한

데이터베이스 및 스키마 권한은 환경 구조를 제어하는 데 중요합니다.

객체 주요 권한 목적
DATABASE CONNECT 데이터베이스에 연결을 허용합니다.
DATABASE CREATE 새 스키마, 테이블스페이스 등을 생성할 수 있도록 허용합니다.
SCHEMA USAGE 스키마 내 객체에 접근을 허용합니다.
SCHEMA CREATE 스키마 내에 새 객체를 생성할 수 있도록 허용합니다.

예시: 스키마 접근 권한 부여

사용자가 app_schema 내의 테이블에 접근해야 하는 경우, 해당 스키마에 대한 USAGE 권한이 있어야 합니다.

GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_user;

시퀀스 및 함수 권한

시퀀스 사용(자동 증가 ID용) 및 함수 실행에는 특정 권한이 필요합니다.

-- 사용자가 시퀀스를 진행할 수 있도록 허용 (INSERT 시 필요)
GRANT USAGE, SELECT ON SEQUENCE app_schema.user_id_seq TO app_user;

-- 특정 저장 프로시저 또는 함수 실행 허용
GRANT EXECUTE ON FUNCTION audit_log_insert(text) TO app_user;

권한 해지

REVOKE 명령을 사용하여 이전에 부여된 특정 권한을 제거합니다. 구문은 GRANT와 유사합니다.

-- orders 테이블에 새 레코드를 삽입하는 기능 해지
REVOKE INSERT ON TABLE production.orders FROM app_user;

-- 이전에 부여된 모든 권한 해지 (참고: 소유권은 해지하지 않음)
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM data_analysts;

해지에 대한 참고: 역할에 권한이 부여되었고, 해당 역할이 다른 그룹의 멤버인 경우, 권한을 해지해도 직접 부여된 권한에만 영향을 미칩니다. 역할이 그룹 멤버십을 통해 여전히 권한을 상속받는다면, 접근 권한을 유지합니다.

향후 객체에 권한 적용

아직 존재하지 않는 객체에 대한 권한을 관리하려면 ALTER DEFAULT PRIVILEGES를 사용해야 합니다. 이는 애플리케이션이 새 테이블을 자주 생성하는 스키마에 필수적입니다.

예시: app_owner 역할이 app_schema 내에서 생성할 모든 향후 테이블에서 app_user가 SELECT할 수 있도록 보장:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app_schema
    GRANT SELECT ON TABLES TO app_user;

4. 현재 권한 보기

보안 설정을 감사하기 위해 PostgreSQL은 여러 도구와 카탈로그 뷰를 제공합니다.

명령/뷰 설명
\du (psql에서) 모든 역할과 해당 속성을 나열합니다.
\du+ role_name (psql에서) 상세한 역할 속성 및 멤버십을 보여줍니다.
\dp table_name (psql에서) 테이블에 부여된 권한을 나열합니다.
pg_roles 역할 정의를 포함하는 시스템 카탈로그 뷰입니다.
information_schema.table_privileges 테이블에 부여된 권한을 보여주는 뷰입니다.

예시: psql을 통해 테이블 권한 확인

=> \dp production.orders

사용자 관리를 위한 보안 모범 사례

  1. 최소 권한 원칙 (PoLP): 역할이 기능을 수행하는 데 필요한 최소한의 권한만 부여하십시오. 절대적으로 필요한 경우가 아니면 ALL PRIVILEGES 또는 SUPERUSER를 부여하지 마십시오.
  2. 애플리케이션 역할 분리: 사람 DBA 역할과 분리된 애플리케이션 전용 역할(예: api_service_role)을 사용하십시오. 애플리케이션은 일반적으로 DML 권한(SELECT, INSERT, UPDATE, DELETE)만 가져야 하며 DDL 권한(CREATE, DROP)은 가지지 않아야 합니다.
  3. 그룹을 광범위하게 사용: 권한 집합을 관리하기 위해 비로그인 역할(그룹)을 생성하십시오(예: read_only_group, etl_writer_group). 개별 사용자에게 개별적으로 권한을 부여하는 대신 이 그룹에 할당하십시오.
  4. 기본 역할 사용 피하기: 일반적인 애플리케이션 또는 개발 작업에 postgres 슈퍼유저 역할을 절대 사용하지 마십시오.
  5. 안전한 인증: 항상 강력한 암호를 사용하고, 가능하면 암호 기반 인증 대신 클라이언트 인증서 인증 또는 중앙 집중식 ID 관리 솔루션을 활용하십시오.

결론

역할 및 권한의 효과적인 관리는 PostgreSQL 보안의 초석입니다. CREATE ROLE, ALTER ROLE, GRANT, REVOKE를 숙달함으로써 데이터베이스 관리자는 세부적인 제어를 구현하여 데이터베이스에 접근하는 모든 사용자 또는 애플리케이션이 필요한 접근만 정확히 가지도록 보장할 수 있습니다. 최소 권한 원칙을 지속적으로 적용하고 그룹 역할을 활용하면 장기적인 유지 관리가 단순화되고 무단 접근에 대한 데이터베이스 방어 태세가 강화됩니다.