sql - 您如何对编码或加密数据进行 PostgreSQL 全文搜索？

Question

出于各种在这里无关紧要的原因，我们在 PostgreSQL 中以加密或 base64 编码格式存储文本 block 。但是，我们希望能够使用 PostgreSQL 的全文搜索来查找和返回未加密/解码形式的与搜索查询匹配的数据。

如何实现这一目标？我看到其他帖子提到了在将数据发送到数据库之前构建 tsvector 值的能力，但我希望在 Postgres 端有一些可用的东西(至少对于 base64 文本)。

Answer 1

加密值

对于加密值你不能。即使您创建了 tsvector 客户端，tsvector 也会包含一种加密文本形式，因此大多数应用程序都无法接受它。观察:

regress=> SELECT to_tsvector('my secret password is CandyStrip3r');
               to_tsvector                
------------------------------------------
 'candystrip3r':5 'password':3 'secret':2
(1 row)

...哎呀。如果您在客户端创建该值而不是使用 to_tsvector 并不重要，它仍然会以明文形式显示您的密码。您可以加密 tsvector，但是您不能将它用于全文搜索。

当然，给定加密值:

CREATE EXTENSION pgcrypto;

regress=> SELECT encrypt( convert_to('my s3kritPassw1rd','utf-8'), '\xdeadbeef', 'aes');
                              encrypt                               
--------------------------------------------------------------------
 \x10441717bfc843677d2b76ac357a55ac5566ffe737105332552f98c2338480ff
(1 row)

你可以(但不应该)做这样的事情:

regress=> SELECT to_tsvector( convert_from(decrypt('\x10441717bfc843677d2b76ac357a55ac5566ffe737105332552f98c2338480ff', '\xdeadbeef', 'aes'), 'utf-8') );
    to_tsvector     
--------------------
 's3kritpassw1rd':2
(1 row)

...但是如果在代码显示框中向右滚动后问题没有立即显现出来，那么您真的应该让其他人为您进行安全设计；-)

关于如何在不解密的情况下对加密值执行操作的方法进行了大量研究，例如将两个加密数字加在一起以产生使用相同 key 加密的结果，因此执行添加的过程不需要能够解密输入以获得输出。有可能其中一些可以应用于 fts - 但它超出了我在该领域的专业知识水平，而且可能效率低得可怕和/或密码学无论如何都很弱。

Base64 编码值

对于 base64，您可以在将 base64 送入 to_tsvector 之前对其进行解码。因为 decode 返回一个 bytea 并且您知道编码数据是文本，所以您需要使用 convert_from 来解码 bytea转化为数据库编码中的text，eg:

regress=> SELECT encode(convert_to('some text to search','utf-8'), 'base64');
            encode            
------------------------------
 c29tZSB0ZXh0IHRvIHNlYXJjaA==
(1 row)

regress=> SELECT to_tsvector(convert_from( decode('c29tZSB0ZXh0IHRvIHNlYXJjaA==', 'base64'), getdatabaseencoding() ));
     to_tsvector     
---------------------
 'search':4 'text':2
(1 row)

在这种情况下，我使用数据库编码作为 convert_from 的输入，但您需要确保使用底层 base64 编码文本的编码中。您的应用程序负责获得此权利。我建议要么将编码存储在第二列中，要么确保您的应用程序始终在应用 base64 编码之前将文本编码为 utf-8。