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.

• I could create RLS on the users table with no policies, but then how can I add a specific RLS to the users_view?

• Would this also mean that I can't use the users_view to update a value since it has to go through the users table and that has no permissions?

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.

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.

• I could create RLS on the users table with no policies, but then how can I add a specific RLS to the users_view?

• Would this also mean that I can't use the users_view to update a value since it has to go through the users table and that has no permissions?

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.

• postgresql
• supabase
• sql-grant
• row-level-security
• rls

• 1 since there are columns that shouldn't be touched Why don't you revoke the UPDATE permissions for these columns? – Frank Heikens Commented Feb 17 at 18:29

1 Answer 1

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]