PostgreSQL: 使用 pgcrypto 给敏感数据加密

文章来源：http://francs3.blog.163.com/blog/static/4057672720123267457650/

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

一 环境准备
--1.1 创建外部模块 pgcrypto

 mydb=# create extension pgcrypto ;
CREATE EXTENSION

   

--1.2 创建测试表

 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 加密    
--2.1 插入测试用户信息

 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

   

--2.2 查询用户信息，解密

 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() 函数加密      
--3.1 使用 crypt() 函数增加两条用户信息

 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

   

--3.2 查询新增用户信息

 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 安全。

--3.3 查询，解密测试

 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)

   

 

四附：函数
--4.1 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.

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

 

--4.3 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.