Column Mask with RLS
Hi, I've been attempting to create a column mask for one of our tables and want to check in if anyone's seen anything similar or if there are some hidden gotchas that would make this setup vulnerable.
For example, let's say we have a table 'items' with 3 columns: user_id, name, and value. We want to allow only specific authorized users to see the rows in the items table at all, so we handle that with RLS. But then, we want ONLY a subset of those authorized users to be able to see the actual 'value' column of the row. Think of making an item you own public, but only wanting to share the explicit value of that item with close friends.
My solution right now is to revoke all access from anon, authenticated to public.items, and then create a view that decides if the value column should be accessible or not. The view is owned by a new role 'view_role', that was granted SELECT permissions on public.items. The view runs with
The solution above does appear to be working, but it looks like it is potentially frowned upon in general. I know some people have suggested using multiple tables to represent different levels of visibility, but my method above appears to work without needing to manage the state between more than 1 table.
So the big question is: Am I missing something that makes the protected 'value' data visible or editable to a non-authorized user? And if my method is undesirable, is there a universally accepted method of achieving what I'm trying to do?
Thanks!
For example, let's say we have a table 'items' with 3 columns: user_id, name, and value. We want to allow only specific authorized users to see the rows in the items table at all, so we handle that with RLS. But then, we want ONLY a subset of those authorized users to be able to see the actual 'value' column of the row. Think of making an item you own public, but only wanting to share the explicit value of that item with close friends.
My solution right now is to revoke all access from anon, authenticated to public.items, and then create a view that decides if the value column should be accessible or not. The view is owned by a new role 'view_role', that was granted SELECT permissions on public.items. The view runs with
security_invoker = offThe solution above does appear to be working, but it looks like it is potentially frowned upon in general. I know some people have suggested using multiple tables to represent different levels of visibility, but my method above appears to work without needing to manage the state between more than 1 table.
So the big question is: Am I missing something that makes the protected 'value' data visible or editable to a non-authorized user? And if my method is undesirable, is there a universally accepted method of achieving what I'm trying to do?
Thanks!
