0

I am bulding a supabase app. Instead of a login every user gets a uuid (or multiple, if he wants to). Everyone that knows the uuid has full acces to the data behind this uuid.

So basically the database is open to anyone, as long as you use any valid uuid to write/read your stuff.

Now the problem: I don't want users being able to select all entries in the table. I want to enforce that every query has a condition where id = xxx. Of course I could do this in my app, but it is not enough, since we should never trust a client... I need to enforce this in the backend (i.e. in postgrest/supabase).

In firestore the read permission is broken down in get and list, so I can just allow get and disallow list and I am good to go. Is there something similar in postgrest/supabase?

Frank van Puffelen
  • 418,229
  • 62
  • 649
  • 645
Tagas
  • 1,114
  • 9
  • 24

1 Answers1

1

I want to enforce that every query has a condition where id = xxx.

This is exactly what a PostgreSQL RLS policy would do.

You have some examples on the supabase docs:

https://supabase.io/docs/guides/auth#policy-examples

Steve Chavez
  • 387
  • 4
  • 6
  • thx @steve-chavez, I already checked these out, but I dont see how I can solve my problem with RLS. My attempt: `create policy "can select only 1 result at a time" on board for select using (id LIKE '_%')`. So I want that the user _must_ pass an id to the query. But this doesnt work, cannot use LIKE operator on uuid... (id is a uuid) – Tagas May 05 '21 at 21:18
  • 1
    You could cast the uuid to text, like: `using (id::text LIKE '_%')`. – Steve Chavez May 07 '21 at 00:14
  • ok thx, with the cast the policy is valid, however it doesn't do what i want... I want that the user MUST pass an id for all queries! He should not be able to just `select * from xxx`! A query should only work if an id is passed, like e.g. `select * from xxx where id='123'`... How can I do this? – Tagas May 07 '21 at 09:10
  • 1
    Hm, I see what you mean. Perhaps this is not a task for RLS. We have an upcoming feature in PostgREST that I think would solve that, see the proposal [here](https://github.com/PostgREST/postgrest/pull/1710#issuecomment-808788905). As you mention, it would enforce that a condition is present for the query to succeed. – Steve Chavez May 10 '21 at 01:55
  • @Tagas The way to accomplish that right now, would be to create SQL functions that enforce the presence of the condition. And only grant access to clients to these functions - not grant clients access to the tables. Calling functions through [rpc](https://supabase.io/docs/reference/javascript/rpc) makes them pretty flexible, they support the same filters as tables. Let me know if that would work for you and I'll post another answer with an example. – Steve Chavez May 10 '21 at 17:41
  • Thanks, I guess that could do the trick.. Too bad I just saw your response now, and in the meantime I already decided to build an auth system after all not what I originally intended, but it solved most of my problems, so I guess it was worth the extra effort. Anyhow, thanks for your help! – Tagas May 17 '21 at 09:46