閱讀266 返回首頁    go 阿裏雲 go 技術社區[雲棲]

多國語言字符串的加密、全文檢索、模煳查詢的支持

標簽

PostgreSQL , 全文檢索 , 模煳查詢 , 前後模煳 , 多國字符串 , 透明加密 , 不可逆加密 , 可逆加密 , 函數安全 , C函數

背景

PostgreSQL中的全文檢索和模煳查詢是很好實現的。

通過pg_trgm插件，可以實現模煳查詢（前、後、全模煳），近似查詢，正則表達式查詢的索引加速。

通過中文分詞插件（pg_jieba, pg_scws, zhparser），可以實現中文分詞，其他語種的分詞，參考對應的插件。

但是，如果要讓數據庫支持加密存儲，同時對加密前的數據實現全文檢索和模煳查詢，有什麼方法呢？

數據加密

加密分為幾種，用戶應該根據實際情況來選擇。

可逆加密

可逆加密，例如pgcrypto插件，支持對稱加密等方法，用戶可以將數據存為加密後的形態。問題是對加密形態的數據，索引就不好弄了。

不過還好PostgreSQL支持表達式索引，可以對解密後的表達式創建索引，從而實現加速。

但是注意，索引的表達式就變加密前的了。

因此，這種方法，為了盡量的減少風險，也需要一些技巧來避免。

不可逆加密

不可逆加密，例如MD5，這個的索引就更不好弄了，但是是可以實現的，並且安全。

透明加密

透明加密，需要改造內核，數據存儲為加密狀態，在input和output時進行自動的加解密。

可逆加密數據的全文檢索和模煳查詢實現例子

1、創建crypto插件

create extension pgcrypto;

2、用超級用戶創建一個immutable秘鑰函數

create or replace function f1(int) returns text as $$    
  select md5(md5(md5($1::text)));    
$$ language sql strict immutable;

如果用C函數，可以更好的隱藏。

也可以將秘鑰存在一個外部表(比如另一個數據庫、或者LDAP、或者其他網絡服務)中(通過PostgreSQL fdw接口可以訪問到即可)，並賦予用戶相應的查詢權限才能得到。

3、屏蔽秘鑰函數的代碼

revoke select on pg_proc from public;     
    
grant select(oid,proname,pronamespace,proowner,prolang,procost,prorows,provariadic,protransform,proisagg,proiswindow,prosecdef,proleakproof,proisstrict,proretset,provolatile,pronargs,pronargdefaults,prorettype,proargtypes,proallargtypes,proargmodes,proargnames,proargdefaults,probin,proconfig,proacl) on pg_proc to public;    
    
revoke select(prosrc) on pg_proc from public;    
    
revoke execute on function pg_get_functiondef(oid) from public;

4、設置秘鑰函數的權限

grant execute on function f1(int) to digoal;

5、創建測試表

create table test (id int , info bytea);

6、創建表達式(解密)索引

create or replace function enc(bytea,text,text) returns text as $$    
  select pgp_sym_decrypt($1,$2,$3);          
$$ language sql strict immutable;    
    
create index idx1 on test (enc(info, f1(1), 'cipher-algo=bf, compress-algo=2, compress-level=9'));

7、寫入舉例

insert into test values (1, pgp_sym_encrypt('abcdefg', f1(1), 'cipher-algo=bf, compress-algo=2, compress-level=9'));

8、查詢舉例

postgres=> select * from test;    
 id |                                                                            info                                                                                
----+------------------------------------------------------------------------------------------------------------------------------------------------------------    
  1 | \xc30d040403029b1c64cd9b1093ba62d23b019368155e5c6ff91bb144bc1c2852c9ab21971d62ea529056ff3a588229044ff54fe15292db6765c9d69ad0e6649f57b34f6e374883c87903b099    
(1 row)

索引查詢

postgres=> select * from test where enc(info, f1(1), 'cipher-algo=bf, compress-algo=2, compress-level=9') = 'abcdefg';    
 id |                                                                            info                                                                                
----+------------------------------------------------------------------------------------------------------------------------------------------------------------    
  1 | \xc30d040403029b1c64cd9b1093ba62d23b019368155e5c6ff91bb144bc1c2852c9ab21971d62ea529056ff3a588229044ff54fe15292db6765c9d69ad0e6649f57b34f6e374883c87903b099    
(1 row)    
    
postgres=> explain select * from test where enc(info, f1(1), 'cipher-algo=bf, compress-algo=2, compress-level=9') = 'abcdefg';    
                                                                             QUERY PLAN                                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------    
 Bitmap Heap Scan on test  (cost=1.40..6.78 rows=6 width=36)    
   Recheck Cond: (pgp_sym_decrypt(info, '40f5888b67c748df7efba008e7c2f9d2'::text, 'cipher-algo=bf, compress-algo=2, compress-level=9'::text) = 'abcdefg'::text)    
   ->  Bitmap Index Scan on idx1  (cost=0.00..1.40 rows=6 width=0)    
         Index Cond: (pgp_sym_decrypt(info, '40f5888b67c748df7efba008e7c2f9d2'::text, 'cipher-algo=bf, compress-algo=2, compress-level=9'::text) = 'abcdefg'::text)    
(4 rows)

9、支持全文檢索、模煳查詢

針對表達式 enc(info, f1(1), 'cipher-algo=bf, compress-algo=2, compress-level=9') 創建對應的索引，即可實現全文檢索和模煳查詢。

不可逆加密數據的全文檢索和模煳查詢實現例子

不可逆加密碼實現全文檢索和模煳查詢，我們需要對字符串TOKEN化，然後對TOKEN進行不可逆加密，存成數組。

查詢時，使用查詢串進行TOKEN話，對TOKEN進行不可逆加密，然後進行數組的包含或相交的索引檢索，加速查詢。

同時實現最高安全。

例子表

create table tbl(    
  id int,     
  info bytea,         -- 原始字符串加密存儲，使用可逆加密(秘鑰存儲在客戶端)，不建立索引。    
  info_arr text[]     -- 客戶端解密後，TOKEN化，然後使用不可逆加密，得到加密後的TOKEN數組。    
);

1、寫入

1、內容串TOKEN化(全文檢索)

使用對應語言的全文檢索插件，對字符串進行全文檢索TOKEN化。

得到單字以及詞組的TOKEN(tsvector)。然後將tsvector轉換為array。

tsvector_to_array(tsvector) 得到 text[]

2、內容串TOKEN化(雙字TOKEN)

create or replace function two_token(text) returns text[] as $$    
declare    
  res text[] := '{}'::text[];    
  len int := length($1);    
begin    
  if len<=1 then     
    return array[$1];     
  end if;     
  for i in 1..len-1 loop    
    res := array_append(res, substring($1, i, 2));    
  end loop;    
  return res;    
end;    
$$ language plpgsql strict immutable;    

postgres=> select two_token('abcde');    
   two_token       
---------------    
 {ab,bc,cd,de}    
(1 row)

3、內容串TOKEN化(單字TOKEN)

使用regexp_split_to_array得到單字數組

postgres=# select regexp_split_to_array('abcde','');
 regexp_split_to_array 
-----------------------
 {a,b,c,d,e}
(1 row)

4、加密TOKEN

create or replace function md5_token(text[]) returns text[] as $$    
declare     
  res text[] := '{}'::text[];     
  i text;    
begin    
  foreach i in array $1 loop    
    res := array_append(res, md5(i));    
  end loop;    
  return res;    
end;    
$$ language plpgsql strict immutable;    

postgres=> select md5_token(two_token('abcde'));    
                                                               md5_token                                                                   
---------------------------------------------------------------------------------------------------------------------------------------    
 {187ef4436122d1cc2f40dc2b92f0eba0,5360af35bde9ebd8f01f492dc059593c,6865aeb3a9ed28f9a79ec454b259e5d0,5f02f0889301fd7be1ac972c11bf3e7d}    
(1 row)

5、存儲加密後的數組

insert into tbl values (1, 客戶端加密的bytea, 加密後的TEXT數組);

6、數組GIN索引

create index idx on tbl using gin (info_arr);

2、查詢

1、查詢串TOKEN化，加密

postgres=> select md5_token(two_token('abcde'));    
                                                               md5_token                                                                   
---------------------------------------------------------------------------------------------------------------------------------------    
 {187ef4436122d1cc2f40dc2b92f0eba0,5360af35bde9ebd8f01f492dc059593c,6865aeb3a9ed28f9a79ec454b259e5d0,5f02f0889301fd7be1ac972c11bf3e7d}    
(1 row)

2、一級查詢過濾

select * from tbl where info_arr @> md5_token(two_token('abcde'));    
    
或    
    
select * from tbl where info_arr && md5_token(two_token('abcde'));

3、二級CPU過濾

一級過濾使用了GIN索引，二級過濾使用CPU運算。

做到了高效和安全兼具。

select * from tbl where info_arr && md5_token(two_token('abcde')) and 對稱加密解密(info,'秘鑰') ~ '正則表達式';

小結

1、透明加密當然是最好的，但是實現需要改造PG內核。

拖庫：安全。

數據庫被攻擊：安全。

2、不可逆加密，安全性很高，但是會指數級的放大存儲空間。

拖庫：安全。

數據庫被攻擊：安全。

3、可逆加密，安全性一般，前提是使用安全的秘鑰函數（不要使用明文秘鑰），即使這樣，如果數據庫用戶被攻，用戶還是能將明文拖走。

拖庫：不安全。

數據庫被攻擊：有限安全。（超級用戶、OWNER權限被獲取時，不安全。）

參考

1、透明加密

《PostgreSQL 透明加密(TDE,FDE) - 塊級加密》

2、pg_trgm

https://www.postgresql.org/docs/10/static/pgtrgm.html

《中文模煳查詢性能優化 by PostgreSQL trgm》

3、pg_scws

https://github.com/jaiminpan/pg_scws

4、pg_jieba

https://github.com/jaiminpan/pg_jieba

5、zhparser

https://github.com/amutu/zhparser

6、《PostgreSQL 函數代碼隱藏 - How to control who can see PostgreSQL function's source code》

7、PostgreSQL C函數例子：

《get PostgreSQL's next oid》

https://www.postgresql.org/docs/10/static/xfunc-c.html

8、《固若金湯 - PostgreSQL pgcrypto加密插件》

最後更新：2017-10-29 00:03:53

  上一篇：go  快速計算Distinct Count
  下一篇：go  Greenplum, PostgreSQL 數據實時訂閱的幾種方式