News for hackers. Stuff that matters.
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?