9.5 SSL/TLS와 SCRAM

SELECT rolname, LEFT(rolpassword, 14) AS kind
  FROM pg_authid WHERE rolcanlogin;
-- kind = 'SCRAM-SHA-256' 또는 'md5...'

ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

-- 각 사용자의 비밀번호를 다시 설정 (이때 SCRAM으로 저장됨)
ALTER ROLE alice PASSWORD 'new_secret';

# pg_hba.conf
hostssl  app_main  all  10.0.0.0/8  scram-sha-256

psql 'postgresql://user@host/db?sslmode=verify-full&channel_binding=require'

# 1. CA (조직의 root)
openssl req -new -x509 -days 3650 -nodes \
  -out ca.crt -keyout ca.key \
  -subj "/CN=My PG CA"

# 2. 서버 키·CSR
openssl req -new -nodes \
  -out server.csr -keyout server.key \
  -subj "/CN=db.example.com"

# 3. CA로 서버 인증서 서명
openssl x509 -req -in server.csr -days 365 \
  -CA ca.crt -CAkey ca.key -CAcreateserial \
  -out server.crt

# 4. 파일 권한 (0600 — 안 그러면 PG가 거부)
chmod 0600 server.key
chown postgres:postgres server.{crt,key} ca.crt

ssl = on
ssl_cert_file     = '/etc/postgresql/ssl/server.crt'
ssl_key_file      = '/etc/postgresql/ssl/server.key'
ssl_ca_file       = '/etc/postgresql/ssl/ca.crt'           # 클라이언트 인증서 검증 시
ssl_min_protocol_version = 'TLSv1.2'                        # 또는 TLSv1.3
ssl_ciphers       = 'HIGH:!aNULL:!MD5:!RC4'                 # 안전한 cipher만

SHOW ssl;          -- on

psql 'postgresql://app@db.example.com/app?sslmode=verify-full&sslrootcert=/etc/ssl/ca.crt'

# 클라이언트 인증서 발급 (CN = DB 사용자명)
openssl req -new -nodes -out alice.csr -keyout alice.key \
  -subj "/CN=alice"
openssl x509 -req -in alice.csr -days 365 \
  -CA ca.crt -CAkey ca.key -CAcreateserial \
  -out alice.crt

hostssl  all  all  10.0.0.0/8  cert  clientcert=verify-full

psql 'postgresql://alice@db.example.com/app?sslmode=verify-full&sslcert=/etc/ssl/alice.crt&sslkey=/etc/ssl/alice.key&sslrootcert=/etc/ssl/ca.crt'

# 인증서 파일 교체
sudo cp /tmp/new_server.crt /etc/postgresql/ssl/server.crt
sudo systemctl reload postgresql-17

# 새 연결로 검증
psql ... # 새 인증서로 접속됨

SHOW ssl;
-- 현재 세션의 SSL 사용 여부
SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid();

-- 비-SSL 접속 추적
SELECT count(*) FROM pg_stat_ssl WHERE ssl IS NOT true;