xDev Asia

SSL/TLS、認証方法、ネットワークセキュリティ、保存時の暗号化、監査ログ、およびクラスターセキュリティの強化を構成します。

Patroni と PostgreSQL の高可用性etcd

パート 5: セキュリティと上級_

xdev.asia_

目標_

このレッスンの後、次のことを行います:

PostgreSQL に SSL/TLS 暗号化を実装する_
安全な認証を構成する
ネットワークを強化するセキュリティ
保存時の暗号化を有効にする_
ログ監査を設定
HA クラスターにセキュリティのベストプラクティスを適用

1。 SSL/TLS 暗号化

1.1。 SSL 証明書を生成_

# Create certificate directory
sudo mkdir -p /etc/postgresql/ssl
cd /etc/postgresql/ssl
Generate CA private key
sudo openssl genrsa -out ca-key.pem 4096
Generate CA certificate
sudo openssl req -new -x509 -days 3650 -key ca-key.pem -out ca-cert.pem 

-subj "/CN=PostgreSQL-CA/O=MyOrg/C=US"
Generate server private key
sudo openssl genrsa -out server-key.pem 4096
Generate server certificate signing request
sudo openssl req -new -key server-key.pem -out server-req.pem 

-subj "/CN=postgres.example.com/O=MyOrg/C=US"
Sign server certificate
sudo openssl x509 -req -in server-req.pem -days 365 

-CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial 

-out server-cert.pem
Set permissions
sudo chmod 600 server-key.pem
sudo chown postgres:postgres /etc/postgresql/ssl/*

1.2。 PostgreSQL を SSL_

-- Enable SSL
ALTER SYSTEM SET ssl = on;
ALTER SYSTEM SET ssl_cert_file = '/etc/postgresql/ssl/server-cert.pem';
ALTER SYSTEM SET ssl_key_file = '/etc/postgresql/ssl/server-key.pem';
ALTER SYSTEM SET ssl_ca_file = '/etc/postgresql/ssl/ca-cert.pem';
-- SSL ciphers (strong only)
ALTER SYSTEM SET ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL';
ALTER SYSTEM SET ssl_prefer_server_ciphers = on;
ALTER SYSTEM SET ssl_min_protocol_version = 'TLSv1.2';
-- Restart required

sudo systemctl restart patroni

1.3 用に構成します。 pg_hba.conf を SSL

# /var/lib/postgresql/18/data/pg_hba.conf
Require SSL for all connections
hostssl all all 0.0.0.0/0 scram-sha-256
Or mixed (SSL preferred but not required)
hostssl all all 0.0.0.0/0 scram-sha-256
host    all all 0.0.0.0/0 scram-sha-256
Reload
sudo -u postgres psql -c "SELECT pg_reload_conf();"

1.4 用に構成します。 SSL 接続をテスト

# Connect with SSL
psql "host=10.0.1.11 port=5432 dbname=postgres user=postgres sslmode=require"
Verify SSL
psql -h 10.0.1.11 -U postgres -c "SELECT ssl_is_used();"
ssl_is_used
------------
t          ← true = SSL enabled ✅
Check SSL cipher
psql -h 10.0.1.11 -U postgres -c "
SELECT pid, usename, ssl, client_addr, backend_type
FROM pg_stat_ssl
JOIN pg_stat_activity USING (pid);
"

1.5。クライアント証明書の認証

# Generate client certificate sudo openssl genrsa -out client-key.pem 4096 sudo openssl req -new -key client-key.pem -out client-req.pem -subj "/CN=app_user/O=MyOrg/C=US" sudo openssl x509 -req -in client-req.pem -days 365 -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -out client-cert.pem Copy to client machine

scp client-*.pem app_user@app-server:~/.postgresql/

# pg_hba.conf - require client cert
hostssl all all 0.0.0.0/0 cert
Connect with client cert
psql "host=10.0.1.11 dbname=postgres user=app_user 

sslmode=verify-full 

sslcert=/.postgresql/client-cert.pem 

sslkey=/.postgresql/client-key.pem 

sslrootcert=~/.postgresql/ca-cert.pem"

2。認証_

2.1。 SCRAM-SHA-256 (推奨)

-- Set password encryption method ALTER SYSTEM SET password_encryption = 'scram-sha-256'; -- Create user with SCRAM CREATE USER app_user WITH PASSWORD 'strong_password';

-- Update existing user password \password existing_user -- Will use SCRAM

# pg_hba.conf
host all all 0.0.0.0/0 scram-sha-256

2.2。弱い認証

# DON'T USE: host all all 0.0.0.0/0 trust ❌ No password! host all all 0.0.0.0/0 md5 ❌ Weak encryption USE: hostssl all all 0.0.0.0/0 scram-sha-256 ✅ hostssl all all 0.0.0.0/0 cert ✅

2.3 を無効にします。パスワードポリシー

-- Install passwordcheck extension CREATE EXTENSION IF NOT EXISTS passwordcheck; -- Configure in postgresql.conf ALTER SYSTEM SET passwordcheck.min_length = 12; ALTER SYSTEM SET passwordcheck.require_special = on; ALTER SYSTEM SET passwordcheck.require_digit = on; ALTER SYSTEM SET passwordcheck.require_upper = on;

-- Test - weak password will be rejected CREATE USER weak_user WITH PASSWORD '12345'; -- ERROR CREATE USER strong_user WITH PASSWORD 'MyStr0ng!Pass123'; -- OK

2.4。ロールベースのアクセス制御_

-- Create roles
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin;
-- Grant privileges
GRANT CONNECT ON DATABASE myapp TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT CONNECT ON DATABASE myapp TO readwrite;
GRANT USAGE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
-- Create users with roles
CREATE USER app_readonly WITH PASSWORD 'pass123';
GRANT readonly TO app_readonly;
CREATE USER app_readwrite WITH PASSWORD 'pass456';
GRANT readwrite TO app_readwrite;
-- Default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

3。ネットワークセキュリティ_

3.1。ファイアウォール構成

# Using ufw sudo ufw default deny incoming sudo ufw default allow outgoing Allow SSH sudo ufw allow 22/tcp Allow PostgreSQL only from specific IPs sudo ufw allow from 10.0.1.0/24 to any port 5432 proto tcp Allow Patroni REST API (internal only) sudo ufw allow from 10.0.1.0/24 to any port 8008 proto tcp Allow etcd (internal only) sudo ufw allow from 10.0.1.0/24 to any port 2379 proto tcp sudo ufw allow from 10.0.1.0/24 to any port 2380 proto tcp Enable firewall

sudo ufw enable sudo ufw status

# Using iptables Allow PostgreSQL from specific subnet sudo iptables -A INPUT -p tcp -s 10.0.1.0/24 --dport 5432 -j ACCEPT sudo iptables -A INPUT -p tcp --dport 5432 -j DROP Allow Patroni REST API (internal) sudo iptables -A INPUT -p tcp -s 10.0.1.0/24 --dport 8008 -j ACCEPT sudo iptables -A INPUT -p tcp --dport 8008 -j DROP Save rules

sudo iptables-save > /etc/iptables/rules.v4

3.2。ネットワークセグメンテーション

Architecture:
Internet
↓
[Load Balancer / Application Layer]
↓ (Private Network 10.0.1.0/24)
[PostgreSQL HA Cluster]
node1: 10.0.1.11
node2: 10.0.1.12
node3: 10.0.1.13
Security Rules:

PostgreSQL port 5432: Only accessible from app servers
Patroni port 8008: Internal cluster only
etcd ports 2379/2380: Internal cluster only
SSH port 22: Bastion host only

3.3。 VPN/プライベートネットワーク

# Use WireGuard for inter-node communication

Install WireGuard
sudo apt-get install -y wireguard
Generate keys
wg genkey | tee privatekey | wg pubkey > publickey
Configure /etc/wireguard/wg0.conf
[Interface]
PrivateKey = <private_key>
Address = 10.100.0.1/24
ListenPort = 51820
[Peer]
PublicKey = <peer_public_key>
AllowedIPs = 10.100.0.2/32
Endpoint = 203.0.113.2:51820
Start WireGuard
sudo wg-quick up wg0
sudo systemctl enable wg-quick@wg0

4。保存時の暗号化_

4.1。ファイルシステム暗号化 (LUKS)

# Encrypt data volume sudo cryptsetup luksFormat /dev/sdb sudo cryptsetup luksOpen /dev/sdb pgdata_encrypted Create filesystem sudo mkfs.ext4 /dev/mapper/pgdata_encrypted Mount sudo mkdir -p /var/lib/postgresql/encrypted sudo mount /dev/mapper/pgdata_encrypted /var/lib/postgresql/encrypted Auto-mount on boot (/etc/crypttab) pgdata_encrypted /dev/sdb none luks /etc/fstab

/dev/mapper/pgdata_encrypted /var/lib/postgresql/encrypted ext4 defaults 0 2

4.2。透過的データ暗号化 (TDE)

Note: PostgreSQL 18 doesn't have built-in TDE
Options:

Use filesystem encryption (LUKS) ✅
Use pgcrypto extension for column-level encryption
Wait for future PostgreSQL TDE support
Use commercial solutions (EDB, Cybertec)

4.3。列レベルの暗号化_

-- Install pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt data
CREATE TABLE sensitive_data (
id SERIAL PRIMARY KEY,
name TEXT,
ssn BYTEA  -- Encrypted
);
-- Insert encrypted data
INSERT INTO sensitive_data (name, ssn)
VALUES ('John Doe', pgp_sym_encrypt('123-45-6789', 'encryption_key'));
-- Decrypt data
SELECT id, name,
pgp_sym_decrypt(ssn, 'encryption_key') AS ssn
FROM sensitive_data;
-- Better: Store encryption key in vault (HashiCorp Vault)

5。監査ログ_

5.1。 pgAudit 拡張機能_

# Install pgAudit sudo apt-get install -y postgresql-18-pgaudit Or compile from source

cd /tmp git clone https://github.com/pgaudit/pgaudit.git cd pgaudit make install USE_PGXS=1

-- Enable pgAudit ALTER SYSTEM SET shared_preload_libraries = 'pgaudit';

-- Restart required

sudo systemctl restart patroni

-- Create extension
CREATE EXTENSION IF NOT EXISTS pgaudit;
-- Configure audit logging
ALTER SYSTEM SET pgaudit.log = 'write, ddl, role';
ALTER SYSTEM SET pgaudit.log_catalog = off;
ALTER SYSTEM SET pgaudit.log_client = on;
ALTER SYSTEM SET pgaudit.log_level = 'log';
ALTER SYSTEM SET pgaudit.log_parameter = on;
ALTER SYSTEM SET pgaudit.log_relation = on;
ALTER SYSTEM SET pgaudit.log_statement_once = off;
-- Reload
SELECT pg_reload_conf();

Auditクラス_:

READ: SELECT、COPY FROM
WRITE: INSERT、UPDATE、DELETE、TRUNCATE、 COPY TO_
FUNCTION: 関数呼び出し
ROLE: GRANT、REVOKE、CREATE/DROP ROLE
DDL: 作成、変更、ドロップ
MISC: 破棄、フェッチ、チェックポイント、バキューム、 SET
ALL: すべて

5.2。ログ分析

# Audit logs in PostgreSQL log files
sudo grep "AUDIT:" /var/lib/postgresql/18/data/log/postgresql-*.log
Example audit log entry:
2024-11-25 10:00:00 UTC [12345]: [1-1] user=admin,db=myapp AUDIT: SESSION,1,1,WRITE,DELETE,TABLE,public.users,"DELETE FROM users WHERE id = 123",<not logged>

5.3。一元化されたログ_

# Use filebeat to ship logs to ELK stack /etc/filebeat/filebeat.yml filebeat.inputs: type: log enabled: true paths: /var/lib/postgresql/18/data/log/*.log fields: type: postgresql cluster: postgres-ha node: node1

output.elasticsearch: hosts: ["elasticsearch:9200"] index: "postgresql-%{+yyyy.MM.dd}"

6。セキュリティ強化

6.1。不要な機能を無効にする_

-- Disable file system access functions (for non-superusers)
REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM PUBLIC;
-- Disable COPY TO/FROM file
-- (Use COPY TO/FROM PROGRAM or stdin/stdout instead)

6.2。スーパーユーザーのアクセスを制限

-- Create admin role with specific privileges instead of superuser
CREATE ROLE db_admin WITH LOGIN PASSWORD 'strong_password';
GRANT ALL PRIVILEGES ON DATABASE myapp TO db_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO db_admin;
-- Don't use 'postgres' superuser for applications!

6.3。リソース制限_

-- Connection limits per user ALTER USER app_user CONNECTION LIMIT 50; -- Statement timeout per user ALTER USER app_user SET statement_timeout = '30s'; -- Lock timeout ALTER USER app_user SET lock_timeout = '5s';

-- Idle in transaction timeout ALTER USER app_user SET idle_in_transaction_session_timeout = '5min';

6.4。 PostgreSQL バージョンを非表示_

-- Change server_version_num (cosmetic security)
-- Note: Doesn't actually hide from determined attackers
ALTER SYSTEM SET application_name = 'myapp';

6.5。安全なレプリケーション

# In patroni.yml postgresql: authentication: replication: username: replicator password: !vault | # Use Ansible Vault $ANSIBLE_VAULT;1.1;AES256 ...encrypted... superuser: username: postgres password: !vault | $ANSIBLE_VAULT;1.1;AES256 ...encrypted...

7。コンプライアンスと標準

7.1。 PCI DSS 準拠_

Requirements for PostgreSQL:
✅ Encryption in transit (SSL/TLS)
✅ Encryption at rest (LUKS)
✅ Strong authentication (SCRAM-SHA-256)
✅ Access control (RBAC)
✅ Audit logging (pgAudit)
✅ Regular updates and patches
✅ Network segmentation
✅ Principle of least privilege

-- Data anonymization
CREATE TABLE users_anonymized AS
SELECT id,
md5(name) AS name,
md5(email) AS email,
age,
country
FROM users;
-- Right to be forgotten
DELETE FROM users WHERE id = 12345;
DELETE FROM user_logs WHERE user_id = 12345;
DELETE FROM user_sessions WHERE user_id = 12345;

7.3。 HIPAA 準拠

Requirements:
✅ PHI encryption at rest and in transit
✅ Access controls and authentication
✅ Audit logs (who accessed what, when)
✅ Automatic logoff (idle_in_transaction_session_timeout)
✅ Unique user identification
✅ Emergency access procedures
✅ Data backup and recovery

8。セキュリティ監視_

-- Query for failed authentication
SELECT datname, usename, client_addr,
COUNT() as failed_attempts
FROM pg_stat_database_conflicts
GROUP BY datname, usename, client_addr
HAVING COUNT() > 10;
-- Or from logs
-- sudo grep "authentication failed" /var/lib/postgresql/18/data/log/*.log

8.2。権限昇格を監視

-- Track role changes
SELECT * FROM pg_stat_activity
WHERE query ILIKE '%GRANT%' OR query ILIKE '%REVOKE%';
-- Audit log will capture these with pgAudit

8.3。セキュリティイベントに関するアラート_

# Prometheus alert rules
groups:

name: security
rules:


alert: FailedAuthentication
expr: increase(pg_stat_database_conflicts_total[5m]) > 10
labels:
severity: warning
annotations:
summary: "Multiple failed authentication attempts"


alert: UnencryptedConnection
expr: pg_stat_ssl_count{ssl="false"} > 0
labels:
severity: warning
annotations:
summary: "Unencrypted connection detected"

9。ベストプラクティスの概要

✅ DO

常に SSL/TLS を使用 - すべての接続を暗号化
使用するSCRAM-SHA-256 - 強力なパスワード暗号化
RBAC の実装 - 最小権限の原則
監査を有効にするロギング - コンプライアンスのための pgAudit
保存時の暗号化 - データ量の LUKS
HTMLTAG_217___強力なパスワード - 12 文字以上、複雑さ
ネットワークセグメンテーション - ファイアウォールルール_
定期的なセキュリティ監査 - 四半期ごとレビュー_
ソフトウェアを最新の状態に保つ - セキュリティパッチ
セキュリティイベントを監視 - アラートとロギング

❌ 禁止_

「信頼」認証を使用しない_ - 常に必要パスワード_
インターネットに公開しない - プライベートネットワークを使用
デフォルトのパスワードを使用しない_ - 変更すぐに_
スーパーユーザーを使用しない - 特定の権限を使用する
更新を無視しない - セキュリティ脆弱性_
パスワードをプレーンのまま保存しない - シークレット管理を使用する_
バックアップをスキップしない - ランサムウェア保護_
etcd セキュリティを忘れないでください - クラスターシークレット_

10 があります。セキュリティチェックリスト

Network Layer: ☐ Firewall configured (only necessary ports) ☐ Private network for inter-node communication ☐ VPN for remote access ☐ DDoS protection Authentication: ☐ SSL/TLS enabled ☐ SCRAM-SHA-256 password encryption ☐ Client certificate authentication (optional) ☐ Strong password policy ☐ No default passwords ☐ Separate users for different applications Authorization: ☐ RBAC implemented ☐ Least privilege principle ☐ Superuser access limited ☐ Resource limits set Encryption: ☐ SSL/TLS for connections ☐ File system encryption (LUKS) ☐ Column-level encryption for sensitive data ☐ Backup encryption Auditing: ☐ pgAudit enabled ☐ Audit logs centralized ☐ Log retention policy ☐ Regular log reviews Monitoring: ☐ Failed login attempts tracked ☐ Security alerts configured ☐ Anomaly detection ☐ Regular vulnerability scans

Maintenance: ☐ Regular security updates ☐ Patch management process ☐ Quarterly security audits ☐ Incident response plan ☐ Disaster recovery tested

11。ラボ演習

ラボ 1: SSL/TLS のセットアップ

タスク:

SSL の生成証明書_
SSL 用に PostgreSQL を構成
SSL を要求するように pg_hba.conf を更新
SSL 接続をテスト
pg_stat_ssl で検証

ラボ 2: 実装RBAC_

タスク:

読み取り専用ロールと読み取り書き込みロールの作成
異なるロールを持つユーザーの作成_
テストアクセス権限_
権限の付与/取り消し_
ドキュメントの役割階層_

ラボ 3: 監査を有効にするログ

タスク:

pgAudit のインストールと構成
DDL と監査ログの有効化書き込み_
監査された操作の実行
監査ログの確認_
ログ集約との統合_

ラボ 4: セキュリティ強化

タスク:

ファイアウォールルールの構成_
不要な機能の無効化
リソースの設定制限
セキュリティの改善をテスト
セキュリティ体制を文書化

12。概要_

セキュリティレイヤー_

1. Network: Firewall, VPN, segmentation
2. Authentication: SCRAM-SHA-256, SSL certificates
3. Authorization: RBAC, least privilege
4. Encryption: SSL/TLS, LUKS, pgcrypto
5. Auditing: pgAudit, centralized logging
6. Monitoring: Alerts, anomaly detection

重要なセキュリティ設定_

-- SSL
ssl = on
-- Authentication
password_encryption = 'scram-sha-256'
-- Audit
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl, role'
-- Limits
statement_timeout = '30s'
idle_in_transaction_session_timeout = '5min'

次のステップ

レッスン 21 では、表紙 マルチデータセンターのセットアップ:

DC 間レプリケーション戦略
カスケードレプリケーション
災害復旧計画
地理的負荷分散
ネットワーク遅延に関する考慮事項

レッスン 20: セキュリティのベスト プラクティス

目標_

1。 SSL/TLS 暗号化

1.1。 SSL 証明書を生成_

Generate CA private key

Generate CA certificate

Generate server private key

Generate server certificate signing request

Sign server certificate

Set permissions

1.2。 PostgreSQL を SSL_

1.3 用に構成します。 pg_hba.conf を SSL

Require SSL for all connections

Or mixed (SSL preferred but not required)

Reload

1.4 用に構成します。 SSL 接続をテスト

Verify SSL

ssl_is_used

------------

t ← true = SSL enabled ✅

Check SSL cipher

1.5。クライアント証明書の認証

Copy to client machine

Connect with client cert

2。認証_

2.1。 SCRAM-SHA-256 (推奨)

2.2。弱い認証

host all all 0.0.0.0/0 trust ❌ No password!

host all all 0.0.0.0/0 md5 ❌ Weak encryption

USE:

hostssl all all 0.0.0.0/0 scram-sha-256 ✅

hostssl all all 0.0.0.0/0 cert ✅

2.3 を無効にします。パスワード ポリシー

2.4。ロールベースのアクセス制御_

3。ネットワーク セキュリティ_

3.1。ファイアウォール構成

Allow SSH

Allow PostgreSQL only from specific IPs

Allow Patroni REST API (internal only)

Allow etcd (internal only)

Enable firewall

Allow PostgreSQL from specific subnet

Allow Patroni REST API (internal)

Save rules

3.2。ネットワーク セグメンテーション

3.3。 VPN/プライベート ネットワーク

Install WireGuard

Generate keys

Configure /etc/wireguard/wg0.conf

Start WireGuard

4。保存時の暗号化_

4.1。ファイル システム暗号化 (LUKS)

Create filesystem

Mount

Auto-mount on boot (/etc/crypttab)

/etc/fstab

4.2。透過的データ暗号化 (TDE)

4.3。列レベルの暗号化_

5。監査ログ_

5.1。 pgAudit 拡張機能_

Or compile from source

5.2。ログ分析

Example audit log entry:

2024-11-25 10:00:00 UTC [12345]: [1-1] user=admin,db=myapp AUDIT: SESSION,1,1,WRITE,DELETE,TABLE,public.users,"DELETE FROM users WHERE id = 123",<not logged>

5.3。一元化されたログ_

/etc/filebeat/filebeat.yml

6。セキュリティ強化

6.1。不要な機能を無効にする_

6.2。スーパーユーザーのアクセスを制限

6.3。リソース制限_

6.4。 PostgreSQL バージョンを非表示_

6.5。安全なレプリケーション

7。コンプライアンスと標準

7.1。 PCI DSS 準拠_

7.2。 GDPR 準拠_

7.3。 HIPAA 準拠

8。セキュリティ監視_

8.1。失敗したログイン試行を監視

8.2。権限昇格を監視

8.3。セキュリティ イベントに関するアラート_

レッスン 20: セキュリティのベストプラクティス

2.3 を無効にします。パスワードポリシー

3。ネットワークセキュリティ_

3.2。ネットワークセグメンテーション

3.3。 VPN/プライベートネットワーク

4.1。ファイルシステム暗号化 (LUKS)

8.3。セキュリティイベントに関するアラート_

9。ベストプラクティスの概要

10 があります。セキュリティチェックリスト

セキュリティレイヤー_