Обсуждение: BUG #18941: PostgreSQL planner does not select a multicolumn btree_gin index under RLS

The following bug has been logged on the website:

Bug reference:      18941
Logged by:          Pepe Fagoaga
Email address:      pepe@prowler.com
PostgreSQL version: 16.3
Operating system:   Alpine Linux
Description:

We have the following `findings` table:
prowler_db=> \d findings
Partitioned table "public.findings"
     Column      |           Type           | Collation | Nullable |
Default

-----------------+--------------------------+-----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 id              | uuid                     |           | not null |
 inserted_at     | timestamp with time zone |           | not null |
 updated_at      | timestamp with time zone |           | not null |
 uid             | character varying(300)   |           | not null |
 delta           | finding_delta            |           |          |
 status          | status                   |           | not null |
 status_extended | text                     |           |          |
 severity        | severity                 |           | not null |
 impact          | severity                 |           | not null |
 impact_extended | text                     |           |          |
 raw_result      | jsonb                    |           | not null |
 check_id        | character varying(100)   |           | not null |
 check_metadata  | jsonb                    |           | not null |
 tags            | jsonb                    |           |          |
 scan_id         | uuid                     |           | not null |
 tenant_id       | uuid                     |           | not null |
 text_search     | tsvector                 |           |          |
generated always as (((setweight(to_tsvector('english'::regconfig,
COALESCE(impact_extended, ''::text)), 'A'::"char") ||
setweight(to_tsvector('english'::regconfig, COALESCE(status_extended,
''::text)), 'B'::"char")) ||
setweight(jsonb_to_tsvector('simple'::regconfig, check_metadata, '["string",
"numeric"]'::jsonb), 'D'::"char")) ||
setweight(jsonb_to_tsvector('simple'::regconfig, tags, '["string",
"numeric"]'::jsonb), 'D'::"char")) stored
 first_seen_at   | timestamp with time zone |           |          |
 muted           | boolean                  |           | not null |
 compliance      | jsonb                    |           |          |
Partition key: RANGE (id)
Indexes:
    "findings_pkey" PRIMARY KEY, btree (id)
    "find_delta_new_idx" btree (tenant_id, id) WHERE delta =
'new'::finding_delta
    "find_tenant_scan_id_idx" btree (tenant_id, scan_id, id)
    "findings_filter_idx" btree (scan_id, impact, severity, status,
check_id, delta)
    "findings_scan_id_4df6a7a0" btree (scan_id)
    "findings_search_tenant" gin (text_search, tenant_id)
    "findings_tenant_and_id_idx" btree (tenant_id, id)
    "findings_tenant_id_924c8b16" btree (tenant_id)
    "findings_tenant_search" gin (tenant_id, text_search)
    "findings_uid_idx" btree (uid)
    "uid_tenant_inserted_at" btree (uid, tenant_id, inserted_at DESC)
Foreign-key constraints:
    "findings_scan_id_4df6a7a0_fk_scans_id" FOREIGN KEY (scan_id) REFERENCES
scans(id) DEFERRABLE INITIALLY DEFERRED
    "findings_tenant_id_924c8b16_fk_tenants_id" FOREIGN KEY (tenant_id)
REFERENCES tenants(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "resource_finding_mappings" CONSTRAINT
"resource_finding_mappings_finding_id_d5a5c48e_fk_findings_id" FOREIGN KEY
(finding_id) REFERENCES findings(id) DEFERRABLE INITIALLY DEFERRED
Policies (forced row security enabled):
    POLICY "prowler_findings_default_select" FOR SELECT
      TO prowler
      USING ((current_tenant_id() = tenant_id))
    POLICY "prowler_findings_delete" FOR DELETE
      TO prowler
      USING (
CASE
    WHEN (current_setting('api.tenant_id'::text, true) IS NULL) THEN false
    ELSE (tenant_id = (current_setting('api.tenant_id'::text))::uuid)
END)
    POLICY "prowler_findings_insert" FOR INSERT
      TO prowler
      WITH CHECK (
CASE
    WHEN (current_setting('api.tenant_id'::text, true) IS NULL) THEN false
    ELSE (tenant_id = (current_setting('api.tenant_id'::text))::uuid)
END)
    POLICY "prowler_findings_select" FOR SELECT
      TO prowler
      USING ((current_tenant_id() = tenant_id))
    POLICY "prowler_findings_update" FOR UPDATE
      TO prowler
      USING (
CASE
    WHEN (current_setting('api.tenant_id'::text, true) IS NULL) THEN false
    ELSE (tenant_id = (current_setting('api.tenant_id'::text))::uuid)
END)
Number of partitions: 8 (Use \d+ to list them.)
When a non superuser tries to run the following query "select id from
findings_default where tenant_id = 'a3b89764-af41-4a9c-a735-38b03d3473ac'
and text_search @@ plainto_tsquery('analyzer');" the planner decides not to
pick the btree_gin index with tenant_id and text_search in favor of just
picking the tenant_id index. The following is the query's explain:
prowler_db=> explain (analyze, buffers) select id from findings_default
where tenant_id = 'a3b89764-af41-4a9c-a735-38b03d3473ac' and text_search @@
plainto_tsquery('analyzer');
                                                                      QUERY
PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=246.87..47877.83 rows=10 width=16) (actual
time=36.039..36.041 rows=0 loops=1)
   One-Time Filter: (current_tenant_id() =
'a3b89764-af41-4a9c-a735-38b03d3473ac'::uuid)
   Buffers: shared hit=1185
   ->  Bitmap Heap Scan on findings_default  (cost=246.87..47877.83 rows=10
width=16) (actual time=35.858..35.859 rows=0 loops=1)
         Recheck Cond: (tenant_id =
'a3b89764-af41-4a9c-a735-38b03d3473ac'::uuid)
         Filter: (text_search @@ plainto_tsquery('analyzer'::text))
         Rows Removed by Filter: 20999
         Heap Blocks: exact=1166
         Buffers: shared hit=1185
         ->  Bitmap Index Scan on findings_default_tenant_id_idx
(cost=0.00..246.62 rows=21092 width=0) (actual time=0.854..0.855 rows=20999
loops=1)
               Index Cond: (tenant_id =
'a3b89764-af41-4a9c-a735-38b03d3473ac'::uuid)
               Buffers: shared hit=19
 Planning:
   Buffers: shared hit=2
 Planning Time: 9.587 ms
 Execution Time: 36.190 ms
(16 rows)
After a thorough investigation I get to the point that RLS is getting into
conflict with how the btree_gin index works because it is never picked
regardless of how the RLS policy is configured -- I tried using security
definer functions, wrapping the condition as an InitPlan, and some other
workarounds that did not work.
I'm convinced that the issue comes from how RLS affects during the plan
because using a role with BYPASSRLS, like the superuser, makes the planner
to pick the index I expect. See the following explain:
prowler_db=# explain (analyze, buffers) select id from findings_default
where tenant_id = 'a3b89764-af41-4a9c-a735-38b03d3473ac' and text_search @@
plainto_tsquery('analyzer');
                                                                      QUERY
PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on findings_default  (cost=786.90..829.16 rows=10
width=16) (actual time=4.035..4.039 rows=0 loops=1)
   Recheck Cond: ((text_search @@ plainto_tsquery('analyzer'::text)) AND
(tenant_id = 'a3b89764-af41-4a9c-a735-38b03d3473ac'::uuid))
   Buffers: shared hit=185
   ->  Bitmap Index Scan on findings_default_text_search_tenant_id_idx
(cost=0.00..786.90 rows=10 width=0) (actual time=4.017..4.019 rows=0
loops=1)
         Index Cond: ((text_search @@ plainto_tsquery('analyzer'::text)) AND
(tenant_id = 'a3b89764-af41-4a9c-a735-38b03d3473ac'::uuid))
         Buffers: shared hit=185
 Planning:
   Buffers: shared hit=2
 Planning Time: 6.783 ms
 Execution Time: 4.251 ms
(10 rows)
Thanks in advance.
Best,
Pepe.

PG Bug reporting form <noreply@postgresql.org> writes:
> When a non superuser tries to run the following query "select id from
> findings_default where tenant_id = 'a3b89764-af41-4a9c-a735-38b03d3473ac'
> and text_search @@ plainto_tsquery('analyzer');" the planner decides not to
> pick the btree_gin index with tenant_id and text_search in favor of just
> picking the tenant_id index.

I think what's happening there is that the RLS-derived filter
condition is required to be enforced before any user-provided
condition, so that the user-provided condition cannot be applied
to any rows the user isn't supposed to see.  Without this,
the user-provided condition might be able to leak data about
those rows.

You would be okay, probably, if the system thought that the @@
operator is "leakproof", because then it can be applied before or
concurrently with the RLS condition, which is what's needed to allow
an indexscan using the @@ condition.  But our rules for marking things
leakproof are pretty strict and TS search operators don't qualify.

If you're desperate enough, you could mark ts_match_vq() as leakproof
and hope that (a) there's not a way to abuse it or at least (b) your
users aren't smart enough to find one.  I think you might also need to
use the 2-parameter form of plainto_tsquery(), to allow that call to
be reduced to a constant before these decisions are made.

Anyway, I don't see a bug here.  The real story is that RLS is a
performance killer in multiple ways, so if you need speed you're
better off looking for an alternative solution to your security
requirements.

            regards, tom lane