环境：PostgreSQL 13
PostgreSQL中，pgcrypto是contrib下的一个插件，它提供了一些加密解密函数，可以实现服务器端的数据加密解密。可以在SQL语句中调用这些函数来完成数据的加密和解密。
使用pgcrypto中的加密函数，可以加密比较重要的字段，提高数据的安全性。
pgcrypto模块提供的加密函数有以下几类：

1. 通用Hash函数
2. Password Hash函数
3. PGP加密函数
4. Raw加密函数
5. 随机数据函数

1 通用Hash函数

1.1 Digest()

函数原型：

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

计算给定数据的二进制哈希。

• data: 要进行hash的数据
• type：要使用的算法。 标准算法有md5，sha1，sha224，sha256，sha384和sha512。 如果pgcrypto是使用OpenSSL构建的，则可以使用更多算法。
  如果要将digest结果按照十六进制字符串显示，请使用encode()。 例如：

CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$
    SELECT encode(digest($1, 'sha1'), 'hex')
$$ LANGUAGE SQL STRICT IMMUTABLE;

1.2 Hmac()

函数原型：

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

• data: 待进行hash计算的数据
• key: 密钥
• type: 加密算法，与digest()中的类型相同
  计算具有密钥的数据的哈希MAC。type与digest()中的类型相同。
  Hmac()类似于digest()，但是只能在知道密钥的情况下重新计算哈希值。 这样可以防止有人更改数据并更改哈希值以使其匹配的情况。
  如果密钥大于哈希块大小，则将首先对其进行哈希处理，然后将结果用作密钥。

2 Password Hash函数

函数crypt()和gen_salt()专为哈希密码而设计。crypt()进行哈希处理，gen_salt()为它准备算法参数。
crypt()使用的算法在以下方面与通常的MD5或SHA1哈希算法不同：

• 慢。 由于数据量非常小，因此这是使暴力破解密码难以使用的唯一方法。
• 随机加盐。算法使用一个随机值（称为salt），以便用户即便具有相同密码也将具有不同的加密密码。 这也是防止逆向算法的另一种防御措施。
• 加密密码结果中包含加密算法类型。加密算法在结果中包括算法类型，因此可以将使用不同算法散列的密码共存。
• 部分加密算法自适应。其中一些加密算法是自适应的，这意味着当计算机运行速度更快时，您可以将算法调整得更慢，而不会导致与现有密码不兼容。
  crypt()支持的算法：
  | Algorithm | Max Password Length | Adaptive? | Salt Bits | Output Length | Description |
  | --------- | ------------------- | --------- | --------- | ------------- | -------------------------- |
  | bf | 72 | yes | 128 | 60 | Blowfish-based, variant 2a |
  | md5 | unlimited | no | 48 | 34 | MD5-based crypt |
  | xdes | 8 | yes | 24 | 20 | Extended DES |
  | des | 8 | no | 12 | 13 | Original UNIX crypt |

2.1 crypt()

crypt()原型：

crypt(password text, salt text) returns text

计算password的crypt(3)样式哈希。 存储新密码时，需要使用gen_salt()生成新的salt值。 要检查密码，请将数据库中存储的密码哈希值作为salt传递，然后测试新密码的hash结果是否与存储的值匹配。
设置新密码的示例：

UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));

认证示例：

SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ;

如果输入的密码正确，则返回true。

2.2 Gen_salt()

Gen_salt()函数原型：

gen_salt(type text [, iter_count integer ]) returns text

生成一个新的随机salt字符串以用于crypt()。 salt字符串还告诉crypt()使用哪种算法。

• type: 算法类型，指定哈希算法。 可接受的类型为：des，xdes，md5和bf。
• iter_count: 用户可以为hash算法指定迭代计数。 计数越高，散列密码花费的时间越多，因此破解密码的时间也就越多。尽管使用大的计数，计算散列的时间可能要花费数年，但这在某些情况下是不切实际的。 如果省略iter_count参数，则使用默认的迭代计数。iter_count的允许值取决于算法，如下表所示。

对于xdes，还有一个额外的限制，即迭代计数必须为奇数。
为了选择适当的迭代计数，请考虑将原始DES加密设计为在当时的硬件上具有每秒4个哈希计算的速度。 每秒低于4个散列的速度可能会削弱可用性。 每秒快于100个散列可能太快了。
请注意，“尝试所有组合”是不现实。 通常，密码破解是在词典的帮助下完成的，词典既包含常规单词，又包含它们的各种变体。 因此，即使是有点像单词的密码，也可能比上述数字提示的破解速度快得多，而6个字符的非单词的密码可能会免于破解（或延长破解所需要的时间）。

3 PGP加密函数

此处的功能实现了OpenPGP（RFC 4880）标准的加密部分。支持对称密钥和公共密钥加密。
加密的PGP消息由2部分或数据包组成：

• 包含会话密钥的数据包-对称密钥或公共密钥已加密。

• 包含使用会话密钥加密的数据的数据包。

• 使用对称密钥（即密码）加密时：

  1. 给定的密码使用String2Key（S2K）算法进行哈希处理。这与crypt()算法相当类似（故意使它变慢并带有随机盐），但它会生成全长的二进制密钥。
  2. 如果请求一个单独的会话密钥，将生成一个新的随机密钥。否则，S2K密钥将直接用作会话密钥。
  3. 如果要直接使用S2K密钥，则仅S2K设置将被放入会话密钥包中。否则，会话密钥将使用S2K密钥加密，并放入会话密钥数据包中。

• 使用公钥加密时：

  1. 生成一个新的随机会话密钥。
  2. 它使用公钥加密，并放入会话密钥包中。

• 在任何一种情况下，要加密的数据均按以下方式处理：

  1. 可选的数据处理：压缩，转换为UTF-8和/或行尾转换。
  2. 数据的前缀是一个随机字节块。这等效于使用随机IV。
  3. 将添加随机前缀和数据的SHA1哈希。
  4. 所有这些都使用会话密钥加密，并放置在数据包中。

3.1 Pgp_sym_encrypt()

函数原型：

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

使用对称PGP密钥psw加密data。 options参数可以包含选项设置，后面详述。

3.2 Pgp_sym_decrypt()

函数原型：

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

解密对称密钥加密的PGP消息。
不允许使用pgp_sym_decrypt解密bytea数据。 这是为了避免输出无效的字符数据。 使用pgp_sym_decrypt_bytea解密原始文本数据是可以的。
options参数可以包含选项设置，后面详述。

3.3 Pgp_pub_encrypt()

函数原型：

pgp_pub_encrypt(data text, key bytea [, options text ]) returns bytea
pgp_pub_encrypt_bytea(data bytea, key bytea [, options text ]) returns bytea

使用公共PGP密钥密钥加密数据data。 给此函数一个秘密密钥将产生一个错误。
options参数可以包含选项设置，后面详述。

3.4 Pgp_pub_decrypt()

函数原型：

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

解密公共密钥加密的消息。 key必须是与用于加密的公共密钥相对应的秘密密钥。 如果密钥受密码保护，则必须以psw输入密码。 如果没有密码，但是要指定选项，则需要输入一个空密码。
不允许使用pgp_pub_decrypt解密bytea数据。 这是为了避免输出无效的字符数据。 使用pgp_pub_decrypt_bytea解密原始文本数据是可以的。
options参数可以包含选项设置，后面详述。

3.5 Pgp_key_id()

函数原型：

pgp_key_id(bytea) returns text

pgp_key_id提取PGP公共或秘密密钥的密钥ID。 如果给出了加密的消息，它会提供用于加密数据的密钥ID。
它可以返回2个特殊的密钥ID：

• SYMKEY
  该消息使用对称密钥加密。
• ANYKEY
  该消息是公共密钥加密的，但是密钥ID已被删除。 这意味着将需要尝试所有密钥以查看哪个密钥可以对其进行解密。pgcrypto本身不会产生这样的消息。

请注意，不同的密钥可能具有相同的ID。 这很少见，但属正常现象。 然后，客户端应用程序应尝试使用每个Key进行解密以找出适合的key-就像处理ANYKEY一样。

3.6 Armor(), Dearmor()

函数原型：

armor(data bytea [ , keys text[], values text[] ]) returns text
dearmor(data text) returns bytea

这些函数将二进制数据包封装装/解包为PGP ASCII-Armor格式，该格式基本上是具有CRC和其他格式的Base64。
如果指定了keys和值数组values，则会为每个键/值对将armor header添加到armor格式。 两个数组都必须是一维的，并且它们的长度必须相同。 键和值不能包含任何非ASCII字符。

3.7 Pgp_armor_headers

函数原型：

pgp_armor_headers(data text, key out text, value out text) returns setof record

pgp_armor_headers()从数据data中提取armor headers。 返回值是一组包含两列（键和值）的行。 如果键或值包含任何非ASCII字符，则将它们视为UTF-8。

3.8 PGP函数的选项Options

options 选项的命名类似于GnuPG。option的值应在等号后给出；用逗号将各个选项分开。例如：

pgp_sym_encrypt(data, psw, 'compress-algo=1, cipher-algo=aes256')

除convert-crlf以外的所有选项仅适用于加密函数。解密功能从PGP数据获取参数。
最感兴趣的选项可能是compress-algo和unicode-mode。其余应具有合理的默认值。

3.8.1 cipher-algo

cipher算法使用。
值选项: bf, aes128, aes192, aes256 (OpenSSL-only: 3des, cast5)
默认值: aes128
应用函数: pgp_sym_encrypt, pgp_pub_encrypt

3.8.2 compress-algo

压缩算法使用。只有PostgreSQL使用zlib编译的时候才可用。
值选项:

• 0 - 无压缩
• 1 - ZIP 压缩
• 2 - ZLIB 压缩(= ZIP + meta-data 和块 CRCs)
  默认值: 0
  应用函数: pgp_sym_encrypt, pgp_pub_encrypt`

3.8.3 compress-level

压缩级别（压缩程度）。级别越高的压缩结果越小，但是压缩过程越慢。
0 禁用压缩。
值选项: 0, 1-9
默认值: 6
应用函数: pgp_sym_encrypt, pgp_pub_encrypt

3.8.4 convert-crlf

加密时是否将\n转换为\r\n，解密时是否将\r\n转换为\n。 RFC 4880指定应使用\r\n换行符存储文本数据。使用它来获得完全符合RFC的行为。
值选项: 0, 1
默认值: 0
应用函数: pgp_sym_encrypt, pgp_pub_encrypt, pgp_sym_decrypt, pgp_pub_decrypt

3.8.5 disable-mdc

不要使用SHA-1保护数据。使用此选项的唯一理由是要与旧的PGP产品兼容，而不是在RFC 4880中添加SHA-1保护的数据包。最近的gnupg.org和pgp.com软件都很好地支持它。
值选项: 0, 1
默认值: 0
应用函数: pgp_sym_encrypt, pgp_pub_encrypt

3.8.6 sess-key

使用单独的会话密钥。公钥加密始终使用单独的会话密钥。此选项用于对称密钥加密，默认情况下直接使用S2K密钥。
值选项: 0, 1
默认值: 0
应用函数: pgp_sym_encrypt

3.8.7 s2k-mode

S2K算法使用的选项。
值选项:

• 0 - 不加盐. 危险!
• 1 - 含盐但迭代计数固定.
• 3 - 可变迭代计数.
  默认值: 3
  应用函数: pgp_sym_encrypt

3.8.8 s2k-count

S2K算法要使用的迭代次数。它的值必须介于1024和65011712之间（含）。
默认值: 65536到253952之间的随机值
应用函数: pgp_sym_encrypt, 仅在 s2k-mode=3时

3.8.9 ·s2k-digest-algo·

在S2K计算中使用哪种摘要算法。
值选项: md5, sha1
默认值: sha1
应用函数: pgp_sym_encrypt

3.8.10 s2k-cipher-algo

cipher算法用于加密单独的会话密钥。
值选项: bf, aes, aes128, aes192, aes256
默认值: use cipher-algo
应用函数: pgp_sym_encrypt

3.8.11 unicode-mode

是否将文本数据从数据库内部编码转换为UTF-8并返回。如果数据库已经是UTF-8，则不会进行任何转换，但是消息将被标记为UTF-8。如果没有此选项，它将不会。
值选项: 0, 1
默认值: 0
应用函数: pgp_sym_encrypt, pgp_pub_encrypt

3.9 使用GnuPG生成PGP keys

生成一个新的key：

gpg --gen-key

首选的密钥类型是DSA和Elgamal。

gpg --list-secret-keys

要以ASCII-aemor格式导出公钥，执行以下操作：

gpg -a --export KEYID > public.key

要以ASCII-armor格式导出私钥：

gpg -a --export-secret-keys KEYID > secret.key

在将它们提供给PGP函数之前，需要在这些键上使用dearmo()。 或者，如果可以处理二进制数据，则可以从命令中删除-a。有关更多详细信息，请参见man gpg，《GNU隐私手册》和其他文档，网址为https://www.gnupg.org/。

3.10 PGP代码的局限性

• 不支持签名。 这也意味着不检查加密子密钥是否属于主密钥。
• 不支持将加密密钥用作主密钥。 由于通常不鼓励这种做法，因此这不应该成为问题。
• 不支持几个子项。 这似乎是一个问题，因为这是常见的做法。 另一方面，不应将常规的GPG/PGP密钥与pgcrypto一起使用，而应创建新的密钥，因为使用情况大不相同。

4 Raw加密函数

Raw加密函数提供了加密函数和相对应的解密函数，包含如下的函数声明：

encrypt(data bytea, key bytea, type text) returns bytea
decrypt(data bytea, key bytea, type text) returns bytea

encrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea
decrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea

这些功能仅对数据运行加密，相比于PGP加密没有任何高级功能。 因此，Raw加密函数存在一些主要问题：

1. 它们直接使用用户密钥作为密码密钥。
2. 它们不提供任何完整性检查，以查看加密的数据是否已被修改。
3. 它们希望用户自己管理所有加密参数，甚至IV。
4. 它们不处理文本。
   因此，随着PGP加密的引入，不鼓励使用Raw加密函数。
   使用type指定的加密方法对数据进行加密/解密。 type字符串的语法为：

algorithm [ - mode ] [ /pad: padding ]

• algorithm 算法，取值为以下之一
  • bf: Blowfish
  • aes: AES (Rijndael-128, -192 or -256)
• mode，取值为以下之一
  • cbc: 下一个块依赖于上一个块（默认）
  • ecb: 每个块独立进行加密（当前仅仅用于测试）
• padding，取值为以下之一
  • pkcs: 数据任意长度（默认）
  • none: 数据必须是密码块大小的倍数

示例
以下算法是等价的：

encrypt(data, 'fooz', 'bf')
encrypt(data, 'fooz', 'bf-cbc/pad:pkcs')

在encrypt_iv和decrypt_iv中，iv参数是CBC模式的初始值； ECB将忽略它。 如果块大小不完全，则将其剪切或填充为零。 不带该参数的函数默认为全零。

5 随机数据函数

函数原型：

gen_random_bytes(count integer) returns bytea
gen_random_uuid() returns uuid

• gen_random_bytes
  返回count个加密强度高的随机字节。 一次最多可以提取1024个字节。 这是为了避免耗尽随机数生成器池。
• gen_random_uuid
  返回v4的UUID。(该函数已经过时，但是现在仍然在PostgreSQL内核中包含)

6 使用方法

6.1 安装pgcrypto模块

安装外部模块的语法：

CREATE EXTENSION [ IF NOT EXISTS ] extension_name
    [ WITH ] [ SCHEMA schema_name ]
             [ VERSION version ]
             [ CASCADE ]

查看PostgreSQL可用的扩展模块：

postgres=# select * from pg_available_extensions;
        name        | default_version | installed_version |                                comment                                 
--------------------+-----------------+-------------------+------------------------------------------------------------------------
 sslinfo            | 1.2             |                   | information about SSL certificates
 pgrowlocks         | 1.2             |                   | show row-level locking information
 plpgsql            | 1.0             | 1.0               | PL/pgSQL procedural language
 refint             | 1.0             |                   | functions for implementing referential integrity (obsolete)
 hstore             | 1.7             |                   | data type for storing sets of (key, value) pairs
 ltree              | 1.2             |                   | data type for hierarchical tree-like structures
 unaccent           | 1.1             |                   | text search dictionary that removes accents
 moddatetime        | 1.0             |                   | functions for tracking last modification time
 pg_freespacemap    | 1.2             |                   | examine the free space map (FSM)
 tcn                | 1.0             |                   | Triggered change notifications
 pg_trgm            | 1.5             |                   | text similarity measurement and index searching based on trigrams
 tsm_system_time    | 1.0             |                   | TABLESAMPLE method which accepts time in milliseconds as a limit
 pgstattuple        | 1.5             |                   | show tuple-level statistics
 cube               | 1.4             |                   | data type for multidimensional cubes
 citext             | 1.6             |                   | data type for case-insensitive character strings
 pageinspect        | 1.8             |                   | inspect the contents of database pages at a low level
 dblink             | 1.2             |                   | connect to other PostgreSQL databases from within a database
 intagg             | 1.1             |                   | integer aggregator and enumerator (obsolete)
 dict_int           | 1.0             |                   | text search dictionary template for integers
 tablefunc          | 1.0             |                   | functions that manipulate whole tables, including crosstab
 pg_prewarm         | 1.2             |                   | prewarm relation data
 insert_username    | 1.0             |                   | functions for tracking who changed a table
 adminpack          | 2.1             |                   | administrative functions for PostgreSQL
 btree_gist         | 1.5             |                   | support for indexing common datatypes in GiST
 xml2               | 1.1             |                   | XPath querying and XSLT
 autoinc            | 1.0             |                   | functions for autoincrementing fields
 dict_xsyn          | 1.0             |                   | text search dictionary template for extended synonym processing
 tsm_system_rows    | 1.0             |                   | TABLESAMPLE method which accepts number of rows as a limit
 earthdistance      | 1.1             |                   | calculate great-circle distances on the surface of the Earth
 intarray           | 1.3             |                   | functions, operators, and index support for 1-D arrays of integers
 uuid-ossp          | 1.1             |                   | generate universally unique identifiers (UUIDs)
 amcheck            | 1.2             |                   | functions for verifying relation integrity
 postgres_fdw       | 1.0             |                   | foreign-data wrapper for remote PostgreSQL servers
 fuzzystrmatch      | 1.1             |                   | determine similarities and distance between strings
 btree_gin          | 1.3             |                   | support for indexing common datatypes in GIN
 seg                | 1.3             |                   | data type for representing line segments or floating-point intervals
 pg_buffercache     | 1.3             |                   | examine the shared buffer cache
 bloom              | 1.0             |                   | bloom access method - signature file based index
 pg_stat_statements | 1.8             |                   | track planning and execution statistics of all SQL statements executed
 pg_visibility      | 1.2             |                   | examine the visibility map (VM) and page-level visibility info
 isn                | 1.2             |                   | data types for international product numbering standards
 pgcrypto           | 1.3             | 1.3               | cryptographic functions
 file_fdw           | 1.0             |                   | foreign-data wrapper for flat file access
 lo                 | 1.1             |                   | Large Object maintenance
(44 行记录)

安装pgcrypto模块：

postgres=# create extension pgcrypto cascade;
CREATE EXTENSION

6.2 使用pgcrypto模块进行列的加密和解密

postgres=# select crypt('123',gen_salt('bf',10));
                            crypt                             
--------------------------------------------------------------
 $2a$10$vxS7a0dqspxgWaNltW23/e1SGBsZaBZyQLlEIKyhtD5/q0Ir9LI8W
(1 行记录)

postgres=# create table t_user(id int primary key, uname char(64), passwd varchar(256));
postgres=# insert into t_user values(1,'Jerry',encrypt('password','Keypwd','des-ecb'));
INSERT 0 1
postgres=# select * from t_user;
 id |                              uname                               |               passwd               
----+------------------------------------------------------------------+------------------------------------
  1 | Jerry                                                            | \x9beb1476093ddf71521d49fc1b8f787e
(1 行记录)
postgres=# select encode(decrypt(passwd::bytea,'Keypwd','des-ecb'),'escape') from t_user;
  encode  
----------
 password
(1 行记录)

7 注意事项

7.1 配置

pgcrypto根据PostgreSQL加载主要配置脚本进行配置。 影响它的选项是--with-zlib和--with-openssl。
使用zlib编译时，PGP加密功能能够在加密之前压缩数据。
使用OpenSSL进行编译时，将有更多可用的算法。 此外，由于OpenSSL具有更多优化的BIGNUM功能，因此public-key加密功能也将更快。
下表展示了有和没有OpenSSL功能的摘要

OpenSSL支持的任何摘要算法都会自动获取。 密码算法是不可能的，需要明确支持。

7.2 NULL处理

按照SQL的标准，如果任何参数为NULL，则所有函数都将返回NULL。 如果不小心使用，可能会造成安全风险。

7.3 安全限制

所有pgcrypto函数都在数据库服务器内部运行。 这意味着所有数据和密码都以明文形式在pgcrypto和客户端应用程序之间传递。 因此，必须：

1. 在本地连接或使用SSL连接。
2. 信任系统管理员和数据库管理员。
   如果不能，那么最好在客户端应用程序内部进行加密。

该实现不抵抗side-channel 攻击。 例如，pgcrypto解密功能完成所需的时间在给定大小的密文中会有所不同。

参考：

1. Additional Supplied Modules - F.25. pgcrypto
2. SQL Commands - CREATE EXTENSION
3. Appendix F. Additional Supplied Modules
4. Encryption Options