Re: policies with security definer option for allowing inline optimization

On Fri, 2 Apr 2021 at 01:44, Dan Lynch <pyramation(at)gmail(dot)com> wrote:

> RLS policies quals/checks are optimized inline, and so I generally avoid
> writing a separate procedure so the optimizer can do it's thing.
>
> However, if you need a security definer to avoid recursive RLS if you're
> doing a more complex query say, on a join table, anyone wish there was a
> flag on the policy itself to specify that `WITH CHECK` or `USING`
> expression could be run via security definer?
>
> The main reason for this is to avoid writing a separate security definer
> function so you can benefit from the optimizer.
>
> Is this possible? Would this be worth a feature request to postgres core?
>

If we're going to do this we should do the same for triggers as well.

It's easy to imagine a situation in which RLS policies need to refer to
information which should not be accessible to the role using the table, and
similarly it's easy to imagine a situation in which a trigger needs to
write to another table which should not be accessible to the role using the
table which has the trigger.