RLS filter on tenant_id runs query on all nodes
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Hello, I am trying to setup row level security to ensure that a tenant cannot read data from other tenants, I achieve this with a policy that filters on the distribution column using a local variable.
It works perfectly, except that when I run `EXPLAIN ANALYZE`, it seems to be **running the query on all shards** instead of just the one containing the tenant set in the variable.
To replicate the issue, just create a new database and run the following with the admin user (postgres):
```sql
create extension citus;
-- Create the test user
CREATE ROLE myuser WITH LOGIN PASSWORD 'ciao';
-- Create the table
CREATE TABLE products (
id SERIAL,
tenant_id INT,
name VARCHAR(255)
);
-- Insert data
INSERT INTO products (tenant_id, name)
VALUES (1, 'Product A1'),
(1, 'Product A2'),
(2, 'Product B1'),
(2, 'Product B2');
-- Distribute the table
select create_distributed_table('products', 'tenant_id');
-- Add privileges to test user
GRANT ALL PRIVILEGES ON "products" to myuser;
-- Create the policy
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_policy ON products FOR ALL TO myuser USING (
tenant_id = current_setting('app.tenant_id', false)::integer
);
```
And verify the policy logging in the test user (myuser/ciao):
```sql
BEGIN;
set citus.propagate_set_commands = 'local';
set local app.tenant_id = '1';
SELECT * FROM "products";
EXPLAIN ANALYZE SELECT * FROM "products";
```
Contributor guide
Assessment
This issue has not been assessed yet.