PostgreSQL教程

密码哈希函数

MD5哈希

MD5哈希函数（不推荐用于密码存储）：

SELECT 
    MD5('password123') AS md5_hash,                       -- MD5哈希
    MD5('username' || 'password123') AS salted_md5;       -- 加盐MD5哈希

Crypt函数

更安全的密码哈希函数：

SELECT 
    CRYPT('password123', GEN_SALT('bf')) AS bcrypt_hash,  -- bcrypt哈希
    CRYPT('password123', GEN_SALT('md5')) AS md5_crypt_hash, -- MD5 crypt哈希
    CRYPT('password123', GEN_SALT('xdes')) AS xdes_crypt_hash; -- Extended DES哈希

密码验证

验证密码哈希：

-- 验证bcrypt哈希
SELECT 
    CRYPT('password123', '$2a$06$hashsaltvalue') = '$2a$06$hashsaltvalue' AS password_valid;

-- 验证MD5哈希
SELECT 
    CRYPT('password123', '$1$hashsaltvalue') = '$1$hashsaltvalue' AS password_valid;

数据加密函数

PGP加密

使用PGP进行数据加密：

-- PGP加密
SELECT 
    PGP_SYM_ENCRYPT('Sensitive Data', 'my_secret_key') AS encrypted_data;

-- PGP解密
SELECT 
    PGP_SYM_DECRYPT(
        PGP_SYM_ENCRYPT('Sensitive Data', 'my_secret_key'), 
        'my_secret_key'
    ) AS decrypted_data;

非对称加密

使用公钥/私钥加密：

-- 生成密钥对（需要在操作系统层面生成）
-- openssl genrsa -out private.key 2048
-- openssl rsa -in private.key -pubout -out public.key

-- PGP公钥加密
SELECT 
    PGP_PUB_ENCRYPT('Secret Message', DECODE('public_key_content', 'base64')) AS encrypted_message;

-- PGP私钥解密
SELECT 
    PGP_PUB_DECRYPT(
        PGP_PUB_ENCRYPT('Secret Message', DECODE('public_key_content', 'base64')),
        DECODE('private_key_content', 'base64')
    ) AS decrypted_message;

编码函数

Base64编码

Base64编码和解码：

SELECT 
    ENCODE('Hello World'::BYTEA, 'base64') AS base64_encoded, -- Base64编码
    DECODE(ENCODE('Hello World'::BYTEA, 'base64'), 'base64') AS base64_decoded; -- Base64解码

十六进制编码

十六进制编码和解码：

SELECT 
    ENCODE('Hello World'::BYTEA, 'hex') AS hex_encoded,       -- 十六进制编码
    DECODE(ENCODE('Hello World'::BYTEA, 'hex'), 'hex') AS hex_decoded; -- 十六进制解码

实际应用场景

用户密码存储

-- 创建用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入用户（使用bcrypt哈希）
INSERT INTO users (username, email, password_hash)
VALUES (
    'john_doe',
    'john@example.com',
    CRYPT('password123', GEN_SALT('bf'))
);

-- 验证用户登录
SELECT id, username 
FROM users 
WHERE username = 'john_doe' 
  AND password_hash = CRYPT('password123', password_hash);

敏感数据加密存储

-- 创建包含加密字段的表
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    phone_encrypted BYTEA,                                -- 加密的电话号码
    ssn_encrypted BYTEA,                                  -- 加密的社会安全号码
    credit_card_encrypted BYTEA,                          -- 加密的信用卡号
    encryption_key_name VARCHAR(50) DEFAULT 'default_key'
);

-- 插入加密数据
INSERT INTO user_profiles (user_id, phone_encrypted, ssn_encrypted)
VALUES (
    1,
    PGP_SYM_ENCRYPT('13800138000', 'user_encryption_key'),
    PGP_SYM_ENCRYPT('123-45-6789', 'user_encryption_key')
);

-- 查询解密数据
SELECT 
    user_id,
    PGP_SYM_DECRYPT(phone_encrypted, 'user_encryption_key') AS phone,
    PGP_SYM_DECRYPT(ssn_encrypted, 'user_encryption_key') AS ssn
FROM user_profiles 
WHERE user_id = 1;

审计日志加密

-- 创建审计日志表
CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    action VARCHAR(100),
    details_encrypted BYTEA,
    ip_address INET,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入加密的审计日志
INSERT INTO audit_logs (user_id, action, details_encrypted, ip_address)
VALUES (
    1,
    'password_change',
    PGP_SYM_ENCRYPT('User changed password from 192.168.1.100', 'audit_key'),
    '192.168.1.100'::INET
);

-- 查询审计日志
SELECT 
    id,
    user_id,
    action,
    PGP_SYM_DECRYPT(details_encrypted, 'audit_key') AS details,
    ip_address,
    created_at
FROM audit_logs 
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';

安全最佳实践

密钥管理

安全的密钥管理策略：

-- 创建密钥管理表
CREATE TABLE encryption_keys (
    id SERIAL PRIMARY KEY,
    key_name VARCHAR(100) UNIQUE NOT NULL,
    key_value TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- 插入密钥（实际应用中应从安全的密钥管理系统获取）
INSERT INTO encryption_keys (key_name, key_value, expires_at)
VALUES (
    'user_data_key',
    'super_secret_encryption_key_2023',
    CURRENT_DATE + INTERVAL '1 year'
);

-- 获取活动密钥
CREATE OR REPLACE FUNCTION get_active_encryption_key(key_name VARCHAR)
RETURNS TEXT AS $$
DECLARE
    key_value TEXT;
BEGIN
    SELECT key_value INTO key_value
    FROM encryption_keys
    WHERE key_name = key_name
      AND is_active = TRUE
      AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP);
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Active encryption key not found for: %', key_name;
    END IF;
    
    RETURN key_value;
END;
$$ LANGUAGE plpgsql;

密码策略

-- 创建密码强度检查函数
CREATE OR REPLACE FUNCTION check_password_strength(password TEXT)
RETURNS TABLE(
    is_strong BOOLEAN,
    score INTEGER,
    feedback TEXT
) AS $$
DECLARE
    length_score INTEGER := 0;
    uppercase_score INTEGER := 0;
    lowercase_score INTEGER := 0;
    digit_score INTEGER := 0;
    special_score INTEGER := 0;
    total_score INTEGER := 0;
BEGIN
    -- 长度检查
    IF LENGTH(password) >= 8 THEN
        length_score := 25;
    ELSIF LENGTH(password) >= 6 THEN
        length_score := 15;
    END IF;
    
    -- 大写字母检查
    IF password ~ '[A-Z]' THEN
        uppercase_score := 15;
    END IF;
    
    -- 小写字母检查
    IF password ~ '[a-z]' THEN
        lowercase_score := 15;
    END IF;
    
    -- 数字检查
    IF password ~ '[0-9]' THEN
        digit_score := 15;
    END IF;
    
    -- 特殊字符检查
    IF password ~ '[^A-Za-z0-9]' THEN
        special_score := 20;
    END IF;
    
    total_score := length_score + uppercase_score + lowercase_score + digit_score + special_score;
    
    RETURN QUERY SELECT 
        total_score >= 70 AS is_strong,
        total_score AS score,
        CASE 
            WHEN total_score >= 90 THEN 'Very Strong'
            WHEN total_score >= 70 THEN 'Strong'
            WHEN total_score >= 50 THEN 'Medium'
            WHEN total_score >= 30 THEN 'Weak'
            ELSE 'Very Weak'
        END AS feedback;
END;
$$ LANGUAGE plpgsql;

-- 使用密码强度检查
SELECT * FROM check_password_strength('MyP@ssw0rd123');

性能考虑

加密性能

加密操作的性能影响：

-- 测试不同加密方法的性能
EXPLAIN ANALYZE
SELECT 
    id,
    username,
    CRYPT(password_hash, password_hash) AS bcrypt_verify,
    MD5(password_hash) AS md5_hash
FROM users 
LIMIT 1000;

-- 为加密字段创建索引（谨慎使用）
-- 注意：加密数据通常不适合创建索引，因为加密后数据变得随机
-- 如果需要搜索，考虑使用确定性加密或搜索加密

批量加密处理

-- 批量加密用户数据
CREATE OR REPLACE FUNCTION batch_encrypt_user_data()
RETURNS INTEGER AS $$
DECLARE
    updated_count INTEGER := 0;
    user_record RECORD;
    encryption_key TEXT;
BEGIN
    -- 获取加密密钥
    encryption_key := get_active_encryption_key('user_data_key');
    
    -- 批量处理用户数据
    FOR user_record IN 
        SELECT id, phone, ssn 
        FROM users 
        WHERE phone_encrypted IS NULL OR ssn_encrypted IS NULL
    LOOP
        UPDATE users 
        SET 
            phone_encrypted = PGP_SYM_ENCRYPT(user_record.phone, encryption_key),
            ssn_encrypted = PGP_SYM_ENCRYPT(user_record.ssn, encryption_key)
        WHERE id = user_record.id;
        
        updated_count := updated_count + 1;
    END LOOP;
    
    RETURN updated_count;
END;
$$ LANGUAGE plpgsql;

扩展加密功能

安装加密扩展

-- 安装pgcrypto扩展
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 验证扩展是否安装
SELECT * FROM pg_extension WHERE extname = 'pgcrypto';

-- 查看可用的加密函数
SELECT proname, prosrc 
FROM pg_proc 
WHERE proname ILIKE '%crypt%' 
   OR proname ILIKE '%pgp%' 
   OR proname ILIKE '%digest%'
ORDER BY proname;

高级加密功能

-- 使用不同的哈希算法
SELECT 
    DIGEST('password123', 'sha256') AS sha256_hash,
    DIGEST('password123', 'sha512') AS sha512_hash,
    DIGEST('password123', 'md5') AS md5_hash;

-- HMAC签名
SELECT 
    HMAC('message', 'secret_key', 'sha256') AS hmac_sha256,
    ENCODE(HMAC('message', 'secret_key', 'sha256'), 'hex') AS hmac_hex;

-- 随机数据生成
SELECT 
    GEN_RANDOM_BYTES(16) AS random_bytes,
    ENCODE(GEN_RANDOM_BYTES(16), 'hex') AS random_hex,
    GEN_RANDOM_UUID() AS random_uuid;

-- 创建完整的用户管理系统
CREATE TABLE secure_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    phone_encrypted BYTEA,
    ssn_encrypted BYTEA,
    is_active BOOLEAN DEFAULT TRUE,
    failed_login_attempts INTEGER DEFAULT 0,
    locked_until TIMESTAMP,
    last_login TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建用户注册函数
CREATE OR REPLACE FUNCTION register_user(
    p_username VARCHAR(50),
    p_email VARCHAR(255),
    p_password TEXT,
    p_phone VARCHAR(20),
    p_ssn VARCHAR(20)
)
RETURNS INTEGER AS $$
DECLARE
    user_id INTEGER;
    encryption_key TEXT;
BEGIN
    -- 检查密码强度
    IF (SELECT is_strong FROM check_password_strength(p_password)) = FALSE THEN
        RAISE EXCEPTION 'Password is not strong enough';
    END IF;
    
    -- 获取加密密钥
    encryption_key := get_active_encryption_key('user_data_key');
    
    -- 插入用户
    INSERT INTO secure_users (
        username, 
        email, 
        password_hash,
        phone_encrypted,
        ssn_encrypted
    )
    VALUES (
        p_username,
        p_email,
        CRYPT(p_password, GEN_SALT('bf')),
        PGP_SYM_ENCRYPT(p_phone, encryption_key),
        PGP_SYM_ENCRYPT(p_ssn, encryption_key)
    )
    RETURNING id INTO user_id;
    
    -- 记录审计日志
    INSERT INTO audit_logs (user_id, action, details_encrypted, ip_address)
    VALUES (
        user_id,
        'user_registration',
        PGP_SYM_ENCRYPT('User registered with email: ' || p_email, 'audit_key'),
        inet_client_addr()
    );
    
    RETURN user_id;
END;
$$ LANGUAGE plpgsql;

-- 创建用户登录函数
CREATE OR REPLACE FUNCTION login_user(
    p_username VARCHAR(50),
    p_password TEXT
)
RETURNS TABLE(
    user_id INTEGER,
    username VARCHAR(50),
    is_authenticated BOOLEAN
) AS $$
DECLARE
    user_record secure_users%ROWTYPE;
BEGIN
    -- 查找用户
    SELECT * INTO user_record 
    FROM secure_users 
    WHERE username = p_username 
      AND is_active = TRUE;
    
    -- 检查用户是否存在
    IF NOT FOUND THEN
        RETURN QUERY SELECT NULL::INTEGER, NULL::VARCHAR(50), FALSE;
        RETURN;
    END IF;
    
    -- 检查账户是否被锁定
    IF user_record.locked_until IS NOT NULL AND user_record.locked_until > CURRENT_TIMESTAMP THEN
        RAISE EXCEPTION 'Account is locked until %', user_record.locked_until;
    END IF;
    
    -- 验证密码
    IF user_record.password_hash = CRYPT(p_password, user_record.password_hash) THEN
        -- 登录成功
        UPDATE secure_users 
        SET 
            failed_login_attempts = 0,
            last_login = CURRENT_TIMESTAMP,
            updated_at = CURRENT_TIMESTAMP
        WHERE id = user_record.id;
        
        -- 记录登录日志
        INSERT INTO audit_logs (user_id, action, details_encrypted, ip_address)
        VALUES (
            user_record.id,
            'user_login',
            PGP_SYM_ENCRYPT('User logged in successfully', 'audit_key'),
            inet_client_addr()
        );
        
        RETURN QUERY SELECT user_record.id, user_record.username, TRUE;
    ELSE
        -- 登录失败
        UPDATE secure_users 
        SET 
            failed_login_attempts = failed_login_attempts + 1,
            locked_until = CASE 
                WHEN failed_login_attempts >= 4 THEN CURRENT_TIMESTAMP + INTERVAL '30 minutes'
                ELSE locked_until
            END,
            updated_at = CURRENT_TIMESTAMP
        WHERE id = user_record.id;
        
        RETURN QUERY SELECT NULL::INTEGER, NULL::VARCHAR(50), FALSE;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 使用示例
-- 注册用户
SELECT register_user('john_doe', 'john@example.com', 'MyP@ssw0rd123', '13800138000', '123-45-6789');

-- 用户登录
SELECT * FROM login_user('john_doe', 'MyP@ssw0rd123');