Supabase - Weweb Security Question

Ask us anything
1

Hello,

I have a quick question for anyone familiar - when I use Weweb with Supabase and setup collections, do my users have any way of knowing the Postgres table schemas used in the collection?

2

Yes, thatā€™s why you should secure it with RLS

3

Got it. So I have a situation where I have RLS configured and setup on a table (both read and write perms) however there are 2-3 columns where I want read-only access for the user. I think there are 2 ways to handle this

  1. Use Column Level Security on Supabase and revoke write access to those columns.
  2. Create a new table and migrate the columns to that and set appropriate RLS policies (read-only)

Is there any correct design pattern to handle this situation?

I would prefer to do 1 because it requires no migration work, any gotchas to be aware of?

1 Like
4

Column level security I tend to avoid it feels like an incomplete workaround, last time I checked it was in Beta testing or sonething like that. Iā€™d just split it.

1 Like
5

There is a Alternative to the Column level Security.

In your RLS for your WRITE
make a Assert that the NEW.collumn value = OLD.Collumn value.

This forces the RLS security on update to be only Readable

You can do this with both Security policies and with ā€œsimpleā€ check statements.

1 Like
6

Have you done this? Last time I tried to do something like this I found it was not a thing. You donā€™t get old. Only new?

One other workaround I found is creating a trigger before insert, there you have the old and new values. But that also feels bad.

Found a nice question about this in stackoverflow.

7

Assuming the rls is in place to keep users from editing other users dataā€¦ Perhaps revoke all write access to the table and then create a postgres function with a security definer (so that it can still write to the table) with an if statement that checks that the auth.uid() equals the user_id field of the row they want to write to.

8

Yeah i made it like This:

CREATE OR REPLACE FUNCTION public.validate_TaBLE_update()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.all_info_submitted <> OLD.all_info_submitted THEN
IF is_global_admin() = false THEN
RAISE EXCEPTION ā€˜Updating ā€œall_info_submittedā€ is not allowedā€™;
END IF;
END IF;

RETURN NEW;
END;
$$;

CREATE TRIGGER before_TaBLE_update
BEFORE INSERT OR UPDATE
ON TaBLE
FOR EACH ROW
WHEN (row_security_active(ā€˜TaBLEā€™))
EXECUTE FUNCTION validate_TaBLE_update();

This makes it so that only the GlobalAdmins can edit this Column