postgresql row level security RESTRICTIVE policies write single select query-CodePudding

Below are 3 tables script.

CREATE TABLE rls_permission(upn text,is_all boolean ,reference int[]);
CREATE TABLE objects(key serial primary key,status text );
CREATE TABLE object_attributes(key serial primary key,status text ,values text,reference int[], type_key int);
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(
         (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) 
                      )
             ) 
        )

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.

Thanks

CodePudding user response：

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.

The permissive policy seems to be quite redundant, and you should get rid of it.

CodePudding user response：

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))