Hi, Is there a way to visualize RLS policy check(s) in the query plan?
Regards, Akash Anand On Mon, Jul 10, 2023 at 11:33 AM Akash Anand <ak...@hasura.io> wrote: > Hi, > > ------ > Postgres version > ------ > postgres=# SELECT version(); > version > > > ----------------------------------------------------------------------------------------------------------------------------------- > PostgreSQL 15.3 (Debian 15.3-1.pgdg110+1) on aarch64-unknown-linux-gnu, > compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit > (1 row) > ------ > > ------ > Load data > ------ > Chinook database > > https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_PostgreSql.sql > ------ > > ------ > Insert dummy data into Track to bring rows count to 10 million > ------ > INSERT INTO "Track"("TrackId", "Name", "AlbumId", "MediaTypeId", > "GenreId", "Milliseconds", "Bytes", "UnitPrice") > SELECT i::int, i::text, 1, 1, 1, 276349, 9056902, 0.99 > FROM generate_series(3504, 10000000) AS t(i); > ------ > > ------ > Setup role and policies > ------ > create role "User"; > grant select on "Album" to "User"; > CREATE POLICY artist_rls_policy ON "Album" FOR SELECT TO public USING > ("ArtistId"=((current_setting('rls.artistID'))::integer)); > ALTER TABLE "Album" ENABLE ROW LEVEL SECURITY; > grant select on "Track" to "User"; > CREATE POLICY album_rls_policy ON "Track" FOR SELECT to public > USING ( > EXISTS ( > select 1 from "Album" where "Track"."AlbumId" = "Album"."AlbumId" > ) > ); > ALTER TABLE "Track" ENABLE ROW LEVEL SECURITY; > ------ > > ------ > Query and verify the policies through psql > ------ > set role "User"; > set rls.artistID = '116'; > select * from "Track"; > ------ > > ------ > Query plan for postgres > ------ > postgres=> explain analyze select * from "Track"; > QUERY PLAN > > > ------------------------------------------------------------------------------------------------------------------------------------- > Seq Scan on "Track" (cost=0.00..34589179.11 rows=2110303 width=58) > (actual time=68.097..350.074 rows=14 loops=1) > Filter: (hashed SubPlan 2) > Rows Removed by Filter: 4220538 > SubPlan 2 > -> Index Scan using "IFK_AlbumArtistId" on "Album" (cost=0.15..8.17 > rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1) > Index Cond: ("ArtistId" = > (current_setting('rls.artistID'::text))::integer) > Planning Time: 0.091 ms > JIT: > Functions: 17 > Options: Inlining true, Optimization true, Expressions true, Deforming > true > Timing: Generation 1.008 ms, Inlining 11.450 ms, Optimization 33.233 > ms, Emission 22.443 ms, Total 68.135 ms > Execution Time: 350.922 ms > (12 rows) > ------ > > ------ > Disabled ROW LEVEL SECURITY and get appropriate tracks > ------ > > > QUERY PLAN > > > -------------------------------------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=7657.40..7657.41 rows=1 width=32) (actual > time=0.070..0.071 rows=1 loops=1) > -> Nested Loop Left Join (cost=7650.01..7657.38 rows=1 width=55) > (actual time=0.061..0.068 rows=1 loops=1) > -> Seq Scan on "Album" (cost=0.00..7.34 rows=1 width=27) > (actual time=0.020..0.026 rows=1 loops=1) > Filter: ("ArtistId" = 116) > Rows Removed by Filter: 346 > -> Aggregate (cost=7650.01..7650.02 rows=1 width=32) (actual > time=0.040..0.040 rows=1 loops=1) > -> Nested Loop (cost=0.43..6107.07 rows=102863 width=11) > (actual time=0.016..0.026 rows=14 loops=1) > -> Seq Scan on "Album" "__be_0_Album" > (cost=0.00..8.21 rows=1 width=4) (actual time=0.008..0.015 rows=1 loops=1) > Filter: (("AlbumId" = "Album"."AlbumId") AND > ("ArtistId" = 116)) > Rows Removed by Filter: 346 > -> Index Scan using "IFK_TrackAlbumId" on "Track" > (cost=0.43..5070.23 rows=102863 width=15) (actual time=0.008..0.009 > rows=14 loops=1) > Index Cond: ("AlbumId" = "Album"."AlbumId") > SubPlan 2 > -> Result (cost=0.00..0.01 rows=1 width=32) (actual > time=0.000..0.000 rows=1 loops=14) > SubPlan 1 > -> Result (cost=0.00..0.01 rows=1 width=32) (actual > time=0.000..0.000 rows=1 loops=1) > Planning Time: 0.182 ms > Execution Time: 0.094 ms > (18 rows) > ------ > > Why did Postgres choose to do a sequential scan on Track when RLS is > enabled? > > Regards, > Akash Anand > >
