今天有同事问我如何给数据库中的敏感数据加密问题，开始没相到什么方法，后来查了下手册，可以通过 PostgreSQL 的 pgcrypto 模块实现，下面是实验步骤：

环境准备

创建外部模块 pgcrypto

1
2

mydb=# create extension pgcrypto ;  
CREATE EXTENSION

创建测试表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

mydb=> create table test_user(id serial,username varchar(32),password text);  
NOTICE: CREATE TABLE will create implicit sequence "test_user_id_seq" for serial column "test_user.id"  
CREATE TABLE

mydb=> create unique index idx_test_user_username on test_user using btree (username);  
CREATE INDEX

mydb=> /d test_user  
 Table "mydb.test_user"  
 Column | Type | Modifiers  
----------+-----------------------+--------------------------------------------------------  
id | integer | not null default nextval('test_user_id_seq'::regclass)  
username | character varying(32) |  
password | text |  
Indexes:  
 "idx_test_user_username" UNIQUE, btree (username)

方法一: 使用 md5 加密

插入测试用户信息

1
2
3
4

mydb=> insert into test_user(username,password) values ('user1',md5('123456'));  
INSERT 0 1  
mydb=> insert into test_user(username,password) values ('user2',md5('123456'));  
INSERT 0 1

查询用户信息，解密

1
2
3
4
5
6
7
8
9
10
11
12
13

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

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

备注：使用 md5 加密后，如果两个用户的密码相同，那么 md5 加密后的密码也一样，如果破解了一个 md5 密码，那么很容易破解 md5 值相同的密码。

方法二: 使用 crypt() 加密

使用 crypt() 函数增加两条用户信息

1
2
3
4

mydb=> insert into test_user(username,password) values ('user3',crypt('123456',gen_salt('md5')));  
INSERT 0 1  
mydb=> insert into test_user(username,password) values ('user4',crypt('123456',gen_salt('md5')));  
INSERT 0 1

查询新增用户信息

1
2
3
4
5

mydb=> select * From test_user where username in ('user3','user4');  
id | username | password  
----+----------+------------------------------------  
 5 | user3 | $1$cS7Bs67A$5c2FTClGTOBYiHpG1HyvA/  
 6 | user4 | $1$L6Rao5/l$7URcaCbT9Hrsrt9JcoBGq.(2 rows)

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

查询，解密测试

1
2
3
4
5
6
7
8
9
10
11

mydb=> select * From test_user where username ='user3' and password=crypt('123456',password);  
id | username | password  
----+----------+------------------------------------  
 5 | user3 | $1$cS7Bs67A$5c2FTClGTOBYiHpG1HyvA/  
(1 row)

mydb=> select * From test_user where username ='user4' and password=crypt('123456',password);  
id | username | password  
----+----------+------------------------------------  
 6 | user4 | $1$L6Rao5/l$7URcaCbT9Hrsrt9JcoBGq.  
(1 row)

附: 函数解释

crypt()
crypt(password text, salt text) returns text
Calculates a crypt(3)-style hash of password. When storing a new password, you need to use gen_salt() to generate a new salt value. To check a password, pass the stored hash value as salt, and test whether the result matches the stored value.

crypt() 函数支持的加密算法

gen_salt()
gen_salt(type text [, iter_count integer ]) returns text
Generates a new random salt string for use in crypt(). The salt string also tells crypt() which algorithm to use.
The type parameter specifies the hashing algorithm. The accepted types are: des, xdes, md5 and bf.