postgresql row level security RESTRICTIVE policies write single select query

Question

Below are 3 tables script.

CREATE TABLE rls_permission(upn text,is_all boolean ,reference int[]);
CREATE TABLE objects(key serial primary key,object_type_key int,status text );
CREATE TABLE object_attributes(key serial primary key,objects_key int,status text ,values text,reference int[], type_key int);
---Indexes
CREATE INDEX objects_object_type_key_status ON objects USING btree    (object_type_key, status )    

CREATE UNIQUE INDEX object_attributes_objects_key_type_key_uniq ON object_attributes (objects_key, type_key);

CREATE INDEX object_attributes_reference on object_attributes USING gin(value_reference gin__int_ops)

Web application will retrieve certain objects first and then its respective object attribute values. An object may have many attribute values in object_attributes table.

User permission settings configured in table rls_permission, if column is_all value is true then user can see all rows/objects , otherwise as references mentioned in column reference. (references values populated into rls_permission by an other interface, which has full access and gets values from object_attributes)

I have created below row level policy on objects table.


CREATE POLICY no_rls_objects ON objects AS PERMISSIVE FOR ALL TO PUBLIC USING (TRUE);

CREATE POLICY rls_on_objects ON objects AS RESTRICTIVE TO web_app_user
   USING( (current_setting('db.rls_user')='web_system')
          OR (SELECT per.is_all FROM rls_permission per
                WHERE (lower(per.upn) = 
                      lower(current_setting('db.rls_user'::text)))
              ) 
          OR (EXISTS ( SELECT 1 FROM object_attributes att
                               JOIN rls_permission per ON ((per.reference && att.reference)))
                                WHERE ((lower(per.upn) = lower(current_setting('db.rls_user'::text))) 
                                 AND (att.objects_key = objects.key) 
                       )
               )
          OR (object_type_key NOT IN (1,24))
          )

rls_on_objects RESTRICTIVE Policy has two SELECT quires separated by OR.

I can't create two RESTRICTIVE policies, with one query in each policy because having two RESTRICTIVE policies would be combined using AND. But I need combine two queries using OR.

Is there way to rewrite the query and make singe query ?

because both queries has (lower(per.upn) = lower(current_setting('db.rls_user'::text))), it has compute in both, when is_all is false or null then it check/execute 2nd query. Making into single query would improve the RLS performance as it no need to compute twice.

Update1: below is executed query plan for my policy and query select * from objects where status='active' and object_type_key=1

Aggregate  (cost=122653500.78..122653500.79 rows=1 width=8) (actual time=3087.257..3087.357 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Seq Scan on rls_permission  (cost=0.00..27.40 rows=4 width=1) (actual time=0.014..0.015 rows=1 loops=1)
          Filter: (lower(upn) = lower(current_setting('db.rls_user'::text)))
          Rows Removed by Filter: 3
  ->  Index Scan using objects_object_type_key_status on objects  (cost=0.43..122650563.66 rows=1163888 width=0) (actual time=1454.965..3086.453 rows=7697 loops=1)
        Index Cond: (object_type_key = 1)
        Filter: (((current_setting('db.rls_user'::text) = 'web_system'::text) OR $0 OR (alternatives: SubPlan 2 or hashed SubPlan 3) OR (object_type_key <> ALL ('{1,24}'::integer[]))) AND (status = 'active'::enm_status))
        Rows Removed by Filter: 3024827
        SubPlan 2
          ->  Nested Loop  (cost=0.44..40.35 rows=1 width=0) (never executed)
                Join Filter: (per.reference && att.reference)
                ->  Index Scan using object_attributes_object_key_type_key_uniq on object_attributes att  (cost=0.44..12.90 rows=1 width=25) (never executed)
                      Index Cond: ((object_key = objects.key) AND (type_key = ANY ('{6,192}'::integer[])))
                ->  Seq Scan on rls_permission per  (cost=0.00..27.40 rows=4 width=32) (never executed)
                      Filter: (lower(upn) = lower(current_setting('db.rls_user'::text)))
        SubPlan 3
          ->  Nested Loop  (cost=1000.00..741248.75 rows=45627 width=4) (actual time=0.333..1449.186 rows=9079 loops=1)
                Join Filter: (per_1.reference && att_1.reference)
                Rows Removed by Join Filter: 1170632
                ->  Gather  (cost=1000.00..672780.72 rows=1140677 width=29) (actual time=0.256..1072.187 rows=1179711 loops=1)
                      Workers Planned: 2
                      Workers Launched: 2
                      ->  Parallel Seq Scan on object_attributes att_1  (cost=0.00..557713.02 rows=475282 width=29) (actual time=0.032..1214.816 rows=393237 loops=3)
                            Filter: (type_key = ANY ('{6,192}'::integer[]))
                            Rows Removed by Filter: 7566828
                ->  Materialize  (cost=0.00..27.42 rows=4 width=32) (actual time=0.000..0.000 rows=1 loops=1179711)
                      ->  Seq Scan on rls_permission per_1  (cost=0.00..27.40 rows=4 width=32) (actual time=0.009..0.010 rows=1 loops=1)
                            Filter: (lower(upn) = lower(current_setting('db.rls_user'::text)))
                            Rows Removed by Filter: 3
Planning Time: 0.423 ms
Execution Time: 3087.431 ms

Updated2--- query plan for Edouard H. suggested queries

query1
Aggregate  (cost=239792868.03..239792868.04 rows=1 width=8) (actual time=33737.946..33737.947 rows=1 loops=1)
  ->  Index Scan using object_type_key_status on objects  (cost=0.43..239790834.63 rows=813358 width=0) (actual time=4.949..33735.611 rows=7697 loops=1)
        Index Cond: (object_type_key = 1)
        Filter: (((current_setting('db.rls_user'::text) = 'web_system'::text) OR (SubPlan 2) OR (object_type_key <> ALL ('{1,24}'::integer[]))) AND (status = 'active'::enm_status))
        Rows Removed by Filter: 3024827
        SubPlan 2
          ->  Seq Scan on rls_permission per  (cost=0.00..79.03 rows=4 width=1) (actual time=0.009..0.009 rows=1 loops=3032524)
                Filter: (lower(upn) = lower(current_setting('db.rls_user'::text)))
                Rows Removed by Filter: 3
                SubPlan 1
                  ->  Index Scan using object_attributes_objects_key_type_key_uniq on object_attributes att  (cost=0.44..12.91 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=3032524)
                        Index Cond: ((objects_key = objects.key) AND (type_key = ANY ('{6,192}'::integer[])))
                        Filter: (reference && per.reference)
                        Rows Removed by Filter: 0
Planning Time: 0.781 ms
Execution Time: 33738.102 ms

query2
Aggregate  (cost=122743462.16..122743462.17 rows=1 width=8) (actual time=24355.184..24355.186 rows=1 loops=1)
  ->  Index Scan using objects_object_type_key_status on objects  (cost=0.43..122741428.77 rows=813358 width=0) (actual time=1.222..24353.622 rows=7697 loops=1)
        Index Cond: (object_type_key = 1)
        Filter: (((current_setting('db.rls_user'::text) = 'web_system'::text) OR (SubPlan 1) OR (object_type_key <> ALL ('{1,24}'::integer[]))) AND (status = 'active'::enm_status))
        Rows Removed by Filter: 3024827
        SubPlan 1
          ->  GroupAggregate  (cost=40.36..40.38 rows=1 width=2) (actual time=0.007..0.007 rows=0 loops=3032524)
                Group Key: per.is_all
                ->  Sort  (cost=40.36..40.37 rows=1 width=26) (actual time=0.007..0.007 rows=0 loops=3032524)
                      Sort Key: per.is_all
                      Sort Method: quicksort  Memory: 25kB
                      ->  Nested Loop  (cost=0.44..40.35 rows=1 width=26) (actual time=0.006..0.006 rows=0 loops=3032524)
                            Join Filter: (att.reference && per.reference)
                            Rows Removed by Join Filter: 0
                            ->  Index Scan using object_attributes_objects_key_type_key_uniq on object_attributes att  (cost=0.44..12.90 rows=1 width=25) (actual time=0.004..0.004 rows=0 loops=3032524)
                                  Index Cond: ((objects_key = objects.key) AND (type_key = ANY ('{6,192}'::integer[])))
                            ->  Seq Scan on rls_permission per  (cost=0.00..27.40 rows=4 width=33) (actual time=0.004..0.004 rows=1 loops=1098057)
                                  Filter: (lower(upn) = lower(current_setting('db.rls_user'::text)))
                                  Rows Removed by Filter: 3
Planning Time: 0.340 ms
Execution Time: 24355.242 ms

Thanks

Edouard · Accepted Answer · 2021-11-25 20:06:28Z

1

Try something like this :

SELECT per.is_all
    OR EXISTS ( SELECT 1
                  FROM object_attributes att
                 WHERE att.reference && per.reference
                   AND att.objects_key = objects.key
              )
  FROM rls_permission per
 WHERE lower(per.upn) = lower(current_setting('db.rls_user'::text))

or this could be faster :

SELECT per.is_all
    OR bool_or(att.reference IS NOT NULL)
  FROM rls_permission per
  LEFT JOIN object_attributes att
    ON att.reference && per.reference
 WHERE lower(per.upn) = lower(current_setting('db.rls_user'::text))
   AND att.objects_key = objects.key
 GROUP BY per.is_all

edited Nov 25, 2021 at 20:06

answered Nov 24, 2021 at 21:27

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hari Over a year ago

thanks for single query (completed in 50 sec). But it seems this is much slower than my query(completes in 10 sec) with two select statements.

Edouard Over a year ago

@Hari - I've added a variant solution that may be faster, but I didn't test it. Can you execute your query and both I suggest with EXPLAIN ANALYZE and update your question with the results so that to compare their execution plans ?

Hari Over a year ago

I've added query plan for my policy for query. I also updated my complete RLS policy. Also updated the query plan for your suggested query.

Laurenz Albe · Accepted Answer · 2021-11-26 05:13:23Z

0

I think you are concerned about the wrong problem.

The repeated WHERE clause will not be a major performance problem. The OR and the subquery with the join that will have to executed for every table row will be a much bigger problem. Not that I have an idea how to improve that, except using a simpler permission system.

edited Nov 26, 2021 at 5:13

answered Nov 24, 2021 at 21:24

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

2 Comments

Hari Over a year ago

permissive policy is just true for all users. however its RESTRICTIVE policy restricts one user.

Laurenz Albe Over a year ago

Ah, you are right about that.

Collectives™ on Stack Overflow

postgresql row level security RESTRICTIVE policies write single select query

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related