表和列级别数据加密的例子

数据库里存的信息对公司来说通常超级重要。但很可惜，这些数据对黑客来说也很有吸引力。所以加密就很有必要——这是保护数据不被乱看的一种方式。

加密能保护敏感信息，比如密码、信用卡号或者个人数据。它还能帮你满足各种法律要求，比如GDPR或者HIPAA。如果哪天数据泄露了，有加密的数据就没那么容易被利用，能大大减少损失。

PostgreSQL里有很方便的对称加密函数。用pgp_sym_encrypt(data, key)你可以把数据加密，然后用pgp_sym_decrypt(encrypted_data, key)和同一个key解密。很简单，也很安全。

数据加密的例子

第1步：创建表

我们来建一个users表，有一列专门存加密过的手机号：

-- 创建带加密数据列的表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    phone_encrypted BYTEA -- 这里存加密过的手机号
);

第2步：加密插入数据

现在我们加个用户，把他的手机号加密存进去：

-- 插入加密数据
INSERT INTO users (username, phone_encrypted)
VALUES ('john_doe', pgp_sym_encrypt('123-456-7890', 'my_secret_key'));

注意这里用到了pgp_sym_encrypt函数。my_secret_key就是我们的对称密钥。实际项目里密钥要复杂点，而且要好好保护。

第3步：解密读取数据

需要用到数据时，我们可以解密：

-- 解密读取数据
SELECT 
    username, 
    pgp_sym_decrypt(phone_encrypted, 'my_secret_key') AS phone
FROM users;

只要密钥对，你就能看到原始手机号。

进阶：给已有表加加密

如果表已经存在，我们想给某一列加密怎么办？来看个例子。

第1步：加新列

比如我们有个customers表，想加密信用卡号那一列：

-- 给加密数据加新列
ALTER TABLE customers ADD COLUMN card_number_encrypted BYTEA;

第2步：把数据转到加密列

我们把已有数据加密后存到新列里：

-- 加密数据并转到新列
UPDATE customers
SET card_number_encrypted = pgp_sym_encrypt(card_number, 'my_other_secret_key');

第3步：删掉明文列

加密完后可以把原来的明文列删掉：

-- 删除原来的明文列
ALTER TABLE customers DROP COLUMN card_number;

现在数据已经被加密保护了，只有有密钥的人才能访问。

和加密数据打交道的注意事项

用加密列时有几个重要点：

数据类型：

• 加密后的值是二进制格式（BYTEA），不是明文。
• 查询时要用解密函数。

查找和筛选：

• 不能直接用加密数据查找，比如：

  SELECT * FROM users WHERE phone_encrypted = '123-456-7890'; -- 不会有结果！

• 要查的话得先解密：

  SELECT *
  FROM users
  WHERE pgp_sym_decrypt(phone_encrypted, 'my_secret_key') = '123-456-7890';

性能：

加密和解密会让查询慢一些。只在必要的地方用就行。

实际场景：密码保护

存密码是最常见的加密需求之一。密码绝不能明文存（超级不安全），要用哈希。

用pgcrypto哈希密码

我们用crypt()函数安全哈希密码：

-- 插入时哈希密码
INSERT INTO users (username, phone_encrypted)
VALUES ('alice', crypt('my_secure_password', gen_salt('bf')));

这里gen_salt('bf')会生成哈希用的salt。

验证密码时，比较哈希值：

-- 比较哈希密码
SELECT username
FROM users
WHERE crypt('my_secure_password', phone_encrypted) = phone_encrypted;

安全建议

1. 密钥要单独存：

千万别把对称密钥和加密数据放在同一个数据库里。

2. 用复杂密钥：

像"123"这种简单密钥很容易被猜到。

3. 定期换密钥：

为了防止数据泄露，建议定期换密钥，同时把数据重新加密。