I've got the following table:
CREATE TYPE "weekday" AS ENUM ('mo', 'tu', 'we', 'th', 'fr', 'sa', 'su');
CREATE TABLE index_test
(
school_id uuid NOT NULL,
record_number bigint NOT NULL,
weekday weekday NOT NULL,
room_record_number int NULL,
from_time time NOT NULL,
to_time time NOT NULL,
from_date date NOT NULL,
to_date date NULL,
CONSTRAINT "index_test_pkey" PRIMARY KEY (school_id, record_number)
);
CREATE INDEX "temp_multiple_idx" ON index_test (school_id, weekday, room_record_number, from_time, to_time);
ALTER TABLE index_test ENABLE ROW LEVEL SECURITY;
The RLS policy of the table is the following:
CREATE POLICY "separate_schools" ON "index_test"
USING (school_id = nullif(current_setting('mm_cloud.rls_restricted_by_school_id', true), '')::uuid);
There also exists a user called index_test for whom RLS applies:
CREATE USER index_test ENCRYPTED PASSWORD 'index_test';
GRANT SELECT ON TABLE index_test to index_test;
I vacuumed the table:
VACUUM (ANALYSE , VERBOSE) index_test;
Then i run the following query using the index_test user:
SET mm_cloud.rls_restricted_by_school_id = '5a950d02-00f1-4bd8-ba27-9ac6c2c5d193';
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT *
FROM index_test
WHERE weekday = 'we'
AND room_record_number = 3
AND from_time = '15:15'
AND to_time = '16:00';
This results in the following query plan:
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Index Scan using temp_multiple_idx on public.index_test (cost=0.43..2020.22 rows=1 width=56) (actual time=0.785..0.786 rows=0 loops=1) |
| Output: school_id, record_number, weekday, room_record_number, from_time, to_time, from_date, to_date |
| Index Cond: ((index_test.school_id = (NULLIF(current_setting('mm_cloud.rls_restricted_by_school_id'::text, true), ''::text))::uuid) AND (index_test.room_record_number = 3) AND (index_test.from_time = '15:15:00'::time without time zone) AND (index_test.to_time = '16:00:00'::time without time zone))|
| Filter: (index_test.weekday = 'we'::weekday) |
| Buffers: shared hit=200 |
|Planning Time: 0.144 ms |
|Execution Time: 0.806 ms |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Notice that only a part of the index is used.
Then i run a similar query having the RLS rule inlined, using the table owner user which does not have RLS applied:
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT *
FROM index_test
WHERE school_id = '5a950d02-00f1-4bd8-ba27-9ac6c2c5d193'
AND weekday = 'we'
AND room_record_number = 3
AND from_time = '15:15'
AND to_time = '16:00';
This results in a much better query planin using the full index:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Index Scan using temp_multiple_idx on public.index_test (cost=0.42..8.45 rows=1 width=56) (actual time=0.073..0.074 rows=0 loops=1) |
| Output: school_id, record_number, weekday, room_record_number, from_time, to_time, from_date, to_date |
| Index Cond: ((index_test.school_id = '5a950d02-00f1-4bd8-ba27-9ac6c2c5d193'::uuid) AND (index_test.weekday = 'we'::weekday) AND (index_test.room_record_number = 3) AND (index_test.from_time = '15:15:00'::time without time zone) AND (index_test.to_time = '16:00:00'::time without time zone))|
| Buffers: shared hit=3 |
|Planning Time: 0.260 ms |
|Execution Time: 0.128 ms |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The problem is that the equality operator for enum types is not leakproof:
SELECT f.proleakproof
FROM pg_proc AS f
JOIN pg_operator AS o ON o.oprcode = f.oid
WHERE o.oprname = '='
AND o.oprleft = 'anyenum'::regtype
AND o.oprright = 'anyenum'::regtype;
proleakproof
══════════════
f
(1 row)
So the optimizer has to make sure that the condition from the row level security policy is evaluated before that WHERE condition. If both were evaluated with the same index scan, the operator might have to process data that don't qualify for row level security. Using a filter makes sure that the correct order is maintained.
Don't ask me why that operator is not leakproof. It should be in my opinion, because under the hood, an enum is nothing but a real, and the equality operator for real is leakproof. Perhaps you should bring it up with the pgsql-hackers mailing list (once the current commitfest is over).