CODEWITHSUNDEEP
postgresqlrow-level-security
Benjamin Barrois
Benjamin Barrois

Reputation: 2686

Postgresql simple Row-Level Security (RLS)

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.

Upvotes: 3

Views: 1061

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246308

There are several exceptions from row-level security:

  • by default, the table owner is excepted

  • any superuser is excepted

  • any user with BYPASSRLS is excepted

  • if the parameter row_security is turned off, row-level security is disabled

From your example, it looks like you fall into the first category. To enable row level security also for the table owner, run

ALTER TABLE tenants FORCE ROW LEVEL SECURITY;

Upvotes: 7

Related Questions