Supabase Needs Column Level Security

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

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

CREATE POLICY "rls_posts_read_public"
  ON public.posts FOR SELECT
  USING (true);

INSERT

CREATE POLICY "rls_posts_create_own"
  ON public.posts FOR INSERT
  WITH CHECK (auth.uid() = author);

DELETE

CREATE POLICY "rls_posts_delete_own"
  ON public.posts FOR DELETE
  USING (auth.uid() = author);

UPDATE

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.

CREATE ROLE moderator;

GRANT

Creates privileges

-- 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

-- 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
4. service_role (admin)

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

-- 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:

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

SELECT rolname FROM pg_roles ORDER BY rolname;

View privileges by role

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.

Imagine seeing options like Hasura has here:

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. 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

Update: 7/31/23 - Supabase is now working on this as a new Dashboard!!!

• PR - Add Column-level privileges
• Privileges Video

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:

CREATE VIEW public_posts AS
  -- List all columns except "title"
  SELECT column1, column2, column3, ...
  FROM posts;

and on Supabase:

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). Your View also can't use RLS, unless you add WITH (security_barrier), which is known to give slow performance. 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

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:

const { data, error } = await supabase.rpc('get_posts');

Now something problematic happens with RPC functions. If you wanted to join a table, postgREST, which is what Supabase uses under the hood, would automatically detect fk relationships.

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:

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 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.

J

Comments

[Ivens Joris]

Here are some tools you made need while tweaking with this:

i think the made is may, a typo error on the article

[Jonathan Gamble]

Fixed. Thanks!