Skip to main content
Tilnote
T
tilnote
책에 포함된 노트PostgreSQL 입문부터 실전 운영까지

사용자, 권한, 보안 설계

Views 14약 7분생성형 AI 활용
Summary

데이터베이스 보안은 운영 직전에 추가하는 설정이 아니다. 테이블을 만들고 애플리케이션을 연결하는 순간부터 누가 접속할 수 있는지, 어떤 객체를 읽고 바꿀 수 있는지, 같은 테이블 안에서도 어떤 행을 볼 수 있는지 정해야 한다.

PostgreSQL 보안은 크게 네 단계로 나누어 생각하면 쉽다.

  1. 서버 접속을 허용할지 결정한다.

  2. 접속한 역할이 데이터베이스와 스키마에 들어갈 수 있는지 결정한다.

  3. 테이블, 시퀀스, 함수 같은 객체를 사용할 수 있는지 결정한다.

  4. 필요한 경우 행 단위로 다시 제한한다.

이 장에서는 쇼핑몰 데이터베이스를 기준으로 읽기 전용 계정, 애플리케이션 계정, 배치 계정, 관리자 계정을 분리하는 구조를 만든다.

사용자, 권한, 보안 설계AI 생성 이미지

role은 사용자이면서 그룹이다

PostgreSQL에서는 사용자와 그룹을 모두 role로 다룬다. LOGIN 속성이 있으면 직접 접속할 수 있는 사용자처럼 동작하고, NOLOGIN이면 여러 사용자에게 권한을 묶어 주는 그룹처럼 사용할 수 있다.1

실무에서는 권한을 로그인 계정에 직접 많이 붙이지 않는 편이 좋다. 대신 권한 묶음 역할을 만들고, 로그인 계정은 그 역할의 구성원으로 둔다.

-- 권한 묶음 역할: 직접 로그인하지 않는다.
CREATE ROLE shop_owner NOLOGIN;
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_writer NOLOGIN;
CREATE ROLE batch_worker NOLOGIN;

-- 실제 로그인 계정
CREATE ROLE report_api LOGIN PASSWORD 'change-this-password';
CREATE ROLE shop_api LOGIN PASSWORD 'change-this-password';
CREATE ROLE settlement_batch LOGIN PASSWORD 'change-this-password';

-- 로그인 계정에 권한 묶음을 부여한다.
GRANT app_readonly TO report_api;
GRANT app_writer TO shop_api;
GRANT batch_worker TO settlement_batch;

shop_api는 주문 생성과 고객 정보 수정에 쓰고, report_api는 대시보드 조회에만 쓴다. 정산 배치는 settlement_batch로 분리한다. 이렇게 나누면 한 계정의 비밀번호가 노출되더라도 피해 범위를 줄일 수 있다.

관리자 계정도 구분해야 한다. postgres 같은 슈퍼유저를 애플리케이션 연결 문자열에 넣으면 안 된다. 슈퍼유저는 권한 검사와 RLS 같은 보호 장치를 우회할 수 있으므로 장애 대응, 백업, 마이그레이션처럼 꼭 필요한 작업에만 사용한다.

GRANT와 REVOKE는 단계적으로 적용한다

권한은 한 번에 끝나지 않는다. 데이터베이스 접속 권한, 스키마 사용 권한, 테이블 권한, 시퀀스 권한이 따로 있다. 어느 하나가 빠지면 접속은 되었는데 조회가 실패하거나, 테이블 INSERT는 되는데 serial이나 bigserial 기본값이 사용하는 시퀀스에서 권한 오류가 나는 문제가 생긴다.

먼저 기본 공개 권한을 줄인다. PUBLIC은 모든 역할을 뜻한다. public 스키마와 이름이 비슷하지만 다른 개념이다.

-- 모든 역할의 무분별한 접속과 객체 생성을 막는다.
REVOKE CONNECT ON DATABASE shop FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 필요한 역할에만 데이터베이스 접속을 허용한다.
GRANT CONNECT ON DATABASE shop
TO app_readonly, app_writer, batch_worker;

다음으로 업무 스키마를 만들고 스키마 접근 권한을 준다.

CREATE SCHEMA IF NOT EXISTS shop AUTHORIZATION shop_owner;

GRANT USAGE ON SCHEMA shop
TO app_readonly, app_writer, batch_worker;

USAGE ON SCHEMA는 스키마 안의 객체 이름을 찾아갈 수 있게 하는 권한이다. 이것만으로 테이블을 읽을 수 있는 것은 아니다. 테이블 권한은 별도로 부여해야 한다.

-- 읽기 전용 역할
GRANT SELECT ON ALL TABLES IN SCHEMA shop
TO app_readonly;

-- 애플리케이션 쓰기 역할
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA shop
TO app_writer;

-- serial, bigserial 또는 직접 nextval/currval을 호출하는 시퀀스 사용
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA shop
TO app_writer, batch_worker;

GENERATED ... AS IDENTITY 컬럼은 일반적인 INSERT에서 별도 시퀀스 권한을 요구하지 않는 경우가 많다. 그래도 기존 serial, bigserial 컬럼이 함께 있거나 애플리케이션이 시퀀스를 직접 조회한다면 시퀀스 권한을 따로 점검해야 한다. 테이블 권한과 시퀀스 권한은 같은 것이 아니기 때문이다.

앞으로 새로 만들어질 테이블에도 같은 권한을 자동으로 주려면 기본 권한을 설정한다.

ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA shop
GRANT SELECT ON TABLES TO app_readonly;

ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA shop
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;

ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA shop
GRANT USAGE, SELECT ON SEQUENCES TO app_writer, batch_worker;

주의할 점이 있다. 기본 권한은 "앞으로 해당 역할이 만드는 객체"에 적용된다. 마이그레이션 도구가 migration_user로 테이블을 만든다면 FOR ROLE migration_user에 대해서도 설정해야 한다.

권한을 줄이는 예도 중요하다. 고객센터 직원이 고객의 이름과 이메일은 봐야 하지만 비밀번호 해시나 내부 메모는 보면 안 되는 경우가 있다. 이때는 테이블 전체 SELECT를 주지 말고 필요한 열만 허용한다.

CREATE ROLE support_reader NOLOGIN;
CREATE ROLE support_api LOGIN PASSWORD 'change-this-password';
GRANT support_reader TO support_api;

GRANT USAGE ON SCHEMA shop TO support_reader;

GRANT SELECT (id, email, name, created_at)
ON shop.customers
TO support_reader;

이미 넓게 준 권한은 REVOKE로 제거한다.

REVOKE DELETE ON ALL TABLES IN SCHEMA shop FROM app_writer;

권한 설계의 기준은 "이 계정이 실수하거나 탈취되었을 때 어디까지 망가질 수 있는가"이다. 읽기 전용 계정에 UPDATE가 필요해 보인다면, 먼저 애플리케이션 흐름이 잘못 섞인 것은 아닌지 확인한다.

pg_hba.conf는 접속의 첫 번째 문이다

SQL 권한은 PostgreSQL 서버에 접속한 뒤에 적용된다. 그 전에 pg_hba.conf가 접속 요청을 먼저 검사한다. 이 파일은 접속 방식, 데이터베이스, 사용자, 클라이언트 주소, 인증 방식을 기준으로 규칙을 적용한다. 규칙은 위에서 아래로 읽으며, 처음 일치한 규칙 하나만 사용한다. 그 규칙에서 인증에 실패해도 아래 규칙을 다시 시도하지 않는다.2

예시는 다음과 같다.

# 로컬 관리자는 운영체제 사용자와 매핑해 접속
local   all        postgres                         peer

# 애플리케이션 서버 대역은 SSL 연결만 허용
hostssl shop       shop_api          10.10.0.0/16   scram-sha-256

# 리포트 서버 대역은 읽기 전용 계정만 허용
hostssl shop       report_api        10.20.5.0/24   scram-sha-256

# 정산 배치 서버 한 대만 허용
hostssl shop       settlement_batch  10.30.1.25/32  scram-sha-256

# 나머지 원격 접속은 거부
host    all        all               0.0.0.0/0      reject
host    all        all               ::0/0          reject

여기서 중요한 점은 넓은 규칙을 위에 두면 아래의 세부 규칙이 의미 없어질 수 있다는 것이다. 예를 들어 host all all 0.0.0.0/0 trust 같은 규칙은 운영 환경에 두면 안 된다. 비밀번호 없이 모든 IPv4 원격 접속을 허용하기 때문이다.

hostssl 규칙을 쓴다고 해서 자동으로 SSL이 켜지는 것은 아니다. 서버의 ssl 설정과 인증서 구성이 준비되어 있어야 하고, 원격 접속 자체를 받으려면 listen_addresses와 방화벽도 함께 맞아야 한다. pg_hba.conf는 PostgreSQL 안에서의 인증 관문이지, 네트워크 전체를 대신 막아 주는 장치는 아니다.

변경 후에는 서버를 재시작하거나 설정을 다시 읽어야 한다.

SELECT pg_reload_conf();

규칙을 고친 뒤에는 오타도 확인하는 습관을 들이는 것이 좋다. PostgreSQL은 pg_hba_file_rules 뷰를 통해 해석된 규칙과 오류를 보여 준다.

SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules
WHERE error IS NOT NULL;

pg_hba.conf는 "누가 어디서 어떤 방식으로 인증할 수 있는가"를 정한다. GRANT는 "인증된 뒤 무엇을 할 수 있는가"를 정한다. 둘 중 하나만 맞아서는 접근이 완성되지 않는다.

같은 테이블에서도 행을 제한하는 RLS

권한만으로는 테이블 전체 접근 여부를 제어한다. 하지만 SaaS 서비스처럼 여러 회사의 데이터가 한 테이블에 섞여 있을 때는 같은 테이블 안에서도 회사별로 볼 수 있는 행을 제한해야 한다. 이때 Row Level Security, 줄여서 RLS를 사용한다.

RLS를 활성화하면 일반 쿼리의 SELECT, INSERT, UPDATE, DELETE가 정책을 통과한 행에만 적용된다. 정책이 없으면 기본적으로 어떤 행도 보이지 않는다. 다만 슈퍼유저와 BYPASSRLS 속성을 가진 역할은 RLS를 우회하며, 테이블 소유자도 기본적으로 우회할 수 있다. 소유자에게도 정책을 적용하려면 FORCE ROW LEVEL SECURITY를 사용한다.3

고객 문의 테이블을 예로 보자.

CREATE TABLE shop.support_tickets (
    id bigserial PRIMARY KEY,
    tenant_id bigint NOT NULL,
    customer_email text NOT NULL,
    title text NOT NULL,
    body text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE shop.support_tickets ENABLE ROW LEVEL SECURITY;
ALTER TABLE shop.support_tickets FORCE ROW LEVEL SECURITY;

GRANT SELECT, INSERT, UPDATE ON shop.support_tickets TO app_writer;
GRANT USAGE, SELECT ON SEQUENCE shop.support_tickets_id_seq TO app_writer;

애플리케이션은 요청을 처리할 때 현재 테넌트 ID를 세션 설정값으로 넣는다. 정책은 그 값을 읽어 현재 테넌트의 행만 허용한다.

CREATE POLICY support_ticket_select
ON shop.support_tickets
FOR SELECT
TO app_writer
USING (
    tenant_id = current_setting('app.tenant_id', true)::bigint
);

CREATE POLICY support_ticket_insert
ON shop.support_tickets
FOR INSERT
TO app_writer
WITH CHECK (
    tenant_id = current_setting('app.tenant_id', true)::bigint
);

CREATE POLICY support_ticket_update
ON shop.support_tickets
FOR UPDATE
TO app_writer
USING (
    tenant_id = current_setting('app.tenant_id', true)::bigint
)
WITH CHECK (
    tenant_id = current_setting('app.tenant_id', true)::bigint
);

current_setting('app.tenant_id', true)에서 두 번째 인자인 true는 설정값이 없을 때 오류 대신 NULL을 돌려주게 한다. 값이 없으면 tenant_id = NULL 비교가 참이 되지 않으므로 행이 보이지 않는다. 다만 애플리케이션은 이 값을 사용자 입력에서 그대로 가져오면 안 된다. 로그인 세션이나 토큰에서 확인한 테넌트 ID를 넣어야 하고, 커넥션 풀을 쓴다면 요청이나 트랜잭션마다 값이 제대로 설정되고 초기화되는지도 확인해야 한다.

검증은 역할을 바꿔 직접 해 본다.

SET ROLE shop_api;

BEGIN;
SELECT set_config('app.tenant_id', '10', true);

INSERT INTO shop.support_tickets (tenant_id, customer_email, title, body)
VALUES (10, '[email protected]', '배송 문의', '배송이 지연되었습니다.');

COMMIT;

BEGIN;
SELECT set_config('app.tenant_id', '10', true);

-- tenant_id가 다르면 WITH CHECK 정책에 막혀야 한다.
INSERT INTO shop.support_tickets (tenant_id, customer_email, title, body)
VALUES (20, '[email protected]', '결제 문의', '결제가 두 번 되었습니다.');

ROLLBACK;

RESET ROLE;

두 번째 INSERT는 현재 트랜잭션의 app.tenant_id가 10인데 입력하려는 행의 tenant_id가 20이므로 실패해야 한다. 실패한 트랜잭션은 ROLLBACK으로 정리한다. 정책을 만들었다는 사실보다, 다른 테넌트 데이터가 실제로 보이지 않고 수정되지 않는지 확인하는 일이 더 중요하다.

RLS를 사용할 때는 백업과 운영 계정도 주의한다. 백업 계정이 RLS에 걸리면 일부 행만 백업되는 위험이 있다. 백업, 마이그레이션, 정산 검증처럼 전체 데이터가 필요한 작업은 별도 역할과 별도 접속 경로로 관리하고, 사용 내역을 로그로 남긴다.

운영 보안 체크리스트

마지막으로 실무에서 자주 놓치는 항목을 정리한다.

비밀번호와 계정

  • 애플리케이션에서 슈퍼유저로 접속하지 않는다.

  • 사람 계정과 애플리케이션 계정을 분리한다.

  • 읽기 전용, 쓰기용, 배치용, 마이그레이션용 계정을 나눈다.

  • 운영 비밀번호를 SQL 파일, psql 히스토리, Git 저장소에 남기지 않는다.

  • 새 구성에서는 scram-sha-256 인증을 사용한다.

  • 장기 계정에는 만료일과 교체 절차를 둔다.

ALTER ROLE report_api VALID UNTIL '2026-12-31';

SSL 연결

  • 원격 접속은 가능하면 hostssl로 제한한다.

  • 애플리케이션 연결 문자열에는 운영 환경에서 sslmode=verify-full을 사용한다.

  • 서버 인증서와 CA 인증서를 관리하고, 개인키 파일 권한을 제한한다.

  • 내부망이라고 해서 평문 접속을 기본값으로 두지 않는다.

로그와 감사

  • log_connections, log_disconnections로 접속 흐름을 남긴다.

  • log_line_prefix에 시간, 사용자, 데이터베이스, 클라이언트 주소, application_name을 포함한다.

  • 권한 변경, DDL, 민감 테이블 접근은 별도 감사 정책을 둔다.

  • 더 상세한 감사가 필요하면 pgaudit 같은 확장을 검토한다.

민감 정보 암호화

  • 비밀번호는 복호화 가능한 암호화가 아니라 단방향 해시로 저장한다.

  • 주민번호, 토큰, 결제 관련 식별자처럼 유출 피해가 큰 값은 컬럼 단위 암호화를 검토한다.

  • 암호화 키를 데이터베이스 안에 함께 저장하지 않는다.

  • 암호화는 권한 설계를 대체하지 않는다. 권한, 접속 제한, 로그, 키 관리가 함께 있어야 한다.

보안 설계의 목표는 모든 위험을 한 설정으로 해결하는 것이 아니다. 접속 정책, 역할 분리, 최소 권한, RLS, 암호화, 감사 로그를 겹겹이 두어 하나가 실패해도 전체 데이터가 바로 노출되지 않게 만드는 것이다.

참고

1PostgreSQL Documentation: Database Roles

2PostgreSQL Documentation: The pg_hba.conf File

3PostgreSQL Documentation: Row Security Policies

책 목록

PostgreSQL 입문부터 실전 운영까지

전체 목차 보기

이 노트는 보도·비평·교육·연구 목적으로 작성되었습니다. 저작권 문의가 있으시면 에서 알려주세요.

틸노트는 사용자가 AI를 활용해 노트를 쉽게 작성하거나 편집할 수 있는 서비스입니다.

회원 가입

AI 스퀘어 단톡방에 함께해요

AI 활용과 틸노트 사용 팁을 나누는 오픈채팅방입니다. 입장 비밀번호는 tilnote입니다.

참여하기
이용약관·개인정보처리방침