安全與權限管理：從認證到 Row Level Security 全方位防護 | PostgreSQL

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

-- 建立使用者，密碼以 SCRAM 格式儲存
CREATE ROLE app_user WITH LOGIN PASSWORD 'StrongP@ssw0rd!';

-- 驗證密碼加密格式
SELECT rolname, LEFT(rolpassword, 14) AS format_prefix
FROM pg_authid
WHERE rolname = 'app_user';
-- 正確輸出：SCRAM-SHA-256$

# 1. 產生 CA 憑證
openssl genrsa -out ca.key 4096
openssl req -new -x509 -days 3650 -key ca.key -out ca.crt \
  -subj "/CN=PostgreSQL CA"

# 2. 產生伺服器憑證
openssl genrsa -out server.key 4096
openssl req -new -key server.key -out server.csr \
  -subj "/CN=db.example.com"
openssl x509 -req -days 365 -in server.csr -CA ca.crt \
  -CAkey ca.key -CAcreateserial -out server.crt

# 3. 產生客戶端憑證（CN 需與 PostgreSQL 使用者名稱一致）
openssl genrsa -out client.key 4096
openssl req -new -key client.key -out client.csr \
  -subj "/CN=app_user"
openssl x509 -req -days 365 -in client.csr -CA ca.crt \
  -CAkey ca.key -CAcreateserial -out client.crt

-- 建立群組角色（不可登入）
CREATE ROLE readonly_group NOLOGIN;
CREATE ROLE readwrite_group NOLOGIN;

-- 建立可登入使用者
CREATE ROLE app_user WITH
  LOGIN
  PASSWORD 'StrongP@ssw0rd!'
  CONNECTION LIMIT 50      -- 限制連線數
  VALID UNTIL '2027-01-01' -- 帳號有效期
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE;

-- 建立唯讀分析帳號
CREATE ROLE analyst WITH LOGIN PASSWORD 'AnalystP@ss!' NOSUPERUSER;

-- 授予資料庫連線權限
GRANT CONNECT ON DATABASE app_db TO readonly_group;
GRANT CONNECT ON DATABASE app_db TO readwrite_group;

-- 授予 Schema 使用權
GRANT USAGE ON SCHEMA public TO readonly_group;
GRANT USAGE ON SCHEMA public TO readwrite_group;

-- 授予資料表權限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public TO readwrite_group;

-- 授予 Sequence（INSERT 需要）
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readwrite_group;

-- 將群組角色指派給使用者
GRANT readonly_group TO analyst;
GRANT readwrite_group TO app_user;

-- 撤銷 public schema 的預設公開建立權限（PG15 前的安全隱患）
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 未來由 app_user 建立的資料表，自動授予 readonly_group SELECT
ALTER DEFAULT PRIVILEGES FOR ROLE app_user IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_group;

-- 未來的 Sequence
ALTER DEFAULT PRIVILEGES FOR ROLE app_user IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO readwrite_group;

-- 未來的函式
ALTER DEFAULT PRIVILEGES FOR ROLE app_user IN SCHEMA public
  GRANT EXECUTE ON FUNCTIONS TO readwrite_group;

-- INHERIT（預設）：自動繼承權限
CREATE ROLE senior_analyst INHERIT;
GRANT readonly_group TO senior_analyst;
GRANT senior_analyst TO john_doe;
-- john_doe 自動擁有 readonly_group 的所有權限

-- NOINHERIT：需明確 SET ROLE 才能取得權限
CREATE ROLE privileged_ops NOINHERIT;
GRANT privileged_ops TO ops_user;

-- ops_user 需執行：
SET ROLE privileged_ops;
-- 執行特權操作...
RESET ROLE;

-- 可讀取所有資料表、視圖、序列
GRANT pg_read_all_data TO analyst;

-- 可寫入所有資料表、視圖、序列
GRANT pg_write_all_data TO etl_user;

-- 其他實用預定義角色
GRANT pg_monitor TO monitoring_user;         -- 監控相關視圖
GRANT pg_read_all_settings TO config_reader; -- 讀取 GUC 設定
GRANT pg_read_all_stats TO stats_reader;     -- 讀取統計資訊
GRANT pg_signal_backend TO ops_user;         -- 可發送 pg_terminate_backend

-- 步驟 1：啟用 RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 步驟 2：建立政策
-- 使用者只能看到自己的訂單
CREATE POLICY user_orders_policy ON orders
  FOR ALL
  TO app_user
  USING (user_id = current_user::TEXT)         -- 查詢過濾（SELECT/UPDATE/DELETE）
  WITH CHECK (user_id = current_user::TEXT);   -- 寫入驗證（INSERT/UPDATE）

-- 唯讀政策
CREATE POLICY select_own_data ON orders
  FOR SELECT
  USING (user_id = current_user::TEXT);

-- PERMISSIVE：admin 看到全部
CREATE POLICY admin_sees_all ON orders
  AS PERMISSIVE
  FOR SELECT
  TO admin_role
  USING (true);

-- RESTRICTIVE：無論其他政策，已刪除的資料都不可見
CREATE POLICY active_records_only ON orders
  AS RESTRICTIVE
  FOR ALL
  USING (is_deleted = false);

-- 建立多租戶資料表
CREATE TABLE tenant_data (
  id          BIGSERIAL PRIMARY KEY,
  tenant_id   INTEGER NOT NULL,
  payload     JSONB,
  created_at  TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY;

-- 透過 Session Variable 隔離租戶
CREATE POLICY tenant_isolation ON tenant_data
  USING (tenant_id = current_setting('app.tenant_id')::INTEGER)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::INTEGER);

-- 應用程式連線後設定：
-- SET app.tenant_id = '42';

-- 連表擁有者也受 RLS 控制
ALTER TABLE tenant_data FORCE ROW LEVEL SECURITY;

-- 為管理員建立繞過政策
CREATE POLICY admin_bypass ON tenant_data
  TO admin_role
  USING (true);

CREATE TABLE employees (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(100),
  department  VARCHAR(50),
  salary      NUMERIC(10,2),   -- 敏感
  ssn         VARCHAR(20),     -- 敏感
  email       VARCHAR(100)
);

-- 一般查詢角色：只看非敏感欄位
GRANT SELECT (id, name, department, email) ON employees TO hr_viewer;

-- 薪資管理角色：只看薪資相關
GRANT SELECT (id, name, salary) ON employees TO payroll_role;

-- 欄位級 UPDATE
GRANT UPDATE (email) ON employees TO self_service_user;

-- 建立遮罩視圖，SSN 只顯示後四碼
CREATE VIEW employees_public AS
SELECT
  id, name, department, email,
  '***-**-' || RIGHT(ssn, 4) AS ssn_masked
FROM employees;

GRANT SELECT ON employees_public TO hr_viewer;
REVOKE SELECT ON employees FROM hr_viewer;

# 產生自簽憑證（生產環境建議使用受信任 CA）
openssl req -new -x509 -days 365 -nodes \
  -out server.crt -keyout server.key \
  -subj "/CN=db.example.com"

# 設定正確的檔案權限
chmod 600 server.key
chown postgres:postgres server.key server.crt

-- 驗證當前連線是否使用 SSL
SELECT ssl, version, cipher, bits
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();

# 安裝（Ubuntu/Debian）
apt-get install postgresql-17-pgaudit

-- 啟用擴充
CREATE EXTENSION pgaudit;

-- 物件級稽核：針對特定資料表
CREATE ROLE auditor;
GRANT SELECT ON sensitive_table TO auditor;
ALTER ROLE app_user SET pgaudit.role = 'auditor';
-- 現在 app_user 對 sensitive_table 的所有 SELECT 都會被記錄

CREATE EXTENSION pgcrypto;

-- 對稱加密（AES）
INSERT INTO sensitive_data (encrypted_payload)
VALUES (
  pgp_sym_encrypt(
    '{"ssn": "123-45-6789"}',
    'key_from_vault',          -- 金鑰應來自密鑰管理服務
    'cipher-algo=aes256'
  )
);

-- 解密
SELECT pgp_sym_decrypt(encrypted_payload::BYTEA, 'key_from_vault')
FROM sensitive_data WHERE id = 1;

-- 密碼雜湊（bcrypt，不可逆）
SELECT crypt('user_password', gen_salt('bf', 12));

-- 驗證密碼
SELECT crypt('input_password', stored_hash) = stored_hash AS is_valid
FROM users WHERE username = 'john_doe';

-- 產生安全隨機 Token
SELECT encode(gen_random_bytes(32), 'hex') AS api_token;

-- 1. 找出所有 SUPERUSER
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole
FROM pg_roles WHERE rolsuper = true;

-- 2. 找出無密碼的可登入帳號
SELECT rolname FROM pg_roles
WHERE rolcanlogin = true AND rolpassword IS NULL;

-- 3. 找出已過期但仍可登入的帳號
SELECT rolname, rolvaliduntil FROM pg_roles
WHERE rolcanlogin = true AND rolvaliduntil < now();

-- 4. 找出 PUBLIC 擁有權限的敏感資料表
SELECT schemaname, tablename, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'PUBLIC';

-- 5. 找出未啟用 RLS 的多租戶資料表
SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE tablename IN (
  SELECT table_name FROM information_schema.columns
  WHERE column_name = 'tenant_id'
) AND rowsecurity = false;

-- 6. 查看 pg_hba.conf 目前設定
SELECT type, database, user_name, address, auth_method
FROM pg_hba_file_rules ORDER BY line_number;

-- 7. 找出長時間閒置的連線
SELECT pid, usename, client_addr, state,
       now() - state_change AS idle_duration
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - state_change > INTERVAL '30 minutes';

-- 陷阱 1：public schema 對所有使用者開放（PG15 前）
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 陷阱 2：應用程式使用 postgres 超級使用者帳號
-- 正確：建立最小權限帳號
CREATE ROLE app_service WITH LOGIN PASSWORD '...'
  NOSUPERUSER NOCREATEDB NOCREATEROLE;

-- 陷阱 3：未設定連線限制
ALTER ROLE app_user CONNECTION LIMIT 100;

-- 陷阱 4：GRANT ... TO PUBLIC（影響所有現有和未來帳號）
REVOKE ALL ON TABLE sensitive_data FROM PUBLIC;

-- 陷阱 5：RLS 未啟用 FORCE，表擁有者繞過 RLS
ALTER TABLE tenant_data FORCE ROW LEVEL SECURITY;

-- 陷阱 6：未設定 search_path，可能導致 Schema 注入
ALTER ROLE app_user SET search_path = '$user', public;

-- 陷阱 7：密碼記錄在命令列歷史
-- 改用 \password 命令（提示輸入，不記錄）
-- \password app_user