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

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.

> So I like #3 the best. We already have the ability to specify that
> particular policies apply to just specific users, but it seems like
> what you want here is the inverse: to be able to name specific users
> that are exempt from a given policy. (While that's not absolutely
> essential, without it you might need very long and hard-to-maintain
> lists of every-role-but-that-one.) It doesn't seem to me to be
> unreasonable to extend CREATE/ALTER POLICY in that direction.

I know that it's not exactly the same, but I've used a setup like this
to have a role that's exempt from RLS:

CREATE POLICY tenant_scope ON my_table FOR ALL
TO tenant_role
USING ("tenant_id" = current_setting('rls.tenant_id')::INTEGER);

CREATE POLICY across_tenants ON my_table FOR ALL
TO across_tenants_role
USING (true);

As a bonus, the across_tenants policy doesn't receive the leakproof
guardrails. I guess USING (true) is special cased in some way.

Best regards,
Andreas Lind

On Mon, Jun 16, 2025 at 5:59 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> writes:
> > Andreas Lind <andreaslindpetersen(at)gmail(dot)com> wrote:
> >> I dug into the code and noticed that restrictinfo->leakproof is only
> >> being checked in two places (createplan.c and equivclass.c), so it seems
> >> fairly easy to only selectively enforce it. Then there's the question of
> >> how to configure it. I can think of a few possible ways:
> >>
> >> 1) Add a BYPASSLEAKPROOF role attribute that can only be granted by a
> >> superuser, similar to the BYPASSRLS flag.
> >> 2) Add a session variable, eg. enable_security_leakproof, that can only
> >> be set or granted to another role by a superuser.
> >> 3) Make it a property of the individual POLICY that grants access to the
> >> table. This would be a bit more granular than a global switch, but
> >> there'd be some ambiguity when multiple policies are involved.
>
> > 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.
>
> So I like #3 the best. We already have the ability to specify that
> particular policies apply to just specific users, but it seems like
> what you want here is the inverse: to be able to name specific users
> that are exempt from a given policy. (While that's not absolutely
> essential, without it you might need very long and hard-to-maintain
> lists of every-role-but-that-one.) It doesn't seem to me to be
> unreasonable to extend CREATE/ALTER POLICY in that direction.
> Perhaps like
>
> CREATE POLICY name ON table_name
> [ AS { PERMISSIVE | RESTRICTIVE } ]
> [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
> [ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER
> } [, ...] ]
> + [ EXCEPT { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER |
> SESSION_USER } [, ...] ]
> [ USING ( using_expression ) ]
> [ WITH CHECK ( check_expression ) ]
>
> (Not sure that EXCEPT PUBLIC is sensible; also we'd need a decision
> about what to do if same role appears in both lists.)
>
> regards, tom lane
>