EDB 11 遮碼功能小筆記—用群組控制遮碼

之前紀錄 EDB 11 的遮碼功能的小小應用，主要是使用特別一點的函數來處理。這邊再紀錄一下，怎麼樣對多個帳號遮碼

因為遮碼的示範不是重點，這邊沿用 EDB 關於 Data Redaction 教學文章的範例就好。
在範例中，原本使用的遮碼設定如下：

CREATE REDACTION POLICY emp_data_protect 
ON employees FOR (session_user <> 'privilegeduser')
ADD COLUMN ssn USING redact_ssn(ssn),
ADD COLUMN salary USING redact_salary(salary);

如紅色部份的設定，只有除了 Superuser 以及這邊指定的 privilegeduser 之外，其他帳號都不能查看原本內容。

如果要透過群組控制呢？（例如，非群組成員跟 Superuser 之外的帳號都不能偷看～）
雖然要檢查群組成員是 OK 的。

edb=# select session_user::regrole = any (grolist::regrole[]) 
 from pg_group 
 where groname = 'grp';
 ?column? 
----------
 f
(1 row)

edb=# 

但這樣的 SQL 沒辦法塞進去 Redaction Policy 裡面。

edb=# CREATE REDACTION POLICY emp_data_protect ON employees 
FOR (select session_user::regrole = any (grolist::regrole[]) from pg_group where groname = 'grp')
ADD COLUMN ssn USING redact_ssn(ssn);
ERROR:  syntax error at or near "select"
LINE 1: ...DACTION POLICY emp_data_protect ON employees FOR (select ses...
                                                             ^
edb=# 

因此要塞一個表達式才行，不能塞一句 SQL。。。
所幸 Postgres 提供許多系統函數可以改寫上述的檢查方式。
這邊使用的函數是檢查權限的函數之一 pg_has_role()。

首先，先建立一下新的測試帳號跟群組。這邊帳號 aaa 屬於群組 grp，然後有一個對照組，帳號 bbb。

edb=# create role grp;
CREATE ROLE
edb=# create user aaa;
CREATE USER
edb=# grant grp to aaa;
GRANT
edb=# create user bbb;
CREATE USER
edb=# 
edb=# \du
                                         List of roles
       Role name       |                         Attributes                         | Member of 
-----------------------+------------------------------------------------------------+-----------
 aaa                   | Profile default                                            | {grp}
 aq_administrator_role | No inheritance, Cannot login                              +| {}
                       | Profile default                                            | 
 bbb                   | Profile default                                            | {superu}
 enterprisedb          | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                       | Profile default                                            | 
 grp                   | Cannot login                                              +| {}
                       | Profile default                                            | 
 superu                | Superuser, No inheritance, Cannot login                   +| {}
                       | Profile default                                            | 

edb=# 

然後讓我們變更 Redaction Policy，調整為「非群組成員」不能看到完整內容（注意，Superuser不受本設定影響）

ALTER REDACTION POLICY emp_data_protect ON employees 
FOR ( NOT pg_has_role('grp'::name, 'member') );

修改之後，表格資訊如下：

edb=# \d enterprisedb.employees
                              Table "enterprisedb.employees"
 Column |         Type          | Collation | Nullable |             Default              
--------+-----------------------+-----------+----------+----------------------------------
 id     | integer               |           | not null | generated by default as identity
 name   | character varying(40) |           | not null | 
 ssn    | character varying(11) |           | not null | 
 salary | money                 |           |          | 
Indexes:
    "employees_pkey" PRIMARY KEY, btree (id)
Redaction Policies:
    REDACTION POLICY "emp_data_protect" FOR (NOT pg_has_role('grp'::name, 'member'::text)) ENABLED
      Number of redacted columns: 2 (Use \d+ to list them.)

edb=# 

接著進行測試。

1) 登入帳號 aaa，確認群組成員看得到資料內容

edb=# \c edb aaa
You are now connected to database "edb" as user "aaa".
edb=> select * from enterprisedb.employees;
 id |     name     |     ssn     |   salary   
----+--------------+-------------+------------
  1 | Sally Sample | 020-78-9345 | $51,234.34
  2 | Jane Doe     | 123-33-9345 | $62,500.00
  3 | Bill Foo     | 123-89-9345 | $45,350.00
(3 rows)

edb=> 

b) 再登入帳號 bbb，確認群組以外的普通帳號，只看得到遮碼結果

edb=> \c edb bbb
You are now connected to database "edb" as user "bbb".
edb=> select * from enterprisedb.employees;
 id |     name     |     ssn     | salary 
----+--------------+-------------+--------
  1 | Sally Sample | xxx-xx-9345 |  $0.00
  2 | Jane Doe     | xxx-xx-9345 |  $0.00
  3 | Bill Foo     | xxx-xx-9345 |  $0.00
(3 rows)

edb=> 

若是現在將 bbb 也加入群組，就會發現 bbb 帳號也可以看資料了

edb=# grant grp to bbb;
GRANT ROLE
edb=# \c edb bbb
You are now connected to database "edb" as user "bbb".
edb=> select * from enterprisedb.employees;
 id |     name     |     ssn     |   salary   
----+--------------+-------------+------------
  1 | Sally Sample | 020-78-9345 | $51,234.34
  2 | Jane Doe     | 123-33-9345 | $62,500.00
  3 | Bill Foo     | 123-89-9345 | $45,350.00
(3 rows)

edb=> 

基本上這樣就測試的差不多了～如果要反過來，只針對某群組的帳號作限制，把 NOT 拿掉就好

ALTER REDACTION POLICY emp_data_protect ON employees 
FOR ( pg_has_role('grp'::name, 'member') );

參考資料：
之前的筆記