postgresql数据脱敏

# 执行此命令后退出session重连
# 或者可以直接配置全局的shared_preload_libraries
ALTER DATABASE test SET session_preload_libraries = 'anon';
# 在db中使用此插件
CREATE EXTENSION IF NOT EXISTS anon CASCADE;
# 初始化插件，主要是将一些用于数据脱敏的假数据存入schema名为anon的一些表中
SELECT anon.init();
# 启动脱敏
SELECT anon.start_dynamic_masking();

# 测试用的表和用户
CREATE TABLE t1(id int, name text, age int);
insert into t1 values (1, 'abcdefg', 2);
CREATE USER testuser password '1';

# 给用户加脱敏标识
SECURITY LABEL FOR anon ON ROLE testuser IS 'MASKED';

# 给表的特定字段设置脱敏，可以用anon已经初始化好的一些脱敏函数
SECURITY LABEL FOR anon ON COLUMN t1.name IS 'MASKED WITH FUNCTION anon.partial(name,2,$$**$$,2)';

# 使用普通用户查询
test=# select * from t1;
 id |  name   | age
----+---------+-----
  1 | abcdefg |   2
(1 row)

# 使用脱敏用户testuser查询
test=> select * from t1;
 id |  name  | age
----+--------+-----
  1 | ab**fg |   2
(1 row)

# 根据标记在schema mask下生成同名的view
CREATE OR REPLACE VIEW mask.t1 AS SELECT id,CAST(anon.partial(name,2,$$**$$,2) AS text) AS name,age FROM public.t1;
# 将testuser在public下的权限移除
REVOKE ALL ON SCHEMA public FROM testuser;
# 将anon、mask这两个schema的权限提供给testuser
GRANT USAGE ON SCHEMA anon TO testuser;
GRANT SELECT ON ALL TABLES IN SCHEMA anon TO testuser;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA anon TO testuser;
GRANT USAGE ON SCHEMA mask TO testuser;
GRANT SELECT ON ALL TABLES IN SCHEMA mask TO testuser;
# 将testuser的读取路径默认添加上了mask schema，查找表的时候优先搜索mask再搜索public。即select * from t1，被自动变成了select * from mask.t1
ALTER ROLE testuser SET search_path TO mask,public;

# 切换完schema后，退出session重连
ALTER DATABASE test SET anon.sourceschema TO 's1';
# 重连后，执行dynamic_masking命令
SELECT anon.start_dynamic_masking();

# 用testuser连接，可以看到对应的表都是mask schema下的了
test=> \d
              List of relations
 Schema |     Name      |   Type   |  Owner
--------+---------------+----------+----------
 mask   | t1            | view     | pdbadmin
 mask   | t2            | view     | pdbadmin
(2 rows)

test=# \d+ t1
                               View "mask.t1"
 Column |  Type   | Collation | Nullable | Default | Storage  | Description
--------+---------+-----------+----------+---------+----------+-------------
 id     | integer |           |          |         | plain    |
 name   | text    |           |          |         | extended |
 age    | integer |           |          |         | plain    |
View definition:
 SELECT t1.id,
    anon.partial(t1.name, 2, '**'::text, 2) AS name,
    t1.age
   FROM public.t1;

test=# \d+ t2
                               View "mask.t2"
 Column |  Type   | Collation | Nullable | Default | Storage  | Description
--------+---------+-----------+----------+---------+----------+-------------
 id     | integer |           |          |         | plain    |
 name   | text    |           |          |         | extended |
 age    | integer |           |          |         | plain    |
View definition:
 SELECT t2.id,
    anon.partial(t2.name, 2, '**'::text, 2) AS name,
    t2.age
   FROM s1.t2;

# 查询到的也都是脱敏的数据
test=> select * from t1;
 id |  name  | age
----+--------+-----
  1 | ab**fg |   2
(1 row)

test=> select * from t2;
 id |  name  | age
----+--------+-----
  1 | aa**aa |   1
(1 row)

# 使用时需要打开GUC：
set anon.transparent_dynamic_masking=true;

# 例如执行上述已经被设置脱敏标签的t1：
# 脱敏目前只支持 COPY {TABLE} TO 的格式
# COPY FROM和COPY (SELECT ...) TO都不支持
COPY t1 TO stdout;

# 动态替换实际上就是把mask.t1展开了(因为view不能直接用作copy)：
SELECT id AS id, name AS name, age AS age FROM mask.t1;