1、创建插件
create
extension pgcrypto;
2、创建表:
test=# create table user_test(username varchar( 20 ),password varchar( 60 )); CREATE TABLE test=# insert into user_test values( 'miya' ,encode(encrypt( '123' , 'abc' , 'aes' ), 'hex' )); INSERT 0 1 test=# insert into user_test values( 'kimi' ,encode(encrypt( '456' , 'abc' , 'aes' ), 'hex' )); INSERT 0 1 test=# select * from user_test; username | password ----------+---------------------------------- miya | a4bf9afce727dbd2805393a86a24096c kimi | 84279efc7942ca7364abcce78db90b0b ( 2 rows) |
3、解密后可以看到之前的密码:
test=# select convert_from(decrypt(decode(password, 'hex' ), 'abc' , 'aes' ), 'SQL_ASCII' ) as real_pw,* from user_test; real_pw | username | password ---------+----------+---------------------------------- 123 | miya | a4bf9afce727dbd2805393a86a24096c 456 | kimi | 84279efc7942ca7364abcce78db90b0b |
参考:
postgresql 中的加密扩展插件pgcrypto用法说明_PostgreSQL_脚本之家
【PG安全】-PG的数据加密和解密实践 - 墨天轮
Postgresql数据加密函数介绍_pg加密函数-CSDN博客
PostgreSQL: Documentation: 12: F.25. pgcrypto