CODEWITHSUNDEEP
postgresqlrow-level-security
Pyrocks
Pyrocks

Reputation: 481

Skip RLS checks temporarily

I have tables with enabled row-level-security and the relevant policies in place - working really well.

My problem is that sometimes, based on some conditions, I want to bypass a policy for a specific statement during a function execution.

something like:

...
statement 1
statement 2
if (some cond) then
   disable rls temporarily 
   statement 3 -- mostly delete rows the user can't normally see
   enable rls
else
   statement 3
end if

The way I've implemented it is by creating a function check_cond which returns a boolean evaluating some cond and created an additional select policy which calls this check_cond.

It works - but the actual problem is that the query select * from tab now looks like this: select * from tab where <original policy condition> or check_cond()

This or check_cond() causes postges to always do a full table scan since it cannot evaluate the result pre-planning.

If I were able to write "dynamic" code in the policy I would have been able to add/remove conditions based on the value of check_cond() but as far as I know it's not possible.

Any smart way which would allow me to temporarily disable rls or dynamically add conditions without sacrificing performance?

Thanks.

Upvotes: 1

Views: 2352

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246308

The easiest way would be to have a SECURITY DEFINER function owned by a superuser which runs:

ALTER ROLE someuser BYPASSRLS;

where someuser is the user that runs the SQL statements.

Afterwards, you can re-enable it in the same way.

But that's pretty insecure, because nothing keeps the user from calling theses functions at other times.

A better way would be to define a security definer function owned by a user with BYPASSRLS that does the deletes for you.

Note: for security reasons, always SET search_path when you define a SECURITY DEFINER function.

Upvotes: 2

Related Questions