-- pg_hba.conf 현재 설정 내용 확인 (PostgreSQL 10 이상)
SELECT * FROM pg_hba_file_rules;

-- pg_hba.conf 파일 경로 확인
SHOW hba_file;

# /etc/postgresql/15/main/pg_hba.conf 예시

# 특정 IP에서 특정 DB/유저 접속 허용 (scram-sha-256 인증)
host    mydb        myuser      192.168.1.100/32    scram-sha-256

# 특정 서브넷 전체 허용
host    all         all         10.0.0.0/8          scram-sha-256

# 로컬 소켓 접속 허용
local   all         all                             peer

-- PostgreSQL 내부에서 설정 리로드
SELECT pg_reload_conf();

# OS 레벨에서 리로드
sudo systemctl reload postgresql
# 또는
sudo pg_ctlcluster 15 main reload

# PostgreSQL 포트 리스닝 여부 확인
ss -tlnp | grep 5432
# 또는
netstat -tlnp | grep 5432

# iptables 방화벽 5432 포트 허용 (Linux)
sudo iptables -A INPUT -p tcp --dport 5432 -s 192.168.1.0/24 -j ACCEPT
sudo iptables-save > /etc/iptables/rules.v4

-- 현재 접속 중인 클라이언트 IP 및 상태 확인
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    client_port,
    state,
    backend_start
FROM pg_stat_activity
WHERE state IS NOT NULL
ORDER BY backend_start DESC;

-- 클라이언트 IP별 연결 수 집계
SELECT 
    client_addr,
    count(*) AS connection_count
FROM pg_stat_activity
WHERE client_addr IS NOT NULL
GROUP BY client_addr
ORDER BY connection_count DESC;

-- 현재 listen_addresses 및 max_connections 설정 확인
SHOW listen_addresses;
SHOW max_connections;

-- 현재 사용 중인 연결 수 vs 최대 연결 수 비교
SELECT 
    count(*) AS current_connections,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') - count(*) AS available_connections
FROM pg_stat_activity;

# /etc/postgresql/15/main/postgresql.conf

# 모든 IP에서 접속 허용 (보안 그룹/방화벽으로 별도 제어)
listen_addresses = '*'

# 최대 연결 수 조정 (서버 메모리에 따라 조정 필요)
max_connections = 200

# 슈퍼유저 전용 예약 연결 (긴급 접속용)
superuser_reserved_connections = 3

-- 재시작 없이 적용 가능한 항목 vs 재시작 필요 항목 확인
SELECT 
    name,
    setting,
    context
FROM pg_settings
WHERE name IN ('listen_addresses', 'max_connections', 'superuser_reserved_connections');
-- context = 'postmaster' 이면 재시작 필요
-- context = 'sighup' 이면 reload만으로 적용 가능

-- 유휴 상태 연결 강제 종료 (긴급 시 사용)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < now() - interval '10 minutes'
  AND usename != 'postgres';

# /etc/pgbouncer/pgbouncer.ini 핵심 설정
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = scram-sha-256
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

-- 모니터링: 현재 연결 수 임계치 알림용 쿼리 (모니터링 시스템에 등록)
SELECT 
    CASE 
        WHEN count(*) > (SELECT setting::int * 0.8 FROM pg_settings WHERE name = 'max_connections')
        THEN 'WARNING: 연결 수 80% 초과'
        ELSE 'NORMAL'
    END AS connection_status,
    count(*) AS current_conn
FROM pg_stat_activity;

-- 설정 리로드 전 pg_hba.conf 파싱 오류 확인
SELECT * FROM pg_hba_file_rules
WHERE error IS NOT NULL;

-- 설정 파일 변경 이력 관리 (변경 시 코멘트 추가 권장)
-- 예: # 2024-01-15 홍길동 - 신규 앱서버 IP 추가 (192.168.10.50)