本文共 7200 字，大约阅读时间需要 24 分钟。
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、内容串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);
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、透明加密
2、pg_trgm
3、pg_scws
4、pg_jieba
5、zhparser
6、
7、PostgreSQL C函数例子：
8、
转载地址：http://gijta.baihongyu.com/