主页 / dba / 问题 / 316565
Accepted
Pascal Ognibene
Asked: 2022-09-08 07:34:04 +0800 CST

Postgresql：在 WHERE 子句中使用时仅解密一次列

  • 772

我有以下请求，使用 pg_trm 和 gin 索引：

SELECT
        email,
        first_name,
        last_name
         
    FROM   external_patient
    WHERE  PGP_SYM_DECRYPT(cat_name::bytea, 'SUPER_AES_KEY') ILIKE $2
    OR     PGP_SYM_DECRYPT(cat_name::bytea, 'SUPER_AES_KEY') %     $1
    ORDER  BY 
           (PGP_SYM_DECRYPT(cat_name::bytea, 'SUPER_AES_KEY') ILIKE $3) DESC
          ,(PGP_SYM_DECRYPT(cat_name::bytea, 'SUPER_AES_KEY') ILIKE $2) DESC
          ,(PGP_SYM_DECRYPT(cat_name::bytea, 'SUPER_AES_KEY') <->   $1)
          ,PGP_SYM_DECRYPT(cat_name::bytea, 'SUPER_AES_KEY')
    LIMIT  30

列 cat_name 已加密，因此必须对其进行解密才能运行请求。但是，我想确保尽可能只解密一次（因为请求是 3ms 没有加密字段，3500 ms 有加密......

是否有特定的语法来确保该列只被解密一次？谢谢您的帮助。

postgresql encryption

2 个回答

  1. Best Answer

    您可以将解密的列添加到 SELECT 并将其包装到派生表中：

    select email, first_name, last_name
from (
    SELECT email,
       first_name,
       last_name,
       PGP_SYM_DECRYPT(cat_name::bytea, 'SUPER_AES_KEY')   as cat_name_decrypted
    FROM   external_patient
) t
WHERE cat_name_decrypted ILIKE $2
   OR cat_name_decrypted %     $1
ORDER  BY 
       (cat_name_decrypted ILIKE $3) DESC
      ,(cat_name_decrypted ILIKE $2) DESC
      ,(cat_name_decrypted <->   $1)
      ,cat_name_decrypted
LIMIT  30
    • 1

  2. 我会添加一个带有搜索哈希的新列（或 3 个）。它占用一些空间，但您只能解密一次。

    ALTER TABLE external_patient
ADD COLUMN cat_name_hash TEXT;

UPDATE external_patient
SET cat_name_hash = SHA256(LOWER(email || first_name || last_name));

SELECT
   email,
   first_name,
   last_name, 
   PGP_SYM_DECRYPT(cat_name::bytea, 'SUPER_AES_KEY') as cat_name_decrypted
WHERE cat_name_hash = SHA256(LOWER($1));

    这里SHA256()和LOWER()在 where 子句中，但只计算一次。$1应该是搜索三元组。

    • -1

相关问题