3

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.

1
  • Did you test that using the postgres database user? Commented Feb 16, 2021 at 16:26

1 Answer 1

7

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;
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, I'll definitely try having a second user for this usage

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.