Thread
-
BUG #18941: PostgreSQL planner does not select a multicolumn btree_gin index under RLS
PG Bug reporting form <noreply@postgresql.org> — 2025-05-30T19:39:43Z
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. -
Re: BUG #18941: PostgreSQL planner does not select a multicolumn btree_gin index under RLS
Tom Lane <tgl@sss.pgh.pa.us> — 2025-05-30T20:37:15Z
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 -
Re: BUG #18941: PostgreSQL planner does not select a multicolumn btree_gin index under RLS
Pepe Fagoaga <pepe@prowler.com> — 2025-05-30T21:14:26Z
Hello Tom, First of all, thank you for your thorough explanation. I apologise for opening it as a bug, probably I should've opened it just as a question. I'd like to explore what you mention but it seems something extreme, I'm not sure about how this can be abused and the possible impact of that without ensuring security. Regards, Pepe. On Fri, May 30, 2025 at 8:37 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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 >