Re:Zero-downtime schema migrations in Postgres using views
by brycelarkin on Wednesday June 16, 2021 @04:20PM
(#27533025)
I think views are great way of abstracting out business logic. The one thing keeping me from using it more frequently is that it doesn’t work well with Row Level Security.
[ Parent
| on HN ]
Re:Zero-downtime schema migrations in Postgres using views
by xemoka on Wednesday June 16, 2021 @05:22PM
(#27533643)
I was having issues with this as well; it seemed my problem was that I was creating the view as a superuser. Since view creation is essentially WITH SECURITY DEFINER (to take a term from UDFs) any user accessing the view to see the underlying rls enabled table would see all the results (since `postgres` user bypasses all RLS).
Creating a separate "data owner" and "api owner" to own the api and data schema+tables/views respectively alleviates this problem. Your API schema owner != your table owner unless you `ALTER TABLE FORCE ROW LEVEL SECURITY` in addition to just enabling it.
What issues are you running into with RLS?
Re:Zero-downtime schema migrations in Postgres using views
by corey_moncure on Wednesday June 16, 2021 @07:32PM
(#27534688)
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.
Re:Zero-downtime schema migrations in Postgres using views
by xemoka on Thursday June 17, 2021 @12:07PM
(#27541172)
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?
Re:Zero-downtime schema migrations in Postgres using views
by ftc on Thursday June 17, 2021 @03:12AM
(#27536924)
You probably already know this, but in case you don't a workaround for views not respecting RLS is to use an SQL function and then create a view which selects the fields from the function. Definitely more awkward than creating a view from a table without RLS though.
CREATE OR REPLACE FUNCTION my_view() RETURNS TABLE (...columns) AS $$
SELECT ...;
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_view();