Обсуждение: Per-role disabling of LEAKPROOF requirements for row-level security?

Hi hackers,

Row-level security is an awesome feature. I was originally won over by
the simple mental model of implicitly adding WHERE clauses to all
queries, and that it generally comes for free when they can be
satisfied by Index Conds.

I've recently helped implement row-level security in a fairly big
multi-tenant application with about 100 tables. RLS is being used
"voluntarily" by the application, which switches to a role that is
subject to RLS as the first thing after starting a transaction. I
realize that this doesn't offer bulletproof tenant isolation, but it
still makes a lot of sense as a defense-in-depth measure.

It took some time to work through various performance regressions
caused by selectivity estimates changing due to "WHERE tenantId = ..."
implicitly being added to all relations in all queries. Extended
statistics (dependencies) helped with that, as tenantId is highly
correlated with the other id columns that we were already querying and
joining on.

The roughest edge turned out to be the well-known challenge with the
LEAKPROOFness of built-in functions, previously discussed in earlier
threads [1], in blog posts [2], on Stack Overflow [3] and pointed out
in the docs for Row Security Policies [4].

I'm aware that the usual workaround is to ALTER FUNCTION ... LEAKPROOF
for each of the relevant built-in functions after making an evaluation
of whether that's acceptable for the given use case. We weren't able to
do this because we're using a hosted Postgres solution where it's not
permitted. We've tried to talk them (AWS) into offering a way to do it,
but they haven't budged yet.

As in the earlier thread we were affected by enum_eq not being
leakproof, which caused our indexes involving enum columns to not be
fully utilized while RLS is active (because WHERE clauses on the enum
columns cannot be implemented as Index Conds). We mostly overcame that
by replacing each index with a series of partial ones, each with
WHERE enum_column = ...

We also had some functional indexes that either explicitly used a
non-leakproof built-in function, or implicitly exercised a non-leakproof
conversion function such as textin or int8out. Here we ended up cheating
the system by adding LEAKPROOF wrappers around the built-in functions,
similar to [5].

The most difficult thing to overcome was GIN indexes on bigint[]
columns not being used because the built-in arrayoverlap/arraycontained/
array_eq/... functions aren't LEAKPROOF. We eventually figured out that
we could make our own version of gin_array_ops which used functions
marked as LEAKPROOF, and then recreate all the GIN indices with that.

My hunch is that there's a big class of applications like ours where
enforcing LEAKPROOFness isn't really important. When application code is
interacting with the database via its own role, you're not that
concerned with invisible tuples leaking out via error messages or
elaborate timing attacks. That seems more relevant when your
adversaries have direct access to the database and are able to create
their own functions and execute arbitrary queries. I think it would be
extremely useful to be able to forefeit the LEAKPROOF guarantees when
the database is being accessed by an application in exchange for
avoiding the footguns described above.

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 took a stab at implementing solution 1) above, mostly to understand if
I had found the right places in the code and how hard it'd be. This is
my first time hacking on Postgres, and I'm not really sure if it's OK to
introduce this extra check in a possibly hot code path. I thought about
instead adding a "does the current user have bypassleakproof" flag in
some struct available to the planner, but this is beyond my current
abilities. Also, it's probably better to get some feedback on the idea
before spending too much time optimizing.

What do you think?

Best regards,
Andreas Lind

[1] https://www.postgresql.org/message-id/2811772.0XtDgEdalL%40peanuts2
[2] https://pganalyze.com/blog/5mins-postgres-row-level-security-bypassrls-security-invoker-views-leakproof-functions
[3]
https://stackoverflow.com/questions/63008838/postgresql-ignores-pg-trgm-gin-indexes-when-row-level-security-is-enabled-and-no
[4] https://www.postgresql.org/docs/current/ddl-rowsecurity.html#DDL-ROWSECURITY
[5] https://news.ycombinator.com/item?id=20066350

Hi,

On Sat, 19 Apr 2025 18:14:01 +0200
Andreas Lind <andreaslindpetersen@gmail.com> wrote:

> Hi hackers,
> 
> Row-level security is an awesome feature. I was originally won over by
> the simple mental model of implicitly adding WHERE clauses to all
> queries, and that it generally comes for free when they can be
> satisfied by Index Conds.
> 
> I've recently helped implement row-level security in a fairly big
> multi-tenant application with about 100 tables. RLS is being used
> "voluntarily" by the application, which switches to a role that is
> subject to RLS as the first thing after starting a transaction. I
> realize that this doesn't offer bulletproof tenant isolation, but it
> still makes a lot of sense as a defense-in-depth measure.
> 
> It took some time to work through various performance regressions
> caused by selectivity estimates changing due to "WHERE tenantId = ..."
> implicitly being added to all relations in all queries. Extended
> statistics (dependencies) helped with that, as tenantId is highly
> correlated with the other id columns that we were already querying and
> joining on.
> 
> The roughest edge turned out to be the well-known challenge with the
> LEAKPROOFness of built-in functions, previously discussed in earlier
> threads [1], in blog posts [2], on Stack Overflow [3] and pointed out
> in the docs for Row Security Policies [4].
> 
> I'm aware that the usual workaround is to ALTER FUNCTION ... LEAKPROOF
> for each of the relevant built-in functions after making an evaluation
> of whether that's acceptable for the given use case. We weren't able to
> do this because we're using a hosted Postgres solution where it's not
> permitted. We've tried to talk them (AWS) into offering a way to do it,
> but they haven't budged yet.
> 
> As in the earlier thread we were affected by enum_eq not being
> leakproof, which caused our indexes involving enum columns to not be
> fully utilized while RLS is active (because WHERE clauses on the enum
> columns cannot be implemented as Index Conds). We mostly overcame that
> by replacing each index with a series of partial ones, each with
> WHERE enum_column = ...
> 
> We also had some functional indexes that either explicitly used a
> non-leakproof built-in function, or implicitly exercised a non-leakproof
> conversion function such as textin or int8out. Here we ended up cheating
> the system by adding LEAKPROOF wrappers around the built-in functions,
> similar to [5].
> 
> The most difficult thing to overcome was GIN indexes on bigint[]
> columns not being used because the built-in arrayoverlap/arraycontained/
> array_eq/... functions aren't LEAKPROOF. We eventually figured out that
> we could make our own version of gin_array_ops which used functions
> marked as LEAKPROOF, and then recreate all the GIN indices with that.
> 
> My hunch is that there's a big class of applications like ours where
> enforcing LEAKPROOFness isn't really important. When application code is
> interacting with the database via its own role, you're not that
> concerned with invisible tuples leaking out via error messages or
> elaborate timing attacks. That seems more relevant when your
> adversaries have direct access to the database and are able to create
> their own functions and execute arbitrary queries. I think it would be
> extremely useful to be able to forefeit the LEAKPROOF guarantees when
> the database is being accessed by an application in exchange for
> avoiding the footguns described above.
> 
> 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 took a stab at implementing solution 1) above, mostly to understand if
> I had found the right places in the code and how hard it'd be. This is
> my first time hacking on Postgres, and I'm not really sure if it's OK to
> introduce this extra check in a possibly hot code path. I thought about
> instead adding a "does the current user have bypassleakproof" flag in
> some struct available to the planner, but this is beyond my current
> abilities. Also, it's probably better to get some feedback on the idea
> before spending too much time optimizing.
> 
> What do you think?

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.

Even if that were the case, I believe the impact of lacking LEAKPROOFness would
still depend more on how much each function can actually leak information, rather
than on the roles accessing the data.

Therefore, if bypassing the LEAKPROOF check is allowed when RLS is enabled,
I think it would be better to make this configurable per individual policy
as in solution 3 you suggested.

I'm not a security expert, so I can't say with confidence whether the overall
idea is sound or not. For broader review and discussion, I would suggest submitting
it to the CommitFest [1].

[1] https://commitfest.postgresql.org/53/

Best regards,
Yugo Nagata

> 
> [1] https://www.postgresql.org/message-id/2811772.0XtDgEdalL%40peanuts2
> [2]
https://pganalyze.com/blog/5mins-postgres-row-level-security-bypassrls-security-invoker-views-leakproof-functions
> [3]
https://stackoverflow.com/questions/63008838/postgresql-ignores-pg-trgm-gin-indexes-when-row-level-security-is-enabled-and-no
> [4] https://www.postgresql.org/docs/current/ddl-rowsecurity.html#DDL-ROWSECURITY
> [5] https://news.ycombinator.com/item?id=20066350


-- 
Yugo Nagata <nagata@sraoss.co.jp>

Yugo Nagata <nagata@sraoss.co.jp> writes:
> Andreas Lind <andreaslindpetersen@gmail.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

Sorry for going on a bit of a tangent, but why is enum_eq not marked
leakproof when its code looks like this?

    Datum
    enum_eq(PG_FUNCTION_ARGS)
    {
        Oid            a = PG_GETARG_OID(0);
        Oid            b = PG_GETARG_OID(1);

        PG_RETURN_BOOL(a == b);
    }

The only previous discussion I see [0] points to discussion about enum_cmp,
which seems to be more obviously non-leakproof due to its use of
enum_cmp_internal().

[0] https://postgr.es/m/14749.1550679857%40sss.pgh.pa.us

-- 
nathan

Nathan Bossart <nathandbossart@gmail.com> writes:
> Sorry for going on a bit of a tangent, but why is enum_eq not marked
> leakproof when its code looks like this?

Perhaps it could be, but I'm not sure how useful that is if we don't
mark the remaining enum comparison functions leakproof.

There might be a genuine hazard if something thinks it can substitute
use of enum_cmp for enum_eq, as indeed would happen in e.g. mergejoin.

            regards, tom lane

On Mon, Jun 16, 2025 at 01:36:20PM -0400, Tom Lane wrote:
> There might be a genuine hazard if something thinks it can substitute
> use of enum_cmp for enum_eq, as indeed would happen in e.g. mergejoin.

Hm.  Wouldn't that be a mergejoin bug?  I guess I'm not sure how to
reconcile the leakproof criteria in the documentation with the idea that
we can't mark a suite of functions as leakproof if any individual one isn't
leakproof.

-- 
nathan

On Mon, 23 Jun 2025 at 09:43, Andreas Lind
<andreaslindpetersen@gmail.com> wrote:
>
> Thanks for your replies!
>
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Yugo Nagata <nagata@sraoss.co.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.

On Mon, 23 Jun 2025 09:56:19 +0200
Jelte Fennema-Nio <postgres@jeltef.nl> wrote:

> On Mon, 23 Jun 2025 at 09:43, Andreas Lind
> <andreaslindpetersen@gmail.com> wrote:
> >
> > Thanks for your replies!
> >
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > Yugo Nagata <nagata@sraoss.co.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@sraoss.co.jp>