Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

多国语言字符串的加密、全文检索、模糊查询的支持

2 minute read

背景

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加密插件》

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...