本文簡單介紹了PostgreSQL的外掛：postgresql_anonymizer 。該外掛利用PG的安全機制，可用於對隱私資料進行“動態”脫敏。

**安裝**
依賴tsm_system_rows和ddlx兩個擴充套件，需提前安裝妥當。
```
[local:/data/pg12]:5432 pg12@testdb=# create extension tsm_system_rows;
CREATE EXTENSION

[local:/data/pg12]:5432 pg12@testdb=# create extension ddlx;
CREATE EXTENSION
[local:/data/pg12]:5432 pg12@testdb=# 
```
clone程式碼，編譯安裝
```
[pg12@localhost contrib]$ git clone https://gitlab.com/dalibo/postgresql_anonymizer.git -b 0.5.0
Cloning into 'postgresql_anonymizer'...
remote: Enumerating objects: 2396, done.
remote: Counting objects: 100% (2396/2396), done.
remote: Compressing objects: 100% (854/854), done.
remote: Total 2396 (delta 1524), reused 2379 (delta 1513)
Receiving objects: 100% (2396/2396), 12.85 MiB | 55.00 KiB/s, done.
Resolving deltas: 100% (1524/1524), done.
Note: checking out 'c108008719a1394b55779ff7115f188511c6ec89'.

You are in 'detached HEAD' state. You can look around, make experimental
changes and commit them, and you can discard any commits you make in this
state without impacting any branches by performing another checkout.

If you want to create a new branch to retain commits you create, you may
do so (now or later) by using -b with the checkout command again. Example:

git checkout -b new_branch_name

```
編譯安裝
```
[pg12@localhost contrib]$ cd postgresql_anonymizer/
[pg12@localhost postgresql_anonymizer]$ make
mkdir -p anon
cp anon.sql anon/anon--0.5.0.sql
cp data/default/* anon/
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -I. -I./ -I/appdb/pg12/pg12.1/include/postgresql/server -I/appdb/pg12/pg12.1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o anon.o anon.c -MMD -MP -MF .deps/anon.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC anon.o -L/appdb/pg12/pg12.1/lib   -Wl,--as-needed -Wl,-rpath,'/appdb/pg12/pg12.1/lib',--enable-new-dtags  -shared -o anon.so
[pg12@localhost postgresql_anonymizer]$ make install
mkdir -p anon
cp anon.sql anon/anon--0.5.0.sql
cp data/default/* anon/
/bin/mkdir -p '/appdb/pg12/pg12.1/share/postgresql/extension'
/bin/mkdir -p '/appdb/pg12/pg12.1/share/postgresql/extension/anon'
/bin/mkdir -p '/appdb/pg12/pg12.1/lib/postgresql'
/bin/install -c -m 644 .//anon.control '/appdb/pg12/pg12.1/share/postgresql/extension/'
/bin/install -c -m 644 .//anon/*  '/appdb/pg12/pg12.1/share/postgresql/extension/anon/'
/bin/install -c -m 755  anon.so '/appdb/pg12/pg12.1/lib/postgresql/'
[pg12@localhost postgresql_anonymizer]$

```

**體驗**
建立擴充套件，新增到載入庫中shared_preload_libraries，重啟例項
```
[local:/data/pg12]:5432 pg12@testdb=# CREATE EXTENSION IF NOT EXISTS anon CASCADE;
CREATE EXTENSION
[local:/data/pg12]:5432 pg12@testdb=# alter system set shared_preload_libraries = 'anon';
ALTER SYSTEM
[local:/data/pg12]:5432 pg12@testdb=# 
[local:/data/pg12]:5432 pg12@testdb=# exit
[pg12@localhost contrib]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-11-19 16:41:45.940 CST [15439] LOG:  starting PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
2019-11-19 16:41:45.940 CST [15439] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-11-19 16:41:45.940 CST [15439] LOG:  listening on IPv6 address "::", port 5432
2019-11-19 16:41:45.943 CST [15439] LOG:  listening on Unix socket "/data/pg12/.s.PGSQL.5432"
2019-11-19 16:41:46.013 CST [15439] LOG:  redirecting log output to logging collector process
2019-11-19 16:41:46.013 CST [15439] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[pg12@localhost contrib]$

```
下面建立一張"隱私"表,使用該外掛實現動態脫敏
```
[local:/data/pg12]:5432 pg12@testdb=# drop table t_masking;
ERROR:  table "t_masking" does not exist
[local:/data/pg12]:5432 pg12@testdb=# create table t_masking(id serial,name varchar(20),zipcode varchar(20));
 values('王五','230100');
CREATE TABLE
[local:/data/pg12]:5432 pg12@testdb=# 
[local:/data/pg12]:5432 pg12@testdb=# insert into t_masking(name,zipcode) values('張三','440100');
INSERT 0 1
[local:/data/pg12]:5432 pg12@testdb=# insert into t_masking(name,zipcode) values('李四','420100');
INSERT 0 1
[local:/data/pg12]:5432 pg12@testdb=# insert into t_masking(name,zipcode) values('王五','230100');
INSERT 0 1
[local:/data/pg12]:5432 pg12@testdb=# 
```
啟用動態脫敏，建立脫敏使用者masking
```
[local:/data/pg12]:5432 pg12@testdb=# SELECT anon.start_dynamic_masking();
 start_dynamic_masking 
-----------------------
 t
(1 row)

[local:/data/pg12]:5432 pg12@testdb=# 
[local:/data/pg12]:5432 pg12@testdb=# CREATE ROLE masking LOGIN with password 'root';
ERROR:  syntax error at or near "with"
LINE 1: CREATE ROLE masking LOGIN with password 'root';
                                  ^
[local:/data/pg12]:5432 pg12@testdb=# CREATE user masking with password 'root';
CREATE ROLE
```
打標籤
```
[local:/data/pg12]:5432 pg12@testdb=# SECURITY LABEL FOR anon ON ROLE masking IS 'MASKED';
SECURITY LABEL
[local:/data/pg12]:5432 pg12@testdb=# SECURITY LABEL FOR anon ON COLUMN t_masking.name is 'MASKED WITH FUNCTION anon.fake_last_name()';
SECURITY LABEL
[local:/data/pg12]:5432 pg12@testdb=# SECURITY LABEL FOR anon ON COLUMN t_masking.zipcode is 'MASKED WITH FUNCTION anon.random_zip()';
SECURITY LABEL
[local:/data/pg12]:5432 pg12@testdb=#

```
t_masking的資料
```
[local:/data/pg12]:5432 pg12@testdb=# select * from t_masking;
 id | name | zipcode 
----+------+---------
  1 | 張三 | 440100
  2 | 李四 | 420100
  3 | 王五 | 230100
(3 rows)

[local:/data/pg12]:5432 pg12@testdb=#

```
使用者masking登入查詢得到的資料
```
[pg12@localhost pgddl]$ psql -U masking
Expanded display is used automatically.
psql (12.1)
Type "help" for help.

[local:/data/pg12]:5432 masking@testdb=>

```
可以看到，資料已被“脫敏”。

**參考資料**
[PostgreSQL Anonymizer](https://gitlab.com/dalibo/postgresql_anonymizer)

PostgreSQL DBA(133) - Extension(postgresql_anonymizer)

相關文章