Postgres row level policy issue

Question

I am trying out the Postgres row level security feature and not being able to see it working. Not sure what I am missing.

CREATE TABLE tenants (id uuid PRIMARY KEY, name TEXT);

INSERT INTO tenants (id, name) values ('ec5e9a6b-ed71-4e41-bc1e-11dac6808e41', 'Tenant1'), ('a684edc2-19b2-40d6-b679-519a6f736981', 'Tenant2');

ALTER TABLE tenants ENABLE ROW LEVEL SECURITY ;
ALTER TABLE tenants FORCE ROW LEVEL SECURITY;

SET app.tenant_id = 'ec5e9a6b-ed71-4e41-bc1e-11dac6808e41';

CREATE POLICY tenants_policy ON tenants FOR ALL USING ( current_setting('app.tenant_id')::uuid = id );

SELECT * FROM tenants;

For the last select, I expected it to return only one row with id 'ec5e9a6b-ed71-4e41-bc1e-11dac6808e41' but it is returning both rows. What am I missing? Thank you!

Laurenz Albe · Accepted Answer · 2019-11-14 19:35:02Z

3

Your example works for me. There are a few possibilities:

The current user is a superuser.
The current user is defined with BYPASSRLS.
The configuration parameter row_security is off.

answered Nov 14, 2019 at 19:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ski Over a year ago

It was the super user (facepalm). Thank you!

Collectives™ on Stack Overflow

Postgres row level policy issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related