pgcrypto

CREATE EXTENSION pgcrypto;

CREATE EXTENSION pgcrypto;

SELECT digest('data', 'sha256');                    -- 二进制哈希
SELECT encode(digest('data', 'sha256'), 'hex');     -- 十六进制输出
SELECT hmac('data', 'secret_key', 'sha256');        -- HMAC

-- 设置新密码
UPDATE users SET pswhash = crypt('new password', gen_salt('bf'));

-- 验证密码
SELECT (pswhash = crypt('entered password', pswhash)) AS valid FROM users;

-- 加密
SELECT pgp_sym_encrypt('secret data', 'password');
SELECT pgp_sym_encrypt('secret data', 'password', 'cipher-algo=aes256, compress-algo=1');

-- 解密
SELECT pgp_sym_decrypt(encrypted_data, 'password');

-- 使用公钥加密
SELECT pgp_pub_encrypt('secret data', dearmor(pubkey));

-- 使用私钥解密
SELECT pgp_pub_decrypt(encrypted_data, dearmor(seckey));
SELECT pgp_pub_decrypt(encrypted_data, dearmor(seckey), 'key_password');

SELECT pgp_key_id(dearmor(key_text));       -- 提取密钥 ID
SELECT armor(binary_data);                   -- ASCII 封装
SELECT dearmor(armored_text);                -- 移除封装
SELECT pgp_armor_headers(armored_text);      -- 提取封装头

SELECT encrypt('data'::bytea, 'key'::bytea, 'aes');
SELECT decrypt(encrypted, 'key'::bytea, 'aes');

-- 带 IV
SELECT encrypt_iv('data'::bytea, 'key'::bytea, 'iv'::bytea, 'aes-cbc/pad:pkcs');
SELECT decrypt_iv(encrypted, 'key'::bytea, 'iv'::bytea, 'aes-cbc/pad:pkcs');

SELECT gen_random_bytes(16);        -- 16个加密随机字节
SELECT gen_random_uuid();           -- 随机 UUID v4