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

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>