PostgreSQL 数据加密怎么弄，应该用哪种方案

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, Oceanbase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，（共2150人左右 1 + 2 + 3 + 4 +5）新人奖直接分配到5群,另欢迎 OpenGauss 的技术人员加入。

数据库加密这个话题在很多大型企业的数据库安全规范中是有严格的要求的，这里数据库加密可以分为2个部分，实际上3个部分，这里由于其中一个部分在很多情况下并不实用，所以我们这边就不讨论了。

加密的方案

1 针对数据库中的数据进行加密

2 在数据传输中进行数据的加密

两种加密方案应对的需求不一样，应对的需求也不一样，数据中的数据加密，主要是针对敏感的数据存储在数据库中的不安全导致的，他需要存在数据库中的数据本身就是加密的，数据仅仅在读取的时候会进行解密，返回正常的数据，平时直接进行查看的时候，字段中的数据是被加密的。

另一种是在数据传输的过程中，处于加密，在数据中间传输环节保证数据的安全性。一个是针对存储，一个是针对传输环节。

那么我们兵分两路，先说存储环节

存储环节

案例1 用户的密码

用户的密码是一些系统中经常存在需要存储的部分，这部分数据在存储环节如何保证对于用户是安全的，不会在数据存储的部分被泄密，首选需要数据存储中的加密是不可逆的。

这里PostgreSQL 中有一个扩展为pgcrypto，其中有一个函数为生成hash函数的功能，digest ，这个部分在生成后，为不可逆的。例如我们这样操作

create table ency_table (
id serial primary key,
name varchar(20),
password varchar(41),
ency varchar(5) );

est=# insert into ency_table (name,password,ency) values ('John',digest('system_password','sha'),'sha');
INSERT 0 1
test=# select * from ency_table;
 id | name |                  password                  | ency 
----+------+--------------------------------------------+------
  1 | John | \x8892c3c9541f29a778b8ad675ca77f2a27e86540 | sha
  
(2 rows)

基于这样的加密后的数据，是无法进行解密的，所以在用户输入密码后，也需要加密后，与存储的password 进行比对。

如下面的方式，可以进行密码的验证和比对以及登录的工作。

test=# select * from ency_table where password = (select digest('system_password','sha'))::varchar(50);
 id | name |                  password                  | ency 
----+------+--------------------------------------------+------
  1 | John | \x8892c3c9541f29a778b8ad675ca77f2a27e86540 | sha

案例2 存储加密信息，提取解密信息

这个是大多数在数据库中解决加解密的一个普通需求，虽然在日常的工作中我认为，加解密都应该是程序来做的，但是我们数据库的提供方案，比如下面的一个方案。

test=# insert into customer_table (name,phone,mail_address,ency) values ('John',encrypt('123456789012345','1234','aes'),encrypt('123456789012345','1234','aes'),'aes');
INSERT 0 1
test=# \d customer_table
                                      Table "public.customer_table"
    Column    |         Type          | Collation | Nullable |                  Default                   
--------------+-----------------------+-----------+----------+--------------------------------------------
 id           | integer               |           | not null | nextval('customer_table_id_seq'::regclass)
 name         | character varying(20) |           |          | 
 phone        | character varying(50) |           |          | 
 mail_address | character varying(50) |           |          | 
 ency         | character varying(5)  |           |          | 
Indexes:
    "customer_table_pkey" PRIMARY KEY, btree (id)

test=# select * from customer_table;
 id | name |               phone                |            mail_address            | ency 
----+------+------------------------------------+------------------------------------+------
  1 | John | \x34591627f9c8eae417fc7cbbf458592c | \x34591627f9c8eae417fc7cbbf458592c | aes
(1 row)

test=#

这里可以通过语句来进行数据解密，这里有一个核心点，数据已经被加密存储在数据库中，如果没有这个字段加密的方法和这个字段加密的秘钥是无法对这个数据进行解密的。

这里的秘钥是1234 加密方法是aes，通过这样的方案可以针对数据库中的特定的表的数据进行加密的计算和解密的提取，基本上不需要程序有相关的变动，属于数据库节点的方案。

select id,name,convert_from(decrypt(phone::bytea,'1234','aes'),'SQL_ASCII') as phone, convert_from(decrypt(mail_address::bytea,'1234','aes'),'SQL_ASCII') as mail_address from customer_table;

test=# 
test=# select id,name,convert_from(decrypt(phone::bytea,'1234','aes'),'SQL_ASCII') as phone, convert_from(decrypt(mail_address::bytea,'1234','aes'),'SQL_ASCII') as mail_address from customer_table;
 id | name |      phone      |  mail_address   
----+------+-----------------+-----------------
  1 | John | 123456789012345 | 123456789012345
(1 row)

test=#

但这里需要提示一些使用这样方案的问题点，首先在大部分开发项目中使用的是框架，他们封装了SQL的生成的过程，，所以以上的方案可能不适合这类系统，因为开发者无法进行语句的修改，达到上面数据的加密和解密的目的，如果使用了手动编写SQL的方案，所以大部分方案都是由程序在产生数据的程序中将核心的数据进行加密，在加密数据提取后，在程序中解密的方案，所以以上的方案仅仅为一个借鉴。

最后还有基于TDE的PostgreSQL加密的方案，percona 退出基于PG16的TDE 方案，如果你的数据库已经使用了PG16 可以尝试这个方案，具体参见，TDE加密的方案中包含了用户的数据，TOAST表等，但愿数据库不会被加密，同时WAL数据也会被加密，临时表也会，但需要特别注意的是，这样的方案不支持逻辑复制，有使用逻辑复制的PG数据库系统，不要使用TDE的方案来进行数据的加密和解密。

https://percona-lab.github.io/pg_tde/main/