PostgreSQL数据库加密详解

概述

1 列加密

PostgreSQL支持对列进行加解密，加解密借助pgcrypto完成。用户需要指定密钥进行对敏感列的加密或者解密。加密时，需要将密钥和明文数据发送到PostgreSQL端，PostgreSQL进行加密并存储密文；解密时，数据在PostgreSQL数据库端进行解密，然后将解密的结果发送给客户端。当进行加解密的时候，密钥和数据仅在PostgreSQL端存在极短的时间并全部在内存中，用过及丢弃。

2 通信信道加密

PostgreSQL可以设置服务端和客户端之间的数据传输是加密的。PostgreSQL利用openssl的库实现这一要求，但前提条件是服务端和客户端都要安装openssl工具包。在服务端安装好openssl之后，就可以利用openssl指令生成一对私钥和证书，用以对数据进行加解密，然后再对配置文件稍作修改就可以了。同样的，PostgreSQL数据库内部的数据引擎端与存储端之间的通信也可以设置基于openssl的加密，但前提条件也是数据库引擎端和分布式存储的DIR/MRC/OSD都要安装openssl工具包。

3 列加密的两种方法

3.1 数据准备

创建外部模块

 create extension pgcrypto;  

创建测试表

create table test_user(id serial,username varchar(32),password text);

给测试表创建索引

create unique index idx_test_user_username on test_user using btree(username);

查看测试表结构

\d test_user
Table "public.test_user"
Column  |         Type          | Collation | Nullable |                Default                
----------+-----------------------+-----------+----------+---------------------------------------
 id       | integer               |           | not null | nextval('test_user_id_seq'::regclass)
 username | character varying(32) |           |          | 
 password | text                  |           |          | 
Indexes:
    "idx_test_user_username" UNIQUE, btree (username)

3.2 md5 加密

插入测试用户信息，使用md5加密方法给password列值进行数据加密。

insert into test_user(username,password) values  ('user1',md5('123456'));
insert into test_user(username,password) values  ('user2',md5('123456'));

查询用户信息

 select * From test_user;
 id | username |             password             
----+----------+----------------------------------
  1 | user1    | e10adc3949ba59abbe56e057f20f883e
  2 | user2    | e10adc3949ba59abbe56e057f20f883e
(2 rows)

认证测试，如果用户输入password的是123456，则通过认证。

select * From test_user where password=md5('123456');
 id | username |             password             
----+----------+----------------------------------
  1 | user1    | e10adc3949ba59abbe56e057f20f883e
  2 | user2    | e10adc3949ba59abbe56e057f20f883e
(2 rows)

备注：使用 md5 加密，密文相同，加密结果也相同，通过结果可推知密文，保密性不够强。

3.3 crypt()函数加密

使用 crypt() 函数增加两条用户信息
插入测试用户信息，使用crypt加密方法给password列值进行数据加密。

insert into test_user(username,password) values('user3',crypt('123456',gen_salt('md5')));
insert into test_user(username,password) values('user4',crypt('123456',gen_salt('md5')));
/*gen_salt('md5')生成一个随机字符串，可接受des, xdes, md5 和 bf算法*/

查询新增用户信息

select * From test_user where username in ('user3','user4');
 id | username |              password              
----+----------+------------------------------------
  3 | user3    | $1$7wuaFG8c$2A7IIKlnO7M3DRGhmVGSB.
  4 | user4    | $1$8CePi7UZ$I/O0jCKSTa6ENxuXooLYX1
(2 rows)

认证测试，如果用户输入password的是123456，则通过认证。

select * From test_user where password=crypt('123456',password); 
 id | username |              password              
----+----------+------------------------------------
  3 | user3    | $1$7wuaFG8c$2A7IIKlnO7M3DRGhmVGSB.
  4 | user4    | $1$8CePi7UZ$I/O0jCKSTa6ENxuXooLYX1
(2 rows)

备注：虽然 user3，user4 使用相同的密码，但经过 crypt() 函数加密后，加密后的密码值不同，显然，crypt()函数加密比 md5 安全。

4、pgcrypto加密组件

4.1 计算hash值的函数

以下是几个计算Hash值的函数：

digest(data text, type text) returns bytea
digest(data bytea, type text) returns bytea
hmac(data text, key text, type text) returns bytea
hmac(data bytea, key text, type text) returns bytea

type为算法.支持 md5, sha1, sha224, sha256, sha384, sha512. 如果编译PostgreSQL时有with-openssl选项, 还可以支持更多算法。

示例：

（1）相同函数调用多次，得到的结果一定相同：

select digest('Hello World!', 'md5');
​               digest   
 \xed076287532e86365e841e92bfc50d8c
(1 row)

（2）不同函数调用，得到的值是不相同的：

select digest('\xffffff'::bytea, 'md5');
​               digest               
 \x8597d4e7e65352a302b63e07bc01a7da
(1 row)

select digest('\xffffff', 'md5');
​               digest 
 \xd721f40e22920e0fd8ac7b13587aa92d
(1 row)

（3）hmac这两个函数与digest类似, 只是多了一个key参数, 也就是说同一个被加密的值, 可以使用不同的key得到不同的hash值.
这样的做法是, 不知道key的话, 也无法逆向破解原始值。使用hmac还有一个好处是, 使用digest如果原始值和hash值同时被别人修改了是无法知道是否被修改的，但是使用hmac, 如果原始值被修改了, 同时key没有泄漏的话, 那么hash值是无法被修改的, 因此就能够知道原始值是否被修改过。

select hmac('Hello World!', 'this is a key', 'md5');
                hmac                
------------------------------------
 \x40e139720a5aaa873c9dd044d1e4229f
(1 row)

select hmac('Hello World!', 'this is another key', 'md5');
                hmac                
------------------------------------
 \xc3ac2c35aec0bbc4f04de52c8b4057b4
(1 row)

4.2 PGP加密函数

pgcrypto包中的pgp加解密函数，加密的PGP消息由2部分组成：

• 这个消息的会话密钥, 会话密钥可以是对称密钥或公钥

• 使用该会话密钥加密的数据, 可以选择加密选项

  例如：
  compress-algo, unicode-mode, cipher-algo, compress-level, convert-crlf, disable-mdc, enable-session-key
  使用对称密钥加解密的函数如下：

pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea
pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea
pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text
pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea

PGP 对称加密

使用对称密钥加密, 这里的对称密钥为’pwd’字符串 :

select pgp_sym_encrypt('Hello World！', 'pwd', 'cipher-algo=bf, compress-algo=2, compress-level=9');
​                                                                          pgp_sym_encrypt 
 \xc30d04040302e16069e78617ced27ad24201eb0fe2c798d9c92c893e37440769b33bad7eca4e275c62f4bd830e415955a2c4
7652442286e2b84bb78af23c648215177e4af77b5d46f3984752214eb486d0d387
(1 row)

解密

select pgp_sym_decrypt('\xc30d04040302e16069e78617ced27ad24201eb0fe2c798d9c92c893e37440769b33bad7eca4e275c62f4bd830e415955a2c4
7652442286e2b84bb78af23c648215177e4af77b5d46f3984752214eb486d0d387'::bytea, 'pwd');
 pgp_sym_decrypt 
 Hello World！
(1 row)

4.3 非对称加解密的函数

使用公钥私钥体系加解密的函数有：

pgp_pub_encrypt(data text, key bytea [, options text ]) returns bytea
pgp_pub_encrypt_bytea(data bytea, key bytea [, options text ]) returns bytea
pgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text ]]) returns text
pgp_pub_decrypt_bytea(msg bytea, key bytea [, psw text [, options text ]]) returns bytea

公钥加密举例

首先，需要生成一堆密钥，当然，这个可以有很多种办法，在这里就不累述了。
转换公钥为bytea :

select dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----Version: GnuPG v1.4.5 (GNU/Linux)
mQGiBFGgIDgRBADALXrWA4Py ...... NHcX/hsefvah7TO4==XGEV-----END PGP PUBLIC KEY BLOCK-----');

得到

\x9901a20451a02038110400c02d7ad60383f24fe323e9b7b6265d2431e5c8b59a9da8ea797ca398369593d8ba8a05af58445303647492d4c21f8f20c48b4ecef932a6f7c85a1c1a306c7074d2a3175bacfa540c6da9d501b7d6f42f9d8ecvbae3c011407f2904d1dc5ff86c79fbda87b4cee

转换私钥为bytea：

select dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----
Version: GnuPG v1.4.5 (GNU/Linux)

lQG7BFGgIDgRBADALXrWA4PyT+Mj6be2Jl0kMeXItZqdqOp5fKOYNpWT2LqKBa9Y
RFMDZHSS1MIfjyDEi0 ...... IDkCGwwACgkQ2jIL
I2NPkS8f0wCgpSCnhRw+soY+Fpg5t7IHjusqNt0An0EeMye7x5uyQc2ikmVtgfZu
NOxi
=FwrW
-----END PGP PRIVATE KEY BLOCK-----');

得到

\x9501bb0451a020381104 ...... f912f1fd300a0a520a7851c3eb2863e169839b7b2078eeb2a36dd009f411e3327bbc79bb241cda292656d81f66e34ec62

使用公钥加密字符串’Hello World!’

select pgp_pub_encrypt('Hello World!', \x9901a20451a02038110400c02d7ad60383f24fe323e9b7b6265d2431e5c8b59a9da8ea797ca398369593d8ba8a05af58445303647 ...... 1a02039021b0c000a0910da320b23634f912f1fd3009e2e02579513158311549b35eaa6f700648113466e009e2bae3c011407f2904d1dc5ff86c79fbda87b4cee');

得到

\xc1c14e031b437bccd670d8451007fe2d921731879c4044946d8f5fc88fb40d4701895534d8d370882ac22b8ea899b014df1a835d3d5520a681c184816acef966e9a57201810eee18136dd8a148d81811de3f39ebaa7ada4e022fa3ce0f5d62937cad62e ...... 184b90e273948e3dc80f121074f0f0b01f980a2d534683fc4b45131380cd70a564f33b72e6c812d858695dbb4

使用私钥解密这串bytea.

select pgp_pub_decrypt('\xc1c14e031b437bccd670d8451007fe2d921731879c4044946d8f5fc88fb40d4701895534d8d370882ac22b8 ...... 4f912f1fd300a0a520a7851c3eb2863e169839b7b2078eeb2a36dd009f411e3327bbc79bb241cda292656d81f66e34ec62');

 pgp_pub_decrypt 
 Hello World!
(1 row)

使用pgp_key_id可以在加密后的数据中取出这份数据是通过对称密钥还是公钥加密的,例如:

select pgp_key_id('\xc30d0404030245811e051118cc136ed23f0198808f069b53264d4a08c2b5dcf3b1c39a34d091263f7f6b64a14808e6ffb32ccc09749105b9cc062d70c628357ab1e2474ff6d109dd083ce892cfa55706');

 pgp_key_id 

 SYMKEY
(1 row)

select pgp_key_id('\xc1c14e031b437bccd670d8451007fe2d921731879c4044946d8f5fc88fb40d4701895534d8d370882ac22b8ea899b ...... 3937184b90e273948e3dc80f121074f0f0b01f980a2d534683fc4b45131380cd70a564f33b72e6c812d858695dbb4');

    pgp_key_id    

 1B437BCCD670D845
(1 row)

4.4 其他函数

（1）bytea和ASCII-armor格式转化

armor(data bytea) returns text
dearmor(data text) returns bytea

（2）产生随机数加密数据bytea的函数

gen_random_bytes(count integer) returns bytea

4.5 小结

• crypt+gen_salt加密速度慢, 被破解的难度高. 密码字符串通常较短, 并且有非常高的安全要求, 适合用于对密码字段进行加密.
• 对称加密则比较适合数据量较大, 并且有安全需求的加密. 通常为了使用方便加密的密钥可以存储在本地库中, 为了提高安全, 也可以把加密的密钥存储在客户端或者其他数据库中.
• 公钥加密手段与对称加密类似, 只是需要生成钥匙对. 使用公钥加密后的数据解密需要用到对应的私钥. 如果私钥加了passphrase保护, 解密时还需要提供私钥的passphrase.