Postgresql 安全管理 PGP加密函数
1 背景知识
1.1 PGP加密函数实现了OpenPGP (RFC 4880) 标准的加密部分。
1、对称加密（加密和解密使用同一把密钥）
2、非对称加密 非对称加密（公钥加密和私钥解密）
3、一个加密的 PGP 消息由两个部分或者包组成：
（1）包含一个会话密钥的包 ： 加密过的对称密钥或者公钥。
（2）包含用会话密钥加密过的数据的包。
参考文档 http://postgres.cn/docs/12/pgcrypto.html#id-1.11.7.34.8
2 对称加密函数
2.1 加密函数定义
pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea
pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea
2.2 解密函数定义
pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text
pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea
Warning
不允许使用pgp_sym_decrypt解密bytea数据。这是为了避免输出非法的字符数据。使用pgp_sym_decrypt_bytea解密原始文本数据是好的（参考非对称加密实验，bytea数据通过convert_from函数转为原始字符串）
2.3 对称加密函数示例
2.3.1 创建测试表
CREATE TABLE custom (id int ,info text);
2.3.2 加密用户表中的数据
INSERT INTO custom
VALUES(1, pgp_sym_encrypt('abcd1234567890', 'password', 'cipher-algo=aes256, compress-algo=2'));
SELECT * FROM custom;
//屏幕输出：
id | info
----+--------------------------
1 | \xc30d04090302a0ea56dea7597e5e77d24a0143a1f4c212345349a3de5a058bb6a3db2c4875fccad34c041f2a10fd1b9f181b56646bd8e1f21db0410b7b0afb0de1317414d4322e0ba
d1a14db40c5fad8ac0a9dd67f5c6beb2c8ff3
(1 row)
2.3.3 解密用户表中的数据
SELECT id, pgp_sym_decryptbytea, 'password' , 'cipher-algo=aes256'
FROM custom;
//屏幕输出：
id | pgp_sym_decrypt
----+-----------------
1 | abcd1234567890
(1 row)
3 非对称加密
3.1 加密函数定义
pgp_pub_encrypt(data text, key bytea [, options text ]) returns bytea
pgp_pub_encrypt_bytea(data bytea, key bytea [, options text ]) returns bytea
Warning
用一个公共 PGP 密钥 key加密data。给这个函数一个私钥会产生一个错误。
3.2 解密函数定义
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
Warning
解密一个公共密钥加密的消息。key必须是对应于用来加密的公钥的私钥。如果私钥是用口令保护的，你必须在psw中给出该口令。如果没有口令，但你想要指定选项，你需要给出一个空口令。
3.3 非对称加密示例
1、产生公钥与密钥时，需要一些随机数，通过rngd快速生成随机数。
2、执行rngd -r /dev/urandom后，可以检查是否有rngd进程在运行
ps -ewf|grep rngd
4、通过gpg生成密钥时报gpg: cancelled by user，是由于tty终端权限问题导致，需要使用root用户修改权限
chmod o+rw $(tty)
5、生成密钥后也可以设置密码，keyid可以通过gpg -k 查询获取
gpg --passwd "test01 (test01) <test01@163.com>"
3.3.1 安装 rng
yum install -y rng-tools
rngd -r /dev/urandom
chmod o+rw $(tty)
3.3.2 生成公钥和私钥
gpg --list-secret-keys # 查看已有密钥，信息不要一致
gpg --gen-key # 生成密钥
输入KEY类别，选择2 -- (2) DSA and Elgamal
选择KEY的长度 -- 2048
输入KEY的有效时间，这里输入的是10年 -- 10y
是否正确 -- y
输入key标识 -- 分别输入real name ,邮箱,comment,随便输入即可
确认 -- O
输入私有密钥的保护密码 -- 输入密码
生成密钥过程中，需要机器有一定的随机输入，所以前面运行了rngd进程。
3.4 导出秘钥
1、导出公钥
gpg --armor --export "jikcheng <jikcheng@163.com>" > public.key
2、导出私钥
gpg --armor --export-secret-keys "jikcheng <jikcheng@163.com>" > secret.key
3.5 加密用户表中的数据
DROP TABLE custom;
CREATE TABLE custom AS
SELECT pgp_pub_encrypt_bytea( '数据001'::bytea,dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----
mQENBGT26LUBCADUuyw93knizmfeO/NImdj9IZGEw0+wdLtZMONnV4zdJBfmaM0H
X1ioEy6ZMt95zVkHenKuWzn2+NQfrZwLmGeEGY8q1P9V/3WDIJ3vbJg9fy5Ck9o0
+zjgjbGY/LTSSYX9M4meCq4GY63sKK7MGZJenAU53RwtZ4XfY/9PNwsbe+cJcUpu
NsMtc7hklBg0twsnB9Wb3mEQrWxXRtSPKiOqciK3btIYe+ifgI57Bc2aG+NS3Vwn
KQOw8gATOSFwbDst28pQW6LuHDO72v5mG7HfbIoLQaapJVXG9w7O9k2+kNp7fw9r
37xdPaV9EJOwxmb8hbtPEY2DWe5E3Sz0IsiVABEBAAG0G2ppa2NoZW5nIDxqaWtj
aGVuZ0AxNjMuY29tPokBVAQTAQgAPhYhBFaMQ13Aqv6YnI2pbbZa0rHnPzzsBQJk
9ui1AhsDBQkDwmcABQsJCAcCBhUKCQgLAgQWAgMBAh4BAheAAAoJELZa0rHnPzzs
NQgH/juAbo4z4vYl5oa/4IDIr4yqHarN35KSLlTfBtMTG3cpe8+n1LLH5myA+/Qw
NQ/gDZjlbuu18ea8Mw767xx1CO6b7XFPZGxBv+bdeK+EhafCkYbaxCtrKBZyM0Dj
i/LA0/PpwsF0c0Vi7qoFDRs74WMEBQvLK3lMF4ggopovVmyqxWYV9ZmRKfDTeJdd
0MNLWbVoYJB2pBUJDW/kL1Bh17YfUaQUpG6DW58m94P/JPxVg9p0QsQn5sA6tLL6
vbtiiKZhuyCOC63ySVqPv924fYocTLy9OotRzzBt7mikf1W8BeFGhoFZHxz/4RE+
yn18xV+qiKdjZ9EUw2OUv3gnW6K5AQ0EZPbotQEIALrQEDBTz/bQ0StF2/K3Z2Un
sw7Pvs7gQznWaydP8Pvq1gQhSeqMBukgodUe32sO8RlR5wxZcUifPhQsXgBiB+wN
MQDrU6S2yvfEO068xvF2QIh3WSFLenziPwxas165j2F3QIQRtxrdZX6cJz5lDZPc
wcZ6ThhwkLEhLmg3kPAtefraD4W4eqVEGqdnpKGDocF5UlpGlfVbXMyWyTDjNSvk
acyjDGMgqHKXCAQ2tAufJwCpWpF4S5EpMOFXeOPoyGbuKgg7aXBvTCtpcYoZkXIF
mie3QXSAcaZ+5qCaEF7oEmmgQCQmHSOp48B1SXZ1c1PUw1AzI5TLHddljeekY38A
EQEAAYkBPAQYAQgAJhYhBFaMQ13Aqv6YnI2pbbZa0rHnPzzsBQJk9ui1AhsMBQkD
wmcAAAoJELZa0rHnPzzswQwIALZB9zLitYt2LBTKVuyzi4+1QwX0x/0TBmsS7jzu
mx4ubeCOMDXs0Zxr42/aEJR4jp4XEbBUsNglTYSan59ZcTmAvpPBfI4dyzN3PtFS
ZTV13RDu+tSvp27XHNJKv5gbWeoEes2ujZjpXveONnLHetX5ELQaqWgjc+IdvfGD
pPh3WGFTCIJp8tNJvP/viEoLdfUZVDmSYAG6WOEDcE1Q1/pio3i6Dw5wicC6z+Gu
0Cmm1GjAIg6MMxrwnl/t+Yt/WpKghoJQ4BflViIOJ+gMAHkMqqwXSsSfaJH2sFec
hFk+rdRcK8tS73z4DVSuec984kbq4S9OKTt8O+RBlVZ76M4=
=i2kk
-----END PGP PUBLIC KEY BLOCK-----
'),'cipher-algo=aes256, compress-algo=2'
) as mm;
3.6 查看加密后的数据
SELECT * FROM custom ; --查看加密后的数据
//屏幕输出：
mm
----------------------
\xc1c04c035aa205d9e009efd001080095289f8d0db21e8805cb2202e87fd84134d8f49d900134c0db90af2f1c3006d612438d3ac47bd65ff3f6b242609221641ffb4cf83876ea62564f15e0
56452b86144edf83d9ef108b8726278390c6cd7272c7d41a47ed01b8be2bd1b3e69a655f3d36dd842f5e02ef1774f5893400c33cfcda4562eb167b2f2887e3c321abad9d4485638c242cf95e6
3c31af6f8b60d7e349e9bbd331218ba20143a0ece2d4528cc628b72332427661341c022b975485aad90609992d6cb4ed595e982c5480311b1b65e01752aa4a20e8cf7d093124b9f183588927c
c40a80973b3ffd4bdbc6491a10f333f2a01b28821cff1e20d618d53d46457d264f096c9075b3a662a9be50d246017a20ddf0c88c36abde534332dbd033c2655ffb4a11d5a5d1bbc1b12dca1a9
8a5b11b7b7bbed3a908e0cf0a973b8a0e406c723c97b613dfa2a564a08c47537fd60aa5ebc175
3.7 解密用户表中的数据
SELECT convert_from( pgp_pub_decrypt_bytea( mm,dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----
lQPGBGT26LUBCADUuyw93knizmfeO/NImdj9IZGEw0+wdLtZMONnV4zdJBfmaM0H
X1ioEy6ZMt95zVkHenKuWzn2+NQfrZwLmGeEGY8q1P9V/3WDIJ3vbJg9fy5Ck9o0
+zjgjbGY/LTSSYX9M4meCq4GY63sKK7MGZJenAU53RwtZ4XfY/9PNwsbe+cJcUpu
NsMtc7hklBg0twsnB9Wb3mEQrWxXRtSPKiOqciK3btIYe+ifgI57Bc2aG+NS3Vwn
KQOw8gATOSFwbDst28pQW6LuHDO72v5mG7HfbIoLQaapJVXG9w7O9k2+kNp7fw9r
37xdPaV9EJOwxmb8hbtPEY2DWe5E3Sz0IsiVABEBAAH+BwMC5UtMebXPq2Pnj18w
j1divikkAxz8/REe2LcYOHssfMgJtSGhQcEH0Ps73AXntHcmCY9TahpZXK04kj6q
30254H3rBAWOOg/C0k1W/hPrZsbTTrkRcx20sJtc5XpdsCz5Xpii5Z2mXhx5xGAU
vsUkNUVoZRcIV7LRFZspZYAe9MBbL0ysPwFhDjwrD9ecps1fHRnG/ltxT4em2z9C
+Nq9yGe7bdOicyppcJm49jBwYM/EiNTFMeOOrTlutnNsKcw9G3lubgaIrNsHHBb5
53uu+kT+6OceCy1jF3Lw2puZjABctCRK71ggaGF0fO+esD8g3bVDKIwfQ7jiw65F
phS56aqFPC4BibtmMhyYMG8yBWm2X1+DfJN7dMqiGn0fD0j/+q8L0uSj8FHC10x8
ixgVmtXl0Z80GAMK669kqiVZXbcNrb9Nfh75i5nDfVyKltRcSHpydSc2HDDi7G7Z
dst+C2kMiNjAdYKGSKZt1Quqjdn3K4MZMQcX19x7+9tE5L7KjkjubgohlJmw/6Py
VTDpQTLb61G1rS+jB/k33yXuYGjW71w8K0gbyKeas8BED44DYuuJcMkcgBk6LCif
5E1mGavCnb0n7jz3MYeoTQb5hrLwPFhScoT/SvWu5VF5idwef1nqC64SLnD/uQpf
QVJQZWJVoZ+xUu+M1PwkxpeGCcIfr5+gV5tsEtfbIHnhEZMup9hnrUhpm8Uzjwkp
I5gTjRTgENrxJEr2TTxvMysUPMllktF0HzboYFCTKg00ofqicFn473NqPbIvJpm4
UCetIEq7eukflSjW9Dsff2yrc2U6s57EVkEU8IjG+1J1jP08AxAqZahHW/BbTD+S
TLqHCFsGOHoK7+SnZoaKLgIugrOKTXbmNi4pHgSgK0GAH6KlAZUvIbCU9aKCsLl7
l0BEkOuAbysKtBtqaWtjaGVuZyA8amlrY2hlbmdAMTYzLmNvbT6JAVQEEwEIAD4W
IQRWjENdwKr+mJyNqW22WtKx5z887AUCZPbotQIbAwUJA8JnAAULCQgHAgYVCgkI
CwIEFgIDAQIeAQIXgAAKCRC2WtKx5z887DUIB/47gG6OM+L2JeaGv+CAyK+Mqh2q
zd+Ski5U3wbTExt3KXvPp9Syx+ZsgPv0MDUP4A2Y5W7rtfHmvDMO+u8cdQjum+1x
T2RsQb/m3XivhIWnwpGG2sQraygWcjNA44vywNPz6cLBdHNFYu6qBQ0bO+FjBAUL
yyt5TBeIIKKaL1ZsqsVmFfWZkSnw03iXXdDDS1m1aGCQdqQVCQ1v5C9QYde2H1Gk
FKRug1ufJveD/yT8VYPadELEJ+bAOrSy+r27YoimYbsgjgut8klaj7/duH2KHEy8
vTqLUc8wbe5opH9VvAXhRoaBWR8c/+ERPsp9fMVfqoinY2fRFMNjlL94J1uinQPG
BGT26LUBCAC60BAwU8/20NErRdvyt2dlJ7MOz77O4EM51msnT/D76tYEIUnqjAbp
IKHVHt9rDvEZUecMWXFInz4ULF4AYgfsDTEA61Oktsr3xDtOvMbxdkCId1khS3p8
4j8MWrNeuY9hd0CEEbca3WV+nCc+ZQ2T3MHGek4YcJCxIS5oN5DwLXn62g+FuHql
RBqnZ6Shg6HBeVJaRpX1W1zMlskw4zUr5GnMowxjIKhylwgENrQLnycAqVqReEuR
KTDhV3jj6Mhm7ioIO2lwb0wraXGKGZFyBZont0F0gHGmfuagmhBe6BJpoEAkJh0j
qePAdUl2dXNT1MNQMyOUyx3XZY3npGN/ABEBAAH+BwMCOSHJux+0K4/npGNDu544
AMiB6mbCrysWPp1tAZdzSNE/+5H1PNDkIHjOwgb9LrsMpGONMSQ4VumA+sEasrCL
OfU4QZjCzpxgPlb5NIEpmwb3F66H1z2U53RL21Dmp7qEj9NFl6dPbHUzGriZcIa6
/UnZiWC0tftABtZsZQNMz1/54hZ8zNzNxBa64JKFE9NzFpKkLFPxW0J/J6GtPkZv
c0zXAMYyj8QJqQJd0p2BFR6CGSEhQFidcz2l+sl2fQHAsVmBicDJwFxLoKAVHftn
mUKh4WNU2vQPnZ1UGVxExgX+XN1xZRMINsqmOS+QM15lGSWVhNsThVAKVbfXhbRi
rUJSV1UhD7K7T+QvjIWenPT2Dp/ptt4GRKHVs9XfClkM1Z00+5180rmqZfPyRo0t
jHjoA3EqSn+LPFFPqJBHGHSiAM+6q5cE7AsqxFNZx6Xk2kxkcYsQ0B7stgfgNuiq
/vDc+ASKq/MJoCyVGnu+9HDbv0FFuRhxLxFT99SZT8bJC57YZ/hfKrFEVfdjP3mz
UtqzOVMbIf8HcRPSbQyIdjjJa3TrKL2A51bth1GKnZ0wBJS4q0j1yp1jVo9InWOm
m3IQ4hZF5JwM+AA70ZuaaQkK4dPMRXid3O8McutTgroaSZ/pkXtp6LWiF49jg+hW
e+RKxwrr2pOR2m+VDGFHUigwjmgxZhB5Wz+Al2uxfS9r6dJwt5zmwMIS6ojBuecx
HtGkfLup8webqATUmXU6DsoGoLnyAxxLg6TqrIRu/ALrlBoCMO2UMaI6P+2LqGhj
YnnqpSBbDWb3vriNHm10ehJDxSUf/E4HIhPG8Cv4GKRgv7Rr4qm7bhZt+NPJ0Pki
WcfdKHL9mxRXIeLH3N4uBLoaXMkRixLYs79kdZS8cpuYPrea3j1/N8XQ+5W+oZxA
dRmychEPiQE8BBgBCAAmFiEEVoxDXcCq/picjalttlrSsec/POwFAmT26LUCGwwF
CQPCZwAACgkQtlrSsec/POzBDAgAtkH3MuK1i3YsFMpW7LOLj7VDBfTH/RMGaxLu
PO6bHi5t4I4wNezRnGvjb9oQlHiOnhcRsFSw2CVNhJqfn1lxOYC+k8F8jh3LM3c+
0VJlNXXdEO761K+nbtcc0kq/mBtZ6gR6za6NmOle9442csd61fkQtBqpaCNz4h29
8YOk+HdYYVMIgmny00m8/++ISgt19RlUOZJgAbpY4QNwTVDX+mKjeLoPDnCJwLrP
4a7QKabUaMAiDowzGvCeX+35i39akqCGglDgF+VWIg4n6AwAeQyqrBdKxJ9okfaw
V5yEWT6t1Fwry1LvfPgNVK55z3ziRurhL04pO3w75EGVVnvozg==
=HbGi
-----END PGP PRIVATE KEY BLOCK-----'),'123'
),'UTF-8') FROM custom ;
//屏幕输出：
convert_from
--------------
数据001
(1 row)
4 混合使用对称加密和非对称加密
1、 对称加密加密与解密使用的是同样的密钥，所以速度快，但由于需要将密钥在网络传输，所以安全性不高。
2、 非对称加密使用了一对密钥，公钥与私钥，所以安全性高，但加密与解密速度慢。
3、解决的办法是将对称加密的密钥使用非对称加密的公钥进行加密，然后发送出去，接收方使用私钥进行解密得到对称加密的密钥，然后双方可以使用对称加密来进行沟通。
5 常用 PGP 加密函数选项
5.1 cipher-algo
1、要使用哪个密码算法。
2、默认值： aes128 。
3、支持的选项：bf,aes128,aes192,aes256
4、支持的函数：pgp_sym_encrypt, pgp_pub_encrypt
5.2 compress-algo
1、要使用哪种压缩算法。
2、默认值：6。
3、支持的选项： 1-9。
4、支持的函数：pgp_sym_encrypt, pgp_pub_encrypt
5.3 convert-crlf
1、加密时是否把 \n 转换成 \r\n。
2、解密时是否把 \r\n 转换成 \n。
3、RFC 4880 指定文本数据存储时应该使用 \r\n 换行。
4、使用这个选项能够得到完全 RFC 兼容的行为。
5、默认值：0
6、支持的选项： 0,1。
7、适用于：pgp_sym_encrypt, pgp_pub_encrypt, pgp_sym_decrypt, pgp_pub_decrypt
5.4 disable-mdc
1、不用 SHA-1 保护数据。
2、使用这个选项的唯一好的理由是实现与古董级别 PGP 产品的兼容，这些产品在受 SHA-1 保护的包被加入到 RFC 4880 之前就已经存在了。
3、默认值：0
4、支持的选项： 0,1。
5、支持的函数：pgp_sym_encrypt, pgp_pub_encrypt
5.5 sess-key
使用单独的会话密钥。公钥加密总是使用一个单独的会话密钥。这个选项是用于对称密钥加密的，对称密钥加密默认直接使用 S2K 密钥。
默认值：0。
支持的选项：0，1。
适用于：pgp_sym_encrypt
5.6 s2k-mode
要使用哪一种 S2K 算法。
默认值：3
值：
0 - 不用 salt。危险！
1 - 用 salt 但是使用固定的迭代计数。
3 - 可变的迭代计数。
适用于：pgp_sym_encrypt
5.7 s2k -count
S2K 算法要使用的迭代次数。它必须是一个位于 1024 和 65011712 之间的值， 首尾两个值包括在内。
默认：65536 和 253952 之间的一个随机值
适用于：pgp_sym_encrypt，只能用于 s2k-mode=3
5.8 s2k -digest-algo
要在 S2K 计算中使用哪种摘要算法。
值：md5, sha1
默认：sha1
适用于：pgp_sym_encrypt
5.9 s2k -cipher-algo
要用哪种密码来加密独立的会话密钥。
值：bf, aes, aes128, aes192, aes256
默认：use cipher-algo
适用于：pgp_sym_encrypt
unicode-mode
是否把文本数据在数据库内部编码和 UTF-8 之间来回转换。如果你的数据库已经是 UTF-8，将不会转换，但是消息将被标记为 UTF-8。没有这个选项它将不会被标记。
值：0，1
默认：0
适用于：pgp_sym_encrypt, pgp_pub_encrypt