I'm struggling with a basic row-level security problem.
I'm first trying to create a "tenants" table which each row can only be seen by the tenant itself, before applying similar rules to other tables referencing the tenants primary key.
I'm using session variables in my policy, and I also want to enforce tenant UUIDs from the client (not generated by the server itself).
So at first I create my table:
CREATE TABLE tenants (
tenant_id UUID,
name text UNIQUE,
PRIMARY KEY("tenant_id")
)
Then I enable RLS and a policy enforcing usage of a given session variable:
ALTER TABLE tenants ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenants_isolation_policy ON tenants
USING (tenant_id = current_setting('my.tenant')::UUID)
WITH CHECK (tenant_id = current_setting('my.tenant')::UUID)
Then I insert 3 entries in tenants table:
INSERT INTO "tenants" ("tenant_id", "name") VALUES ('9c8e4f83-c036-4fcc-a775-228887d20851', 'Tenant 1');
INSERT INTO "tenants" ("tenant_id", "name") VALUES ('1953be83-683e-4960-a689-db8d53ba8cd2', 'Tenant 2');
INSERT INTO "tenants" ("tenant_id", "name") VALUES ('064767f7-9541-4492-9a7d-0466ac94e2ec', 'Tenant 3')
Then I impersonate Tenant 1:
SET my.tenant = '9c8e4f83-c036-4fcc-a775-228887d20851'
Now I expect that if I select all tenants, I should only have one row as a result.
However this is not what happens:
SELECT * FROM tenants
I get :
| tenant_id | name |
|---|---|
| 9c8e4f83-c036-4fcc-a775-228887d20851 | Tenant 1 |
| 1953be83-683e-4960-a689-db8d53ba8cd2 | Tenant 2 |
| 064767f7-9541-4492-9a7d-0466ac94e2ec | Tenant 3 |
What am I missing here ? How could policy not have been called?
Actually I don't even understand that my inserts in tenants work while I have not set any session variable yet. It just feels like RLS policy is just not taken into account.
postgresdatabase user?