-- 创建客户端主密钥CREATE CLIENT MASTER KEY cmk_credit_card WITH ( KEY_STORE = localkms,KEY_PATH = "gsktool@#/key_path/123", ALGORITHM = RSA_2048);

-- 创建列加密密钥CREATE COLUMN ENCRYPTION KEY cek_credit_card WITH ( CLIENT_MASTER_KEY = cmk_credit_card,ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);

-- 确定性加密：支持等值查询CREATE TABLE financial_records ( id BIGSERIAL PRIMARY KEY,account_number VARCHAR(30) ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = cek_account,ENCRYPTION_TYPE = DETERMINISTIC),
-- 随机性加密：最高安全级别balance DECIMAL(15,2) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek_balance, ENCRYPTION_TYPE = RANDOMIZED),

-- 支持范围查询的加密transaction_date DATE ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = cek_date, ENCRYPTION_TYPE = ORDER_PRESERVING));

-- 全密态交易表设计CREATE TABLE secure_transactions ( transaction_id UUID PRIMARY KEY,from_account ENCRYPTED WITH (CEK = cek_account, TYPE = DETERMINISTIC), to_account ENCRYPTED WITH (CEK = cek_account, TYPE = DETERMINISTIC), amount ENCRYPTED WITH (CEK = cek_amount, TYPE = RANDOMIZED),transaction_time ENCRYPTED WITH (CEK = cek_time, TYPE = ORDER_PRESERVING), memo ENCRYPTED WITH (CEK = cek_memo, TYPE = RANDOMIZED));

-- 安全交易处理（密文状态下的业务操作）WITH secure_transfer AS (INSERT INTO secure_transactions VALUES ( gen_random_uuid(),'encrypted_from_account', -- 驱动自动加密'encrypted_to_account',  -- 应用看到的是明文5000.00,CURRENT_TIMESTAMP,'工资发放') RETURNING *)SELECTfrom_account, -- 自动解密显示to_account, amount,transaction_time FROM secure_transfer;

-- 客户信息全密态存储CREATE TABLE customer_profiles (customer_id BIGSERIAL PRIMARY KEY,id_card ENCRYPTED WITH (CEK = cek_id_card, TYPE = DETERMINISTIC), phone ENCRYPTED WITH (CEK = cek_phone, TYPE = DETERMINISTIC),email ENCRYPTED WITH (CEK = cek_email, TYPE = DETERMINISTIC),address ENCRYPTED WITH (CEK = cek_address, TYPE = RANDOMIZED), risk_level INTEGER,create_time TIMESTAMP);

-- 密文数据查询（业务无感知）SELECTcustomer_id,id_card, -- 自动解密phone,risk_levelFROM customer_profilesWHERE id_card = '110101199001011234' -- 条件自动加密AND risk_level > 3;

-- 创建密文列索引CREATE INDEX idx_encrypted_accountON financial_records (account_number);

-- 密文范围查询优化CREATE INDEX idx_encrypted_dateON financial_records (transaction_date);

-- 查询性能对比EXPLAIN (ANALYZE, FORMAT JSON)SELECT * FROM financial_recordsWHERE account_number = '6222021234567890'AND transaction_date BETWEEN '2023-01-01' AND '2023-12-31';

-- 密钥轮转策略CREATE OR REPLACE PROCEDURE rotate_column_key( key_name VARCHAR,new_key_name VARCHAR) AS $$ DECLARErec RECORD; BEGIN-- 创建新密钥EXECUTE format('CREATE COLUMN ENCRYPTION KEY %s WITH (CLIENT_MASTER_KEY = cmk_financial,ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256)', new_key_name);

-- 数据重加密（在线操作）FOR rec INSELECT table_name, column_name FROM information_schema.columnsWHERE column_encryption_key = key_name LOOPEXECUTE format('ALTER TABLE %s ALTER COLUMN %s SET ENCRYPTED WITH (CEK = %s)',rec.table_name, rec.column_name, new_key_name);END LOOP;

-- 删除旧密钥EXECUTE format('DROP COLUMN ENCRYPTION KEY %s', key_name); END;$$ LANGUAGE plpgsql;

-- 安全审计配置CREATE AUDIT POLICY security_audit ALL;

-- 细粒度审计规则CREATE AUDIT POLICY sensitive_data_access ACCESS (SELECT, UPDATE, DELETE)ON (customer_profiles, financial_records)WHEN (current_user NOT IN ('auditor', 'security_admin')) WITH (LOG_TYPE = 'TEXT', RETENTION = '365 days');
-- 实时安全监控CREATE VIEW security_monitor AS SELECTevent_time, username,client_addr,object_name, action_type,statement_text FROM audit_logWHERE event_time >= CURRENT_TIMESTAMP - INTERVAL '1 hour' AND success = true;

-- 基于机器学习的异常检测WITH user_behavior AS ( SELECTusername,COUNT(*) as query_count,COUNT(DISTINCT client_addr) as ip_count, AVG(response_time) as avg_response_timeFROM audit_logWHERE event_time >= CURRENT_TIMESTAMP - INTERVAL '10 minutes' GROUP BY username)SELECTusername,query_count, ip_count,ai_anomaly_detection(query_count, ip_count, avg_response_time) as anomaly_score FROM user_behaviorWHERE ai_anomaly_detection(...) > 0.8;