CODEWITHSUNDEEP
postgresqlsupabasesql-grantrow-level-securityrls
DanMossa
DanMossa

Reputation: 1092

Prevent user from manipulating subset of columns by RLS on view with no RLS on table

I want to use use VIEWS for column security. db<>fiddle

create table users(user_id, first_name, create_time)as values
    (1, 'Adam', 'yesterday'::timestamptz)
  , (2, 'Bob' , 'today');

create view users_view with(  security_barrier=true
                            , security_invoker=true)
as select user_id
        , first_name 
from users;

My understanding is that I can set the RLS on users and have the view with security_barrier and security_invoker.

My client can use the users_view, but this wouldn't actually prevent someone from calling the users table.

The end goal is to prevent directly manipulating the users table since there are columns that shouldn't be touched, and to be able to have RLS rules on the view that allow reads and writes to the underlying table.

Upvotes: 1

Views: 31

Answers (1)

Zegarek
Zegarek

Reputation: 26347

there are columns that shouldn't be touched

You can grant and revoke privileges on column level: demo at db<>fiddle

create table test(c1_can_modify,c2_cannot_modify)as values('a','b');
create role restricted_user;
grant    all(c1_can_modify)   on test to restricted_user;
grant select(c2_cannot_modify)on test to restricted_user;

Now the restricted_user can read all they want as well as update c1 freely. They can't insert/delete/truncate at all, or modify c2 in any way.

how can I add a specific RLS to the users_view

RLS only works on tables, you can't define one for a view or a matview.

can't use the users_view to update a value since it has to go through the users table and that has no permissions

Correct, that's exactly what security_invoker=true does - users of the view need to have their own permissions on objects accessed through the view. [demo2]

The default, security_invoker=false lets you grant access to only the view and if it qualifies as updatable, this lets the users modify the underlying objects through it, without needing direct permissions on those. [demo3]

Upvotes: 1

Related Questions