postgresql数据加密函数使用

可以采用md5函数进行数据加密存储和校验
<pre>
create table usertable(id serial,PASSWORD text);

insert into usertable (PASSWORD) values(md5('222222'));
insert into usertable (PASSWORD) values(md5('111111'));

SELECT * from usertable where PASSWORD=md5('222222')
</pre>
更加安全的是采用加盐模式，密码相同但是结果不同
<pre>
insert into usertable(password) values (crypt('123456',gen_salt('md5')));
insert into usertable(password) values (crypt('123456',gen_salt('md5')));
SELECT * from usertable where PASSWORD=crypt('123456',password);

2 $1$LEt6lBlJ$cSucnCctkaLU2tXCLCpLk0
3 $1$tP/w8ICv$Ucx9BP9j/eWmuAtiJjbTP/
</pre>

附：函数
**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() 函数支持的加密算法**

PostgreSQL: 使用 pgcrypto 给敏感数据加密 - francs - My DBA LIFE

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