5

Is there a way for a user to only be able to read a document only if they have the exact document ID?

I want to avoid creating users, so the only security is a random guid saved in browser memory - settings will be saved in "settings" table with id=guid.

So when page opens it will fetch with

supabase.from('settings').select('*').eq('id', guid)

How do I secure that setting (without creating (dummy) user)

Like this in Firebase: Firebase firestore only allow read if user has exact document ID but for postgresql/supabase

Rune Jeppesen
  • 1,121
  • 13
  • 22
  • This "Is there a way for a user ..." and this "I want to avoid creating users ..." are at odds with each other. Also you have to be some user to connect to Postgres. You will need to provide a more detailed description of what you are trying to achieve. – Adrian Klaver Oct 29 '22 at 21:41
  • 1
    @AdrianKlaver with Supabase the connecting user is either `anon` or `authenticated`. The question boils down to: Is there a way to allow `anon` to read a row where the record ID is known, but prevent selecting all rows. – bummzack Nov 01 '22 at 14:22
  • That I don't know. – Adrian Klaver Nov 01 '22 at 15:06

2 Answers2

5

This is doable, but I would:

  1. Disallow all access to the table for anon users via RLS (return false from the RLS policy)
  2. Write a postgres function using security definer that takes a uuid as a parameter and only returns a single row from the table based on that parameter. (return nothing if the row doesn't exist)
  3. Call the function using the supabase .rpc() format.

Example:

create table people (id uuid primary key default gen_random_uuid(), name text);
alter table people enable row level security;
-- now, with no RLS policy, no anon or authenticated users can access the table
create or replace function get_person(person_id uuid)
  returns table (id uuid, name text) security definer 
  language sql AS $$
  select id, name from people where id = person_id;
$$;

In your client code:

const { data, error } = 
await supabase.rpc('get_person', { person_id: 'some-uuid' });
return { data, error };
Pierre Spring
  • 10,525
  • 13
  • 49
  • 44
Mark Burggraf
  • 181
  • 1
  • 3
  • That is a good solution. I was tempted to use the service-account, but this seems much better. Do you have any recommendations on how to deal with relations? Maybe create a view that aggregates the needed data and use the function/rpc to read from the view? – bummzack Nov 03 '22 at 07:18
0

Simply, you need to create a function in your current schema, which returning data from id and also you need to create only one user, that doesn't have any privilege except usage to that function.

Via this that person can use that function but can not select from your table.

For example codes: how-postgresql-give-permission-what-execute-a-function-in-schema-to-user

utrucceh
  • 1,076
  • 6
  • 11