citusdata / citusdata/citus

RLS filter on tenant_id runs query on all nodes

Open
#7,415 0 comments 0 reactions 0 assignees View on GitHub
needs-investigation RLS
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.