Question 113946 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql: remove ability to query every row in a table

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.

Tags:

3

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    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 public.users
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.

    Login or Signup to reply.
  3. 0

    I had the exact same problem in several tables, and I wanted to use the supabase database client, to mantain the ability to query, sort, paginate, etc.

    Looking at the supabase documentation, I saw that you could access the request headers at the RLS, with a function (https://supabase.com/docs/learn/auth-deep-dive/auth-policies, user_agent example to access the browser user-agent)

    So, I create a function to get a custom header element, in my case named "query_data":

    create or replace function get_header_query_data()
    returns text
    language sql
    as $$
      select nullif(current_setting('request.headers', true)::json->>'query-data', '')::text;
    $$;

    In the tables with user_id, I add the following RLS for select:

    (get_header_query_data() = user_id)

    So, with this elements, I can query with the supabase client, only adding in the header the parameter ‘query-data’ with the id of the user I want to select, and I can still use aditional queries, paginate the results, sort, etc.

    The only thing I miss if that I need to create a new supabase client each time, to pass the proper headings, like so:

    const supabase = createClient(supabaseUrl, supabaseAnonKey, {
  global: { 
    headers: {
      query-data: 'user-name-example',
    },
  }
});

    I don’t know how to change the headers dynamically.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search