2024-06-25 PostgreSQL 数据加密和数据解密

PostgreSQL 数据加密和数据解密

PostgreSQL 数据加密和数据解密
在信息化建设和等保建设中，我们都要求实现对用户数据的隐私保护，也就是我们常说的脱敏。那么在 PostgreSQL 数据库中有没有这样的方法或者策略可以实现呢。

在 PostgreSQL 数据库中要想实现对数据的加密和解密，需要引用数据库的 pgcrypto 插件。关于 pgcrypto 具体是啥我就不仔细介绍了。有需要的可以看下

PostgreSQL 数据加密的实现
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION public.encode_aes(txtstr text)
  RETURNS pg_catalog.text AS $BODY$
  DECLARE encodestr text;
  BEGIN
     SELECT encode(encrypt(txtstr::bytea, '0987654321ABHAEQ', 'aes-cbc/pad:pkcs'), 'base64') INTO encodestr;
     RETURN encodestr;
  END;
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

ALTER FUNCTION public.encode_aes(txtstr text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION public.decode_aes(txtstr text)
  RETURNS pg_catalog.text AS $BODY$
  DECLARE decodestr text;
  BEGIN
    SELECT encode(decrypt(decode(txtstr,'base64'), '0987654321ABHAEQ', 'aes-cbc/pad:pkcs') , 'escape') INTO decodestr; 
    RETURN decodestr;
  END;
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

ALTER FUNCTION public.decode_aes(txtstr text) OWNER TO postgres;

说明
-- 0987654321ABHAEQ：秘钥自己定义一个字符串
-- aes-cbc/pad:pkcs：加密算法
-- base64：编码格式

案例
CREATE TABLE PUBLIC.cloud_user (
    user_id INT8 NOT NULL,
    user_name VARCHAR (30),
    user_type VARCHAR (32),
    user_mobile VARCHAR (30),
    user_gender VARCHAR (5),
    logic_state VARCHAR (16) DEFAULT 0,
    create_time TIMESTAMP (6),
    user_password VARCHAR (255),
    client_id VARCHAR (64),
    user_birth TEXT,
    head_img VARCHAR (2000),
    PRIMARY KEY (user_id));
    
ALTER TABLE PUBLIC.cloud_user OWNER TO postgres;
COMMENT ON COLUMN PUBLIC.cloud_user.user_id IS '用户ID';
COMMENT ON COLUMN PUBLIC.cloud_user.user_name IS '用户姓名';
COMMENT ON COLUMN PUBLIC.cloud_user.user_type IS '用户类型';
COMMENT ON COLUMN PUBLIC.cloud_user.user_mobile IS '用户手机号';
COMMENT ON COLUMN PUBLIC.cloud_user.user_gender IS '用户性别';
COMMENT ON COLUMN PUBLIC.cloud_user.logic_state IS '逻辑状态';
COMMENT ON COLUMN PUBLIC.cloud_user.create_time IS '创建时间';
COMMENT ON COLUMN PUBLIC.cloud_user.user_password IS '用户密码';
COMMENT ON COLUMN PUBLIC.cloud_user.client_id IS '个推CID';
COMMENT ON COLUMN PUBLIC.cloud_user.user_birth IS '出生年月';
COMMENT ON COLUMN PUBLIC.cloud_user.head_img IS '头像';

INSERT INTO "public"."cloud_user" ("user_id", "user_name", "user_type", "user_mobile", "user_gender", "logic_state", "create_time", "user_password", "client_id", "user_birth", "head_img") VALUES (10000, '超级管理员', '0', 'admin', '男', '0', '2020-07-16 10:18:24', '96e79218965eb72c92a549dd5a330112', NULL, '2020-07-16', NULL);
INSERT INTO "public"."cloud_user" ("user_id", "user_name", "user_type", "user_mobile", "user_gender", "logic_state", "create_time", "user_password", "client_id", "user_birth", "head_img") VALUES (10001, '管理员', '1', '19999999998', '男', '0', '2020-07-16 10:19:01', '96e79218965eb72c92a549dd5a330112', NULL, '2020-07-16', NULL);
INSERT INTO "public"."cloud_user" ("user_id", "user_name", "user_type", "user_mobile", "user_gender", "logic_state", "create_time", "user_password", "client_id", "user_birth", "head_img") VALUES (10002, '张三', '0', '19999999996', NULL, '0', '2021-11-18 10:53:17.026', '96e79218965eb72c92a549dd5a330112', NULL, '2021-11-18', NULL);
INSERT INTO "public"."cloud_user" ("user_id", "user_name", "user_type", "user_mobile", "user_gender", "logic_state", "create_time", "user_password", "client_id", "user_birth", "head_img") VALUES (10003, '李四', '0', '19999999997', NULL, '0', '2021-11-23 09:22:43.889', NULL, NULL, '2021-11-23', NULL);
INSERT INTO "public"."cloud_user" ("user_id", "user_name", "user_type", "user_mobile", "user_gender", "logic_state", "create_time", "user_password", "client_id", "user_birth", "head_img") VALUES (10004, '王五', '1', '19999999999', '', '0', '2022-01-18 18:14:17.304', 'c4ca4238a0b923820dcc509a6f75849b', NULL, '2022-01-18', NULL);

现在对用户的出生年月进行加密
我们先创建一个字段保存当前的用户的出生年月信息，然后解密后与他进行比较，用来判断是否正确。

ALTER TABLE cloud_user ADD old_user_birth TEXT;
UPDATE cloud_user SET old_user_birth = user_birth;
SELECT encode_aes(user_birth) FROM cloud_user;
UPDATE cloud_user SET user_birth = encode_aes(user_birth);
SELECT * FROM cloud_user ORDER BY user_id;

现在对字段 user_birth 进行解密，然后将其与字段 old_user_birth 进行比较查看两者是否一致
SELECT decode_aes(user_birth),old_user_birth FROM cloud_user;