Re: policies with security definer option for allowing inline optimization

Greetings,

* Isaac Morland (isaac(dot)morland(at)gmail(dot)com) wrote:
> 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.

... and views.

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

I'm generally +1 on adding the ability for the DBA to choose which user
various things run as. There's definitely use-cases for both in my
experience. Also would be great to add the ability to have policies on
views too which would probably help address some of these cases.

Thanks,

Stephen