postgres服务之加密以及public-schema删除后的问题和处理

数据中往往会出现一些敏感字段，例如电话，邮箱等，这时候有需求进行加密保存

目前可以实现的方式有两种

方式一：这种方法，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并返回