是否有在启用了 RLS 策略的表中搜索文本列的解决方法

问：

令我惊讶的是，我注意到简单的文本搜索 API 不利用作为 RLS 策略一部分的表的索引。例如，mytable 有一个策略，我正在使用一个名为 text_vector 的列来搜索：

SELECT inst_id, text_vector, ts_rank(text_vector, q) AS rank, q
  FROM mytable, plainto_tsquery('apple & orange') q
 WHERE text_vector @@ q

我猜，这与它们不防漏有关。是否有任何一种在不更改系统表的情况下的解决方法（例如，我在 RDS 中无法做到这一点）？

为了重现，我执行了以下操作：

CREATE TABLE mytable (inst_id INTEGER, text_content TEXT);

ALTER TABLE mytable 
ADD COLUMN text_vector TSVECTOR 
GENERATED ALWAYS AS (to_tsvector('english', text_content)) STORED;

CREATE INDEX idx_text_vector ON mytable USING GIN (text_vector);

INSERT INTO mytable (inst_id, text_content) VALUES (1, 'An apple a day is good for you.');
INSERT INTO mytable (inst_id, text_content) VALUES (2, 'My favorite color is orange.');
INSERT INTO mytable (inst_id, text_content) VALUES (3, 'You can have an orange or apple, not both.');

（又插入了 10K 行......

EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT inst_id, text_vector, ts_rank(text_vector, q) AS rank, q
FROM mytable, plainto_tsquery('apple & orange') q
WHERE text_vector @@ q

Nested Loop  (cost=17.03..264.54 rows=100 width=159) (actual time=0.037..0.038 rows=1 loops=1)
  Output: mytable.inst_id, mytable.text_vector, ts_rank(mytable.text_vector, q.q), q.q
  Buffers: shared hit=8
  ->  Function Scan on pg_catalog.plainto_tsquery q  (cost=0.25..0.26 rows=1 width=32) (actual time=0.019..0.020 rows=1 loops=1)
        Output: q.q
        Function Call: plainto_tsquery('apple & orange'::text)
  ->  Bitmap Heap Scan on myschema.mytable  (cost=16.78..263.03 rows=100 width=123) (actual time=0.013..0.013 rows=1 loops=1)
        Output: mytable.inst_id, mytable.text_content, mytable.text_vector
        Recheck Cond: (mytable.text_vector @@ q.q)
        Heap Blocks: exact=1
        Buffers: shared hit=8
        ->  Bitmap Index Scan on idx_text_vector  (cost=0.00..16.75 rows=100 width=0) (actual time=0.010..0.010 rows=1 loops=1)
              Index Cond: (mytable.text_vector @@ q.q)
              Buffers: shared hit=7
Planning:
  Buffers: shared hit=1
Planning Time: 0.065 ms
Execution Time: 0.065 ms

（在mytable上创建了一个调用函数has_access（）的策略）

CREATE OR REPLACE FUNCTION has_access(p_inst_id BIGINT) RETURNS BOOLEAN
LANGUAGE 'plpgsql' SECURITY DEFINER
AS $has_access$
DECLARE
  v_has_access BOOLEAN;
BEGIN
  v_has_access := (SELECT TRUE WHERE p_inst_id < 4);
  RETURN v_has_access;
END;
$has_access$;

CREATE POLICY pol_has_access 
ON mytable 
FOR SELECT TO PUBLIC USING (has_access(inst_id));
ALTER TABLE mytable ENABLE ROW LEVEL SECURITY;

（以非所有者身份登录系统，在 mytable 上具有 SELECT 权限）

EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT inst_id, text_vector, ts_rank(text_vector, q) AS rank, q
FROM mytable, plainto_tsquery('apple & orange') q
WHERE text_vector @@ q

Nested Loop  (cost=0.25..5816.14 rows=33 width=159) (actual time=0.053..39.206 rows=1 loops=1)
  Output: mytable.inst_id, mytable.text_vector, ts_rank(mytable.text_vector, q.q), q.q
  Join Filter: (mytable.text_vector @@ q.q)
  Rows Removed by Join Filter: 2
  Buffers: shared hit=479
  ->  Function Scan on pg_catalog.plainto_tsquery q  (cost=0.25..0.26 rows=1 width=32) (actual time=0.018..0.020 rows=1 loops=1)
        Output: q.q
        Function Call: plainto_tsquery('apple & orange'::text)
  ->  Seq Scan on myschema.mytable  (cost=0.00..5732.41 rows=6671 width=123) (actual time=0.026..39.181 rows=3 loops=1)
        Output: mytable.inst_id, mytable.text_content, mytable.text_vector
        Filter: has_access((mytable.inst_id)::bigint)
        Rows Removed by Filter: 20010
        Buffers: shared hit=479
Planning Time: 0.048 ms
Execution Time: 39.220 ms

请注意，索引被用作所有者，但当我以非所有者身份执行相同的查询时，RLS 策略会阻止使用该索引。