고급 PostgreSQL 역할 기반 접근 제어: 행 수준 보안 및 정책 관리

소개

PostgreSQL 행 수준 보안(RLS)은 행 수준에서 세밀한 접근 제어를 제공하여 데이터베이스 내에서 복잡한 인증 규칙을 직접 적용할 수 있게 합니다. 이를 통해 애플리케이션 계층 필터링이 필요 없어지고, 애플리케이션이 데이터베이스에 어떻게 접근하든 데이터 보안이 보장됩니다.

왜 행 수준 보안인가?

기존 방식의 문제점

애플리케이션 계층 필터링:

# 모든 쿼리가 필터를 기억해야 함
query = "SELECT * FROM documents WHERE tenant_id = %s"
results = db.execute(query, [current_user_tenant_id])

문제점:
- 필터를 쉽게 잊어버릴 수 있음 (보안 취약점)
- 애플리케이션 전반에 코드 중복
- 직접 데이터베이스 접근에 대한 보호 없음
- 복잡한 감사 요구사항

행 수준 보안의 이점

• 자동 적용: 정책이 투명하게 적용됨
• 중앙 집중식 규칙: 보안 로직이 한 곳에 위치
• 멀티 테넌트 격리: SaaS 애플리케이션에 완벽함
• 감사 준수: 내장 보안 보장
• 성능: 효율적인 쿼리 계획

기본 RLS 설정

1단계: 테이블에서 RLS 활성화

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    owner_id INTEGER NOT NULL,
    department VARCHAR(50),
    is_public BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 행 수준 보안 활성화
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

2단계: 역할 생성

-- 다른 사용자 역할 생성
CREATE ROLE regular_user;
CREATE ROLE department_manager;
CREATE ROLE admin;

-- 테이블 접근 권한 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO regular_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO department_manager;
GRANT ALL ON documents TO admin;

3단계: 정책 생성

-- 정책: 사용자는自己的 문서를 볼 수 있음
CREATE POLICY user_own_documents ON documents
    FOR SELECT
    TO regular_user
    USING (owner_id = current_setting('app.user_id')::INTEGER);

-- 정책: 사용자는 自己의 문서를 삽입할 수 있음
CREATE POLICY user_insert_own ON documents
    FOR INSERT
    TO regular_user
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

-- 정책: 사용자는 自己의 문서를 수정할 수 있음
CREATE POLICY user_update_own ON documents
    FOR UPDATE
    TO regular_user
    USING (owner_id = current_setting('app.user_id')::INTEGER)
    WITH CHECK (owner_id = current_setting('app.user_id')::INTEGER);

4단계: 세션 변수 설정

애플리케이션 코드에서:

import psycopg2

# 역할로 연결
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="regular_user",
    password="password"
)

# 사용자 컨텍스트 설정
cursor = conn.cursor()
cursor.execute("SET app.user_id = %s", [current_user_id])

# 이제 쿼리는 자동으로 필터링됨
cursor.execute("SELECT * FROM documents")
# owner_id = current_user_id인 문서만 반환

멀티 테넌트 SaaS 애플리케이션

완전한 멀티 테넌트 설정

-- 테넌트 테이블 생성
CREATE TABLE tenants (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 사용자 테이블 생성
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    tenant_id INTEGER REFERENCES tenants(id),
    role VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 애플리케이션 데이터 테이블 생성
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL REFERENCES tenants(id),
    customer_name VARCHAR(100),
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW()
);

-- RLS 활성화
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 정책: 사용자는 自己의 테넌트 주문만 볼 수 있음
CREATE POLICY tenant_isolation ON orders
    FOR ALL
    TO public
    USING (tenant_id = current_setting('app.tenant_id')::INTEGER)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::INTEGER);

애플리케이션 통합

Django 예시:

from django.db import connection

class TenantMiddleware:
    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        if request.user.is_authenticated:
            with connection.cursor() as cursor:
                cursor.execute(
                    "SET app.tenant_id = %s",
                    [request.user.tenant_id]
                )

        response = self.get_response(request)
        return response

Node.js 예시:

const { Pool } = require('pg');
const pool = new Pool();

async function setTenantContext(tenantId) {
    const client = await pool.connect();
    try {
        await client.query('SET app.tenant_id = $1', [tenantId]);
        return client;
    } catch (err) {
        client.release();
        throw err;
    }
}

// 사용법
app.use(async (req, res, next) => {
    if (req.user) {
        req.dbClient = await setTenantContext(req.user.tenantId);
    }
    next();
});

고급 정책 패턴

1. 계층적 접근 제어

-- 사용자는 自己의 부서 문서와 공개 문서를 볼 수 있음
CREATE POLICY department_access ON documents
    FOR SELECT
    TO regular_user
    USING (
        department = current_setting('app.user_department', true)
        OR is_public = true
        OR owner_id = current_setting('app.user_id')::INTEGER
    );

2. 시간 기반 접근

CREATE TABLE subscriptions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    content TEXT,
    valid_from TIMESTAMP NOT NULL,
    valid_until TIMESTAMP NOT NULL
);

ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;

-- 정책: 사용자는 활성 구독만 볼 수 있음
CREATE POLICY active_subscriptions ON subscriptions
    FOR SELECT
    TO public
    USING (
        user_id = current_setting('app.user_id')::INTEGER
        AND NOW() BETWEEN valid_from AND valid_until
    );

3. 복잡한 로직이 포함된 역할 기반 접근

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    owner_id INTEGER,
    team_id INTEGER,
    visibility VARCHAR(20) -- 'private', 'team', 'public'
);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- 복잡한 가시성 정책
CREATE POLICY project_visibility ON projects
    FOR SELECT
    TO public
    USING (
        CASE current_setting('app.user_role', true)
            WHEN 'admin' THEN true
            WHEN 'manager' THEN (
                team_id = current_setting('app.user_team_id')::INTEGER
                OR visibility = 'public'
            )
            ELSE (
                owner_id = current_setting('app.user_id')::INTEGER
                OR visibility = 'public'
            )
        END
    );

4. 공유 접근 목록

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    owner_id INTEGER NOT NULL
);

CREATE TABLE document_shares (
    document_id INTEGER REFERENCES documents(id),
    shared_with_user_id INTEGER,
    permission VARCHAR(20), -- 'read', 'write'
    PRIMARY KEY (document_id, shared_with_user_id)
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- 정책: 사용자는 소유하거나 공유된 문서를 볼 수 있음
CREATE POLICY document_access ON documents
    FOR SELECT
    TO public
    USING (
        owner_id = current_setting('app.user_id')::INTEGER
        OR id IN (
            SELECT document_id 
            FROM document_shares 
            WHERE shared_with_user_id = current_setting('app.user_id')::INTEGER
        )
    );

-- 정책: 사용자는 소유 문서나 쓰기 권한이 공유된 문서를 수정할 수 있음
CREATE POLICY document_modify ON documents
    FOR UPDATE
    TO public
    USING (
        owner_id = current_setting('app.user_id')::INTEGER
        OR id IN (
            SELECT document_id 
            FROM document_shares 
            WHERE shared_with_user_id = current_setting('app.user_id')::INTEGER
              AND permission = 'write'
        )
    );

관리 작업을 위한 RLS 우회

옵션 1: BYPASSRLS 역할 속성

-- 수퍼유저와 같은 접근 권한 부여
CREATE ROLE admin_user WITH LOGIN PASSWORD 'secure_password';
ALTER ROLE admin_user BYPASSRLS;

-- 관리자는 정책에 관계없이 모든 행을 볼 수 있음

옵션 2: 관리자를 위한 허용적 정책

-- 관리자 역할을 위한 허용적 정책 생성
CREATE POLICY admin_all_access ON documents
    FOR ALL
    TO admin
    USING (true)
    WITH CHECK (true);

옵션 3: RLS 일시적으로 비활성화

-- 유지 관리 스크립트용
BEGIN;
    ALTER TABLE documents DISABLE ROW LEVEL SECURITY;
    -- 관리 작업 수행
    UPDATE documents SET archived = true WHERE created_at < NOW() - INTERVAL '1 year';
    ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
COMMIT;

정책 유형: USING vs WITH CHECK

USING 절

기존 행의 가시성/수정 가능성을 제어:

CREATE POLICY select_own ON documents
    FOR SELECT
    USING (owner_id = current_user_id());

-- 사용자는 owner_id가 일치하는 행만 SELECT 가능

WITH CHECK 절

新規/수정 행의 허용 여부를 제어:

CREATE POLICY insert_own ON documents
    FOR INSERT
    WITH CHECK (owner_id = current_user_id());

-- 사용자는 owner_id가 일치하는 행만 INSERT 가능

결합된 정책

CREATE POLICY update_own ON documents
    FOR UPDATE
    USING (owner_id = current_user_id())       -- 自分の行のみ更新可能
    WITH CHECK (owner_id = current_user_id()); -- 所有権 변경 불가

성능 고려사항

1. 정책 컬럼 인덱싱

-- 정책에서 사용되는 컬럼에 인덱스 생성
CREATE INDEX idx_documents_owner ON documents(owner_id);
CREATE INDEX idx_documents_tenant ON documents(tenant_id);
CREATE INDEX idx_documents_department ON documents(department);

2. 쿼리 플랜 분석

EXPLAIN ANALYZE
SELECT * FROM documents
WHERE title LIKE '%report%';

-- 정책 필터가 효율적으로 적용되는지 확인

3. 정책 내 서브쿼리 피하기

나쁨 - 모든 행에 대해 서브쿼리 실행:

CREATE POLICY slow_policy ON documents
    USING (
        id IN (SELECT document_id FROM shares WHERE user_id = current_user_id())
    );

더 나음 - EXISTS 사용:

CREATE POLICY faster_policy ON documents
    USING (
        EXISTS (
            SELECT 1 FROM shares 
            WHERE document_id = documents.id 
              AND user_id = current_user_id()
        )
    );

최적 - 애플리케이션에서 JOIN 또는物性化 뷰 사용:

CREATE MATERIALIZED VIEW user_accessible_documents AS
SELECT d.*, s.user_id AS shared_with
FROM documents d
LEFT JOIN shares s ON d.id = s.document_id;

CREATE INDEX ON user_accessible_documents(shared_with);

모니터링 및 감사

활성 정책 보기

-- RLS 정책 나열
SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,
    with_check
FROM pg_policies
ORDER BY tablename, policyname;

정책 변경 감사

-- 정책 변경을 위한 감사 로그 생성
CREATE TABLE policy_audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100),
    policy_name VARCHAR(100),
    action VARCHAR(20),
    changed_by VARCHAR(50),
    changed_at TIMESTAMP DEFAULT NOW()
);

-- 변경 로그를 위한 트리거 함수
CREATE OR REPLACE FUNCTION log_policy_changes()
RETURNS event_trigger AS $$
BEGIN
    INSERT INTO policy_audit_log (table_name, policy_name, action, changed_by)
    SELECT 
        objid::regclass::text,
        object_identity,
        'CREATED',
        current_user
    FROM pg_event_trigger_ddl_commands()
    WHERE command_tag = 'CREATE POLICY';
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER log_policy_create
    ON ddl_command_end
    WHEN TAG IN ('CREATE POLICY')
    EXECUTE FUNCTION log_policy_changes();

정책 효과 테스트

-- 테스트 함수 생성
CREATE OR REPLACE FUNCTION test_user_access(
    test_user_id INTEGER,
    test_tenant_id INTEGER
)
RETURNS TABLE(document_id INTEGER, title VARCHAR) AS $$
BEGIN
    -- 세션 변수 설정
    PERFORM set_config('app.user_id', test_user_id::text, true);
    PERFORM set_config('app.tenant_id', test_tenant_id::text, true);

    -- 접근 가능한 문서 반환
    RETURN QUERY SELECT id, documents.title FROM documents;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 접근 테스트
SELECT * FROM test_user_access(123, 5);

문제 해결

문제 1: 정책이 적용되지 않음

RLS 활성화 여부 확인:

SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public' AND tablename = 'documents';

비활성화된 경우 활성화:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

문제 2: 세션 변수가 설정되지 않음

현재 설정 디버깅:

SHOW app.user_id;
SHOW app.tenant_id;

-- 또는 current_setting 사용
SELECT current_setting('app.user_id', true);

문제 3: 성능 저하

느린 정책 식별:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM documents WHERE title = 'test';

-- 정책 조건이 있는 "Filter" 작업 확인

해결책: 정책 단순화 또는 인덱스 추가

문제 4: 예상치 않은 접근 거부

특정 사용자로 테스트:

SET ROLE regular_user;
SET app.user_id = '123';

SELECT * FROM documents;
-- 해당 사용자가 실제로 볼 수 있는 내용 표시

RESET ROLE;

모범 사례

1. 항상 세션 변수 설정: current_setting() 값이 채워지도록 보장
2. 정책 컬럼 인덱싱: USING/WITH CHECK에서 사용되는 모든 컬럼에 인덱스 생성
3. 철저한 테스트: 다양한 역할과 시나리오로 정책 검증
4. 단순한 정책 유지: 복잡한 로직은 성능에 영향을 줌
5. EXPLAIN 사용: RLS 활성화 시 쿼리 플랜 분석
6. 정책 문서화: 유지보수를 위한 정책 로직 주석
7. 관심사 분리: SELECT, INSERT, UPDATE, DELETE에 다른 정책 사용
8. 성능 모니터링: 느린 쿼리에 대한 알림 설정

결론

PostgreSQL 행 수준 보안은 강력하고 데이터베이스 기반의 접근 제어를 제공합니다:

• 자동 필터링: 보안이 투명하게 적용됨
• 멀티 테넌트 격리: SaaS 애플리케이션에 완벽함
• 유연한 정책: 복잡한 인증 규칙 지원
• 중앙 집중식 보안: 로직이 데이터베이스에 위치, 코드에 분산되지 않음

간단한 테넌트 격리로 시작하고, 성능 영향을 테스트한 다음, 필요에 따라 더 정교한 인증 패턴으로 확장하세요.