PostgreSQL 安全管理 pgcrypto

1 环境准备

CREATE EXTENSION pgcrypto ;

CREATE TABLE test_user(id serial,username varchar(32),password text);
CREATE UNIQUE INDEX idx_test_user_username
ON test_user USING BTREE(username);
\d test_user

2 使用 md5 加密

2.1 插入数据

INSERT INTO test_user(username,password) VALUES('user1',md5('123456'));
INSERT INTO test_user(username,password) VALUES('user2',md5('123456'));

2.2 数据解密

SELECT * FROM test_user;

SELECT * FROM test_user WHERE password=md5('123456');

3 使用crypt() 函数加密

INSERT INTO test_user(username,password) VALUES('user3',crypt('123456',gen_salt('md5')));
INSERT INTO test_user(username,password) Values('user4',crypt('123456',gen_salt('md5')));

3.1 数据解密

 SELECT * FROM test_user Where username IN ('user3','user4');

SELECT * FROM test_user WHERE username ='user3' 
AND password=crypt('123456',password);
 id | username |              password              
----+----------+------------------------------------
  5 | user3    | $1$cS7Bs67A$5c2FTClGTOBYiHpG1HyvA/
(1 row)
SELECT * FROM test_user WHERE username ='user4' 
AND password=crypt('123456',password);
 id | username |              password              
----+----------+------------------------------------
  6 | user4    | $1$L6Rao5/l$7URcaCbT9Hrsrt9JcoBGq.
(1 row)