Re: How to optimize PostgreSQL Row Security Policies that involve related tables? - Mailing list pgsql-general

Hello!

What are some good ways to create Row Security Policies that restrict rows in one table using a filter on a related table, and get good performance?  It's difficult to describe but let me try to explain.

I'm using the venerable old Chinook database.  Among its tables are Artist, Album, and Track.  Track has a foreign key constraint on Track.AlbumId, and Album has a foreign key constraint on Album.ArtistId.  Albums belong to Artists while Tracks belong to Albums.  I want to have two policies:

1. Restrict access to Album based on Album.ArtistId = current_setting('rls.artistId'), supposing that 'rls.artistId' will be set to the current artist's valid artistId.
2. Restrict access to Track such that the current artist only sees tracks on their own albums and not on anybody else's albums.

One challenge is that the Track table doesn't actually have ArtistId (naturally).  However, if we know there's a valid policy on Album, then we should be able to leverage that in a policy on Track.  The trouble is, I can't do a join in the Track policy (or at least, I haven't figured out how to do a join), so I have to resort to a "exists" check.  Specifically, the two policies look like this:

create policy artist_rls_policy ON "Album" for select to public using ("ArtistId"=(current_setting('rls.artistID'))::integer);

create policy album_rls_policy on "Track" for select to public
  using (
    exists (select * from "Album" where "Album"."AlbumId" = "AlbumId") -- THIS IS THE TROUBLESOME POLICY CHECK HERE
    );

But, the presence of the "exists" check leads to a suboptimal plan:

explain analyze select * from "Track";

                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on "Track"  (cost=8.17..203181.49 rows=5001816 width=56) (actual time=4.631..699.831 rows=10003504 loops=1)
   Filter: $0
   InitPlan 1 (returns $0)
     ->  Index Scan using "IFK_AlbumArtistId" on "Album"  (cost=0.15..8.17 rows=1 width=0) (actual time=0.022..0.022 rows=1 loops=1)
           Index Cond: ("ArtistId" = (current_setting('rls.artistID'::text))::integer)
           Filter: ("AlbumId" IS NOT NULL)
 Planning Time: 0.209 ms
 JIT:
   Functions: 7
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.673 ms, Inlining 0.000 ms, Optimization 0.391 ms, Emission 3.793 ms, Total 4.857 ms
 Execution Time: 876.035 ms
(12 rows)

In my query, sure, I can explicitly join Track to Album to get a better plan:

explain analyze select * from "Track" natural join "Album";

                                                                  QUERY PLAN                                                                  
-----------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=8.75..37450.90 rows=14414 width=83) (actual time=0.184..0.314 rows=14 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using "IFK_AlbumArtistId" on "Album" "Album_1"  (cost=0.15..8.17 rows=1 width=0) (actual time=0.033..0.034 rows=1 loops=1)
           Index Cond: ("ArtistId" = (current_setting('rls.artistID'::text))::integer)
           Filter: ("AlbumId" IS NOT NULL)
   ->  Index Scan using "PK_Album" on "Album"  (cost=0.15..22.82 rows=1 width=31) (actual time=0.136..0.252 rows=1 loops=1)
         Filter: ("ArtistId" = (current_setting('rls.artistID'::text))::integer)
         Rows Removed by Filter: 346
   ->  Index Scan using "IFK_TrackAlbumId" on "Track"  (cost=0.43..32418.09 rows=500182 width=56) (actual time=0.044..0.051 rows=14 loops=1)
         Index Cond: ("AlbumId" = "Album"."AlbumId")
         Filter: $0
 Planning Time: 0.509 ms
 Execution Time: 0.364 ms
(13 rows)

But, that's redundant since there's enough information to process the query correctly (albeit more slowly) without the join.  The question is, how if at all can I create the policies to have both the desired composability and also good performance?  I hope I've explained this well enough. I asked this question a while back on StackOverflow but got no interest.  I also have sample code in a public GitHub repository here that illustrates the setup.  Any advice would be greatly appreciated.  Thank you!

Kind regards,

David A. Ventimiglia