Search Postgresql Archives

Re: Index selection issues with RLS using expressions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Tom,

This is the solution I went with.

create policy X on tableX for select to new_role using ( has_table_read_permission(tableX.column) );

This covers all usage of the table and then for APIs that utilise leaky operators:

create function with_leaky_operator(args) returns setof tableX as
$$
    select * from tableX where column @@ $1 and has_table_read_permission(tableX.column);
$$ language sql security definer;

Best regards,

Alastair



From: Alastair McKinley <a.mckinley@xxxxxxxxxxxxxxxxxxxx>
Sent: 31 March 2020 22:09
To: Tom Lane <tgl@xxxxxxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Index selection issues with RLS using expressions
 
Hi Tom,

Thanks for looking at this!  It seems like there are quite a few performance gotchas around leaky operators and RLS, this is my second encounter with this issue in the last few weeks.

What would you recommend as a reasonable workaround? 

I have a large table with a gin index that I would like to use RLS on and use the @@ text search operator.  My initial thought is to use a security definer set-returning function that implements the RLS policy explicitly.  Would a security barrier view also potentially work?

Best regards and thanks again,

Alastair



From: Tom Lane <tgl@xxxxxxxxxxxxx>
Sent: 31 March 2020 20:18
To: Alastair McKinley <a.mckinley@xxxxxxxxxxxxxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Index selection issues with RLS using expressions
 
Alastair McKinley <a.mckinley@xxxxxxxxxxxxxxxxxxxx> writes:
> I am running in to an issue with RLS and index selection in my queries.  I created a toy example to try to illustrate the issue below.  Postgres version is PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit.

> Is there some subtle reason as to why the role "new_user" cannot seem to generate a query plan that uses the gin index?

The && operator is not marked leakproof, so it can't be applied till
after the RLS filter, making an indexscan with it impossible when
RLS is active.

Perhaps arrayoverlap() itself could be proven leakproof, but the
underlying type-specific equality operator might or might not be.
We don't have enough infrastructure to handle indirect leakproofness
requirements like that, so you lose :-(

                        regards, tom lane

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux