PostgreSQL column cryptographic use pgcrypto extension and optional openssl lib-白红宇

前面介绍了PostgreSQL 服务端和客户端之间的数据传输加密, 还介绍了数据库服务器的文件系统或者目录加密.

但是要知道数据都是以明文存储在数据库中的。如果未使用数据传输加密的话, 数据一旦在传输中被截获的话就很容易泄漏数据。

本文将要介绍的是数据内容的加密。

数据内容的加密可以在数据库服务端进行加解密, 也可以在客户端进行加解密.

在数据库服务端加解密的话, 网络上传输的还是未加密的内容, 所以为了防御网络数据截获, 建议还是要搭配使用数据传输加密.

如果是在客户端进行加解密的话, 网络上传输的是加密后的内容, 所以不必担心网络数据截获的问题.

下面以pgcrypto这个扩展包为例, 讲一下数据库服务端加密.

1. 创建pgcrypto扩展包

pg92@db-172-16-3-40-> psql -h 172.16.3.33 -p 1999 -U postgres digoalpsql (9.2beta1, server 9.3devel)WARNING: psql version 9.2, server version 9.3.         Some psql features might not work.SSL connection (cipher: RC4-SHA, bits: 128)Type "help" for help.digoal=# create extension pgcrypto;CREATE EXTENSION

2. 扩展包新建了一些加密和解密的函数.

详见末尾部分.

下面主要针对新增的加解密函数进行讲解.

3. 计算hash值的函数.

digest(data text, type text) returns byteadigest(data bytea, type text) returns bytea

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

例如 :

digoal=# select digest('I am digoal.', 'md5');               digest               ------------------------------------ \xc3b0fb1147858d2259d92f20668fc8f9(1 row)每次运算得到的hash值都一样.digoal=# select digest('I am digoal.', 'md5');               digest               ------------------------------------ \xc3b0fb1147858d2259d92f20668fc8f9(1 row)

但是请注意函数参数, 如果要对bytea hash, 那么请在输入参数时指定参数类型bytea.

例如以下两次调用分别调用了2个函数. 所以得到的结果也是不一样的.

digoal=# select digest('\xffffff'::bytea, 'md5');               digest               ------------------------------------ \x8597d4e7e65352a302b63e07bc01a7da(1 row)digoal=# select digest('\xffffff', 'md5');               digest               ------------------------------------ \xd721f40e22920e0fd8ac7b13587aa92d(1 row)

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

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

这两个函数与digest类似, 只是多了一个key参数, 也就是说同一个被加密的值, 可以使用不同的key得到不同的hash值.

这样的做法是, 不知道key的话, 也无法逆向破解原始值.

使用hmac还有一个好处是, 使用digest如果原始值和hash值同时被别人修改了是无法知道是否被修改的.

但是使用hmac, 如果原始值被修改了, 同时key没有泄漏的话, 那么hash值是无法被修改的, 因此就能够知道原始值是否被修改过.

digoal=# select hmac('I am digoal.', 'this is a key', 'md5');                hmac                ------------------------------------ \xc70d0fd2af2382ea8e0a7ffd9edcbd58(1 row)digoal=# select hmac('I am digoal.', 'this is a key', 'md5');                hmac                ------------------------------------ \xc70d0fd2af2382ea8e0a7ffd9edcbd58(1 row)digoal=# select hmac('I am digoal.', 'this is b key', 'md5');                hmac                ------------------------------------ \x4518090fb07fc672b66c829e43fd62dc(1 row)digoal=# select hmac('I am digoal.', 'this is b key', 'md5');                hmac                ------------------------------------ \x4518090fb07fc672b66c829e43fd62dc(1 row)

如果key的长度超过了block size, 那么key会先hash一次, 然后hash值作为key.

4. 以上hash函数只要原始值一致, 每次得到的hash值是一样的, 虽然hmac多了key的参数, 但是只要key和原始数据一样, 得到的hash值也是一样的. 这样的加密很可能被逆向破解掉.

下面的2个函数主要是提高了逆向破解的难度, 增强了数据的安全性.

crypt(password text, salt text) returns textgen_salt(type text [, iter_count integer ]) returns text

crypt(), 用来计算hash值.

gen_salt(), 用来产生随机的参数给crypt. type参数为des, xdes, md5, bf. iter_count指迭代次数, 数字越大加密时间越长, 被破解需要的时间也越长.

Table F-16. Iteration Counts for crypt()

Algorithm	Default	Min	Max
xdes	725	1	16777215
bf	6	4	31

xdes的iter_count必须为奇数.

crypt和gen_salt结合使用的话, 同一个原始值, 每次得到的hash值是不一样的.

digoal=# select crypt('this is a pwd source', gen_salt('md5'));               crypt                ------------------------------------ $1$CAp4ifAa$p261Vfku7HDnwx8cuFhsq/(1 row)digoal=# select crypt('this is a pwd source', gen_salt('bf',10));                            crypt                             -------------------------------------------------------------- $2a$10$tvNK2H9mPu1tU5L6oAHdSeze5Hlz7G0y4oEKNg9SlGa06J2sywZHu(1 row)

# 每次运算可以得到不同的hash值.

digoal=# select crypt('this is a pwd source', gen_salt('md5'));               crypt                ------------------------------------ $1$p2Sg93iZ$SCdUePtZVuyIJBDO1cEYh1(1 row)digoal=# select crypt('this is a pwd source', gen_salt('bf',10));                            crypt                             -------------------------------------------------------------- $2a$10$cgJiTAs55vMBqYR1kGMGXuMKZI4dsayna4wgEL4K7duYkD0g25ufW(1 row)

原因是gen_salt每次都会给出1个随机值.

digoal=# select gen_salt('bf',10);           gen_salt            ------------------------------- $2a$10$qY0amXGalzj14rooSpTf5e(1 row)digoal=# select gen_salt('bf',10);           gen_salt            ------------------------------- $2a$10$TmqxqmOd8R3scnPk25Pp2O(1 row)

计算好hash后匹配是怎么做的呢?

看下面, hash值作为第二个参数, 得到的值就是hash值. 所以这样就可以进行匹配了.

digoal=# select crypt('this is a pwd source', gen_salt('bf',10));                            crypt                             -------------------------------------------------------------- $2a$10$x5bQ0wcHc0.li1XcDbzSyOvpjPOKlzg.psbNaA33VH.gAfKbA7.IK(1 row)digoal=# select crypt('this is a pwd source', gen_salt('bf',10));                            crypt                             -------------------------------------------------------------- $2a$10$X7D5eMxcaXNn.BPkPnb1GuSVxDUjNiO5M/9ss9GLHpgGq6s3hbPrG(1 row)digoal=# select crypt('this is a pwd source', '$2a$10$X7D5eMxcaXNn.BPkPnb1GuSVxDUjNiO5M/9ss9GLHpgGq6s3hbPrG');                            crypt                             -------------------------------------------------------------- $2a$10$X7D5eMxcaXNn.BPkPnb1GuSVxDUjNiO5M/9ss9GLHpgGq6s3hbPrG(1 row)digoal=# select crypt('this is a pwd source', '$2a$10$x5bQ0wcHc0.li1XcDbzSyOvpjPOKlzg.psbNaA33VH.gAfKbA7.IK');                            crypt                             -------------------------------------------------------------- $2a$10$x5bQ0wcHc0.li1XcDbzSyOvpjPOKlzg.psbNaA33VH.gAfKbA7.IK(1 row)

假设用它来存密码 :

digoal=# create table userpwd(userid int8 primary key, pwd text);CREATE TABLEdigoal=# insert into userpwd (userid,pwd) values (1, crypt('this is a pwd source', gen_salt('bf',10)));INSERT 0 1输入错误的密码, 返回假digoal=# select crypt('this is a error pwd source', pwd)=pwd from userpwd where userid =1; ?column? ---------- f(1 row)输入正确的密码, 返回真digoal=# select crypt('this is a pwd source', pwd)=pwd from userpwd where userid =1; ?column? ---------- t(1 row)

crypt支持的算法如下 :

Table F-15. Supported Algorithms for crypt()

Algorithm	Max Password Length	Adaptive?	Salt Bits	Description
bf	72	yes	128	Blowfish-based, variant 2a
md5	unlimited	no	48	MD5-based crypt
xdes	8	yes	24	Extended DES
des	8	no	12	Original UNIX crypt

当然, crypt和gen_salt是以牺牲hash速度为代价来换取安全性的.

Algorithm	Hashes/sec	For [a-z]	For [A-Za-z0-9]
crypt-bf/8	28	246 years	251322 years
crypt-bf/7	57	121 years	123457 years
crypt-bf/6	112	62 years	62831 years
crypt-bf/5	211	33 years	33351 years
crypt-md5	2681	2.6 years	2625 years
crypt-des	362837	7 days	19 years
sha1	590223	4 days	12 years
md5	2345086	1 day	3 years

以上hash计算速度取自1.5G的奔腾4 CPU. 以及如下测试软件 :

crypt-des and crypt-md5 algorithm numbers are taken from John the Ripper v1.6.38 -test output.md5 numbers are from mdcrack 1.2.sha1 numbers are from lcrack-20031130-beta.crypt-bf numbers are taken using a simple program that loops over 1000 8-character passwords. That way I can show the speed with different numbers of iterations. For reference: john -test shows 213 loops/sec for crypt-bf/5. (The very small difference in results is in accordance with the fact that the crypt-bf implementation in pgcrypto is the same one used in John the Ripper.)

原则上, iter_count的选择以hash计算速度为准则, 以当前流行的CPU, 并且实测后取4-100每秒之间为宜.

5. PGP 加密函数.

PostgreSQL pgcrypto扩展包中的pgp加解密函数遵循OpenPGP (RFC 4880)标准.

加密的PGP消息由2部分组成 :

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

2. 使用该会话密钥加密的数据, 可以选择加密选项例如

compress-algo, unicode-mode, cipher-algo, compress-level, convert-crlf, disable-mdc, enable-session-key, s2k-mode, s2k-digest-algo, s2k-cipher-algo

使用对称密钥加解密的函数如下 :

pgp_sym_encrypt(data text, psw text [, options text ]) returns byteapgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns byteapgp_sym_decrypt(msg bytea, psw text [, options text ]) returns textpgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea

options参考pgcrypto加密选项部分.

5.1 对称加密举例 :

使用对称密钥加密, 这里的对称密钥为'pwd'字符串 : digoal=# select pgp_sym_encrypt('i am digoal', 'pwd', 'cipher-algo=bf, compress-algo=2, compress-level=9');                                                                          pgp_sym_encrypt                                                                           -------------------------------------------------------------------------------------------------------------------------------------------------------------------- \xc30d0404030245811e051118cc136ed23f0198808f069b53264d4a08c2b5dcf3b1c39a34d091263f7f6b64a14808e6ffb32ccc09749105b9cc062d70c628357ab1e2474ff6d109dd083ce892cfa55706(1 row)解密也需要使用加密时的对称密钥 : digoal=# select pgp_sym_decrypt('\xc30d0404030245811e051118cc136ed23f0198808f069b53264d4a08c2b5dcf3b1c39a34d091263f7f6b64a14808e6ffb32ccc09749105b9cc062d70c628357ab1e2474ff6d109dd083ce892cfa55706'::bytea, 'pwd'); pgp_sym_decrypt ----------------- i am digoal(1 row)

5.2 使用公钥加解密的函数如下 :

pgp_pub_encrypt(data text, key bytea [, options text ]) returns byteapgp_pub_encrypt_bytea(data bytea, key bytea [, options text ]) returns byteapgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text ]]) returns textpgp_pub_decrypt_bytea(msg bytea, key bytea [, psw text [, options text ]]) returns bytea

options参考

pgcrypto

加密选项部分.

公钥加密举例 :

首先要使用gpg生成一对公钥和密钥. 当然你也可以生成很多对.

加密用公钥, 解密用对应的私钥即可.

公钥和私钥都可以放在客户端, 这样的话数据库中只存储加密后的数据, 攻击者获取数据后也无法对它进行解密.

生成一对公钥密钥

pg92@db-172-16-3-40-> gpg --gen-keygpg (GnuPG) 1.4.5; Copyright (C) 2006 Free Software Foundation, Inc.This program comes with ABSOLUTELY NO WARRANTY.This is free software, and you are welcome to redistribute itunder certain conditions. See the file COPYING for details.Please select what kind of key you want:   (1) DSA and Elgamal (default)   (2) DSA (sign only)   (5) RSA (sign only)Your selection? 1  #选择1DSA keypair will have 1024 bits.ELG-E keys may be between 1024 and 4096 bits long.What keysize do you want? (2048) #选择2048Requested keysize is 2048 bitsPlease specify how long the key should be valid.         0 = key does not expire      
                 
                    = key expires in n days      
                  
                   w = key expires in n weeks      
                   
                    m = key expires in n months      
                    
                     y = key expires in n yearsKey is valid for? (0) #选择0, 永不过期.Key does not expire at allIs this correct? (y/N) y #输入yYou need a user ID to identify your key; the software constructs the user IDfrom the Real Name, Comment and Email Address in this form:    "Heinrich Heine (Der Dichter) 
                     
                      "Real name: digoal  # 必须输入的是name, 后面要用到这个name导出公钥和密钥.Email address: Comment: You selected this USER-ID:    "digoal"Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O  # 输入O确认You need a Passphrase to protect your secret key.# 这里可以选择输入保护密钥的passphrase, 当然也可以不输入留空. # 如果输入了, 那么使用pgp_pub_decrypt函数解密数据时则需要输入这个 passphrase.# 本例不输入passphrase.You don't want a passphrase - this is probably a *bad* idea!I will do it anyway.  You can change your passphrase at any time,using this program with the option "--edit-key".# 马上在另一个会话中执行ll -R /. 这样可以让服务器产生一些动作. 否则可能造成如下错误 : # Not enough random bytes available.  Please do some other work to giveWe need to generate a lot of random bytes. It is a good idea to performsome other action (type on the keyboard, move the mouse, utilize thedisks) during the prime generation; this gives the random numbergenerator a better chance to gain enough entropy.+++++++++++++++.++++++++++.++++++++++..++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++.+++++.+++++.+++++.++++++++++++++++++++>.++++++++++....>+++++............<+++++............+++++We need to generate a lot of random bytes. It is a good idea to performsome other action (type on the keyboard, move the mouse, utilize thedisks) during the prime generation; this gives the random numbergenerator a better chance to gain enough entropy.++++++++++++++++++++.+++++..+++++...+++++.+++++.++++++++++.++++++++++++++++++++..+++++.+++++.++++++++++..+++++++++++++++++++++++++.+++++++++++++++.++++++++++.+++++>.+++++.+++++>+++++.>+++++>+++++<+++++....+++++^^^gpg: key 634F912F marked as ultimately trustedpublic and secret key created and signed.gpg: checking the trustdbgpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust modelgpg: depth: 0  valid:   1  signed:   0  trust: 0-, 0q, 0n, 0m, 0f, 1upub   1024D/634F912F 2013-05-25      Key fingerprint = D2CD D9B8 F1C3 D940 33E9  9E94 DA32 0B23 634F 912Fuid                  digoalsub   2048g/D670D845 2013-05-25

#　接下来要生成公钥和密钥.

# 列出当前系统中的钥匙串

pg92@db-172-16-3-40-> gpg --list-secret-keys/home/pg92/.gnupg/secring.gpg-----------------------------sec   1024D/634F912F 2013-05-25uid                  digoalssb   2048g/D670D845 2013-05-25

# 导出uid=digoal的公钥

pg92@db-172-16-3-40-> gpg -a --export digoal > public.key

# 导出uid=digoal的密钥

pg92@db-172-16-3-40-> gpg -a --export-secret-keys digoal > secret.key

# 查看公钥和密钥内容

公钥内容 :

pg92@db-172-16-3-40-> cat public.key -----BEGIN PGP PUBLIC KEY BLOCK-----Version: GnuPG v1.4.5 (GNU/Linux)mQGiBFGgIDgRBADALXrWA4PyT+Mj6be2Jl0kMeXItZqdqOp5fKOYNpWT2LqKBa9YRFMDZHSS1MIfjyDEi07O+TKm98haHBowbHB00qMXW6z6VAxtqdUBt9b0L52OwkA9awsclpalPvLwDAQGpxlJh7aQJ0hHjQRgvfTqJpOoCF4WoMVVSin5Ox2P4wCgzcr7FtLhbysH/Axqmx6Oc3wG3FMD/ij4ES38IDNAoacCOuWlA6MeaxWzgVka1Zl/h2ktUVJOLeM0saF6Z570/RYYFASC5cVazCG9Gbq6a3WvCy6LW9hV5XZIOU1VBXYxaCP1sMeWgRSbVathdJMqbcz+kzqabiCVHDt5Q8k/TuIEvmkTnUbk1ca1GTxRJMhXYldZLDAcA/4hnPbjxUUtkQ9S6gP3Cih/8SOA/E4YIj5PK3S9nRc6OKZ9NiGVXmhqff8PA4TmX08DiVclDzsaxpmB1yYUc44/rhEZ53XxKfHzjvowNKEevBm+cPEc0U9a5VSFIBwfwuAAVbbfdmLKM+HcBsPc/3uRnNGRZX6mMvUAG9UCk50zUrQGZGlnb2FsiGAEExECACAFAlGgIDgCGwMGCwkIBwMCBBUCCAMEFgIDAQIeAQIXgAAKCRDaMgsjY0+RL6mpAJ97/he5t1uatNKyO5v00bfuq9hMrQCgyihwLNzCn3immQp1E8fsg0Rfmaq5Ag0EUaAgORAIAPf4AB8dn2tYQlqOu3cC4g+yD95RV+lxERWLwMNzH1aVuSmBQzZA7pZuAyt9Joy6kwgkcilWtP1XgwUQBnyfS5QOiqNAbDoKFZWxLVKPpvb8jk20zbiWvqH9IUVlMaRjJrY1kdC1ckPFmx17k4uFU9GKFrfx/VukaTBhK3iByUD4JGUvmqKJvu3DX3JliIH4PJaBFxp/nOIy/66gPPz2DkSTBBNYTNVtMqkDLz2gcAWQ99TWsEXRskehPk9FSUDvrs62vC7ZsGmwihSMt/B/gQHc9rmEs5RkqNfyYKUoI4d5UCQdWnjI+V5Sppq6HQhRJ9M0ytoqmYJDKioKR0ewBQMAAwUH/jOZn7ei1rZLv0RP0Y+/E0ROkzpNmMuP+mNvZNrf/PCd1SvPxFZ2MNnhB0JN9a1OjJD8otqqvxMujyTx5z0RqD+7mWKb/q96NpG+fApZNGt6YiTc4a9FV9jpf+fYZyfpOj/bmPpHIUtheGzx/+WIL9gHWDiFR0nP9uXZoDZotuPqEsH2acoIE4oB4lLBvajuDtwnAZlajHMgXZD9W/xzdAlR5frfGNVIdvylwN2SOfSavl4VM4hG1uFc2J4szmivK0TesP1UcIdxnTlTvFieEqaP2rpG6WfVVO7N5ZiWXYOuazzRSEtfTjnGZRnx+WmkUb5KNvVhSg8F7oB7WrKMH/2ISQQYEQIACQUCUaAgOQIbDAAKCRDaMgsjY0+RLx/TAJ4uAleVExWDEVSbNeqm9wBkgRNGbgCeK648ARQH8pBNHcX/hsefvah7TO4==XGEV-----END PGP PUBLIC KEY BLOCK-----

私钥内容 :

pg92@db-172-16-3-40-> cat secret.key -----BEGIN PGP PRIVATE KEY BLOCK-----Version: GnuPG v1.4.5 (GNU/Linux)lQG7BFGgIDgRBADALXrWA4PyT+Mj6be2Jl0kMeXItZqdqOp5fKOYNpWT2LqKBa9YRFMDZHSS1MIfjyDEi07O+TKm98haHBowbHB00qMXW6z6VAxtqdUBt9b0L52OwkA9awsclpalPvLwDAQGpxlJh7aQJ0hHjQRgvfTqJpOoCF4WoMVVSin5Ox2P4wCgzcr7FtLhbysH/Axqmx6Oc3wG3FMD/ij4ES38IDNAoacCOuWlA6MeaxWzgVka1Zl/h2ktUVJOLeM0saF6Z570/RYYFASC5cVazCG9Gbq6a3WvCy6LW9hV5XZIOU1VBXYxaCP1sMeWgRSbVathdJMqbcz+kzqabiCVHDt5Q8k/TuIEvmkTnUbk1ca1GTxRJMhXYldZLDAcA/4hnPbjxUUtkQ9S6gP3Cih/8SOA/E4YIj5PK3S9nRc6OKZ9NiGVXmhqff8PA4TmX08DiVclDzsaxpmB1yYUc44/rhEZ53XxKfHzjvowNKEevBm+cPEc0U9a5VSFIBwfwuAAVbbfdmLKM+HcBsPc/3uRnNGRZX6mMvUAG9UCk50zUgAAn2j3pAZqpbN3xfFFL2YYhP+ePmrnCzm0BmRpZ29hbIhgBBMRAgAgBQJRoCA4AhsDBgsJCAcDAgQVAggDBBYCAwECHgECF4AACgkQ2jILI2NPkS+pqQCfe/4XubdbmrTSsjub9NG37qvYTK0AoMoocCzcwp94ppkKdRPH7INEX5mqnQI9BFGgIDkQCAD3+AAfHZ9rWEJajrt3AuIPsg/eUVfpcREVi8DDcx9WlbkpgUM2QO6WbgMrfSaMupMIJHIpVrT9V4MFEAZ8n0uUDoqjQGw6ChWVsS1Sj6b2/I5NtM24lr6h/SFFZTGkYya2NZHQtXJDxZsde5OLhVPRiha38f1bpGkwYSt4gclA+CRlL5qiib7tw19yZYiB+DyWgRcaf5ziMv+uoDz89g5EkwQTWEzVbTKpAy89oHAFkPfU1rBF0bJHoT5PRUlA767Otrwu2bBpsIoUjLfwf4EB3Pa5hLOUZKjX8mClKCOHeVAkHVp4yPleUqaauh0IUSfTNMraKpmCQyoqCkdHsAUDAAMFB/4zmZ+3ota2S79ET9GPvxNETpM6TZjLj/pjb2Ta3/zwndUrz8RWdjDZ4QdCTfWtToyQ/KLaqr8TLo8k8ec9Eag/u5lim/6vejaRvnwKWTRremIk3OGvRVfY6X/n2Gcn6To/25j6RyFLYXhs8f/liC/YB1g4hUdJz/bl2aA2aLbj6hLB9mnKCBOKAeJSwb2o7g7cJwGZWoxzIF2Q/Vv8c3QJUeX63xjVSHb8pcDdkjn0mr5eFTOIRtbhXNieLM5orytE3rD9VHCHcZ05U7xYnhKmj9q6Ruln1VTuzeWYll2Drms80UhLX045xmUZ8flppFG+Sjb1YUoPBe6Ae1qyjB/9AAFUCNmPaOMHsDg6bZpo7ApZJbJsiW6ZBTCojWYF4E2C+5bPQoietziIE5V6LhPdiEkEGBECAAkFAlGgIDkCGwwACgkQ2jILI2NPkS8f0wCgpSCnhRw+soY+Fpg5t7IHjusqNt0An0EeMye7x5uyQc2ikmVtgfZuNOxi=FwrW-----END PGP PRIVATE KEY BLOCK-----

# 使用公钥加密数据, 使用私钥解密数据时,

# pgcrypto 需要字节流数据.

# 所以在使用公钥和私钥前请先把他们转换为bytea.

转换公钥为bytea :

digoal=# select dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----digoal'# Version: GnuPG v1.4.5 (GNU/Linux)digoal'# digoal'# mQGiBFGgIDgRBADALXrWA4PyT+Mj6be2Jl0kMeXItZqdqOp5fKOYNpWT2LqKBa9Ydigoal'# RFMDZHSS1MIfjyDEi07O+TKm98haHBowbHB00qMXW6z6VAxtqdUBt9b0L52OwkA9digoal'# awsclpalPvLwDAQGpxlJh7aQJ0hHjQRgvfTqJpOoCF4WoMVVSin5Ox2P4wCgzcr7digoal'# FtLhbysH/Axqmx6Oc3wG3FMD/ij4ES38IDNAoacCOuWlA6MeaxWzgVka1Zl/h2ktdigoal'# UVJOLeM0saF6Z570/RYYFASC5cVazCG9Gbq6a3WvCy6LW9hV5XZIOU1VBXYxaCP1digoal'# sMeWgRSbVathdJMqbcz+kzqabiCVHDt5Q8k/TuIEvmkTnUbk1ca1GTxRJMhXYldZdigoal'# LDAcA/4hnPbjxUUtkQ9S6gP3Cih/8SOA/E4YIj5PK3S9nRc6OKZ9NiGVXmhqff8Pdigoal'# A4TmX08DiVclDzsaxpmB1yYUc44/rhEZ53XxKfHzjvowNKEevBm+cPEc0U9a5VSFdigoal'# IBwfwuAAVbbfdmLKM+HcBsPc/3uRnNGRZX6mMvUAG9UCk50zUrQGZGlnb2FsiGAEdigoal'# ExECACAFAlGgIDgCGwMGCwkIBwMCBBUCCAMEFgIDAQIeAQIXgAAKCRDaMgsjY0+Rdigoal'# L6mpAJ97/he5t1uatNKyO5v00bfuq9hMrQCgyihwLNzCn3immQp1E8fsg0Rfmaq5digoal'# Ag0EUaAgORAIAPf4AB8dn2tYQlqOu3cC4g+yD95RV+lxERWLwMNzH1aVuSmBQzZAdigoal'# 7pZuAyt9Joy6kwgkcilWtP1XgwUQBnyfS5QOiqNAbDoKFZWxLVKPpvb8jk20zbiWdigoal'# vqH9IUVlMaRjJrY1kdC1ckPFmx17k4uFU9GKFrfx/VukaTBhK3iByUD4JGUvmqKJdigoal'# vu3DX3JliIH4PJaBFxp/nOIy/66gPPz2DkSTBBNYTNVtMqkDLz2gcAWQ99TWsEXRdigoal'# skehPk9FSUDvrs62vC7ZsGmwihSMt/B/gQHc9rmEs5RkqNfyYKUoI4d5UCQdWnjIdigoal'# +V5Sppq6HQhRJ9M0ytoqmYJDKioKR0ewBQMAAwUH/jOZn7ei1rZLv0RP0Y+/E0ROdigoal'# kzpNmMuP+mNvZNrf/PCd1SvPxFZ2MNnhB0JN9a1OjJD8otqqvxMujyTx5z0RqD+7digoal'# mWKb/q96NpG+fApZNGt6YiTc4a9FV9jpf+fYZyfpOj/bmPpHIUtheGzx/+WIL9gHdigoal'# WDiFR0nP9uXZoDZotuPqEsH2acoIE4oB4lLBvajuDtwnAZlajHMgXZD9W/xzdAlRdigoal'# 5frfGNVIdvylwN2SOfSavl4VM4hG1uFc2J4szmivK0TesP1UcIdxnTlTvFieEqaPdigoal'# 2rpG6WfVVO7N5ZiWXYOuazzRSEtfTjnGZRnx+WmkUb5KNvVhSg8F7oB7WrKMH/2Idigoal'# SQQYEQIACQUCUaAgOQIbDAAKCRDaMgsjY0+RLx/TAJ4uAleVExWDEVSbNeqm9wBkdigoal'# gRNGbgCeK648ARQH8pBNHcX/hsefvah7TO4=digoal'# =XGEVdigoal'# -----END PGP PUBLIC KEY BLOCK-----');得到\x9901a20451a02038110400c02d7ad60383f24fe323e9b7b6265d2431e5c8b59a9da8ea797ca398369593d8ba8a05af58445303647492d4c21f8f20c48b4ecef932a6f7c85a1c1a306c7074d2a3175bacfa540c6da9d501b7d6f42f9d8ec2403d6b0b1c9696a53ef2f00c0406a7194987b6902748478d0460bdf4ea2693a8085e16a0c5554a29f93b1d8fe300a0cdcafb16d2e16f2b07fc0c6a9b1e8e737c06dc5303fe28f8112dfc203340a1a7023ae5a503a31e6b15b381591ad5997f87692d51524e2de334b1a17a679ef4fd1618140482e5c55acc21bd19baba6b75af0b2e8b5bd855e57648394d550576316823f5b0c79681149b55ab6174932a6dccfe933a9a6e20951c3b7943c93f4ee204be69139d46e4d5c6b5193c5124c8576257592c301c03fe219cf6e3c5452d910f52ea03f70a287ff12380fc4e18223e4f2b74bd9d173a38a67d3621955e686a7dff0f0384e65f4f038957250f3b1ac69981d72614738e3fae1119e775f129f1f38efa3034a11ebc19be70f11cd14f5ae55485201c1fc2e00055b6df7662ca33e1dc06c3dcff7b919cd191657ea632f5001bd502939d3352b4066469676f616c8860041311020020050251a02038021b03060b090807030204150208030416020301021e01021780000a0910da320b23634f912fa9a9009f7bfe17b9b75b9ab4d2b23b9bf4d1b7eeabd84cad00a0ca28702cdcc29f78a6990a7513c7ec83445f99aab9020d0451a02039100800f7f8001f1d9f6b58425a8ebb7702e20fb20fde5157e97111158bc0c3731f5695b92981433640ee966e032b7d268cba930824722956b4fd57830510067c9f4b940e8aa3406c3a0a1595b12d528fa6f6fc8e4db4cdb896bea1fd21456531a46326b63591d0b57243c59b1d7b938b8553d18a16b7f1fd5ba46930612b7881c940f824652f9aa289beedc35f72658881f83c9681171a7f9ce232ffaea03cfcf60e44930413584cd56d32a9032f3da0700590f7d4d6b045d1b247a13e4f454940efaeceb6bc2ed9b069b08a148cb7f07f8101dcf6b984b39464a8d7f260a52823877950241d5a78c8f95e52a69aba1d085127d334cada2a9982432a2a0a4747b0050300030507fe33999fb7a2d6b64bbf444fd18fbf13444e933a4d98cb8ffa636f64dadffcf09dd52bcfc4567630d9e107424df5ad4e8c90fca2daaabf132e8f24f1e73d11a83fbb99629bfeaf7a3691be7c0a59346b7a6224dce1af4557d8e97fe7d86727e93a3fdb98fa47214b61786cf1ffe5882fd8075838854749cff6e5d9a03668b6e3ea12c1f669ca08138a01e252c1bda8ee0edc2701995a8c73205d90fd5bfc73740951e5fadf18d54876fca5c0dd9239f49abe5e15338846d6e15cd89e2cce68af2b44deb0fd547087719d3953bc589e12a68fdaba46e967d554eecde598965d83ae6b3cd1484b5f4e39c66519f1f969a451be4a36f5614a0f05ee807b5ab28c1ffd8849041811020009050251a02039021b0c000a0910da320b23634f912f1fd3009e2e02579513158311549b35eaa6f700648113466e009e2bae3c011407f2904d1dc5ff86c79fbda87b4cee

转换私钥为bytea :

digoal=# select dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----digoal'# Version: GnuPG v1.4.5 (GNU/Linux)digoal'# digoal'# lQG7BFGgIDgRBADALXrWA4PyT+Mj6be2Jl0kMeXItZqdqOp5fKOYNpWT2LqKBa9Ydigoal'# RFMDZHSS1MIfjyDEi07O+TKm98haHBowbHB00qMXW6z6VAxtqdUBt9b0L52OwkA9digoal'# awsclpalPvLwDAQGpxlJh7aQJ0hHjQRgvfTqJpOoCF4WoMVVSin5Ox2P4wCgzcr7digoal'# FtLhbysH/Axqmx6Oc3wG3FMD/ij4ES38IDNAoacCOuWlA6MeaxWzgVka1Zl/h2ktdigoal'# UVJOLeM0saF6Z570/RYYFASC5cVazCG9Gbq6a3WvCy6LW9hV5XZIOU1VBXYxaCP1digoal'# sMeWgRSbVathdJMqbcz+kzqabiCVHDt5Q8k/TuIEvmkTnUbk1ca1GTxRJMhXYldZdigoal'# LDAcA/4hnPbjxUUtkQ9S6gP3Cih/8SOA/E4YIj5PK3S9nRc6OKZ9NiGVXmhqff8Pdigoal'# A4TmX08DiVclDzsaxpmB1yYUc44/rhEZ53XxKfHzjvowNKEevBm+cPEc0U9a5VSFdigoal'# IBwfwuAAVbbfdmLKM+HcBsPc/3uRnNGRZX6mMvUAG9UCk50zUgAAn2j3pAZqpbN3digoal'# xfFFL2YYhP+ePmrnCzm0BmRpZ29hbIhgBBMRAgAgBQJRoCA4AhsDBgsJCAcDAgQVdigoal'# AggDBBYCAwECHgECF4AACgkQ2jILI2NPkS+pqQCfe/4XubdbmrTSsjub9NG37qvYdigoal'# TK0AoMoocCzcwp94ppkKdRPH7INEX5mqnQI9BFGgIDkQCAD3+AAfHZ9rWEJajrt3digoal'# AuIPsg/eUVfpcREVi8DDcx9WlbkpgUM2QO6WbgMrfSaMupMIJHIpVrT9V4MFEAZ8digoal'# n0uUDoqjQGw6ChWVsS1Sj6b2/I5NtM24lr6h/SFFZTGkYya2NZHQtXJDxZsde5OLdigoal'# hVPRiha38f1bpGkwYSt4gclA+CRlL5qiib7tw19yZYiB+DyWgRcaf5ziMv+uoDz8digoal'# 9g5EkwQTWEzVbTKpAy89oHAFkPfU1rBF0bJHoT5PRUlA767Otrwu2bBpsIoUjLfwdigoal'# f4EB3Pa5hLOUZKjX8mClKCOHeVAkHVp4yPleUqaauh0IUSfTNMraKpmCQyoqCkdHdigoal'# sAUDAAMFB/4zmZ+3ota2S79ET9GPvxNETpM6TZjLj/pjb2Ta3/zwndUrz8RWdjDZdigoal'# 4QdCTfWtToyQ/KLaqr8TLo8k8ec9Eag/u5lim/6vejaRvnwKWTRremIk3OGvRVfYdigoal'# 6X/n2Gcn6To/25j6RyFLYXhs8f/liC/YB1g4hUdJz/bl2aA2aLbj6hLB9mnKCBOKdigoal'# AeJSwb2o7g7cJwGZWoxzIF2Q/Vv8c3QJUeX63xjVSHb8pcDdkjn0mr5eFTOIRtbhdigoal'# XNieLM5orytE3rD9VHCHcZ05U7xYnhKmj9q6Ruln1VTuzeWYll2Drms80UhLX045digoal'# xmUZ8flppFG+Sjb1YUoPBe6Ae1qyjB/9AAFUCNmPaOMHsDg6bZpo7ApZJbJsiW6Zdigoal'# BTCojWYF4E2C+5bPQoietziIE5V6LhPdiEkEGBECAAkFAlGgIDkCGwwACgkQ2jILdigoal'# I2NPkS8f0wCgpSCnhRw+soY+Fpg5t7IHjusqNt0An0EeMye7x5uyQc2ikmVtgfZudigoal'# NOxidigoal'# =FwrWdigoal'# -----END PGP PRIVATE KEY BLOCK-----');得到 : \x9501bb0451a02038110400c02d7ad60383f24fe323e9b7b6265d2431e5c8b59a9da8ea797ca398369593d8ba8a05af58445303647492d4c21f8f20c48b4ecef932a6f7c85a1c1a306c7074d2a3175bacfa540c6da9d501b7d6f42f9d8ec2403d6b0b1c9696a53ef2f00c0406a7194987b6902748478d0460bdf4ea2693a8085e16a0c5554a29f93b1d8fe300a0cdcafb16d2e16f2b07fc0c6a9b1e8e737c06dc5303fe28f8112dfc203340a1a7023ae5a503a31e6b15b381591ad5997f87692d51524e2de334b1a17a679ef4fd1618140482e5c55acc21bd19baba6b75af0b2e8b5bd855e57648394d550576316823f5b0c79681149b55ab6174932a6dccfe933a9a6e20951c3b7943c93f4ee204be69139d46e4d5c6b5193c5124c8576257592c301c03fe219cf6e3c5452d910f52ea03f70a287ff12380fc4e18223e4f2b74bd9d173a38a67d3621955e686a7dff0f0384e65f4f038957250f3b1ac69981d72614738e3fae1119e775f129f1f38efa3034a11ebc19be70f11cd14f5ae55485201c1fc2e00055b6df7662ca33e1dc06c3dcff7b919cd191657ea632f5001bd502939d335200009f68f7a4066aa5b377c5f1452f661884ff9e3e6ae70b39b4066469676f616c8860041311020020050251a02038021b03060b090807030204150208030416020301021e01021780000a0910da320b23634f912fa9a9009f7bfe17b9b75b9ab4d2b23b9bf4d1b7eeabd84cad00a0ca28702cdcc29f78a6990a7513c7ec83445f99aa9d023d0451a02039100800f7f8001f1d9f6b58425a8ebb7702e20fb20fde5157e97111158bc0c3731f5695b92981433640ee966e032b7d268cba930824722956b4fd57830510067c9f4b940e8aa3406c3a0a1595b12d528fa6f6fc8e4db4cdb896bea1fd21456531a46326b63591d0b57243c59b1d7b938b8553d18a16b7f1fd5ba46930612b7881c940f824652f9aa289beedc35f72658881f83c9681171a7f9ce232ffaea03cfcf60e44930413584cd56d32a9032f3da0700590f7d4d6b045d1b247a13e4f454940efaeceb6bc2ed9b069b08a148cb7f07f8101dcf6b984b39464a8d7f260a52823877950241d5a78c8f95e52a69aba1d085127d334cada2a9982432a2a0a4747b0050300030507fe33999fb7a2d6b64bbf444fd18fbf13444e933a4d98cb8ffa636f64dadffcf09dd52bcfc4567630d9e107424df5ad4e8c90fca2daaabf132e8f24f1e73d11a83fbb99629bfeaf7a3691be7c0a59346b7a6224dce1af4557d8e97fe7d86727e93a3fdb98fa47214b61786cf1ffe5882fd8075838854749cff6e5d9a03668b6e3ea12c1f669ca08138a01e252c1bda8ee0edc2701995a8c73205d90fd5bfc73740951e5fadf18d54876fca5c0dd9239f49abe5e15338846d6e15cd89e2cce68af2b44deb0fd547087719d3953bc589e12a68fdaba46e967d554eecde598965d83ae6b3cd1484b5f4e39c66519f1f969a451be4a36f5614a0f05ee807b5ab28c1ffd00015408d98f68e307b0383a6d9a68ec0a5925b26c896e990530a88d6605e04d82fb96cf42889eb7388813957a2e13dd8849041811020009050251a02039021b0c000a0910da320b23634f912f1fd300a0a520a7851c3eb2863e169839b7b2078eeb2a36dd009f411e3327bbc79bb241cda292656d81f66e34ec62

使用公钥加密字符串'i am digoal' :

digoal=# select pgp_pub_encrypt('i am digoal', '\x9901a20451a02038110400c02d7ad60383f24fe323e9b7b6265d2431e5c8b59a9da8ea797ca398369593d8ba8a05af58445303647492d4c21f8f20c48b4ecef932a6f7c85a1c1a306c7074d2a3175bacfa540c6da9d501b7d6f42f9d8ec2403d6b0b1c9696a53ef2f00c0406a7194987b6902748478d0460bdf4ea2693a8085e16a0c5554a29f93b1d8fe300a0cdcafb16d2e16f2b07fc0c6a9b1e8e737c06dc5303fe28f8112dfc203340a1a7023ae5a503a31e6b15b381591ad5997f87692d51524e2de334b1a17a679ef4fd1618140482e5c55acc21bd19baba6b75af0b2e8b5bd855e57648394d550576316823f5b0c79681149b55ab6174932a6dccfe933a9a6e20951c3b7943c93f4ee204be69139d46e4d5c6b5193c5124c8576257592c301c03fe219cf6e3c5452d910f52ea03f70a287ff12380fc4e18223e4f2b74bd9d173a38a67d3621955e686a7dff0f0384e65f4f038957250f3b1ac69981d72614738e3fae1119e775f129f1f38efa3034a11ebc19be70f11cd14f5ae55485201c1fc2e00055b6df7662ca33e1dc06c3dcff7b919cd191657ea632f5001bd502939d3352b4066469676f616c8860041311020020050251a02038021b03060b090807030204150208030416020301021e01021780000a0910da320b23634f912fa9a9009f7bfe17b9b75b9ab4d2b23b9bf4d1b7eeabd84cad00a0ca28702cdcc29f78a6990a7513c7ec83445f99aab9020d0451a02039100800f7f8001f1d9f6b58425a8ebb7702e20fb20fde5157e97111158bc0c3731f5695b92981433640ee966e032b7d268cba930824722956b4fd57830510067c9f4b940e8aa3406c3a0a1595b12d528fa6f6fc8e4db4cdb896bea1fd21456531a46326b63591d0b57243c59b1d7b938b8553d18a16b7f1fd5ba46930612b7881c940f824652f9aa289beedc35f72658881f83c9681171a7f9ce232ffaea03cfcf60e44930413584cd56d32a9032f3da0700590f7d4d6b045d1b247a13e4f454940efaeceb6bc2ed9b069b08a148cb7f07f8101dcf6b984b39464a8d7f260a52823877950241d5a78c8f95e52a69aba1d085127d334cada2a9982432a2a0a4747b0050300030507fe33999fb7a2d6b64bbf444fd18fbf13444e933a4d98cb8ffa636f64dadffcf09dd52bcfc4567630d9e107424df5ad4e8c90fca2daaabf132e8f24f1e73d11a83fbb99629bfeaf7a3691be7c0a59346b7a6224dce1af4557d8e97fe7d86727e93a3fdb98fa47214b61786cf1ffe5882fd8075838854749cff6e5d9a03668b6e3ea12c1f669ca08138a01e252c1bda8ee0edc2701995a8c73205d90fd5bfc73740951e5fadf18d54876fca5c0dd9239f49abe5e15338846d6e15cd89e2cce68af2b44deb0fd547087719d3953bc589e12a68fdaba46e967d554eecde598965d83ae6b3cd1484b5f4e39c66519f1f969a451be4a36f5614a0f05ee807b5ab28c1ffd8849041811020009050251a02039021b0c000a0910da320b23634f912f1fd3009e2e02579513158311549b35eaa6f700648113466e009e2bae3c011407f2904d1dc5ff86c79fbda87b4cee');

加密后的数据 :

\xc1c14e031b437bccd670d8451007fe2d921731879c4044946d8f5fc88fb40d4701895534d8d370882ac22b8ea899b014df1a835d3d5520a681c184816acef966e9a57201810eee18136dd8a148d81811de3f39ebaa7ada4e022fa3ce0f5d62937cad62ef28dbef8864f78c48f56ae932e8b2361d74dd3792388689e1443a77a06b81aa4a0bf0cb1d3158130c097d4f22483609e8c317914f3b99966d9274856884894917a1d0b1389ad62d303600edaed7edba52b9fd3890386fa9579089dbbe60124ba5dd211a710fb352e7400e2b13c139b6b9a7fc03308852ac19447afd01e660be6a72b4a2c0985f8abc66fbb874455c6f83cec73b51269538be7cc43229c37ae5658102c02c44e73cd60866550800dbbb466956161875455f5554db33b52b6897d78c8613a75d6627cd7cc5be2f5685bc67bcce04f2d9aef40404bd10ac1a5b3510c60c9b5220f2e8c87564ca133529282c04766aa902dddd19b2da3ddd8b5d95991276c4bcff6c720fa32522898567bb93bacef655f35c6b92bf72d6f75442b37f3c5fa05b1c903f25ff470e69340c946a2d985d2f384c7c1f99f7df8f0189366189af895d1615d6974adafeea2519e27b5b82a5d094a20fb8215bf7ed3f0f58c58b5fbe331da0bcfd49bf94426ba2e5db02b43a54ac9046fb11238e010b10cf4d9fec036ce1abb695b5462934f968682da6861583b58695b312a3d511b5e8091f6668ad116199567b453d7f51dfd23c0142070d0559e7e63c8ca3ed9363937184b90e273948e3dc80f121074f0f0b01f980a2d534683fc4b45131380cd70a564f33b72e6c812d858695dbb4

使用私钥解密这串bytea.

digoal=# select pgp_pub_decrypt('\xc1c14e031b437bccd670d8451007fe2d921731879c4044946d8f5fc88fb40d4701895534d8d370882ac22b8ea899b014df1a835d3d5520a681c184816acef966e9a57201810eee18136dd8a148d81811de3f39ebaa7ada4e022fa3ce0f5d62937cad62ef28dbef8864f78c48f56ae932e8b2361d74dd3792388689e1443a77a06b81aa4a0bf0cb1d3158130c097d4f22483609e8c317914f3b99966d9274856884894917a1d0b1389ad62d303600edaed7edba52b9fd3890386fa9579089dbbe60124ba5dd211a710fb352e7400e2b13c139b6b9a7fc03308852ac19447afd01e660be6a72b4a2c0985f8abc66fbb874455c6f83cec73b51269538be7cc43229c37ae5658102c02c44e73cd60866550800dbbb466956161875455f5554db33b52b6897d78c8613a75d6627cd7cc5be2f5685bc67bcce04f2d9aef40404bd10ac1a5b3510c60c9b5220f2e8c87564ca133529282c04766aa902dddd19b2da3ddd8b5d95991276c4bcff6c720fa32522898567bb93bacef655f35c6b92bf72d6f75442b37f3c5fa05b1c903f25ff470e69340c946a2d985d2f384c7c1f99f7df8f0189366189af895d1615d6974adafeea2519e27b5b82a5d094a20fb8215bf7ed3f0f58c58b5fbe331da0bcfd49bf94426ba2e5db02b43a54ac9046fb11238e010b10cf4d9fec036ce1abb695b5462934f968682da6861583b58695b312a3d511b5e8091f6668ad116199567b453d7f51dfd23c0142070d0559e7e63c8ca3ed9363937184b90e273948e3dc80f121074f0f0b01f980a2d534683fc4b45131380cd70a564f33b72e6c812d858695dbb4','\x9501bb0451a02038110400c02d7ad60383f24fe323e9b7b6265d2431e5c8b59a9da8ea797ca398369593d8ba8a05af58445303647492d4c21f8f20c48b4ecef932a6f7c85a1c1a306c7074d2a3175bacfa540c6da9d501b7d6f42f9d8ec2403d6b0b1c9696a53ef2f00c0406a7194987b6902748478d0460bdf4ea2693a8085e16a0c5554a29f93b1d8fe300a0cdcafb16d2e16f2b07fc0c6a9b1e8e737c06dc5303fe28f8112dfc203340a1a7023ae5a503a31e6b15b381591ad5997f87692d51524e2de334b1a17a679ef4fd1618140482e5c55acc21bd19baba6b75af0b2e8b5bd855e57648394d550576316823f5b0c79681149b55ab6174932a6dccfe933a9a6e20951c3b7943c93f4ee204be69139d46e4d5c6b5193c5124c8576257592c301c03fe219cf6e3c5452d910f52ea03f70a287ff12380fc4e18223e4f2b74bd9d173a38a67d3621955e686a7dff0f0384e65f4f038957250f3b1ac69981d72614738e3fae1119e775f129f1f38efa3034a11ebc19be70f11cd14f5ae55485201c1fc2e00055b6df7662ca33e1dc06c3dcff7b919cd191657ea632f5001bd502939d335200009f68f7a4066aa5b377c5f1452f661884ff9e3e6ae70b39b4066469676f616c8860041311020020050251a02038021b03060b090807030204150208030416020301021e01021780000a0910da320b23634f912fa9a9009f7bfe17b9b75b9ab4d2b23b9bf4d1b7eeabd84cad00a0ca28702cdcc29f78a6990a7513c7ec83445f99aa9d023d0451a02039100800f7f8001f1d9f6b58425a8ebb7702e20fb20fde5157e97111158bc0c3731f5695b92981433640ee966e032b7d268cba930824722956b4fd57830510067c9f4b940e8aa3406c3a0a1595b12d528fa6f6fc8e4db4cdb896bea1fd21456531a46326b63591d0b57243c59b1d7b938b8553d18a16b7f1fd5ba46930612b7881c940f824652f9aa289beedc35f72658881f83c9681171a7f9ce232ffaea03cfcf60e44930413584cd56d32a9032f3da0700590f7d4d6b045d1b247a13e4f454940efaeceb6bc2ed9b069b08a148cb7f07f8101dcf6b984b39464a8d7f260a52823877950241d5a78c8f95e52a69aba1d085127d334cada2a9982432a2a0a4747b0050300030507fe33999fb7a2d6b64bbf444fd18fbf13444e933a4d98cb8ffa636f64dadffcf09dd52bcfc4567630d9e107424df5ad4e8c90fca2daaabf132e8f24f1e73d11a83fbb99629bfeaf7a3691be7c0a59346b7a6224dce1af4557d8e97fe7d86727e93a3fdb98fa47214b61786cf1ffe5882fd8075838854749cff6e5d9a03668b6e3ea12c1f669ca08138a01e252c1bda8ee0edc2701995a8c73205d90fd5bfc73740951e5fadf18d54876fca5c0dd9239f49abe5e15338846d6e15cd89e2cce68af2b44deb0fd547087719d3953bc589e12a68fdaba46e967d554eecde598965d83ae6b3cd1484b5f4e39c66519f1f969a451be4a36f5614a0f05ee807b5ab28c1ffd00015408d98f68e307b0383a6d9a68ec0a5925b26c896e990530a88d6605e04d82fb96cf42889eb7388813957a2e13dd8849041811020009050251a02039021b0c000a0910da320b23634f912f1fd300a0a520a7851c3eb2863e169839b7b2078eeb2a36dd009f411e3327bbc79bb241cda292656d81f66e34ec62'); pgp_pub_decrypt ----------------- i am digoal(1 row)

5.3 使用pgp_key_id可以在加密后的数据中取出这分数据是通过对称密钥还是公钥加密的

pgp_key_id(bytea) returns text

例如 :

digoal=# select pgp_key_id('\xc30d0404030245811e051118cc136ed23f0198808f069b53264d4a08c2b5dcf3b1c39a34d091263f7f6b64a14808e6ffb32ccc09749105b9cc062d70c628357ab1e2474ff6d109dd083ce892cfa55706'); pgp_key_id ------------ SYMKEY(1 row)digoal=# select pgp_key_id('\xc1c14e031b437bccd670d8451007fe2d921731879c4044946d8f5fc88fb40d4701895534d8d370882ac22b8ea899b014df1a835d3d5520a681c184816acef966e9a57201810eee18136dd8a148d81811de3f39ebaa7ada4e022fa3ce0f5d62937cad62ef28dbef8864f78c48f56ae932e8b2361d74dd3792388689e1443a77a06b81aa4a0bf0cb1d3158130c097d4f22483609e8c317914f3b99966d9274856884894917a1d0b1389ad62d303600edaed7edba52b9fd3890386fa9579089dbbe60124ba5dd211a710fb352e7400e2b13c139b6b9a7fc03308852ac19447afd01e660be6a72b4a2c0985f8abc66fbb874455c6f83cec73b51269538be7cc43229c37ae5658102c02c44e73cd60866550800dbbb466956161875455f5554db33b52b6897d78c8613a75d6627cd7cc5be2f5685bc67bcce04f2d9aef40404bd10ac1a5b3510c60c9b5220f2e8c87564ca133529282c04766aa902dddd19b2da3ddd8b5d95991276c4bcff6c720fa32522898567bb93bacef655f35c6b92bf72d6f75442b37f3c5fa05b1c903f25ff470e69340c946a2d985d2f384c7c1f99f7df8f0189366189af895d1615d6974adafeea2519e27b5b82a5d094a20fb8215bf7ed3f0f58c58b5fbe331da0bcfd49bf94426ba2e5db02b43a54ac9046fb11238e010b10cf4d9fec036ce1abb695b5462934f968682da6861583b58695b312a3d511b5e8091f6668ad116199567b453d7f51dfd23c0142070d0559e7e63c8ca3ed9363937184b90e273948e3dc80f121074f0f0b01f980a2d534683fc4b45131380cd70a564f33b72e6c812d858695dbb4');    pgp_key_id    ------------------ 1B437BCCD670D845(1 row)

5.4 使用以下函数可以将加密后的数据bytea, 转换成PGP ASCII-armor格式. 或者反向转换

armor(data bytea) returns textdearmor(data text) returns bytea

5.5 用于产生随机的加密数据bytea的函数 :

gen_random_bytes(count integer) returns bytea

[小结]

1. crypt+gen_salt

加密速度慢, 被破解的难度高.

密码字符串通常较短, 并且有非常高的安全要求, 所以

比较适合用于对密码字段进行加密.

2. 对称加密则比较适合数据量较大, 并且有安全需求的加密. 通常为了使用方便加密的密钥可以存储在本地库中, 为了提高安全, 也可以把加密的密钥存储在客户端或者其他数据库中.

3. 公钥加密手段与对称加密类似, 只是需要生成钥匙对. 使用公钥加密后的数据解密需要用到对应的私钥. 如果私钥加了passphrase保护, 解密时还需要提供私钥的passphrase.

[参考]

8. pgcrypto扩展包新建的函数 :

[root@db-172-16-3-33 extension]# cat pgcrypto--1.0.sql /* contrib/pgcrypto/pgcrypto--1.0.sql */-- complain if script is sourced in psql, rather than via CREATE EXTENSION\echo Use "CREATE EXTENSION pgcrypto" to load this file. \quitCREATE FUNCTION digest(text, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pg_digest'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION digest(bytea, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pg_digest'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION hmac(text, text, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pg_hmac'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION hmac(bytea, bytea, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pg_hmac'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION crypt(text, text)RETURNS textAS 'MODULE_PATHNAME', 'pg_crypt'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION gen_salt(text)RETURNS textAS 'MODULE_PATHNAME', 'pg_gen_salt'LANGUAGE C VOLATILE STRICT;CREATE FUNCTION gen_salt(text, int4)RETURNS textAS 'MODULE_PATHNAME', 'pg_gen_salt_rounds'LANGUAGE C VOLATILE STRICT;CREATE FUNCTION encrypt(bytea, bytea, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pg_encrypt'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION decrypt(bytea, bytea, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pg_decrypt'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION encrypt_iv(bytea, bytea, bytea, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pg_encrypt_iv'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION decrypt_iv(bytea, bytea, bytea, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pg_decrypt_iv'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION gen_random_bytes(int4)RETURNS byteaAS 'MODULE_PATHNAME', 'pg_random_bytes'LANGUAGE C VOLATILE STRICT;---- pgp_sym_encrypt(data, key)--CREATE FUNCTION pgp_sym_encrypt(text, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pgp_sym_encrypt_text'LANGUAGE C STRICT;CREATE FUNCTION pgp_sym_encrypt_bytea(bytea, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pgp_sym_encrypt_bytea'LANGUAGE C STRICT;---- pgp_sym_encrypt(data, key, args)--CREATE FUNCTION pgp_sym_encrypt(text, text, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pgp_sym_encrypt_text'LANGUAGE C STRICT;CREATE FUNCTION pgp_sym_encrypt_bytea(bytea, text, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pgp_sym_encrypt_bytea'LANGUAGE C STRICT;---- pgp_sym_decrypt(data, key)--CREATE FUNCTION pgp_sym_decrypt(bytea, text)RETURNS textAS 'MODULE_PATHNAME', 'pgp_sym_decrypt_text'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION pgp_sym_decrypt_bytea(bytea, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pgp_sym_decrypt_bytea'LANGUAGE C IMMUTABLE STRICT;---- pgp_sym_decrypt(data, key, args)--CREATE FUNCTION pgp_sym_decrypt(bytea, text, text)RETURNS textAS 'MODULE_PATHNAME', 'pgp_sym_decrypt_text'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION pgp_sym_decrypt_bytea(bytea, text, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pgp_sym_decrypt_bytea'LANGUAGE C IMMUTABLE STRICT;---- pgp_pub_encrypt(data, key)--CREATE FUNCTION pgp_pub_encrypt(text, bytea)RETURNS byteaAS 'MODULE_PATHNAME', 'pgp_pub_encrypt_text'LANGUAGE C STRICT;CREATE FUNCTION pgp_pub_encrypt_bytea(bytea, bytea)RETURNS byteaAS 'MODULE_PATHNAME', 'pgp_pub_encrypt_bytea'LANGUAGE C STRICT;---- pgp_pub_encrypt(data, key, args)--CREATE FUNCTION pgp_pub_encrypt(text, bytea, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pgp_pub_encrypt_text'LANGUAGE C STRICT;CREATE FUNCTION pgp_pub_encrypt_bytea(bytea, bytea, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pgp_pub_encrypt_bytea'LANGUAGE C STRICT;---- pgp_pub_decrypt(data, key)--CREATE FUNCTION pgp_pub_decrypt(bytea, bytea)RETURNS textAS 'MODULE_PATHNAME', 'pgp_pub_decrypt_text'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION pgp_pub_decrypt_bytea(bytea, bytea)RETURNS byteaAS 'MODULE_PATHNAME', 'pgp_pub_decrypt_bytea'LANGUAGE C IMMUTABLE STRICT;---- pgp_pub_decrypt(data, key, psw)--CREATE FUNCTION pgp_pub_decrypt(bytea, bytea, text)RETURNS textAS 'MODULE_PATHNAME', 'pgp_pub_decrypt_text'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION pgp_pub_decrypt_bytea(bytea, bytea, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pgp_pub_decrypt_bytea'LANGUAGE C IMMUTABLE STRICT;---- pgp_pub_decrypt(data, key, psw, arg)--CREATE FUNCTION pgp_pub_decrypt(bytea, bytea, text, text)RETURNS textAS 'MODULE_PATHNAME', 'pgp_pub_decrypt_text'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION pgp_pub_decrypt_bytea(bytea, bytea, text, text)RETURNS byteaAS 'MODULE_PATHNAME', 'pgp_pub_decrypt_bytea'LANGUAGE C IMMUTABLE STRICT;---- PGP key ID--CREATE FUNCTION pgp_key_id(bytea)RETURNS textAS 'MODULE_PATHNAME', 'pgp_key_id_w'LANGUAGE C IMMUTABLE STRICT;---- pgp armor--CREATE FUNCTION armor(bytea)RETURNS textAS 'MODULE_PATHNAME', 'pg_armor'LANGUAGE C IMMUTABLE STRICT;CREATE FUNCTION dearmor(text)RETURNS byteaAS 'MODULE_PATHNAME', 'pg_dearmor'LANGUAGE C IMMUTABLE STRICT;