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 ..
See this message thread on the pgsql-general mailing list for details.
And the responses:
And:
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:There's more information in this detailed blog post too.