数据中往往会出现一些敏感字段，例如电话，邮箱等，这时候有需求进行加密保存
目前可以实现的方式有两种
方式一：这种方法，aes的加密方法不支持aes-192,不支持aes-256
#使用encrypt加解密 #加密保存 insert into test2(username,email) values('liulm7',encrypt('liulm7@xxxxx.com','aa','aes-128')); postgres=# select * from test2 where username='liulm7'; username | email | phone | address ----------+--------------------------------------------------------------------+-------+--------- liulm7 | x39162fadc179498413b75b69bc65c98d19e454a0c67bd644118ab9df3c7b49ef | | (1 row) #解密查看 postgres=# select username,convert_from(decrypt(email::bytea,'aa'::bytea,'aes-128'),'utf8') as email from test2 where username='liulm7'; username | email ----------+------------------- liulm7 | liulm7@xxxxx.com (1 row)
方式二：
#使用pgp_sym_encrypt加密 insert into test2(username,email) values('pgp_sym',pgp_sym_encrypt('pgp@lenovo.com','abc','cipher-algo=aes256, compress-algo=2, compress-level=9')); insert into test2(username,email) values('pgp_sym',pgp_sym_encrypt('pgppp@lenovo.com','abc','cipher-algo=aes256, compress-algo=2, compress-level=9')); #解密查看 postgres=# select username,pgp_sym_decrypt(email::bytea,'abc') from test2 where username='pgp_sym'; username | pgp_sym_decrypt ----------+------------------ pgp_sym | pgp@lenovo.com pgp_sym | pgppp@lenovo.com (2 rows)
查看某个数据库下加载的所有函数和存储过程
select routine_name from information_schema.routines where routine_catalog = 'uat_updated' and routine_schema = 'public' order by routine_name;
说明：routine_catalog: DBname routine_schema: schema名称
查看某个函数的信息
uat_updated=# df *pgp_sym_encrypt* List of functions Schema | Name | Result data type | Argument data types | Type --------+-----------------------+------------------+---------------------+------ public | pgp_sym_encrypt | bytea | text, text | func public | pgp_sym_encrypt | bytea | text, text, text | func public | pgp_sym_encrypt_bytea | bytea | bytea, text | func public | pgp_sym_encrypt_bytea | bytea | bytea, text, text | func (4 rows)
查看已经安装或者内置的模块
uat_updated=# select * from pg_available_extensions where name ~ 'pgcrypto'; name | default_version | installed_version | comment ----------+-----------------+-------------------+------------------------- pgcrypto | 1.3 | 1.3 | cryptographic functions (1 row)
一个报错处理：
ERROR: function pgp_sym_encrypt(unknown, unknown, unknown) does not exist LINE 1: insert into my_test values('pgp_sym',pgp_sym_encrypt('pgp@le... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
解决方式：
1 通过上面的命令查看是否已经安装这个模块， 2 uat_updated=# create extension pgcrypto; CREATE EXTENSION
3 测试这个模块里面的一个函数
select encrypt('123456','aa','aes');
发现的一个问题：
描述：库下面的public schema被删除了，导致create extension pgcrypto;能成功，但是执行函数就报错：函数不存在
解决：
create schema public; #新建public cdp_edge=> grant usage on schema public to public; #赋予权限 GRANT cdp_edge=> grant create on schema public to public; GRANT cdp_edge=> drop extension pgcrypto; #删除扩展模块 DROP EXTENSION cdp_edge=> create extension pgcrypto; #重新加载扩展模块 CREATE EXTENSION cdp_edge=> select encrypt('123456','aa','aes'); #验证函数 encrypt ------------------------------------ x39c3c665757a0ff973b83fb98cc3d63f (1 row)
参数说明：
cipher-algo 加密算法 compress-algo 压缩类型，使用的压缩算法，取值 0,1(zip),2(zlib) compress-level 压缩比，级别越高压缩比越大，速度也会更慢，压缩级别(0~9) convert-crlf 是否在加密的时候将 装换为 ，在解密的时候将 转换为 disable-mdc 是否使用SHA1保护数据 sess-key 是否使用单独的会话密钥 s2k-mode 使用哪一种S2K算法 s2k-count 使用S2K的迭代次数 s2k-digest-algo 在s2k中使用哪种摘要算法 s2k-cipher-algo 要用哪种密码来加密独立的会话密钥 unicode-mode 是否将文本数据从数据库内部编码转为UTF-8并返回