Re: Per-role disabling of LEAKPROOF requirements for row-level security?

Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:

> Therefore, if bypassing LEAKPROOF checks is to be allowed, I believe it
> would be better to make this configurable on a per-policy basis.

I've implemented a first iteration of a per-policy BYPASSLEAKPROOF flag.
It makes a distinction between "normal quals" and "security quals" in the
rowsecurity rewriting code. Only if all permissive policies are marked
as BYPASSLEAKPROOF, the combined qual is added as a normal WHERE clause.
Handling a mix of BYPASSLEAKPROOF and non-BYPASSLEAKPROOF permissive
policies would be much more complicated due to the "OR" semantics so I
think it's better to keep it simple for now.

It seems to work well when I test it locally, and I've also fiddled with
some automatic tests (not enclosed in the patch). Let me know if the
general approach seems sane, then I can work on it some more.

Best regards,
Andreas Lind

On Tue, Jun 24, 2025 at 5:19 AM Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:

> On Mon, 23 Jun 2025 09:56:19 +0200
> Jelte Fennema-Nio <postgres(at)jeltef(dot)nl> wrote:
>
> > On Mon, 23 Jun 2025 at 09:43, Andreas Lind
> > <andreaslindpetersen(at)gmail(dot)com> wrote:
> > >
> > > Thanks for your replies!
> > >
> > > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > > Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> writes:
> > > > > I'm not sure whether multi-tenant applications fall into the
> category where
> > > > > LEAKPROOFness isn't considered important, since security is
> typically a key
> > > > > concern for users of such systems.
> > >
> > > > Yeah, ISTM that you might as well just disable the RLS policy as
> > > > ignore leakproofness, because it is completely trivial to examine
> > > > supposedly-hidden data if you can apply a non-leakproof function
> > > > to it.
> > >
> > > In my case the role is only used by the application code, which
> > > is reviewed and trusted. So I'm not too concerned about a malicious
> > > actor using it to apply non-leakproof functions. Or rather, the risk
> > > of that happening seems much lower than the application accidentally
> > > mixing data from different tenants by forgetting WHERE tenant_id = ...
> > > So I'd argue that it can be very valuable to have RLS enabled
> > > for defense-in-depth even without the leakproof guardrails.
> >
> > +1 on this usecase. RLS its primary usecase right now is to protect
> > against arbitrary SQL execution, for which the LEAKPROOF protection
> > makes sense. But there are quite a lot of people (people building SaaS
> > apps with a tenant_id column on each table) that would like to use it
> > as a mechanism to automatically add a filter on each of their queries
> > in a transaction, to avoid coding errors in their own application. In
> > your application you can then have a function like,
> > open_tenant_transaction(). Which basically does: "BEGIN" and "SET
> > LOCAL tenant_id". And from then on you don't have to worry about.
> >
> > For that second usecase LEAKPROOF only gets in the way. These people
> > just want an additional filter to be applied automatically. I've seen
> > multiple users that tried RLS for this purpose, and switched back to
> > not doing any RLS because the leakproof protections got in the way of
> > getting performant queries.
>
> I agree that using RLS is useful in this use case. However, I still don’t
> think it’s a good idea to allow a role to bypass all LEAKPROOF checks,
> since, in a multi-tenant system, a role might need to use RLS in two
> different ways simultaneously: one where bypassing the LEAKPROOF check is
> acceptable to efficiently access their own tenant’s data, and another where
> enforcing the LEAKPROOF check is critical for security.
>
> Therefore, if bypassing LEAKPROOF checks is to be allowed, I believe it
> would be better to make this configurable on a per-policy basis.
>
> Best regards,
> Yugo Nagata
>
> --
> Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>
>