Currently, I'm using a Supabase database. One of the big roadblocks that I'm facing is column-level security, which seems a lot more complicated than RLS.
Say that I have a column called is_banned, that is viewable but not editable. However, the rest of the columns should be both editable and viewable.
The only solution that I can really think of is splitting it into two tables and having RLS on the "sensitive information" table - but creating a private table for every table seems rather unnecessary.
Are there other solutions?
I had to deal with this issue myself. I currently solve it with views, but would rather choose RLS policies, triggers or privileged functions in the future (untested, as of right now). I share the notes from my research into this issue below.
Column-level security ("CLS") means to selectively prohibit column value UPDATEs, INSERTs or even SELECTs based on certain conditions. There are several alternative solutions for this (summary), each with advantages and disadvantages. They are discussed in detail below.
(My favourite option so far, but I have not yet used it in practice.)
Here, for a CLS policy to protect against UPDATEs, you would use a row-level security (RLS) policy that retrieves the old row and compares if your protected column's field value would change from the old to the new row. A solution candidate for this has been posted as a Stack Overflow answer, but this still has to be made into a generic function.
At first glance, this seems better than a trigger: it shares its advantages and in addition, Supabase promotes the use of RLS policies for access control anyway, and has much better UI support for RLS than for triggers. So it would improve consistency and maintainability of the database by reducing complexity.
However, the Supabase RLS editor cannot be used for complex RLS policies (issue report), so as a workaround one should wrap all RLS code into a single or nested function call, or at least something no longer than one line of code. Even better is to maintain the SQL source code under version control outside of Supabase, and to copy-and-paste it into the Supabase SQL Editor whenever you want to change a RLS policy, table, function and so on.
See here for my original instructions, and then the improved instructions by Github user christophemarois.
Advantages:
Does not add another table or view, so that the database structure is determined by the data, as it should be, and not by permission system quirks.
Does not require changes to the default Supabase permissions or table-to-schema assignments.
Combined the powers of RLS policies and column-level permissions.
Disadvantages:
Triggers are not yet supported well in the Supabase UI: only the trigger status can be changed, but it cannot be shown or edited in the UI, only in the PostgreSQL console. In practice, this is not much of an issue, as for any real-life project you will have to work with the PostgreSQL database directly, anyway.
It requires knowledge of PGSQL or another programming language … and for some, programming is what we want to avoid with Supabase. However, the solution uses one abstract function that receives columns to whitelist ("changes allowed"), so no real programming is necessary, just deploying some re-usable code.
"You can hide the table behind a FUNCTION with SECURITY DEFINER. The table itself would not provide UPDATE access, instead users can only update the table through the FUNCTION." (source)
And in that function, you can determine column-level access permissions in any way you like. Any such function in schema public is automatically available through the API:
"write PostgreSQL SQL functions […] and call them via
supabase.rpc('function_name', {param1: 'value'});." (source).
The issue is, however, that the API then no longer has a unified structure of "everything is available in tables".
See the instructions. More instructions:
"You can create a view to only show the columns you want, make sure you secure with a
WHEREstatement as it ignores RLS (normally), and then use RLS to block the original table." (source)
This solution has been recommended by a Supabase maintainer. In total, RLS policies and triggers seem preferable, though.
To make this solution secure, you have to use option security_barrier = on (details), which can severely impact view performance. The only way around that is to not use a WHERE clause and instead to re-use RLS policies of the base table via security_invoker = on. That requires moving the base table to a custom database scheme that is not exposed by API (see below).
Advantages:
Simple. Views are just like tables, and everyone knows PostgreSQL tables – in contrast to triggers or (complex) RLS policies.
You see what you edit. Users (or their applications) who can see records in the table do not have to worry if they are editable due to RLS policies. Whatever a user can see, they can edit.
❓ Extendable as needed. (Still unsure about this.) Only the columns a certain user is allowed to edit can be provided in the view. To find the right column, sometimes more context is needed. Not a problem: join the view and columns from the underlaying base table again as needed, at API access time. Only the surrogate primary key column id needs to be always included into the view; this is not an issue: if a user tries to edit it, it can only succeed when using new values, in which case effectively a new record is created, which the user is probably allowed to do anyway. (To be confirmed that updates with proper access protection are then still possible.)
Disadvantages:
Cluttering the table space. Ideally, the API would expose the data in the form they have in a proper database design. By exposing additional views, the API becomes unnecessarily complex.
Can not really reuse RLS policies of underlaying table. To be done by using security_invoker = on when creating the view (details). However, when doing this, the same user that can, say, update a record through the view can then also update that record in the base table, circumventing the column access restrictions for which the view is used. The only way around that would be to move the base table to a custom database scheme that is not exposed by API. That is possible, but adds yet more structural complexity.
Needs changes to the default view permissions. Since these are simple views, they are "updateable" views in PostgreSQL. Together with the default table-level / view-level permissions in Supabase schema public this means that all users, even anonymous ones, can delete records from these views, leading to the deletion of records in the underlaying tables.
To fix this, one has to remove the INSERT and DELETE privileges from the view. This is a change to the default Supabase permissions that would ideally not be necessary.
There is an alternative solution, but it is not very practical: you can create the views with security_invoker = on to reuse the RLS policies of the underlaying table. Then use these RLS policies to prevent record deletion. However, they have to allow SELECT and UPDATE; so unless you move the underlaying table to a schema not exposed by API, it would allow users to circumvent the column-level security for which the views were created.
No good way to restrict the use of certain values in a column to certain users. This is because views cannot have their own RLS policies. There are several ways to work around this:
Probably the best way to work around that is to structure tables so that a user with write access to a column is allowed to use every value in that column. For example, instead of columns role (user, admin) and status (applied, approved, disapproved), there would be nullable boolean columns user_application, admin_application, user_status, admin_status.
Another option, for complex cases, is to move the underlying table to a custom schema that is not API accessible (while still granting USAGE and permissions to all Supabase roles; see), to create RLS policies on that underlying table, and to re-use them in the views via security_invoker = on.
Another option, also for complex cases, is to use triggers on the view or the underlaying table.
"You can provide UPDATE access to only a subset of columns of the table: GRANT UPDATE(col1, col2). (details)" (source)
Reportedly, it is a hassle to maintain all these access rights. And in Supabase, it would not be possible to give different permissions to different authenticated users, as all of them access the database using the same role authenticated. When working on the PostgREST level, you could have different options here, though.
Compared to views, this splits the main table into multiple parts. Using RLS policies, it is defined who can do what with each partial table; and, different from views where you can only partially emulate RLS policies in a WHERE clause, a RLS policy can also be used to limit which values a user can use for a column. To use them together, they have to be joined in requests.
Quite ok when splitting a table in two. But sometimes the splitting is almost "one table per column", for example for permission management tables with one column per role. This is bad because it "atomizes" the data rather than keeping it in a proper normal form, meaning that the data is not even accessible to admins in a comfortable way. That can be solved with views that combine the split-off tables again and provide write access to these underlaying tables. But still, a lot of tables to deal with. It's "ugly".