Supabase Needs Column Level Security
Tags: supabase, webdev, postgres, database
Published: 2023-07-03 00:37:50

Postgres is wonderful when it comes to security. Unlike mySQL, Postgres has built-in security measures for rows and columns. Let's take a look:

## Row Level Security (RLS)
First, you must enable it for each table.
### ENABLE
```sql
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
```
RLS allows you to create sql policies for **CRUD** operations on individual tables, or more explicitly `INSERT`, `SELECT`, `UPDATE`, and `DELETE`.

### USING
- The `USING` keyword will actually filter existing values in a table. This is good for `SELECT`, `DELETE`, and `UPDATE` statements. You won't have a before value in an `INSERT`.

### WITH CHECK
- The `WITH CHECK` keywords will actually filter values going in a table. This is good for `INSERT` and `UPDATE` statements. With an `UPDATE` statement, you actually have a before and after value, so you need both.

### SELECT

```sql
CREATE POLICY "rls_posts_read_public"
  ON public.posts FOR SELECT
  USING (true);
```
### INSERT
```sql
CREATE POLICY "rls_posts_create_own"
  ON public.posts FOR INSERT
  WITH CHECK (auth.uid() = author);
```
### DELETE
```sql
CREATE POLICY "rls_posts_delete_own"
  ON public.posts FOR DELETE
  USING (auth.uid() = author);
```
### UPDATE
```sql
CREATE POLICY "rls_posts_update_own"
  ON public.posts FOR UPDATE
  USING (auth.uid() = author)
  WITH CHECK (auth.uid() = author);
```
These policies are really filters. Imagine Postgres under the hood will filter values you ask for. If the values you ask for are still there, you can view, update, or delete them. Then imagine Postgres will filter values you try and add or update. If the values are still there after they are filtered, Postgres will allow you to add or update them. Remember, with updating you have to filter the current value and the new value.
### Note
I like naming these policies with keywords like **read, write, update, delete, insert, create, own, group, role, admin, public, restricted, limited, temp**, etc with a pattern like **rls_** + **table_name_access_type**, but that is just me. I like simplicity and patterns. You can equally use more descriptive sentences.

## Column Level Security

Column Level Security does not exists in Supabase. I wish it did. You can, however, achieve similar results by using Postgres roles.

### CREATE ROLE
Create whatever role you want.
```sql
CREATE ROLE moderator;
```
### GRANT
Creates privileges
```sql
-- GRANT operation (columns) ON table_name TO role
GRANT SELECT (id, title, author) ON public.posts TO moderator;
GRANT UPDATE (id, title, author) ON public.posts TO moderator;
...
```
### REVOKE
Remove privileges
```sql
-- REVOKE operation ON table_name FROM role
REVOKE SELECT ON public.posts FROM moderator;
```

Supabase has a few noteworthy roles (keep in mind this could change):

1. **anon** (public)
2. **authenticated**
3. **authenticator** 
3. **service_role** (admin)

So, to hide the `title` column of a post from public viewing, you could do this:

```sql
-- You have to remove privileges to change them
REVOKE SELECT ON public.posts FROM anon;
-- add all values you want, except title
GRANT SELECT (id, content, created_at, updated_at...)
  ON public.posts TO anon;
```
However, instead of filtering the result, it will just throw an error if you try and get the title: 
> permission denied for table posts

I wish it just filtered personally.

Keep in mind, if you update the `authenticated` or `authenticator` role, you may have to log out and log back in for the JWT to refresh, depending on your setup.

### auth.users

Now, if you wanted to allow certain users to have certain roles, you could simply update that user in the `auth.users` table on the `role` column:

```sql
UPDATE auth.users 
  SET role = 'moderator'
  WHERE id = '0x123';
```
You will have to create the role and grant privileges on every table you want that role to have access to, which could be cumbersome.

Here are some tools you may need while tweaking with this:
### View all roles
```sql
SELECT rolname FROM pg_roles ORDER BY rolname;
```
### View privileges by role
```sql
SELECT table_schema, table_name, string_agg(column_name, ', ')
AS allowed_columns
FROM information_schema.columns
 -- Exclude system schemas
WHERE table_schema NOT LIKE 'pg_%'
  AND table_schema != 'information_schema'
  AND table_name NOT LIKE 'pg_%'
  AND table_name NOT LIKE 'sql_%'
  AND column_name IN (
      SELECT column_name
      FROM information_schema.role_column_grants
      --- Put your role here
      WHERE grantee = 'authenticator'
  )
GROUP BY table_schema, table_name;
```
As you can see, this can get complicated, and Supabase could change the way they do this in any new version. 

## What Supabase Needs
Supabase needs a way to toggle what authenticated users and public users can see, simply. Of course, if they made a way to add roles through the UI, that would be incredible too. The main thing is, to have this built-in and standardized for Supabase.

![Table Update](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dlygdx2l5n1r7ghw6i0y.png)

Imagine seeing options like Hasura has here:

![Hasura](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2gojtw6l3nshn0y0csuy.png)
That would be cool!

## Supabase team, where art thou?

What grinds my gears it that someone actually started this process with a simple pull request: [ADD CRUD permissions](https://github.com/supabase/postgres-meta/pull/491). Someone from Supabase said, "sure, no problem, we will work on this next week"... and nope, nothing. Then the PR was randomly closed yesterday! Why? No idea, no explanation. Granted, this was a simple PR and they could be rethinking an easier way to do this. But I digress.

**Update:** So the person that did this PR was not from the Supabase Team. However, I still feel this should be added, so I hope they're looking at this:
- [Assigning PG Roles to Users](https://github.com/orgs/supabase/discussions/11948)

**Update: 7/31/23** - Supabase is now working on this as a new Dashboard!!!
- [PR - Add Column-level privileges](https://github.com/supabase/supabase/pull/13745#issuecomment-1652963538)
- [Privileges Video](https://user-images.githubusercontent.com/6322901/232219207-8ff4ef91-fd02-4c47-8c59-4b03c6eeb8f5.mp4)

## Other Work Arounds
There are simple work arounds for other operations, depending on what you want to do.
### SELECT
So you can't use RLS to hide a column, as you can only hide all columns or none. You're filtering the rows, not columns.
#### Views
You could create a view:
```sql
CREATE VIEW public_posts AS
  -- List all columns except "title"
  SELECT column1, column2, column3, ...
  FROM posts;
```
and on Supabase:
```js
const { error, data } = await supabase.from('public_posts')
  .select('*');
```
Of course your original `posts` table should be blocked using RLS. To me this is a horrible work around. You can't edit your original table schema when you have a View (its locked), and Views are not even indexed (except [materialized views](https://www.postgresql.org/docs/current/rules-materializedviews.html)). Your View also can't use RLS, unless you add `WITH (security_barrier)`, which is known to give [slow performance](https://www.postgresql.org/docs/current/rules-privileges.html). Not a fan.
#### Custom Functions
You could also use a custom function. You would need to block `posts` with RLS, and add `SECURITY DEFINER` to your function so that this function can view it. It also requires you to do more advanced things like translating an RLS policy inside the function. All you would really be doing is a filter.

- `SECURITY INVOKER` - Privileges of user that calls it (default)
- `SECURITY DEFINER` - Privileges of user that owns it

```sql
DROP FUNCTION IF EXISTS get_posts;
CREATE OR REPLACE FUNCTION get_posts()
  RETURNS TABLE(
  -- Define table return types
  )
  SECURITY DEFINER
  AS $$
  BEGIN
    --- Put your RLS filters here
    RETURN QUERY
    --- List all columns except "title"
    SELECT id, author, content, ...
    FROM posts;
  END;
$$ LANGUAGE plpgsql;
```
and on Supabase:
```js
const { data, error } = await supabase.rpc('get_posts');
```
Now something problematic happens with RPC functions. If you wanted to join a table, [postgREST](https://postgrest.org/en/stable/), which is what Supabase uses under the hood, would automatically detect fk relationships.
```js
const { error, data } = await supabase.rpc('get_posts')
  .select('*, author(*)');
```
However, this only works if you return a `SETOF posts`. Since you're return posts minus the *title* column, this won't work. So, you would have make the join inside your function. This can get complicated super quickly. If you love SQL, no problem, but we want things maintainable. I still prefer this to Views personally.

So, the best way is hands down to use `GRANT`.

### DELETE
You don't really need to filter a column to delete, so this is fine.

### INSERT, UPDATE
One of the most notable ways to protect a column here is to use a trigger function:
```sql
CREATE OR REPLACE FUNCTION prevent_title_change()
  RETURNS TRIGGER
  AS $$
  BEGIN
    IF NEW.title <> OLD.title THEN
      RAISE EXCEPTION 'Changing the title column is not allowed.';
    END IF;
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql
CREATE TRIGGER prevent_title_change_trigger
BEFORE INSERT OR UPDATE ON your_table
FOR EACH ROW
EXECUTE FUNCTION prevent_title_change();
```
This will throw an exception, although you could easily just set the new value to the old value. See my [Date Protection](https://dev.to/jdgamble555/supabase-date-protection-on-postgresql-1n91) post.

- You could also use a custom function like with the View statement, and block the original posts table with RLS. 

### Second Table
Sometimes the easiest way is to create a second table. This is common practice with a `public.profiles` table, as you're doing that anyway. The profiles table may share some data with `auth.users`, but not viewable. However, you could do this with `posts` too if you wanted to. In reality, you would probably have a `posts_meta` table that can't be updated, and maybe a `posts` table that can. You set your own RLS policies.

## Conclusion

Supabase needs to make this easy and part of Supabase. None of these options are as simple as creating new roles, and all of them add unnecessary complexity.

For more Supabase tips, see [code.build](https://code.build/t/supabase).

J