[PostgreSQL] 사용자별 보안 설정 가이드

🔒 PostgreSQL 사용자별 보안 설정 완벽 가이드

PostgreSQL에서는 사용자(Role)별로 다양한 보안 설정을 적용하여 데이터베이스를 안전하게 보호할 수 있습니다.
이 문서에서는 세션 타임아웃부터 접속 제한, 권한 관리까지 실무에서 꼭 필요한 보안 설정들을 정리합니다.

1️⃣ 세션 타임아웃 설정

✅ idle_session_timeout (PostgreSQL 14+)

유휴 세션을 자동으로 종료하여 불필요한 연결을 정리합니다.

-- 세션 레벨에서 설정 (30분)
SET idle_session_timeout = '30min';

-- 전역 설정 (postgresql.conf)
idle_session_timeout = '30min'

-- 특정 사용자에 설정
ALTER ROLE webapp_user SET idle_session_timeout = '30min';

-- 특정 데이터베이스에 설정
ALTER DATABASE mydb SET idle_session_timeout = '1h';

✅ statement_timeout

단일 쿼리의 최대 실행 시간을 제한하여 과도한 리소스 사용을 방지합니다.

-- 세션 레벨 (10초)
SET statement_timeout = '10s';

-- 웹 애플리케이션 사용자: 짧은 타임아웃
ALTER ROLE webapp_user SET statement_timeout = '30s';

-- 배치 작업 사용자: 긴 타임아웃
ALTER ROLE batch_user SET statement_timeout = '10min';

-- 관리자: 제한 없음
ALTER ROLE admin_user SET statement_timeout = 0;

✅ idle_in_transaction_session_timeout

트랜잭션 내에서 유휴 상태인 세션을 종료합니다. 데드락과 테이블 락 문제를 예방합니다.

-- 세션 레벨 (5분)
SET idle_in_transaction_session_timeout = '5min';

-- 전역 설정 (postgresql.conf)
idle_in_transaction_session_timeout = '5min'

-- OLTP 애플리케이션: 짧게 설정
ALTER ROLE webapp_user SET idle_in_transaction_session_timeout = '60s';

📊 현재 타임아웃 설정 확인

-- 현재 세션의 설정 확인
SHOW idle_session_timeout;
SHOW statement_timeout;
SHOW idle_in_transaction_session_timeout;

-- 모든 타임아웃 설정 확인
SELECT name, setting, unit, context
FROM pg_settings
WHERE name LIKE '%timeout%'
ORDER BY name;

-- 사용자별 설정 확인
SELECT rolname, rolconfig
FROM pg_roles
WHERE rolconfig IS NOT NULL;

2️⃣ 연결 수 제한

✅ CONNECTION LIMIT

사용자별 동시 연결 수를 제한하여 리소스 고갈을 방지합니다.

-- 새 사용자 생성 시 연결 수 제한
CREATE ROLE webapp_user WITH LOGIN PASSWORD 'secure_password'
CONNECTION LIMIT 50;

-- 기존 사용자의 연결 수 제한
ALTER ROLE readonly_user CONNECTION LIMIT 10;

-- 무제한 연결 (주의: 관리자만)
ALTER ROLE admin_user CONNECTION LIMIT -1;

📊 현재 연결 상태 모니터링

-- 사용자별 현재 연결 수 확인
SELECT
    usename,
    COUNT(*) as active_connections,
    MAX(backend_start) as last_connection
FROM pg_stat_activity
GROUP BY usename
ORDER BY active_connections DESC;

-- 유휴 연결 확인
SELECT
    pid,
    usename,
    application_name,
    state,
    state_change,
    NOW() - state_change as idle_duration
FROM pg_stat_activity
WHERE state = 'idle'
ORDER BY idle_duration DESC;

3️⃣ 비밀번호 정책

✅ 비밀번호 만료 설정

-- 비밀번호 90일 후 만료
ALTER ROLE webapp_user VALID UNTIL '2025-03-01';

-- 현재 시간 기준 90일 후 만료
ALTER ROLE webapp_user VALID UNTIL NOW() + INTERVAL '90 days';

-- 만료 없음
ALTER ROLE admin_user VALID UNTIL 'infinity';

✅ SCRAM-SHA-256 암호화

md5보다 안전한 해시 알고리즘을 사용합니다.

-- postgresql.conf
password_encryption = scram-sha-256

-- 기존 사용자 비밀번호 재설정 (SCRAM으로 변환)
ALTER ROLE webapp_user PASSWORD 'new_secure_password';

-- 사용자의 암호화 방식 확인
SELECT rolname, rolpassword
FROM pg_authid
WHERE rolname = 'webapp_user';

4️⃣ 네트워크 및 IP 접근 제어

✅ pg_hba.conf 설정

# TYPE  DATABASE    USER            ADDRESS         METHOD

# 로컬 연결만 허용 (관리자)
local   all         admin_user                      scram-sha-256

# 특정 IP 대역에서만 접속 허용
hostssl all         webapp_user     10.0.1.0/24     scram-sha-256

# 특정 데이터베이스만 접근 허용
hostssl myapp_db    app_user        10.0.2.0/24     scram-sha-256

# 읽기 전용 사용자는 특정 IP에서만
hostssl all         readonly_user   192.168.1.100/32 scram-sha-256

# SSL 연결 강제 + 클라이언트 인증서 검증
hostssl all         all             0.0.0.0/0       scram-sha-256 clientcert=verify-full

5️⃣ 권한 최소화 원칙 (Least Privilege)

✅ 읽기 전용 사용자

-- 읽기 전용 사용자 생성
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'secure_password';

-- 특정 스키마의 읽기 권한만 부여
GRANT CONNECT ON DATABASE mydb TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- 향후 생성될 테이블에도 자동 적용
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;

✅ 애플리케이션 사용자 (읽기/쓰기)

-- 애플리케이션 사용자 생성
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';

-- 특정 테이블에만 읽기/쓰기 권한
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON TABLE users, orders TO app_user;
GRANT SELECT ON TABLE products TO app_user; -- 읽기만

-- DELETE 권한은 별도 관리
GRANT DELETE ON TABLE orders TO app_user;

-- 시퀀스 사용 권한 (INSERT 시 필요)
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

✅ 관리자 vs 일반 사용자 분리

-- 일반 사용자는 슈퍼유저 권한 없이
CREATE ROLE app_user WITH LOGIN PASSWORD 'password';

-- 관리자는 필요한 권한만
CREATE ROLE db_admin WITH LOGIN PASSWORD 'password'
CREATEROLE CREATEDB;

-- 슈퍼유저는 최소화 (postgres만 사용)
-- CREATE ROLE super_admin WITH LOGIN PASSWORD 'password' SUPERUSER;

6️⃣ Row Level Security (RLS)

테이블 행 단위로 접근을 제어합니다.

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

-- 정책 생성: 사용자는 자신의 데이터만 조회
CREATE POLICY user_isolation_policy ON users
    FOR SELECT
    USING (user_id = current_user::text);

-- 정책 생성: 관리자는 모든 데이터 조회
CREATE POLICY admin_all_policy ON users
    FOR ALL
    TO admin_user
    USING (true);

-- 특정 역할에 RLS 우회 권한
ALTER TABLE users FORCE ROW LEVEL SECURITY;

7️⃣ 감사 로깅 (Audit Logging)

✅ 기본 로깅 설정

-- postgresql.conf
log_connections = on
log_disconnections = on
log_duration = on
log_statement = 'ddl'  # none, ddl, mod, all
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

✅ pgAudit 확장 모듈

-- postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'read,write,ddl'
pgaudit.log_relation = on

-- 특정 사용자만 감사
ALTER ROLE webapp_user SET pgaudit.log = 'write';

📊 로그인 이력 조회

-- 최근 로그인 이력 (PostgreSQL 14+)
SELECT
    usename,
    backend_start as login_time,
    application_name,
    client_addr,
    state
FROM pg_stat_activity
ORDER BY backend_start DESC;

8️⃣ 실전 사용자 프로필 예시

🌐 웹 애플리케이션 사용자

CREATE ROLE webapp_user WITH LOGIN PASSWORD 'secure_password'
CONNECTION LIMIT 100;

-- 타임아웃 설정
ALTER ROLE webapp_user SET statement_timeout = '30s';
ALTER ROLE webapp_user SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE webapp_user SET idle_session_timeout = '30min';

-- 권한 설정
GRANT CONNECT ON DATABASE myapp TO webapp_user;
GRANT USAGE ON SCHEMA public TO webapp_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO webapp_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO webapp_user;

-- 삭제는 특정 테이블만
GRANT DELETE ON TABLE sessions, logs TO webapp_user;

📊 분석/리포팅 사용자

CREATE ROLE analyst_user WITH LOGIN PASSWORD 'secure_password'
CONNECTION LIMIT 5;

-- 긴 쿼리 허용
ALTER ROLE analyst_user SET statement_timeout = '10min';
ALTER ROLE analyst_user SET idle_session_timeout = '2h';

-- 읽기 전용
GRANT CONNECT ON DATABASE myapp TO analyst_user;
GRANT USAGE ON SCHEMA public TO analyst_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst_user;

-- 뷰 사용 권한
GRANT SELECT ON ALL VIEWS IN SCHEMA public TO analyst_user;

🔧 배치 작업 사용자

CREATE ROLE batch_user WITH LOGIN PASSWORD 'secure_password'
CONNECTION LIMIT 3;

-- 긴 트랜잭션 허용
ALTER ROLE batch_user SET statement_timeout = '1h';
ALTER ROLE batch_user SET idle_in_transaction_session_timeout = '30min';

-- 필요한 권한만
GRANT CONNECT ON DATABASE myapp TO batch_user;
GRANT USAGE ON SCHEMA public TO batch_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE batch_jobs, import_data TO batch_user;

👀 읽기 전용 모니터링 사용자

CREATE ROLE monitoring_user WITH LOGIN PASSWORD 'secure_password'
CONNECTION LIMIT 5;

-- 짧은 타임아웃
ALTER ROLE monitoring_user SET statement_timeout = '5s';
ALTER ROLE monitoring_user SET idle_session_timeout = '10min';

-- 시스템 뷰 접근 권한
GRANT pg_monitor TO monitoring_user;
GRANT CONNECT ON DATABASE postgres TO monitoring_user;

9️⃣ 보안 체크리스트

항목	설정	권장값
비밀번호 암호화	password_encryption	scram-sha-256
SSL/TLS	ssl	on
최소 TLS 버전	ssl_min_protocol_version	TLSv1.2
연결 로깅	log_connections	on
해제 로깅	log_disconnections	on
Statement 타임아웃	statement_timeout	사용자별 설정
유휴 세션 타임아웃	idle_session_timeout	30min
트랜잭션 유휴 타임아웃	idle_in_transaction_session_timeout	5min
연결 수 제한	CONNECTION LIMIT	역할별 설정
최소 권한 원칙	Role Privileges	필요한 권한만
Row Level Security	RLS	민감 데이터에 적용
감사 로깅	pgaudit	중요 작업 기록

🔍 유용한 모니터링 쿼리

현재 활성 세션 및 타임아웃 정보

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    NOW() - backend_start as connection_duration,
    NOW() - state_change as state_duration,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY backend_start;

사용자별 권한 확인

SELECT
    grantee,
    table_schema,
    table_name,
    string_agg(privilege_type, ', ') as privileges
FROM information_schema.role_table_grants
WHERE grantee = 'webapp_user'
GROUP BY grantee, table_schema, table_name
ORDER BY table_schema, table_name;

비밀번호 만료 확인

SELECT
    rolname,
    rolvaliduntil,
    CASE
        WHEN rolvaliduntil IS NULL THEN 'No expiration'
        WHEN rolvaliduntil > NOW() THEN 'Valid'
        ELSE 'Expired'
    END as status
FROM pg_roles
WHERE rolcanlogin = true
ORDER BY rolvaliduntil NULLS LAST;

✅ 결론

PostgreSQL의 사용자별 보안 설정을 제대로 구성하면:

1. 리소스 보호: 타임아웃과 연결 수 제한으로 리소스 고갈 방지
2. 최소 권한: 역할별로 필요한 권한만 부여하여 공격 표면 최소화
3. 감사 추적: 모든 접속과 작업을 로깅하여 보안 사고 대응
4. 세밀한 제어: RLS로 행 단위까지 접근 제어
5. 계층적 보안: 네트워크, 인증, 권한, 데이터 레벨까지 다층 방어

보안은 한 번 설정하고 끝이 아닙니다. 정기적으로 권한을 검토하고, 로그를 모니터링하며, 불필요한 권한은 즉시 회수하세요.