渡鴉之丘: EDB 11 新功能 — 內建資料遮碼（Data Redaction）

EDB 的企業版 EDB Postgres Advanced Server 11 新增了幾個內建新功能，其中一個是資料遮碼功能（Data Redaction）。
這個功能主要是把資料打叉叉的功能，其實這已經在 SQL Server 跟 Oracle 有提供了，但是 Postgres 需要 DIY 處理，日前 EDB 有提供教學文章～
現在 EDB 直接提供整合的管理，應用上更貼近 SQL Server 跟 Oracle 一些，不用像使用純 PGSQL 要 DIY；而且 DIY 的物件一多起來，維護人員就會眼花撩亂了～

在這邊整合資料遮碼以及外部表作該功能的練習～

遮碼不要用簡單的打叉叉，而是用洗牌（ Shuffle ）處理，直接對原始資料字串作 Shuffle。

這邊用 Python 的 python-string-utils 模組來偷懶，準備 shuffle 的 stored function。

首先要先對 EDB 的 LanguagePack 提供的 Python 安裝這個模組

[root@edb11 ~]$ /opt/edb/languagepack-10/Python-3.4/bin/pip install python-string-utils

要注意這個函數要使用 Superuser 建立（在企業版裡面是 enterprisedb，不是 postgres）

CREATE FUNCTION redaction_shuffle(inptstr TEXT)
RETURNS VARCHAR
AS $$
import string_utils
return string_utils.shuffle(inptstr)

$$ LANGUAGE plpython3u 
   PARALLEL SAFE
   SECURITY DEFINER
   RETURNS NULL ON NULL INPUT;

測試確認一下

edb=# select redaction_shuffle('hello world');
 redaction_shuffle 
-------------------
  lhoewlrdol
(1 row)

edb=#
edb=# select redaction_shuffle('你好嗎，我很好');
 redaction_shuffle 
-------------------
 ，我你好嗎很好
(1 row)

edb=# 
edb=# select redaction_shuffle('你好嗎，我很好');
 redaction_shuffle 
-------------------
 嗎好，很好你我
(1 row)

edb=#

接著準備一個表格，用來遮碼：這邊直接選 EDB 內建範例來試試，把裡面的 emp 表格的員工名稱打亂，並且對任意的普通帳號都生效（紅色部份）

edb=# \i /opt/edb/as11/share/edb-sample.sql 
edb=# \d public.emp
                           Table "public.emp"
  Column  |            Type             | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
 empno    | numeric(4,0)                |           | not null |
 ename    | character varying(10)       |           |          |
 job      | character varying(9)        |           |          |
 mgr      | numeric(4,0)                |           |          |
 hiredate | timestamp without time zone |           |          |
 sal      | numeric(7,2)                |           |          |
 comm     | numeric(7,2)                |           |          |
 deptno   | numeric(2,0)                |           |          |
Indexes:
    "emp_pk" PRIMARY KEY, btree (empno)
剩下的略過

edb=# CREATE REDACTION POLICY emp_name_shuffle ON emp FOR ( TRUE )
 ADD COLUMN ename USING redaction_shuffle(ename);
CREATE REDACTION POLICY
edb=#

最後，準備一個「被遮碼」的帳戶

edb=# create user aaa;

簡單試一試遮碼

edb=# -- 對 superuser 無效
edb=# select * from emp limit 3;
 empno | ename |   job    | mgr  |      hiredate      |   sal   |  comm  | deptno
-------+-------+----------+------+--------------------+---------+--------+--------
  7369 | SMITH | CLERK    | 7902 | 17-DEC-80 00:00:00 |  800.00 |        |     20
  7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 |     30
  7521 | WARD  | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 |     30
(3 rows)

edb=#
edb=# \c edb aaa
edb=> -- 對普通帳號有效
edb=> select * from emp limit 3;
 empno | ename |   job    | mgr  |      hiredate      |   sal   |  comm  | deptno
-------+-------+----------+------+--------------------+---------+--------+--------
  7369 | IHSTM | CLERK    | 7902 | 17-DEC-80 00:00:00 |  800.00 |        |     20
  7499 | LLEAN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 |     30
  7521 | ADRW  | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 |     30
(3 rows)

edb=>

功能看來 OK～
這邊也可以看到 Redaction Policy 對 Superuser 不適用。
而且，就算普通帳號查看相關的 view 也是被遮住

edb=> select * from salesemp ;
 empno | ename  |      hiredate      |   sal   |  comm
-------+--------+--------------------+---------+---------
  7499 | NELAL  | 20-FEB-81 00:00:00 | 1600.00 |  300.00
  7521 | DRAW   | 22-FEB-81 00:00:00 | 1250.00 |  500.00
  7654 | ITNRMA | 28-SEP-81 00:00:00 | 1250.00 | 1400.00
  7844 | NRRUTE | 08-SEP-81 00:00:00 | 1500.00 |    0.00
(4 rows)

edb=>

然後就是兩個主要測試

來源端的 EDB 表格作遮碼，然後用外部表查看遮碼後的結果
直接把 Redaction Policy 套用在外部表上

事實上，外部表可以對「同一個 Instance」裡面不同的 Database 之間的表格相互存取，不見得要準備兩個獨立的資料庫才能測試～
這邊將在 edb Database 裡面作為來源資料，並以另一個 postgres Database 建立外部表。

測試一：
直接建立外部表，主要是 User Mapping 資訊要使用來源端「被遮碼」的帳號密碼
這邊已經將 pg_hba.conf 的登入政策調整成 localhost 免密碼登入了，所以下面的 USER MAPPING 省略打密碼，方便作測試～

postgres=# create extension postgres_fdw ;
CREATE EXTENSION
postgres=# create server shuffledata foreign data wrapper postgres_fdw OPTIONS (host 'localhost', port '5444', dbname 'edb');
CREATE SERVER
postgres=# create user MAPPING FOR PUBLIC SERVER shuffledata OPTIONS (user 'aaa');
CREATE USER MAPPING
postgres=# import FOREIGN SCHEMA public limit to (emp) from server shuffledata into public;
IMPORT FOREIGN SCHEMA
postgres=#

查看看確認 OK

postgres=# \c
psql.bin (11.0.3, server 11.0.3)
You are now connected to database "postgres" as user "enterprisedb".
postgres=#
postgres=# \d
              List of relations
 Schema | Name |     Type      |    Owner
--------+------+---------------+--------------
 public | emp  | foreign table | enterprisedb
(1 row)

postgres=# select * from emp limit 3;
 empno | ename |   job    | mgr  |      hiredate      |   sal   |  comm  | deptno
-------+-------+----------+------+--------------------+---------+--------+--------
  7369 | SIMHT | CLERK    | 7902 | 17-DEC-80 00:00:00 |  800.00 |        |     20
  7499 | EALNL | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 |     30
  7521 | DARW  | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 |     30
(3 rows)

postgres=#

基本上這個測試比較沒問題，因為 Redaction Policy 跟表格都在同一處；此外，這時候就算是此端的 superuser 也看不到正常資料：正常內容只能用 superuser 連線進去彼端 edb Database 才看得到。

測試二：

因為要在「外部表」上面遮碼，因此遮碼函數也得建立在 postgres Database 裡面，這邊換一個函數名字以免看文章的各位混淆

postgres=# CREATE FUNCTION fdw_redaction_shuffle(inptstr TEXT)
RETURNS VARCHAR
AS $$
import string_utils
return string_utils.shuffle(inptstr)

$$ LANGUAGE plpython3u 
   PARALLEL SAFE
   SECURITY DEFINER
   RETURNS NULL ON NULL INPUT;
CREATE FUNCTION
postgres=#

在 PostgreSQL 裡面，帳號的預設行為是跨內部不同 database 都可以存取的。因此待會有需要的話，一樣使用上面的 aaa 審視 postgres Database 裡面外部表上的遮碼就好。

這邊準備的外部表，換成利用 superuser 連過去，就不會被剛剛的遮碼影響

postgres=# create server rawdata foreign data wrapper postgres_fdw OPTIONS (host 'localhost', port '5444', dbname 'edb');
CREATE SERVER
postgres=# create user MAPPING FOR PUBLIC SERVER rawdata OPTIONS (user 'enterprisedb');
CREATE USER MAPPING
postgres=# create schema rawedb;
CREATE SCHEMA
postgres=# import FOREIGN SCHEMA public limit to (emp) from server rawdata into rawedb;
IMPORT FOREIGN SCHEMA
postgres=#

然後試試在外部表上建立 Redaction Policy

postgres=# CREATE REDACTION POLICY fdw_emp_name_shuffle ON rawedb.emp FOR (TRUE)
 ADD COLUMN ename USING fdw_redaction_shuffle(ename);
ERROR:  "emp" is not a table
postgres=#

結果 Redaction Policy 不能直接套用在外部表上面。
有這個結果的原因，是因為 Foreign Table ≠ Base Table 的緣故，目前的 Redaction Policy 功能不能套用在 Base Table 之外的物件～

其實，目前的遮碼功能只能套用在「普通表格」（Base Table）上，外部表或是 View 這類物件都不能用。下面在 edb 裡面對 View 建立也會出錯

edb=# CREATE REDACTION POLICY vw_emp_name_shuffle ON salesemp FOR (session_user <> NULL)
 ADD COLUMN ename USING redaction_shuffle(ename);
ERROR:  "salesemp" is not a table
edb=#

雖然遮碼功能不可以「直接套用」在 View 或是外部表上，但只要對普通表格遮碼，都有辦法在 View 或是外部表具備一樣的效果，因此這樣的狀況都很夠用了～

老實說，上面用 shuffle 只是練習，其實最好還是要搭配字串替換，不然多執行幾次，說不定就猜出原本內容了～這邊希望展示遮碼功能不只是對資料打叉叉而已～

最後，如果是原生的 PGSQL 要怎辦～其實可以透過 View ＋ Privilege Control 達成的，只是企業版要當企業版，會多提供一些比較方便維運的功能，若用 DIY 會讓 DB 內容物件變比較多，就比較需要同事間的良好溝通囉～

[UPDATE 20230825]：

仔細觀察行為，可以得知 EDB 遮碼功能是直接強制套用遮碼函數到參加欄位上。不過這樣也可以推論出來，遮碼功能不能套用在需要 SQL JOIN 條件的欄位上。此外，這樣的機制可以確定是在執行計畫前面就套用上去了。

edb=# \c edb aaa
psql (15.2.0, server 15.2.0)
You are now connected to database "edb" as user "aaa".
edb=> explain select * from emp;
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on emp  (cost=0.00..4.68 rows=14 width=77)
(1 row)

edb=> explain select * from emp where ename='SMITH';
                            QUERY PLAN
--------------------------------------------------------------------------
 Seq Scan on emp  (cost=0.00..4.96 rows=1 width=77)
   Filter: ((fdw_redaction_shuffle((ename)::text))::text = 'SMITH'::text)
(2 rows)

edb=> select * from emp where ename='SMITH';
 empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)

edb=>

此外，EDB 遮碼功能不影響隔著遮碼屏風對資料作更新，不過 WHERE 條件當然得找沒有被遮住的欄位做指定條件

edb=# \c edb aaa
psql (15.2.0, server 15.2.0)
You are now connected to database "edb" as user "aaa".
edb=> explain update emp set ename='SMILETH' where empno=7369;
                        QUERY PLAN
----------------------------------------------------------
 Update on emp  (cost=0.00..1.18 rows=0 width=0)
   ->  Seq Scan on emp  (cost=0.00..1.18 rows=1 width=44)
         Filter: (empno = '7369'::numeric)
(3 rows)

edb=> update emp set ename='SMILESMITH' where empno=7369;
INFO:  Updating employee 7369
INFO:  ..Old salary: 800.00
INFO:  ..New salary: 800.00
INFO:  ..Raise     : 0.00
INFO:   User aaa updated employee(s) on 22-AUG-23
UPDATE 1
edb=>

edb=# \c
psql (15.2.0, server 15.2.0)
You are now connected to database "edb" as user "enterprisedb".
edb=# select * from emp where empno=7369;
 empno |   ename    |  job  | mgr  |      hiredate      |  sal   | comm | deptno
-------+------------+-------+------+--------------------+--------+------+--------
  7369 | SMILESMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 |      |     20
(1 row)

edb=#

參考資料

Postgres Rocks! Powered by EDB - Native Data Redaction Capability in EDB Postgres A... - Postgres Rocks - 1979

Creating a Data Redaction Capability to Meet GDPR Requirements Using EDB Postgres | EnterpriseDB

shuffle string in python - Stack Overflow

plpython_examples/simple_sql_example_notebook.ipynb at master · ihuston/plpython_examples · GitHub

Masking Data With PostgreSQL - Blog dbi services

damien clochard / postgresql_anonymizer · GitLab

看一下其他商用資料庫的方案

Use Dynamic Data Masking to obfuscate your sensitive data - SQL Server Blog

Oracle Data Redaction数据加密-博客-云栖社区-阿里云

Data Masking in MySQL | MySQL Server Blog