Help me understand how inserting data into Supabase directly from the client can be safe
Hi all,
I am currently creating an app with Supabase. I understand the use case and ease of use to query supabase's data with the supabase client directly from the client. However I just can't wrap my head around how inserting data can be safe.
Let's say I have a table called 'profiles', within this profile table I store username::string, avatar_url::string and a settings::jsonb column.
Now, lets say the user wants to update its username, easy right? I call supabase.insert on the table and only supply the username, since the user passes the rls auth.uid()=profile_user_id its allowed to do so.
Now let's say the user is a developer and finds out I am using supabase, extracts the jwt token and initializes the supabase client with the anon key. Now, he can also insert random avatar_url strings within the avatar_url and even a random JSONB within the settings column. This is all allowed since RLS only secure rows.
I am aware of views, but views can't solve this problem, since views inherit it's RLS from the underlaying table so this table is still insertable by the user, so the developer can just insert into the table directly.
Anything I am missing here?
9 Replies
JWT is timed based (default 1 hour). Then you must use the refresh token (can only be used once to refresh and is "destroyed" on signout) to get a new jwt. You can set jwt expire time shorter depending on security needs.
Not an expert, but seems about the same risk as someone walking away from a logged in device for a bit. You have to get their jwt from that computer and then use it within the expire time.
Yes, but in my scenario the 'hacker' uses its own JWT token
so I am not talking about someone trying to steal/modify other ones data, but someone actively using supabase's client to insert and view data into and from columns they are not allowed to insert/view
If you allow them to do an operation and give them RLS then a logged in user can do what you allow. If you want to protect columns you can do that with before triggers or Postgres column security (which is a bit of a pain to use)
I use before trigger functions and don't allow anon or authenticated role to set updated_at, created_at, etc. by doing new.updated_at = old.updated_at on a before update trigger.
Interesting approach!
And definitely something that should be documented on Supabase docs maybe? Do you have any reference material about these trigger functions used for this specific case?
Postgres trigger functions you can just google. There is at least one example used in SB to set up your profile table when a user is inserted in the auth.users table. https://supabase.com/docs/guides/auth/managing-user-data#advanced-techniques The dashboard database UI does a reasonable job of making triggers and simple functions easy.
Managing User Data | Supabase
Securing your user data with Row Level Security.
Another approach people use is to just separate out data they can't change to it's own table and don't give update policy to them.
Yep am aware of those types of triggers, but it did not occur to me to use triggers to disallow updating columns (like updated_at and created_at). Did you create this trigger for each table, or did you create a reusable trigger? Any psql you can share?
This one does not check for role, so no one can update...
You could put something like "if auth.role() = 'anon' or auth.role() = 'authenticated' then" new... new... "endif;"
Nice. This will definitely solve the problems I have, I can probably loop over the schema and apply the trigger to every table: https://stackoverflow.com/a/60720868
Thank you