CODEWITHSUNDEEP
postgresql
pgmrk0o7
pgmrk0o7

Reputation: 9

PostgreSQL Row-Level Security is NOT Using Table Indexes

I have the following table with a million rows of data.

enter image description here

SELECT COUNT(*) FROM people; -- 1,000,000 rows

I have created the following 2 indexes:

enter image description here

Without the RLS enabled, I checked the explain plan of the following query and you can see that it is using the index that I have created for the ct_data JSONB column.

enter image description here

I enabled Row Level Security by issuing the following commands. The table policy is just a simple condition to have select access on the table.

enter image description here

With the RLS enabled, I rechecked the explain plan of the same query but this time, it did not use the index and it is doing a parallel sequential scan instead on the "people" table.

enter image description here

Why is RLS not using the indexes? Appreciate if you could provide some guidance to still implement RLS but not sacrifice performance.

At the moment, if I try to do an update on the ct_data column of the "people" table, it is taking so much time to complete the update especially after I add another simple table policy for the UPDATE DML operation like:

enter image description here

Upvotes: -1

Views: 589

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

The problem is that the operator -> is not leakproof:

SELECT oprcode
FROM pg_operator
WHERE oprname = '->'
  AND oprleft = 'jsonb'::regtype
  AND oprright = 'text'::regtype;

      oprcode       
════════════════════
 jsonb_object_field
(1 row)

SELECT proleakproof
FROM pg_proc
WHERE proname = 'json_object_field';

 proleakproof 
══════════════
 f
(1 row)

Functions and operators that are not leakproof are not optimized to be applied before the condition from the RLS policy for security reasons.

The solution is to pull that id column out of the jsonb and define it a a regular numeric column. The = operators for numeric data types are leakproof.

Upvotes: 4

Related Questions