3 ms·

The owner of the view is taken as the identity for RLS purposes when determining what data is seen in the underlying table(s). So if you select from a view wit

The owner of the view is taken as the identity for RLS purposes when determining what data is seen in the underlying table(s). So if you select from a view with different users, you always get the same results- whatever the owner of the view would see.

xemoka 5y ago
That is not the case. At least not in postgres 13. The owner of the view only determines if RLS is bypassed or not. If the view is owned by a different user than the table (or `FORCE ROW LEVEL SECURITY`), then the view will evaluate RLS. RLS is bypassed for the owner of the table, superusers, and users with `BYPASSRLS` attribute. We can test this. ```sql create role data; create role api; create role bob; create role alice; create schema api_v1 authorization api; create schema data authorization data; set role data; create table data.user_profile ( id int generated always as identity primary key, username text, value text ) grant usage on schema data to api; grant select on data.user_profile to api; insert into data.user_profile (username, value) values ('bob', 'Bob''s First Value'), ('bob', 'Bob''s Second Value'), ('alice', 'Alice''s First Value'); create policy "Users can only see their own data" on data.user_profile for select using (current_user = username); alter table data.user_profile enable row level security; select * from data.user_profile; -- shows 3 record, both bob and alice's set role api; create view api_v1.user_profile as select * from data.user_profile; grant usage on schema api_v1 to bob; grant usage on schema api_v1 to alice; grant select on api_v1.user_profile to bob; grant select on api_v1.user_profile to alice; select current_user; -- currently api select * from api_v1.user_profile; -- shows 0 records set role bob; select * from api_v1.user_profile; -- shows 2 records, both bob's set role alice; select * from api_v1.user_profile; -- shows 1 record, alice's. -- both alice and bob cannot access the data schema or it's contents. RLS is respected for the api_v1 schema. ``` Does that make sense, or did I misunderstand your meaning?

xemoka 5y ago

That is not the case. At least not in postgres 13. The owner of the view only determines if RLS is bypassed or not. If the view is owned by a different user than the table (or `FORCE ROW LEVEL SECURITY`), then the view will evaluate RLS. RLS is bypassed for the owner of the table, superusers, and users with `BYPASSRLS` attribute. We can test this. ```sql create role data; create role api; create role bob; create role alice; create schema api_v1 authorization api; create schema data authorization data; set role data; create table data.user_profile ( id int generated always as identity primary key, username text, value text ) grant usage on schema data to api; grant select on data.user_profile to api; insert into data.user_profile (username, value) values ('bob', 'Bob''s First Value'), ('bob', 'Bob''s Second Value'), ('alice', 'Alice''s First Value'); create policy "Users can only see their own data" on data.user_profile for select using (current_user = username); alter table data.user_profile enable row level security; select * from data.user_profile; -- shows 3 record, both bob and alice's set role api; create view api_v1.user_profile as select * from data.user_profile; grant usage on schema api_v1 to bob; grant usage on schema api_v1 to alice; grant select on api_v1.user_profile to bob; grant select on api_v1.user_profile to alice; select current_user; -- currently api select * from api_v1.user_profile; -- shows 0 records set role bob; select * from api_v1.user_profile; -- shows 2 records, both bob's set role alice; select * from api_v1.user_profile; -- shows 1 record, alice's. -- both alice and bob cannot access the data schema or it's contents. RLS is respected for the api_v1 schema. ``` Does that make sense, or did I misunderstand your meaning?