PostgreSQL特定列的加密

原创孙莹 2024-07-25

223

前言

通常在设计业务系统中基本都有一个用户表，里面存放了用户的基本信息，包括登录用户名和密码等等。用户在使用系统登陆时，程序会校验后台数据库中用户表的登录用户名和密码，今天我们就来讨论一下PostgreSQL数据库中怎么处理密码。

PostgreSQL特定列的加密

pgcrypto 模块为 PostgreSQL 提供加密函数，我们可以通过函数来对用户表中的密码字段进行加密。

首先pgcrypto模块是官方受信任的，他只要CREATE权限即可安装，使用pgcrypto模块必须在软件编译时候加上OpenSSL的支持，下面我们来实战测试一下PostgreSQL 16.3版本如何对特定列进行加密。

安装pgcrypto模块

记得编译安装时候加上对openssl的支持，如下：

./configure --prefix=/opt/pg16.3 --with-pgport=5432 --with-openssl
复制

创建pgcrypto模块

[postgres@pg16 ~]$ psql
psql (16.3)
Type "help" for help.

postgres=# create extension pgcrypto;
CREATE EXTENSION
postgres=# \dx
                         List of installed extensions
    Name    | Version |   Schema   |                Description
------------+---------+------------+-------------------------------------------
 pgcrypto   | 1.3     | public     | cryptographic functions
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
 plpython3u | 1.0     | pg_catalog | PL/Python3U untrusted procedural language
(3 rows)

postgres=#

复制

创建一个用户表

postgres=# CREATE TABLE users (id SERIAL PRIMARY KEY,username TEXT,password TEXT);
CREATE TABLE
postgres=#

复制

通用哈希函数

使用digest函数用md5, sha1, sha224, sha256, sha384 and sha512标准算法进行加密，产生出16进制的文本。

下面我们用digest函数md5,sha1,sh256三种标准算法对123456这个字符密码进行加密，同时用encode函数转换成16进制。

postgres=# INSERT INTO users (username,password) values('sunying',encode(digest('123456','md5'),'hex'));
INSERT 0 1
postgres=# INSERT INTO users (username,password) values('sunying',encode(digest('123456','sha1'),'hex'));
INSERT 0 1
postgres=# INSERT INTO users (username,password) values('sunying',encode(digest('123456','sha256'),'hex'));
INSERT 0 1
postgres=# select * from users;
 id | username |                             password
----+----------+------------------------------------------------------------------
  1 | sunying  | e10adc3949ba59abbe56e057f20f883e
  2 | sunying  | 7c4a8d09ca3762af61e59520943dc26494f8941b
  3 | sunying  | 8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
(3 rows)

postgres=#

复制

使用这种方法实际是非常不安全的，因为常用的标准算法最终产生的16进制文本容易被识别反向破解，比如我们可以通过某些加解密的工具网站来对常用密码进行分析识别。可以看到123456这个字符串用md5，sha1或者sha256加密后最终产生的16进制文本都是一样的。如下图所示：

hmac函数在digest函数的基础上增加一个密钥 key字符串来加密。相对digest要安全，解密需要知道加密时的密钥key字符串。下面我们就用hmac函数加密123456字符串，同时使用key1这个字符串作为我们的密钥。

postgres=# INSERT INTO users (username,password) values('wanghongmei',encode(hmac('123456','key1','md5'),'hex'));
INSERT 0 1
postgres=# INSERT INTO users (username,password) values('wanghongmei',encode(hmac('123456','key1','sha1'),'hex'));
INSERT 0 1
postgres=# INSERT INTO users (username,password) values('wanghongmei',encode(hmac('123456','key1','sha256'),'hex'));
INSERT 0 1
postgres=# select * from users;
 id |  username   |                             password
----+-------------+------------------------------------------------------------------
  1 | sunying     | e10adc3949ba59abbe56e057f20f883e
  2 | sunying     | 7c4a8d09ca3762af61e59520943dc26494f8941b
  3 | sunying     | 8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
  4 | wanghongmei | 7a6a3fdaa08fb025573e6fccbaaa527a
  5 | wanghongmei | 65050afcdfc474189c9fcd6f6cb07213c3cc997b
  6 | wanghongmei | 8463b03b737063dff16bdb20fe17b22c237655019972a6ec2317abdec0c482b7
(6 rows)

postgres=# 

复制

我们可以看到只要hmac函数只要密钥没有泄露的话，密码是很难被破解的。

密码哈希函数

函数 crypt() 和 gen_salt() 是专门为哈希密码而设计的。 crypt() 进行哈希处理，gen_salt() 为其准备算法参数。

crypt() 中的算法与通常的 MD5 或 SHA1 哈希算法在以下方面有所不同。

1.他们很慢。由于数据量很小，这是使暴力破解密码变得困难的唯一方法。

2.他们使用称为salt的随机值，以便具有相同密码的用户将拥有不同的加密密码。这也是针对逆向算法的额外防御。

3.它们在结果中包含算法类型，因此使用不同算法散列的密码可以共存。

4.其中一些是自适应的——这意味着当计算机变得更快时，您可以将算法调整得更慢，而不会导致与现有密码的不兼容。

crypt()函数支持的算法

算法	最大密码长度	自适应	salt位	输出长度	描述
`bf`	72	yes	128	60	基于 Blowfish 的 2a 变种算法
`md5`	unlimited	no	48	34	基于 MD5 的加密算法
`xdes`	8	yes	24	20	扩展 DES
`des`	8	no	12	13	原始 UNIX 加密算法

gen_salt() 函数用于生成 salt值，该函数每次都会生成一个随机的salt值字符串，该字符串同时决定了 crypt() 函数使用的算法

postgres=# SELECT gen_salt('des'), gen_salt('xdes'), gen_salt('md5'), gen_salt('bf');
 gen_salt | gen_salt  |  gen_salt   |           gen_salt
----------+-----------+-------------+-------------------------------
 G4       | _J9..Sjpv | $1$hFE1rLhy | $2a$06$rX.EtQIuuYhvzJxfgbb8ku
(1 row)

postgres=# SELECT gen_salt('des'), gen_salt('xdes'), gen_salt('md5'), gen_salt('bf');
 gen_salt | gen_salt  |  gen_salt   |           gen_salt
----------+-----------+-------------+-------------------------------
 lU       | _J9..NiW8 | $1$QRsHgeIC | $2a$06$phndDL6RCoMqb1CPF3ffje
(1 row)

postgres=#

复制

然后对用户名为zhangsan和lisi用户通过crypt函数加密，发现相同的密码123456字符串因为不同的salt值所以password值不同

postgres=# INSERT INTO users (username,password) values('zhangsan',crypt('123456', gen_salt('md5')));
INSERT 0 1
postgres=# INSERT INTO users (username,password) values('lisi',crypt('123456', gen_salt('md5')));
INSERT 0 1
postgres=# select * from users where username in ('zhangsan','lisi');
 id | username |              password
----+----------+------------------------------------
  7 | zhangsan | $1$wI3Eenal$KC7N5jvceHh6iciBO2mBc/
  8 | lisi     | $1$ZDgk3NZG$Fr6CohXQdm1AGYkKWf4Ox0
(2 rows)

postgres=#

复制

对用户名和密码进行校验时候将之前生成的哈希结果作为 salt值

postgres=# select crypt('123456','$1$wI3Eenal$KC7N5jvceHh6iciBO2mBc/');
               crypt
------------------------------------
 $1$wI3Eenal$KC7N5jvceHh6iciBO2mBc/
(1 row)

postgres=# select crypt('123456','$1$wI3Eenal$KC7N5jvceHh6iciBO2mBc/')='$1$wI3Eenal$KC7N5jvceHh6iciBO2mBc/';
 ?column?
----------
 t
(1 row)

postgres=# select crypt('123456','$1$ZDgk3NZG$Fr6CohXQdm1AGYkKWf4Ox0')='$1$ZDgk3NZG$Fr6CohXQdm1AGYkKWf4Ox0';
 ?column?
----------
 t
(1 row)

postgres=# select crypt('123456a','$1$wI3Eenal$KC7N5jvceHh6iciBO2mBc/')='$1$wI3Eenal$KC7N5jvceHh6iciBO2mBc/';
 ?column?
----------
 f
(1 row)

postgres=# select * from users where username='zhangsan' and password = crypt('123456', password);
 id | username |              password
----+----------+------------------------------------
  7 | zhangsan | $1$wI3Eenal$KC7N5jvceHh6iciBO2mBc/
(1 row)

postgres=# select * from users where username='lisi' and password = crypt('123456', password);
 id | username |              password
----+----------+------------------------------------
  8 | lisi     | $1$ZDgk3NZG$Fr6CohXQdm1AGYkKWf4Ox0
(1 row)

postgres=#

复制

以上这种加密方式也是非常难破解的，我们也是推荐这种方式来加密。

PGP 加密函数

PGP 加密函数实现了 OpenPGP（RFC 4880）标准中的加密功能，包括对称密钥加密（私钥加密）和非对称密钥加密（公钥加密）。

一个加密后的 PGP 消息由 2 部分（包）组成：

一个包含会话密钥（加密后的对称密钥或者公钥）的包；
一个使用会话密钥对数据加密后的包。

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

1.给定的密码使用 String2Key (S2K) 算法进行哈希处理。这与 crypt() 算法非常相似——故意放慢速度并使用随机salt——但它会生成一个全长的二进制密钥。

2.如果请求单独的会话密钥，则会生成新的随机密钥。否则，S2K 密钥将直接用作会话密钥。

3.如果直接使用S2K密钥，则仅将S2K设置放入会话密钥包中。否则会话密钥将使用S2K密钥加密并放入会话密钥包中。

使用公钥加密时：

1.生成新的随机会话密钥。

2.它使用公钥加密并放入会话密钥数据包中。

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

1.可选的数据操作：压缩、转换为 UTF-8 和/或行结尾转换。

2.数据以随机字节块为前缀。这相当于使用随机 IV。

3.附加随机前缀和数据的 SHA1 哈希值。

4.所有这些都使用会话密钥加密并放置在数据包中。

对称 PGP 密钥加密

使用pgp_sym_encrypt()函数来加密，提供被加密字符串123456两条记录还有密码是abc123一条记录，PGP 对称密钥是key1，PGP 功能选项是cipher-algo=aes192

pgp_sym_encrypt(data, psw, 'compress-algo=1, compress-level=9, cipher-algo=aes256')
复制

postgres=# TRUNCATE TABLE users;
TRUNCATE TABLE
postgres=# INSERT INTO users (username,password) values('n1',pgp_sym_encrypt('123456','key1','cipher-algo=aes192'));
INSERT 0 1
postgres=# INSERT INTO users (username,password) values('n2',pgp_sym_encrypt('123456','key1','cipher-algo=aes192'));
INSERT 0 1
postgres=# INSERT INTO users (username,password) values('n3',pgp_sym_encrypt('abc123','key1','cipher-algo=aes192'));
INSERT 0 1
postgres=# select * from users;
 id | username |                                                                      password
----+----------+----------------------------------------------------------------------------------------------------------------------------------------------------
 11 | n1       | \xc30d040803024f322e2ee0346c416cd23701a9c1cbb70b1417d66e9a2a4d4a410ed9a6e3439dc795a803b4b06a933e37878d901f8b6870df0762238352110d92e11ac14bde64ae06
 12 | n2       | \xc30d040803029e66998350fdc3d36cd2370186b7c15e2263f2a9f8e37ca7da52c19fbfff2e0c37d44271f43f3f5596bf444bd1f070ce00fbf6009caf80e49ca7fce635244c706e6a
 13 | n3       | \xc30d040803020cd711f9b5fccfc66fd23701bb27f8548763eac9f734deb24d45fbba882bafb471b3fe4b54be9a15f16eb40cc0a97fd497dabeec7498e6c9b2f026c8e63ead931a01
(3 rows)

postgres=#


复制

使用pgp_sym_encrypt()函数解密，密码是123456的进行校验

postgres=# select * from users where pgp_sym_decrypt(password::bytea,'key1','cipher-algo=aes192') = '123456';
 id | username |                                                                      password
----+----------+----------------------------------------------------------------------------------------------------------------------------------------------------
 11 | n1       | \xc30d040803024f322e2ee0346c416cd23701a9c1cbb70b1417d66e9a2a4d4a410ed9a6e3439dc795a803b4b06a933e37878d901f8b6870df0762238352110d92e11ac14bde64ae06
 12 | n2       | \xc30d040803029e66998350fdc3d36cd2370186b7c15e2263f2a9f8e37ca7da52c19fbfff2e0c37d44271f43f3f5596bf444bd1f070ce00fbf6009caf80e49ca7fce635244c706e6a
(2 rows)

postgres=#


复制

非对称 PGP 密钥加密

由于非对称加解密使用的是一对公钥和密钥，首先需要生成一对公钥和密钥。我们用 GnuPG 生成 PGP 密钥。

生成主密钥

用root账号gpg命令创建公钥和私钥，gpg --gen-key，注意用户名必须大于5位，邮箱可以随便输入，创建公钥的同时会让您输入私钥的密码，这里我们输入123456作为私钥密码，要记住这个私钥密码我们后面要用到私钥密码来解密。

[root@pg16 ~]# gpg --gen-key
gpg (GnuPG) 2.3.3; Copyright (C) 2021 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

gpg: directory '/root/.gnupg' created
gpg: keybox '/root/.gnupg/pubring.kbx' created
Note: Use "gpg --full-generate-key" for a full featured key generation dialog.

GnuPG needs to construct a user ID to identify your key.

Real name: sy
Name must be at least 5 characters long
Real name: sunying
Email address: a@qq.com
You selected this USER-ID:
    "sunying <a@qq.com>"

Change (N)ame, (E)mail, or (O)kay/(Q)uit? O
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
gpg: /root/.gnupg/trustdb.gpg: trustdb created
gpg: key 22843B94CF389DC1 marked as ultimately trusted
gpg: directory '/root/.gnupg/openpgp-revocs.d' created
gpg: revocation certificate stored as '/root/.gnupg/openpgp-revocs.d/9BD9A5A95C986DD0F53591EB22843B94CF389DC1.rev'
public and secret key created and signed.

pub   rsa3072 2024-07-25 [SC] [expires: 2026-07-25]
      9BD9A5A95C986DD0F53591EB22843B94CF389DC1
uid                      sunying <a@qq.com>
sub   rsa3072 2024-07-25 [E] [expires: 2026-07-25]

[root@pg16 ~]# 
复制

列出密钥

gpg --list-keys显示所有公钥，第一行显示公钥文件名（pubring.kbx），第二行显示公钥特征（2048位，Hash字符串和生成时间），第三行显示"用户ID"，第四行显示私钥特征。

[root@pg16 ~]# gpg --list-keys
gpg: checking the trustdb
gpg: marginals needed: 3  completes needed: 1  trust model: pgp
gpg: depth: 0  valid:   1  signed:   0  trust: 0-, 0q, 0n, 0m, 0f, 1u
gpg: next trustdb check due at 2026-07-25
/root/.gnupg/pubring.kbx
------------------------
pub   rsa3072 2024-07-25 [SC] [expires: 2026-07-25]
      9BD9A5A95C986DD0F53591EB22843B94CF389DC1
uid           [ultimate] sunying <a@qq.com>
sub   rsa3072 2024-07-25 [E] [expires: 2026-07-25]

[root@pg16 ~]# 
复制

输出密钥

公钥文件（.gnupg/pubring.kbx）以二进制形式储存，armor参数可以将其转换为ASCII码显示

--公钥
gpg --armor --output public-key.txt --export [用户ID]
--私钥
gpg --armor --output private-key.txt --export-secret-keys [用户ID]
复制

生成ASCII码，两个文件，将文件移动到/home/postgres目录下

[root@pg16 ~]# gpg --armor --output public-key.txt --export 9BD9A5A95C986DD0F53591EB22843B94CF389DC1
[root@pg16 ~]# cat public-key.txt
-----BEGIN PGP PUBLIC KEY BLOCK-----

mQGNBGahn5cBDADh+uH2DlhFB1UTQgYKt/JU5tVI4THGl51v/egHudnWQ0FEsOZ8
N6tSrJuJkQXcbQs97zuPaiJdROJ2PQLJTrh3Bx1WA4Yd66+Cpn38QuU1Db6BnLJJ
O+PrbRE9Dts03wDkdsP8OclTBT7G7YFgsY46qxzY+MrFRTfD1QCcSpxtCll/Spgu
BKVjAmB3QB5p5N+vB9s9H6l4XROcg9ph70PqabwML0I5BFu5vCPDH+DNT80iATXc
X0Vp4l61g7GJuIekXuAz+yBONQmU7C1QwgqhHNbnRYxzohV+oSV6CyWi94ofZV2V
ctbUQxnwDEMa9+A/urhrvj3pJktzI2TPSW+pq0rQJVUokspd+3AU5X3eONWMlyle
eT9iyS8Pm7YgBDL61cjUk9cv4ojgtRl6qtmDemTdCeeeOFXPl5/PXziafVcIk6ts
dxxWryjpH5uXG1WJBRpLFPYVaLFCzGfT9/SyS8yaihPF/1ANk927P03GQicTFw2k
NTd5SrHFpWD/JWkAEQEAAbQSc3VueWluZyA8YUBxcS5jb20+iQHYBBMBCABCFiEE
m9mlqVyYbdD1NZHrIoQ7lM84ncEFAmahn5cCGwMFCQPCZwAFCwkIBwIDIgIBBhUK
CQgLAgQWAgMBAh4HAheAAAoJECKEO5TPOJ3Bi4YL/j0O9Z0PgHZ1cu0rfxtoBGzv
N73+E2QKvLQNKd4JKPs1uV3Wn1MgOw7SD9xTG/47muMT4MgNZnddF6/uDfAuBDcv
wIARwmQw4vcfF5rgwxaDpEF7W3HIH00XhnyLWhgV4/vCqOhvoz9rk6ZckrN/lkAY
dBxvz4zJmBbyC/iwSVThwNeqiKdcpgsvPhO9OMwamdZIYtp6vjCzimstishBjlTj
WgbpGsbfALsgG0ELMwJmfA/oUyl9W5iJmdsFwZnHphek/aT6v9amIxFgQfuCyrsv
Yc7df19NSFROgHw3/c/ob00Lc41mGSOYVWXe9wO4Pvd9LMegF3zNosfVrkcI7i23
/zWp1GHiVVdv6UdCdHNmL5i9YGqc8c00p1rlqKomM4lKlkFLTJXinqWqaBkN+Htf
PCljomgrKMtr8Z+6z1z/G0vm0ESS4AKX5TRbcLtY3AoCabh+qFR45p/KWOJ5Fadt
cSKdOiHs+rfLVZ8rTwVyu97P6sgSp/YxNZB2dZwlTLkBjQRmoZ+XAQwA5UtIzf9H
9SWtJtoU4ZOmd0PaKgfA1K9CCUBHzpkjfSlVuNBdxEtwCiGRT37B1kUgf4J7VXIR
cplO/sNj4K0Io1yOpr3s0tJ90liM/Rl9/r2n9pwvy2kSObvBJZJ3+UCODEqXx4Wc
sSQ6oNaoKYZh2E1d9WQcR78IdTFOIoudwSNyIsOnM5HWt2Se11+6EtRwmoiPqRv9
9vxH7JFqjtPBggF2JAAgyEtsWTm/xA9bg0g0GryyQ450U5n3ilyMYJONLw4Rjgh2
X71wZ0096YT9MXpaT4IFryC7ALGw+kcleldkof9kFGtCJtpueoenf+8EqrM0ez6O
GvM2SCHDqgWuvn1h2eiN1Gz4TzTkKq68OiQ+7sLxyAzrAnEbv+EckYD3rd0rOqIr
3ZrcI/QvjSYPfSmGUlquO5vTxpHdsqWTpPGi1YzrT/uaEn5mLPf2a73oJTo+aOWr
1FkzHwqDwiO0CzUONOH55kSMZ8XIqdf0F7AYeQ0QscWo37BxQ/CIhAM1ABEBAAGJ
AbwEGAEIACYWIQSb2aWpXJht0PU1kesihDuUzzidwQUCZqGflwIbDAUJA8JnAAAK
CRAihDuUzzidwVXfC/9vH22/m4zxBZhK3sCpO1ZEjvaG8soC+BV6VC4WKqSEuPks
TuiDc/RLQZPC303GSI5yDBsaRvA0VZDFUUlOMIQlcmwFWj2ISAEOzwwVwdbUSe1u
1d04muFBqWm7BHgdGZKIpjt46rPg5tDEuXZz2bxjLAJaHi/oKn4mRJj/pzDOdUoJ
VcEC5HgJOGRQPKbDqy5wLzb29v/Ih2ieL20GKw5Tr6Kh1xJHM4TyUmMuohcQHW8w
bvKrLLceVOTNfcQP33EWhp0qILJlGOcmedI+iAzWihtM56bJ5mlv7X1no7gje9cp
KX+ErLAb2yerKhHmSPo+s/9taO/kC5EMONhebAOY6/XclBVKTvcsDcU1WBlm+nqu
Jzmi43ZmNyWnVJS91glGtWMtfyDxVBo3/TsehZ/2XVd9jVuTPhbRlZlpJt3Oz6nh
fRoX8dyCU+DbnEe1IcBY5FnjyiBav+LLrgZ1J/tRzB9DL3RyCL1+fCspl8lEoGeU
sZ0q8JZfYtj05pgyYww=
=VL88
-----END PGP PUBLIC KEY BLOCK-----
[root@pg16 ~]# gpg --armor --output private-key.txt --export-secret-keys 9BD9A5A95C986DD0F53591EB22843B94CF389DC1
[root@pg16 ~]# cat private-key.txt
-----BEGIN PGP PRIVATE KEY BLOCK-----

lQWGBGahn5cBDADh+uH2DlhFB1UTQgYKt/JU5tVI4THGl51v/egHudnWQ0FEsOZ8
N6tSrJuJkQXcbQs97zuPaiJdROJ2PQLJTrh3Bx1WA4Yd66+Cpn38QuU1Db6BnLJJ
O+PrbRE9Dts03wDkdsP8OclTBT7G7YFgsY46qxzY+MrFRTfD1QCcSpxtCll/Spgu
BKVjAmB3QB5p5N+vB9s9H6l4XROcg9ph70PqabwML0I5BFu5vCPDH+DNT80iATXc
X0Vp4l61g7GJuIekXuAz+yBONQmU7C1QwgqhHNbnRYxzohV+oSV6CyWi94ofZV2V
ctbUQxnwDEMa9+A/urhrvj3pJktzI2TPSW+pq0rQJVUokspd+3AU5X3eONWMlyle
eT9iyS8Pm7YgBDL61cjUk9cv4ojgtRl6qtmDemTdCeeeOFXPl5/PXziafVcIk6ts
dxxWryjpH5uXG1WJBRpLFPYVaLFCzGfT9/SyS8yaihPF/1ANk927P03GQicTFw2k
NTd5SrHFpWD/JWkAEQEAAf4HAwJidELFlG7OpP+aJmNfrtgDPAvmsr5yTwLeEnlW
nHusi8zHolWm10P8ZGVJGRfY/6OPBjOUCOHjBDwczCEkHfvb+akp3I8suoNrlKF+
ubNLFhta5lNu5/LpuKwf/MPUtzVySy/XhmuITmTiNg+Cq7vIQS/Y1brPvm3XdTqC
0dTUPXz/CdjoKK9YfN4CiFJIiLaGtBJe0XTS0HxyaRlvJoCaAYucLq8RexOihMPD
AEVzWHqADK8Cd1Ym2uwh5pgxYkfGZoe12wkRD6zVBoq9p1J8f7aKC7LiQYE8WyXh
xvIqoxJZSIdQ31n3JSoV7Wmsamx1rrg8Hf/yGFT7GGRMJftS4VXib0o4J15LGAiO
3WQS3KPEhXVWUN0CHsimevdIjkCnLIXwjtzwE/SFChe/4z5qym6UWrnQXdLcoKLk
DNxk+QualWM1opEGINJ4tLuKIiYHgWG0JvKmhCwgKtKLdEhxBxicj41EPYLnsdKG
Q+Ug9n/lkZ/VH9KHvJ2Gxsq+ovBLsuvqxXP00KYkm7YURKRLmxswW6r4F0SrMbgJ
M0jqT67d2R1yHArrCEyVWUhAONpqgY55CpXakzCe8WpvBHKRIilvqxMn4q2X4peb
82D5V0Ov13C0YnYcIhOBzGH+eKrOgTyN9VIhtfsbqar5Qor9N/cQvdVbD8DGPuuh
FijSD6Ag2cbehoeChLru2lA0NCFg7aNf4e3G0HpmQ5wXMLqmvdCO02hIII34P3ra
l5x1icJnGfnMUcJOz8xD1pXUP8+UI0W/gwDRLX1YhJwV/SqkADH4NkpdRIm/MW0L
tHwPn1slupu0WpMdxeLPZXRnDIQJuMxk968uo877i/YWKQqOSZQIvUQP7DDqKoV8
FFxKz2M2xZG1zoCeQLlWuqDsB+n6ACD+6aohiDvv4VwNUUQAJ+UJn9vnB7IMNmay
/iKf40Ed6CEhxxMowoidfUJrC1ln3KL/DU5+lzUQby858FjCiabvJfI2aXgA5qMY
5faiEXB+jOiZJOjOp5mjjP+dqXq4iNrCuuXQAKkuZ8SyOh4WVyYg8EjtYVtFE/bj
VhJwg1MC822ZJkNqFHU/5sOvY+gOPowzuXPzJomxfOuqBFB6FYDgCEsCOEOCrYuc
bTqgigNEjVoncTil6Ljgdo18fHRQqoyBGBb7GlLllp/E02bJWSzFerU/xxwtA9AU
A7TA7D+xtVC6BhpC6+AlW6bNNJg7JkPxuB6eadBLEdzK4LuJ4pqykns/xnkSp+iw
3aWKoiANvTQ/kmhZxc5PncQDLYkLUqBqAtBvPR3oCW3TAhF2s6PdRuJOJLCkTWMT
qjqHhw6poLSbeYk/vmTABqEKCfUKuwiD87QSc3VueWluZyA8YUBxcS5jb20+iQHY
BBMBCABCFiEEm9mlqVyYbdD1NZHrIoQ7lM84ncEFAmahn5cCGwMFCQPCZwAFCwkI
BwIDIgIBBhUKCQgLAgQWAgMBAh4HAheAAAoJECKEO5TPOJ3Bi4YL/j0O9Z0PgHZ1
cu0rfxtoBGzvN73+E2QKvLQNKd4JKPs1uV3Wn1MgOw7SD9xTG/47muMT4MgNZndd
F6/uDfAuBDcvwIARwmQw4vcfF5rgwxaDpEF7W3HIH00XhnyLWhgV4/vCqOhvoz9r
k6ZckrN/lkAYdBxvz4zJmBbyC/iwSVThwNeqiKdcpgsvPhO9OMwamdZIYtp6vjCz
imstishBjlTjWgbpGsbfALsgG0ELMwJmfA/oUyl9W5iJmdsFwZnHphek/aT6v9am
IxFgQfuCyrsvYc7df19NSFROgHw3/c/ob00Lc41mGSOYVWXe9wO4Pvd9LMegF3zN
osfVrkcI7i23/zWp1GHiVVdv6UdCdHNmL5i9YGqc8c00p1rlqKomM4lKlkFLTJXi
nqWqaBkN+HtfPCljomgrKMtr8Z+6z1z/G0vm0ESS4AKX5TRbcLtY3AoCabh+qFR4
5p/KWOJ5FadtcSKdOiHs+rfLVZ8rTwVyu97P6sgSp/YxNZB2dZwlTJ0FhgRmoZ+X
AQwA5UtIzf9H9SWtJtoU4ZOmd0PaKgfA1K9CCUBHzpkjfSlVuNBdxEtwCiGRT37B
1kUgf4J7VXIRcplO/sNj4K0Io1yOpr3s0tJ90liM/Rl9/r2n9pwvy2kSObvBJZJ3
+UCODEqXx4WcsSQ6oNaoKYZh2E1d9WQcR78IdTFOIoudwSNyIsOnM5HWt2Se11+6
EtRwmoiPqRv99vxH7JFqjtPBggF2JAAgyEtsWTm/xA9bg0g0GryyQ450U5n3ilyM
YJONLw4Rjgh2X71wZ0096YT9MXpaT4IFryC7ALGw+kcleldkof9kFGtCJtpueoen
f+8EqrM0ez6OGvM2SCHDqgWuvn1h2eiN1Gz4TzTkKq68OiQ+7sLxyAzrAnEbv+Ec
kYD3rd0rOqIr3ZrcI/QvjSYPfSmGUlquO5vTxpHdsqWTpPGi1YzrT/uaEn5mLPf2
a73oJTo+aOWr1FkzHwqDwiO0CzUONOH55kSMZ8XIqdf0F7AYeQ0QscWo37BxQ/CI
hAM1ABEBAAH+BwMCHkrV9MXgLPn/yCIQKI8LflUS4/v6IdvuBMi/R52sNnQUFNen
OO690ugXQkmkTWgADc0neBAeV6uGY5YfR95LSTwaUT+qt6ubC/Izmi/Yo9HZxpwC
4rPf5IcXWWMvaMpMhIG0eli011LekPswIQrI27MdNPxdb21teTaFhHiSk3TNReeV
Vnftd0e8COVavtuLwrD24AYZlSKgeX7+59pUnsMAbP8Xd8MwCx7iBh4Qz62QxAoV
peHJzl6J/g7awGiJuP46vqSofC6QhpF6KahmarB4Rb6kD5jhTmlyuaH3/aWoZaOR
e+osU4BtdtxZjRNrQkbseUBVG/XJZNbXIM3Hq6hWAx4ZNO/Awt636UjQi4N/Tz1E
fMWXdF0OgE8txahSquFZsamax4Yx6x3Ph/wC0GKqtBFenv7916L5f2QQyC8XgZDk
VkPYil8NzQFNwbU1sNu0JqjRQB3cn5GEaKekp/xSErjFYCZTkMJ3s3PPDso9Hf0H
wVZvev/CiXfEB+f6skd0CnokehYGwscUlZlCyLU/HNpuX97sA6ZOX59kxhhNtu3U
z7AzyNWR1hc7RJxSsdHoffVml/5vMCtw/DeyYp7UnA9BSEmQG0He3FP0YqiriYIK
mzEDyoVahyLCOufjWKA6MqXW7TkV1T4NV15ar4sysaXInHLMBKE8K7X/bmKpdUWL
EHGJVilLqQWDvtan/1U3AJLdDAPy4jnwUkRBl5nqgEbq9j6aeQNc26dJySY9sQKo
0SEquN/16fm3OMxNaxFX1kTB1EE0TXpioV3mlK/yNaYU7zhn7UoOB2VV8u/8/4tk
tXpNwz/dstdM5wecwUrhuiuA30Gr0qNGewGoblXncDR40euLoYAejOmoZCMfK2HL
w/5Eno/rLp6DvP1nNgeA4dierJ/IzqDVVRn2NjNTrYRPwuHDTHtXLFA/EhLTcP9J
ikKguQ1AWeKcNSHJ4LyrkF2Y4jkLkD8vG12MVIFMi24ga26XLtzZVtbPNAi4WK6O
akPLxTtwBbxOf1H0BPvIp+U3zHz5xQ6rs19Dnc1eMcteEFV6Wh9eA/j+LpIZuFiz
F0/OFRTmgZkrA4e2lNeXxaODiILCrWgrKYy/TwdkXSfb6t/UAoCFgGMDQH0FQ7Q0
LqrDXAmtRrhMfXqAprutPZvdjAE9SGddHwbE5krP5h0wxpglsQD187tQNhdkVgay
/shMS2rOMUlmj+03AUlivg+nt3t1DndUnEOSvGZXpcNZUBwGNecRuj+UH5bPv7WK
F1uLH1RTO+RgKJLqrmr12qqNWz1yIXY8dI3UPyzYGfpxBwDnJFuEtZMOpqPOSOjb
v5T1Hr0kswaRc+QvcGRQEKCJAbwEGAEIACYWIQSb2aWpXJht0PU1kesihDuUzzid
wQUCZqGflwIbDAUJA8JnAAAKCRAihDuUzzidwVXfC/9vH22/m4zxBZhK3sCpO1ZE
jvaG8soC+BV6VC4WKqSEuPksTuiDc/RLQZPC303GSI5yDBsaRvA0VZDFUUlOMIQl
cmwFWj2ISAEOzwwVwdbUSe1u1d04muFBqWm7BHgdGZKIpjt46rPg5tDEuXZz2bxj
LAJaHi/oKn4mRJj/pzDOdUoJVcEC5HgJOGRQPKbDqy5wLzb29v/Ih2ieL20GKw5T
r6Kh1xJHM4TyUmMuohcQHW8wbvKrLLceVOTNfcQP33EWhp0qILJlGOcmedI+iAzW
ihtM56bJ5mlv7X1no7gje9cpKX+ErLAb2yerKhHmSPo+s/9taO/kC5EMONhebAOY
6/XclBVKTvcsDcU1WBlm+nquJzmi43ZmNyWnVJS91glGtWMtfyDxVBo3/TsehZ/2
XVd9jVuTPhbRlZlpJt3Oz6nhfRoX8dyCU+DbnEe1IcBY5FnjyiBav+LLrgZ1J/tR
zB9DL3RyCL1+fCspl8lEoGeUsZ0q8JZfYtj05pgyYww=
=7Zun
-----END PGP PRIVATE KEY BLOCK-----
[root@pg16 ~]# mv private-key.txt public-key.txt /home/postgres/
[root@pg16 ~]# cd /home/postgres/
[root@pg16 postgres]# chown postgres:postgres private-key.txt public-key.txt
[root@pg16 postgres]# su - postgres
[postgres@pg16 ~]$
复制

公钥加密

dearmor()函数将公钥的ASCII转换成PostgreSQL bytea格式，然后再用pgp_pub_encrypt()函数对abc123这个字符串密码进行加密。

[postgres@pg16 ~]$ psql
psql (16.3)
Type "help" for help.

postgres=# \set publickey `cat /home/postgres/public-key.txt`
postgres=# select pgp_pub_encrypt('abc123',dearmor(:'publickey'));

                                                                                                                                                                                pgp_pub_encrypt


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
 \xc1c0cc03deed41469c7a1acc010c0095b6869440f096ae9eb8659a368939a399548fd37e01095e25b5fd830f13fbfa33ea4a743f2ea9f9eff45a7174ef33eb092f7711a501645c5ce2600c8a57b90eb0f94f89ca930da04973b5bad4161f73298bcdef9918744e38d7f8f5c8d1487dcfc35c52ab926543339a63ff099b195d22b9a958eaf9026ee
f22a254ab65a8b85007e22ffedfd7950711faacbfe531414e8f5e3f3a1f19f35dc2354cc68e26734a49bcbec89df307a75a96e1ab06da413113aaee5c1b8c8e182541d6bdc60537ba4d6fd9c117ec1fd26d50d4a40a999c4a37a3a565543de3574e5f18cfa814799b4a04e209771a4009b414682457b79b64569c64a3c00a43a3cedb87721c0f41784
6867fb66504dbb79a25a696f23191fb7233b6d29e14f5a63ab519e35b03eeef62b0e0a17227254b58f7e1edbf55c509226628b733d5ca77b8117959e26d35210b0c715ddfe7e9ec952b07b2cf98b61f70f393283e0846c80d1c3e6c1e2e8ff6c5a7421b31ea106b319ae60ae1e9e38bd24d203e7decdd16835d6d5b51e222d23701e2e4bb86a597b40
50d598aa4544f8f141ffd8343448f8f0be539de1834153dbf5689076a8ec68d069def90c3ca2957ac57a6cf144ff4
(1 row)


postgres=#

复制

私钥解密

dearmor()函数将私钥的ASCII转换成PostgreSQL bytea格式，注意这里我们要用到私钥的密码，就是我们第一步生成主密钥的哪个私钥密码123456，然后再用pgp_pub_decrypt()函数对之前加密的bytea格式密码进行解密。

postgres=# \set privatekey `cat /home/postgres/private-key.txt`
postgres=# select pgp_pub_decrypt('\xc1c0cc03deed41469c7a1acc010c0095b6869440f096ae9eb8659a368939a399548fd37e01095e25b5fd830f13fbfa33ea4a743f2ea9f9eff45a7174ef33eb092f7711a501645c5ce2600c8a57b90eb0f94f89ca930da04973b5bad4161f73298bcdef9918744e38d7f8f5c8d1487dcfc35c52ab926543339a63ff099b195d22b9a958eaf9026eef22a254ab65a8b85007e22ffedfd7950711faacbfe531414e8f5e3f3a1f19f35dc2354cc68e26734a49bcbec89df307a75a96e1ab06da413113aaee5c1b8c8e182541d6bdc60537ba4d6fd9c117ec1fd26d50d4a40a999c4a37a3a565543de3574e5f18cfa814799b4a04e209771a4009b414682457b79b64569c64a3c00a43a3cedb87721c0f417846867fb66504dbb79a25a696f23191fb7233b6d29e14f5a63ab519e35b03eeef62b0e0a17227254b58f7e1edbf55c509226628b733d5ca77b8117959e26d35210b0c715ddfe7e9ec952b07b2cf98b61f70f393283e0846c80d1c3e6c1e2e8ff6c5a7421b31ea106b319ae60ae1e9e38bd24d203e7decdd16835d6d5b51e222d23701e2e4bb86a597b4050d598aa4544f8f141ffd8343448f8f0be539de1834153dbf5689076a8ec68d069def90c3ca2957ac57a6cf144ff4',dearmor(:'privatekey'),'123456');


 pgp_pub_decrypt
-----------------
 abc123
(1 row)

postgres=#
复制

原始加密函数

这些函数仅对数据运行密码；它们没有 PGP 加密的任何高级功能。因此他们有一些主要问题：

1.他们直接使用用户密钥作为密钥。

2.它们不提供任何完整性检查来查看加密数据是否被修改。

3.他们希望用户自己管理所有加密参数，甚至 IV。

4.他们不处理文本。

加密用encrypt()函数输入密码字符串123456 ,在输入key1密钥，指定的密码方法加密比如bf，aes。

postgres=# TRUNCATE TABLE users;
TRUNCATE TABLE
postgres=# INSERT INTO users (username,password) VALUES('xiaoli',encrypt('123456','key1','aes'));
INSERT 0 1
postgres=# INSERT INTO users (username,password) VALUES('hexi',encrypt('abc123','key1','aes'));
INSERT 0 1
postgres=#

复制

解密使用decrypt()函数输入加密字符串bytea格式，指定key1密钥，指定的密码方法aes。

postgres=# select id,username,password,encode(decrypt(password::bytea,'key1','aes'),'escape')  from users;
 id | username |              password              | encode
----+----------+------------------------------------+--------
 18 | xiaoli   | \xbdad3c988510b3974f42cdf135f21b70 | 123456
 19 | hexi     | \x22b6ad2270dc08ce4516c97fae87fca3 | abc123
(2 rows)

postgres=#

复制

随机数据函数

gen_random_bytes()函数随机生成5个bytea格式的十六进制字符串

postgres=# SELECT gen_random_bytes(5);
 gen_random_bytes
------------------
 \x60c7c634b3
(1 row)

postgres=# SELECT gen_random_bytes(5);
 gen_random_bytes
------------------
 \x995bf43df4
(1 row)

postgres=#
复制

gen_random_uuid()函数随机生成uuid

postgres=# SELECT gen_random_uuid();
           gen_random_uuid
--------------------------------------
 edace3dd-9810-430a-8746-8db6327e9aaf
(1 row)

postgres=# SELECT gen_random_uuid();
           gen_random_uuid
--------------------------------------
 4a0b31d6-0c92-4da3-9c3c-6ed47125d2bc
(1 row)

postgres=#
复制

总结

存放在表中的铭感数据应通过pgcrypto模块函数进行加密。加密的方式最好使用有密钥的函数。

墨力计划 postgresql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

前言
PostgreSQL特定列的加密
安装pgcrypto模块
通用哈希函数
密码哈希函数
PGP 加密函数
- 对称 PGP 密钥加密
- 非对称 PGP 密钥加密
  - 生成主密钥
  - 列出密钥
  - 输出密钥
  - 公钥加密
  - 私钥解密
原始加密函数
随机数据函数
总结

PostgreSQL特定列的加密

前言

PostgreSQL特定列的加密

安装pgcrypto模块

通用哈希函数

密码哈希函数

PGP 加密函数

对称 PGP 密钥加密

非对称 PGP 密钥加密

生成主密钥

列出密钥

输出密钥

公钥加密

私钥解密

原始加密函数

随机数据函数

总结

评论