PostgreSQL 限制谁可以执行解释计划

Question

如果你想要血腥的细节继续阅读，否则我要问的是你是否可以限制用户能够在他们的 SQL 语句上运行 EXPLAIN 命令，像 GRANT EXPLAIN TO billy 这样的东西会很好，但不是似乎存在。

我的目标是加密列中的数据，为加密列编制索引，因为它可用于搜索，并使所有最终用户都隐藏加密 key (可以让系统管理员或 DBA 看到它)。在 PostgreSQL 中有哪些方法可以做到这一点？如果没有办法完成所有这些，您有什么办法可以尝试实现这些目标？下面列出的我当前的方法几乎可以让我到达那里，但是当在查询上运行解释计划时，加密 key 就会暴露出来。这就是为什么我想知道您是否可以限制能够使用解释的用户的访问。

下面是一个小设置的演练，以演示我在说什么。假设我在数据库表的列中加密了一些数据，将通过 gui 前端屏幕或通过 pg_admin 即席查询搜索此列。

CREATE TABLE test(id serial,my_data TEXT);

--Fill table with enough data to need an index.
DO $$

DECLARE counter INTEGER := 0;
BEGIN
    WHILE(counter < 1000)
    LOOP
        EXECUTE 'INSERT INTO test(my_data)
                SELECT pgp_sym_encrypt(''avalue' || CAST(counter AS TEXT) || '''' || ', ''apasswordwithsomeentropy'',''compress-algo=1, cipher-algo=aes256'');';
        counter := counter + 1;
    END LOOP;
END$$;

ANALYZE test;

CREATE INDEX index1
  ON test
  USING btree
  (my_data);

这个 select 仍然需要进行全表扫描，我想避免这种情况。

SELECT id,
       my_data,
       pgp_sym_decrypt(cast(my_data as bytea),'apasswordwithsomeentropy')
FROM test
WHERE pgp_sym_decrypt(cast(my_data as bytea),'apasswordwithsomeentropy') = 'avalue114';

"Seq Scan on test  (cost=0.00..665.00 rows=61 width=246)"
"  Filter: (pgp_sym_decrypt((my_data)::bytea, 'apasswordwithsomeentropy'::text) = 'avalue114'::text)"

如果我使用函数索引来加快查询速度呢？我使用 get_password 函数不使用其中的密码硬代码创建索引。请记住，我不希望用户查看索引定义和密码。假设用户无法从密码中选择或执行 get_password()，只有具有额外权限的单个帐户可以。并且 get_password() 函数仅在函数索引中使用。所以我的理解是用户不需要该功能的执行权限？

CREATE TABLE password
(
  password_id serial NOT NULL,
  password_value text
);

INSERT INTO password(password_value)
SELECT 'apasswordwithsomeentropy';
from get_password();

CREATE FUNCTION get_password() RETURNS TEXT
    AS 'select password_value 
   from password 
  where password_id = 1'
    LANGUAGE SQL
    IMMUTABLE;

CREATE INDEX index2 ON test (pgp_sym_decrypt(cast(my_data as bytea),get_password()));

现在，当我运行 select 时，数据库使用 index2，并且我可以按照自己的意愿快速又好地返回结果。问题是当我对选择查询执行解释计划时，index2 在平面文本中显示密码。

SELECT id,
       my_data,
       pgp_sym_decrypt(cast(my_data as bytea),'apasswordwithsomeentropy')
FROM test
WHERE pgp_sym_decrypt(cast(my_data as bytea),'apasswordwithsomeentropy') = 'avalue114';


"Bitmap Heap Scan on test  (cost=4.73..171.49 rows=61 width=246)"
"  Recheck Cond: (pgp_sym_decrypt((my_data)::bytea, 'apasswordwithsomeentropy'::text) = 'avalue114'::text)"
"  ->  Bitmap Index Scan on index2  (cost=0.00..4.72 rows=61 width=0)"
"        Index Cond: (pgp_sym_decrypt((my_data)::bytea, 'apasswordwithsomeentropy'::text) = 'avalue114'::text)"

我唯一能想到的就是为数据库创建 Web 服务，以防止用户直接与数据库交互。但由于该应用程序在内部，如果一些用户仍然可以偶尔使用 pg_admin，但看不到解释计划等所有内容，那就太好了。这些用户不会知道解释计划是什么，也永远不会使用它们。

我一直回来创建一个网络服务，将他们需要运行的所有查询包装在通过网络服务调用的函数中，但这需要额外的时间才能在正式的开发环境中发布并消除广告 - hoc 查询方法并添加另一个层来维护。有任何想法吗？谢谢

Answer 1

在旁注中你可能想改变

SELECT id,
       my_data,
       pgp_sym_decrypt(cast(my_data as bytea),'apasswordwithsomeentropy')
FROM test
WHERE pgp_sym_decrypt(cast(my_data as bytea),'apasswordwithsomeentropy') = 'avalue114';

进入

SELECT id,
       my_data,
       pgp_sym_decrypt(cast(my_data as bytea),'apasswordwithsomeentropy')
FROM test
WHERE pgp_sym_encrypt(cast('avalue114' as bytea),'apasswordwithsomeentropy') = my_data;

这样 postgres 就不需要解密每个 my_data 来进行比较。如果 pgp_sym_encrypt 是确定的并且可以被缓存(不确定 postgres 是否可以处理这个)，你的目标数据被加密一次并且只有匹配的列才会被解密。

我们已经更改了 where 条件，因此计划可能会更改，您可以尝试发送新计划吗？