RLS that a table can only be modified by a certain postgres function
Is it possible to write a RLS policy that restricts table update access except the update is coming from a certain function?
I tried this:
CREATE POLICY "Public counters can be updated by decrement_function." ON counters
FOR UPDATE
USING (
(CURRENT_SETTING('app.current_function') = 'hidden_functions.decrement_counter')
);
Which seems to block access from non authenticated update calls, but allows all functions to update it. But updates should only be allowed by "decrement_counter" function
Use case: I have a table with a counter and I only want to allo +1 and -1 operations if they are coming from a certain function which is part of an transaction.
3 Replies
What kind of functions that you write and control are you protecting yourself from? Normally locking updates with RLS and using a security definer function to bypass is the way. But nothing stops you writing another security definer function.
But how would I write a security definer function for RLS that only allows a +1 or -1 update? I understand how to write functions that allow updates only to a certain user, but how do I restrict the update value to +1 or -1?
You can't restrict it in RLS. You have to allow NO updates at all and then use security definer function and call that to do the inc/dec.
If restricting the update totally won't work, then look at a trigger function that compares old and new and decide what to do for that column.