Row level security(RLS) performance is significantly slower in postgres.

Question

Description : Here is the sample demonstration of the performance issue.

We first created two tables , enabled row level security and created policy as well .

Table definition:

create table sample_schema.sample_table1(ID numeric(38) PRIMARY KEY NOT NULL,
                 tenant_id VARCHAR(255) NOT NULL,
                 Description VARCHAR(255)
        );

create table sample_schema.sample_table2(ID2 numeric(38) PRIMARY KEY NOT NULL,
                 tenant_id VARCHAR(255) NOT NULL,
                 table1_id numeric (38),
                 Description2 VARCHAR(255)
        );    

Index creation:

CREATE UNIQUE INDEX sample_table1_idx1 ON sample_schema.sample_table1(tenant_id,id);            

Enable row level security:

ALTER TABLE sample_schema.sample_table1 ENABLE ROW LEVEL SECURITY;   

create role:

CREATE ROLE tenant_grp_role_p_id;    

Create policy:I want a policy to select data where tenant_id column value has a role that is same as the user who has logged in.

CREATE POLICY Tenant_Roles ON  sample_schema.sample_table1 TO tenant_grp_role_p_id USING ((tenant_id) IN ( SELECT rolname FROM pg_roles WHERE    pg_has_role( current_user, oid, 'member')));

create sample data:

insert into sample_schema.sample_table1 values (1,'user1_tenant1',1,'Table1 Data');
insert into sample_schema.sample_table2 values (2,'user1_tenant1',1,'Table2 Data');

Problem:below query is not using primary_key index .

SELECT * FROM sample_schema.sample_table1 ST1,  sample_schema.sample_table2 T2 WHERE ST1.id = ST2.table1_id  AND ST1.id = 1;    

Question:If I disable RLS then primary key index is used .why is it not using primary key index scan when RLS is enabled?

Note :
A.If I disable the row level security and run the above query ,it uses the index.
B.below is the explain plan Output when low level security is disabled .

Nested Loop  (cost=0.29..19.19 rows=1 width=1129)  ->  Index Scan using sample_table1_pkey on sample_table1 st1  (cost=0.29..8.30 rows=1 width=37)
    Index Cond: (id = '1'::numeric)  ->  Seq Scan on sample_table2 st2  (cost=0.00..10.88 rows=1 width=1092)        Filter: (table1_id = '1'::numeric);    

C.if I enable the low level security and run the query it does not use index.
and below is the explain plan Output when low level security is enabled.

 Nested Loop  (cost=1.03..946.65 rows=79 width=1129) ->  Seq Scan on sample_table2 st2  (cost=0.00..10.88 rows=1 width=1092)  Filter: (table1_id = '1'::numeric)  ->  Subquery Scan on st1  (cost=1.03..934.98 rows=79 width=37)
    Filter: (st1.id = '1'::numeric)        ->  Hash Join  (cost=1.03..738.11 rows=15750 width=37)              Hash Cond: ((st1_1.tenant_id)::name = pg_authid.rolname)              ->  Seq Scan on sample_table1 st1_1  (cost=0.00..578.00 rows=31500 width=37)              ->  Hash  (cost=1.01..1.01 rows=1 width=68)                    ->  Seq Scan on pg_authid  (cost=0.00..1.01 rows=1 width=68)                          Filter: pg_has_role("current_user"(), oid, 'member'::text);   

Please help me resolve this issue ..

Answer 1

See this message thread on the pgsql-general mailing list for details.

I recently applied RLS to several large (several million rows) tables in my 9.5 database and noticed that queries against a single large RLS protected table perform well however queries that join several large RLS protected tables perform very poorly. The explain plan shows the optimizer is scanning the entire table to enforce the RLS policy before executing the primary key join that would reduce the query results to a single row from each table. Clearly performance would be better if it performed the join before the policy check.

From what I can understand the RLS implementation strives to execute policy checks before user provided predicate checks so as to avoid leaking protected data.

And the responses:

Join cases with RLS aren't optimized very well at the moment. There's work afoot to improve this - see https://www.postgresql.org/message-id/flat/8185.1477432701%40sss.pgh.pa.us - but it won't be in production before v10.

And:

You can use a security barrier view which is owned by the same user that the tables underneath are owned by, that will bypass RLS on the tables themselves and therefore you'll need to implement the appropriate quals in the security barrier view.

So you could wait for PG10, or try using a security barrier view instead. That blog post also explains why Postgres doesn't try to combine (and optimise) the security conditions and the user-specified conditions: a custom function can be used to leak values that would otherwise be hidden from the user.

To create such a view, just add with (security_barrier) to the definition:

rhaas=# create or replace view unclassified_emp with (security_barrier) as
        select * from emp where organization <> 'CIA';
CREATE VIEW

There's more information in this detailed blog post too.