Postgresql: remove ability to query every row in a table-CodePudding

I have the following table called "profile":

id, name, created_at

Every user ( with SupaBase ) has his own row in the "profile" table with his user_id as the key ( id )
I added RLS so that only the user can update/delete his own row but everyone else can only select the row ( anyone can see his profile )

This works great but the issue is that someone could query every single user in the table using SELECT * FROM profile or something similar. I only want people to be able to view a row if they already have their "id" or their "name" with a WHERE statement.

The only solution I see is to remove the ability to SELECT the table ( basically make it private ) and make an API that will query with the admin key ( bypasses the RLS ). That way, no one could query the whole table and the client would just call the API which would then query the data.

However, I would like to query directly from the client so I want to know if there is a different solution that doesn't require an API in between. Currently learning Postgresql so I'm probably missing something really simple.

CodePudding user response：

See Row Security Policies page, which has your exact scenario as an example:

When row security is enabled on a table (with ALTER TABLE ... ENABLE ROW LEVEL SECURITY), all normal access to the table for selecting rows or modifying rows must be allowed by a row security policy.

[...]

If no role is specified, or the special user name PUBLIC is used, then the policy applies to all users on the system. To allow all users to access only their own row in a users table, a simple policy can be used:
CREATE POLICY user_policy ON users
    USING (user_name = current_user);

CodePudding user response：

You cannot obtain the underlining query within your row level security policy, so the exact solution that you are looking for cannot be done, but there is a workaround.

You can deny all access for selecting user, and then create the following database function. security definer at the end means that this function will bypass row level security.

create or replace function get_user(user_id uuid)
returns record
language sql
as
$$
  select * from public.users where id = get_user.user_id;
$$ security definer;

With the above function in place, you can query the data like this from your client:

const { data, error} = await supabase.rpc('get_user', { user_id: 'target_user_id_to_query' })

With this, anyone can only query the user if they know their user_id, and you can still query the user directly from the client.