PostgreSQL DBA 카탈로그/뷰 치트시트 — pg_user, pg_roles부터 실무 점검 스크립트까지

PostgreSQL 17 기준으로 계정/권한/세션/잠금/복제/Autovacuum/설정을 점검할 때 자주 쓰는 시스템 카탈로그와 통계 뷰, 그리고 실무용 SQL 스니펫을 한 번에 모았습니다. (PaaS에서도 대부분 그대로 동작)

용어 요약: 카탈로그(catalog) = pg_로 시작하는 시스템 테이블/뷰. 보통 SELECT만으로 진단 가능.

---

0) 계정/역할 기본 개념

• PostgreSQL의 사용자(User) 개념 = 역할(Role). 로그인 가능한 역할과 로그인 불가한 역할(그룹 역할)을 조합해 씀.
• 대표 속성: LOGIN, SUPERUSER, CREATEDB, CREATEROLE, REPLICATION, BYPASSRLS, INHERIT, CONNECTION LIMIT, VALID UNTIL(만료), rolpassword(SCRAM)

---

1) 계정/역할: pg_user, pg_roles, pg_authid, pg_auth_members

• pg_user: 호환성 뷰(읽기 전용). 로그인 가능한 역할 위주 표시.
• pg_roles: 모든 역할(로그인/비로그인) 표시. 실무에선 이걸 기본으로 사용.
• pg_authid: 내부 카탈로그(슈퍼유저만 접근). 비밀번호 해시 포함.
• pg_auth_members: 역할-멤버십 매핑(누가 어떤 역할의 멤버인지).

1-1) 모든 역할과 주요 속성

SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolreplication,
       rolbypassrls, rolinherit, rolcanlogin, rolconnlimit, rolvaliduntil
FROM pg_roles ORDER BY rolname;

1-2) 멤버십(그룹 역할 포함) — 누가 어떤 역할의 멤버인가

SELECT r.rolname     AS role,
       m.rolname     AS member,
       a.admin_option
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
LEFT JOIN LATERAL (SELECT am.admin_option) a ON TRUE
ORDER BY role, member;

1-3) 로그인 가능한 계정만

SELECT rolname, rolvaliduntil, rolconnlimit
FROM pg_roles
WHERE rolcanlogin
ORDER BY rolname;

1-4) 곧 만료(VALID UNTIL) 예정 계정

SELECT rolname, rolvaliduntil
FROM pg_roles
WHERE rolcanlogin
  AND rolvaliduntil IS NOT NULL
  AND rolvaliduntil < now() + interval '14 days'
ORDER BY rolvaliduntil;

---

2) 권한/오브젝트: 스키마·테이블·시퀀스·기본권한

• pg_namespace(스키마), pg_class(테이블/뷰/인덱스/시퀀스), pg_attribute(컬럼), pg_default_acl(기본 권한)
• 헬퍼 함수: has_schema_privilege, has_table_privilege, has_sequence_privilege

2-1) 특정 스키마에 대한 USAGE/CREATE 보유자 찾기

SELECT n.nspname, r.rolname,
       has_schema_privilege(r.rolname, n.nspname, 'USAGE')  AS usage,
       has_schema_privilege(r.rolname, n.nspname, 'CREATE') AS create
FROM pg_namespace n
CROSS JOIN pg_roles r
WHERE n.nspname NOT LIKE 'pg_%' AND n.nspname <> 'information_schema'
  AND r.rolcanlogin
ORDER BY n.nspname, r.rolname;

2-2) 테이블 권한 매트릭스(SELECT/INSERT/UPDATE/DELETE)

SELECT n.nspname AS schema, c.relname AS table, r.rolname AS grantee,
       has_table_privilege(r.oid, c.oid, 'SELECT') AS sel,
       has_table_privilege(r.oid, c.oid, 'INSERT') AS ins,
       has_table_privilege(r.oid, c.oid, 'UPDATE') AS upd,
       has_table_privilege(r.oid, c.oid, 'DELETE') AS del
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN pg_roles r
WHERE c.relkind IN ('r','p','v','m') -- 테이블/파티션/뷰/머터리얼라이즈드뷰
  AND n.nspname NOT LIKE 'pg_%' AND n.nspname <> 'information_schema'
  AND r.rolcanlogin
ORDER BY 1,2,3;

2-3) 기본 권한(ALTER DEFAULT PRIVILEGES) 확인

SELECT defaclrole::regrole AS owner, defaclnamespace::regnamespace AS nsp,
       defaclobjtype, defaclacl
FROM pg_default_acl
ORDER BY owner, nsp, defaclobjtype;

---

3) 세션/활동/잠금: pg_stat_activity, pg_locks

• pg_stat_activity: 현재 세션/쿼리/상태/대기
• pg_locks: 대상 오브젝트에 대한 잠금 현황

3-1) 오래 실행 중인 쿼리(예: 5분 초과)

SELECT pid, usename, datname, state, now() - query_start AS duration,
       wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND query_start < now() - interval '5 minutes'
ORDER BY duration DESC;

3-2) 블로킹/블록드 세션 찾기

WITH locks AS (
  SELECT pid, locktype, relation::regclass AS rel, mode, granted
  FROM pg_locks
), act AS (
  SELECT pid, usename, datname, state, query
  FROM pg_stat_activity
)
SELECT b.pid   AS blocked_pid, b_us.usename AS blocked_user, b_us.query AS blocked_query,
       a.pid   AS blocker_pid, a_us.usename AS blocker_user, a_us.query AS blocker_query,
       l.rel, l.mode
FROM pg_locks bl
JOIN pg_locks l ON (bl.locktype = l.locktype AND bl.locktype <> 'advisory' AND bl.relation = l.relation AND bl.pid <> l.pid)
JOIN pg_stat_activity b_us ON b_us.pid = bl.pid
JOIN pg_stat_activity a_us ON a_us.pid = l.pid
JOIN act a ON a.pid = l.pid
JOIN act b ON b.pid = bl.pid
WHERE NOT bl.granted AND l.granted
ORDER BY b.pid;

3-3) Idle in transaction(장기 유휴 트랜잭션)

SELECT pid, usename, datname, now() - xact_start AS xact_age, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_age DESC;

---

4) 통계/Autovacuum/인덱스: pg_stat_all_tables, pg_stat_user_indexes, pg_stat_progress_*

• pg_stat_all_tables: 테이블별 스캔/튜플 통계, Autovacuum/Analyze 횟수
• pg_stat_user_indexes: 인덱스 사용 통계(hit/scan)
• pg_stat_progress_vacuum, pg_stat_progress_create_index: 진행 중 작업의 실시간 상태

4-1) Autovacuum가 덜 도는(후보) 테이블 찾기

SELECT schemaname, relname, n_dead_tup, vacuum_count, autovacuum_count,
       now() - last_autovacuum AS since_last_auto
FROM pg_stat_all_tables
WHERE schemaname NOT LIKE 'pg_%'
ORDER BY n_dead_tup DESC
LIMIT 50;

4-2) 사용되지 않는 인덱스 후보

SELECT n.nspname, c.relname AS index, idx.idx_scan
FROM pg_stat_user_indexes idx
JOIN pg_class c ON c.oid = idx.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE idx.idx_scan = 0
ORDER BY n.nspname, c.relname;

4-3) 진행 중 VACUUM/인덱스 생성 확인

SELECT * FROM pg_stat_progress_vacuum;
SELECT * FROM pg_stat_progress_create_index;

---

5) 설정/파일/접속 제어: pg_settings, pg_file_settings, pg_hba_file_rules

• pg_settings: 현재 파라미터 값
• pg_file_settings: postgresql.conf 파싱 결과(적용/오류 여부 포함)
• pg_hba_file_rules: HBA 규칙 파싱 결과(매치 순서/주석 포함)

SELECT name, setting, unit, context, source
FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','maintenance_work_mem','effective_cache_size','random_page_cost','max_connections');

SELECT * FROM pg_file_settings WHERE error IS NOT NULL;  -- 설정 오류 진단

SELECT * FROM pg_hba_file_rules ORDER BY line_number;    -- 접속 규칙 확인

---

6) 확장/파티셔닝/정책: pg_extension, pg_available_extensions, pg_partitions, pg_policies

• pg_extension: 설치된 확장(예: pg_stat_statements, uuid-ossp, pgvector)
• pg_available_extensions: 설치 가능 목록
• pg_policies: RLS(Row-Level Security) 정책

SELECT extname, extversion, extnamespace::regnamespace AS nsp
FROM pg_extension ORDER BY extname;

SELECT * FROM pg_available_extensions ORDER BY name;

SELECT * FROM pg_policies ORDER BY schemaname, tablename;

---

7) 복제/논리 슬롯/아카이브: pg_stat_replication, pg_replication_slots, pg_stat_wal

• pg_stat_replication: 물리 복제 상태/지연
• pg_replication_slots: 논리/물리 슬롯 상태(초과 적체 시 WAL 누적 위험)
• pg_stat_wal: WAL 생성량 등

SELECT pid, application_name, state, sync_state,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

SELECT slot_name, plugin, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

SELECT * FROM pg_stat_wal;  -- 초당 WAL 증가량 모니터링

---

8) 쿼리 성능/탑SQL: pg_stat_statements (확장)

먼저 확장 설치 후 사용: CREATE EXTENSION pg_stat_statements;

SELECT query, calls, total_exec_time, mean_exec_time, rows,
       shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

---

9) 보안 점검 빠른 체크리스트

• SUPERUSER/REPLICATION 보유 계정 최소화 (pg_roles)
• 만료 예정 계정/미사용 계정 파악 (rolvaliduntil, 접속 로그)
• 비밀번호 정책(password_encryption=scram-sha-256) 확인 (pg_settings)
• 스키마/테이블 권한 과다 배포 여부 (has_*_privilege 매트릭스)
• RLS 정책 적용 대상 점검 (pg_policies)
• 논리 슬롯 방치로 인한 WAL 적체 (pg_replication_slots)

---

10) 운영 팁

• 역할 모델링: 로그인 역할 ↔ 권한(그룹) 역할 분리. 애플리케이션은 그룹 역할에만 GRANT.
• DEFAULT PRIVILEGES로 신규 객체 권한 표준화.
• search_path 고정: 애플리케이션 계정의 search_path를 서비스 스키마로 지정.
• 장기 트랜잭션 금지: idle in transaction 점검 배치 상시화.
• 인덱스 위생: 스캔 0 인덱스 주기 점검, 파티션/Autovacuum 현황 모니터.