PGSQL內建加密模組—pgcrypto 練習

Postgres 裡面有很豐富的資料加密函數可以使用，叫做 pgcrypto 模組，可以用來實作資料加密的規劃。
pgcrypto 模組支援資料單向的 hash 處理，摻鹽巴算 hash，加密碼算 hash，GnuPG 的金鑰整合等等功能。

這裡作一個相關的使用練習。

為了不要讓內容太白痴，下面弄了一個簡易的設計，搭配帳號權限管理，達成只讓某個帳號看得到加密結果，而另外帳號可以拿加密後的資料來解密的系統小小不負責規劃。

以下大致上是一個設計的簡要流程圖：

首先，先處理好資料庫的相關物件：外掛、帳戶＆資料。

- 外掛是 PGSQL 內建的，各個主流 Postgres Distribution（如）都有提供，使用 YUM Repo 安裝的記得裝 contrib RPM，例如 postgresql10-contrib-10.4）

- 帳號分成可以操作加密後資料的，以及可以解密的帳戶各一個

- 準備一個放加密密碼的表格（只有一筆資料..就是加密密碼）

- 資料則使用社群套件 pagila 的範例資料（類似 MySQL 的 sakila 範例資料庫）

postgres=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION
postgres=# CREATE USER u_masked;
CREATE USER
postgres=# CREATE USER u_unmasked;
CREATE USER
postgres=# CREATE TABLE mask_pwd (id INT UNIQUE, pwd BYTEA);
CREATE TABLE
postgres=# INSERT INTO mask_pwd VALUES (1, 'password'::BYTEA);
INSERT 0 1
postgres=# ALTER TABLE mask_pwd OWNER TO u_unmasked;
ALTER TABLE
postgres=# \i ~/pagila-schema.sql
postgres=# \i ~/pagila-data.sql
postgres=# \i ~/pagila-fulltext.sql

欄位加密：有幾種演算法組合，這裡隨便選一個（aes）

CREATE OR REPLACE FUNCTION col_encryp(tobeencrypt TEXT) 
RETURNS BYTEA
AS $$
  SELECT encrypt(tobeencrypt::bytea, pwd, 'aes') from mask_pwd where id=1;
$$ LANGUAGE sql 
STRICT 
IMMUTABLE 
SECURITY DEFINER 
PARALLEL SAFE;

上面有對這個函數宣告幾個修飾詞：
STRICT — 告知這個函數一定要吃參數，不然就回傳 NULL
IMMUTABLE — 是用來告訴資料庫，這個函數可以作一些查詢優化
SECURITY DEFINER — 為函數執行時代表的帳戶權限
PARALLEL SAFE — 說明這個函數在 SQL 的執行計畫內可以開多執行緒處理

欄位解密：要配對上面的加密，不然加密結果會長的不一樣～

CREATE OR REPLACE FUNCTION col_decryp(tobedecrypt BYTEA) 
RETURNS TEXT 
AS $$ 
  SELECT convert_from(decrypt(tobedecrypt, pwd, 'aes'), 'utf8') from mask_pwd where id=1;
$$ LANGUAGE sql 
STRICT 
IMMUTABLE 
SECURITY DEFINER 
PARALLEL SAFE;

這裡簡單起見，把函數放到 public Schema 裡面，因此需要先從「所有帳戶 (public)」奪回執行權（謎之音：一開始建立新的 Schema 來放函數不就好了～～）。注意這邊避免兩個帳號查看 Stored Procedure 原始碼～

postgres=# REVOKE EXECUTE ON FUNCTION col_encryp(TEXT) FROM PUBLIC;
REVOKE
postgres=# REVOKE EXECUTE ON FUNCTION col_decryp(BYTEA) FROM PUBLIC;
REVOKE
postgres=# REVOKE EXECUTE ON FUNCTION pg_get_functiondef(oid) FROM PUBLIC;
REVOKE
postgres=# REVOKE ALL ON TABLE mask_pwd FROM PUBLIC;
REVOKE
postgres=# REVOKE SELECT ON TABLE pg_proc FROM public;
REVOKE
postgres=# GRANT EXECUTE ON FUNCTION col_encryp(TEXT) TO u_masked;
GRANT
postgres=# GRANT EXECUTE ON FUNCTION col_decryp(BYTEA) TO u_unmasked;
GRANT
postgres=# 

首先先看一下權限控制有沒有起作用

postgres=# \c postgres u_masked
You are now connected to database "postgres" as user "u_masked".
postgres=> select col_encryp('hello'::text);
             col_encryp
------------------------------------
 \xf368140ac9de6eedf79e284ce0b5a970
(1 row)

postgres=> select col_decryp(col_encryp('hello'::text));
ERROR:  permission denied for function col_decryp
postgres=> 
postgres=> select * from mask_pwd;
ERROR:  permission denied for relation mask_pwd
postgres=> insert into mask_pwd values ('bb'::bytea);
ERROR:  permission denied for relation mask_pwd
postgres=>

postgres=# \c postgres u_unmasked
You are now connected to database "postgres" as user "u_unmasked".
postgres=> select col_encryp('hello'::text);
ERROR:  permission denied for function col_encryp
postgres=> -- 從上面複製結果下來
postgres=> select col_decryp('\xf368140ac9de6eedf79e284ce0b5a970'::bytea);
 col_decryp
------------
 hello
(1 row)

postgres=> 
postgres=> select prosrc from pg_proc where proname ='col_encryp';
ERROR:  permission denied for relation pg_proc
postgres=>
postgres=> select * from mask_pwd;
        pwd
--------------------
 \x70617373776f7264
(1 row)

postgres=> select convert_from(pwd,'utf8') from mask_pwd;
 convert_from
--------------
 password
(1 row)

postgres=> 

確認正常～兩個帳號都有計畫要的效果

再來就是對表格作相關的 View 了：這裡挑選的表格是範例資料裡面的 address 表格，挑選地址與電話等欄位作加密

postgres=# CREATE VIEW m_address AS 
  select address_id, col_encryp(address) as m_address, col_encryp(address2) as m_address2, district, city_id, postal_code, col_encryp(phone) as m_phone, last_update
  from address;
CREATE VIEW
postgres=# 

Note：這裡也可以考慮 Materialized View～

若被加密的欄位是 JOIN 條件呢？若想要有一點點加速的話，可以考慮增加 Function Index 到原表格上

CREATE INDEX ON address ( col_encryp(phone) );

現在可以來試試看查詢了。

先 GRANT 相關的權限給相應的帳戶：
要避免 u_mask 直接查詢表格，同時給予該帳戶查詢加料過的 View 的權限
並且要讓 u_unmask 可以查表格，這樣就可以拿加料過的內容來反查原始資料

postgres=# GRANT SELECT ON m_address TO u_masked ;
GRANT
postgres=# GRANT SELECT ON address TO u_unmasked ;
GRANT
postgres=# 

接著分成加 function idx 前跟加 function idx 後，搭配權限的控制進行查詢看看。這裡就看看執行計畫的差異就好，願意照著操作的可以直接查詢了解～
加 function idx 前

postgres=> \c
You are now connected to database "postgres" as user "u_unmasked".
postgres=> explain select * from m_address where m_phone ='\xc6b4234e1d0709c945113e4f2a9607f7';
                                    QUERY PLAN
-------------------------------------------------------------------------------------
 Seq Scan on address  (cost=0.00..168.54 rows=3 width=124)
   Filter: (col_encryp((phone)::text) = '\xc6b4234e1d0709c945113e4f2a9607f7'::bytea)
(2 rows)

postgres=> 

加 function idx 後

postgres=> \c
You are now connected to database "postgres" as user "u_unmasked".
postgres=> explain select * from m_address where m_phone ='\xc6b4234e1d0709c945113e4f2a9607f7';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on address  (cost=4.30..13.82 rows=3 width=124)
   Recheck Cond: (col_encryp((phone)::text) = '\xc6b4234e1d0709c945113e4f2a9607f7'::bytea)
   ->  Bitmap Index Scan on address_col_encryp_idx  (cost=0.00..4.30 rows=3 width=0)
         Index Cond: (col_encryp((phone)::text) = '\xc6b4234e1d0709c945113e4f2a9607f7'::bytea)
(4 rows)

postgres=> 

執行成功。如此就達成了這裡設定的小題目了。

以上就是本次的練習～～

最後，上面練習範例的檢討

1. 需要請各位了解，這個只是一個練習方案，資安強度是否能滿足實際的應用，還是得取決於用途為何（這邊的密碼很陽春）。不過可以確認的是，這個小練習應該不能符合「最嚴格」的資安要求～
2. 這裡說到底，只有作 DB 內的權限管理，本身只是資料庫端的措施，一個實際的資安方案，還要搭配中間傳輸以及客戶端系統上面的資安策略，總和起來才會是完整的資安規劃。
3. 這份練習中，為了限制兩個帳號查看 Stored Procedure 原始碼，建議額外禁止帳號呼叫相關函數或是系統表。在 EDB Postgres 企業版中，有一個對 Sotred Procedure 原始內容作加密的功能，叫做 EDB*Wrap 的內建功能，可以省去這段負擔
4. 由於每次 View 的呼叫，都會花費一些計算資源來處理，所以會「慢」～ 如果要不慢一些，就是換成直接在表格存放加密結果，需要時再用解密函數看資料。
5. 這個練習中被加密的欄位都不是 SQL JOIN 條件的資訊，影響比較小；若是打算加密的內容偏偏是 Primary Key 的話 . . . 基本上還是可以用，只是作為 Join Key 可能速度有別
6. 這些加密應用有一個超級明顯的特性：只能用等號作查詢條件的過濾，如果原本資料是可以比大小的數值，加密後就沒有辦法這麼作了～
7. 這裡用的是 View，其實也可以考慮用 Materialized View，不過在 PGSQL 裡面沒有 CDC 的特性，每次都需要重刷～

補充：
最近發現使用這些加密函數，有必要知道相關的特性，所以簡單整理一個功能差異的表格，比較好看到需要的函數～如果有錯誤，還請不吝指教

pgcrypto分類	單向 (One-way hash)	可解碼
沒加鹽（結果固定）	無密碼 md5(cleartext) digest(cleartext,'algorithm') 有密碼 hmac(cleartext,pwd,'algorithm')	有密碼 encrypt(cleartext,pwd,'algorithm') / decrypt(ciphertext,pwd,'algorithm')
有加鹽（結果不固定）	crypt(cleartext,gen_salt('algorithm'))	pgp_sym_encrypt() / pgp_sym_decrypt()

參考資料


惡補一點點加密的概念～～偷偷看一下其他資料庫

資料安全與簡單加密演算法見面會 系列文章列表 - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天

加密概念和PKI基础知识简述 - CSDN博客

SQL Server 安全篇——SQL Server加密（1）——加密概念 - CSDN博客
SHA家族 - 維基百科，自由的百科全書：這邊可以看到 hash 演算法輸出的資料長度是固定的，可以看到跟這邊用的密碼加解密不一樣
An Introduction to Hashing - Standard Deviations

Postgres 保全的基本檢查或設定

select * from depesz; » Blog Archive » securing your postgresql database

Total security in a PostgreSQL database

Can I do it with PostgreSQL? – 5 – Generating DDL commands - Blog dbi services

Using search_path and views to hide columns for reporting with Postgres

pgcrypto 的參考資料

固若金汤 - PostgreSQL pgcrypto加密插件-博客-云栖社区-阿里云

blog/20171012_01.md at master · digoal/blog - PostgreSQL 和 Greenplum pgcrypto 加解密bytea处理差异(convert, convert_from)

blog/20171127_02.md at master · digoal/blog - Greenplum , PostgreSQL pgcrypto 加密算法、mode、PAD的选择 - 与Oracle, MySQL的差异(安全性差异)

blog/20180226_03.md at master · digoal/blog - PostgreSQL 对称加密、非对称加密用法介绍

Encrypting data with pgcrypto - Postgres OnLine Journal

postgresql encrypt / decrypt - Stack Overflow

PostgreSQL 會員註冊系統的加密範例 @ 彥霖 實驗筆記 :: 痞客邦 ::

添加 index 相關資訊
Large Text Indexes in Postgres – Andrew Kane – Medium
how to speed up index on bytea, text, etc. | Gj's SQL blog

用 Python 作漂亮的去識別處理

A simple way to anonymize data with Python and Pandas - DEV Community 👩‍💻👨‍💻