RLS to allow SELECT only if column matches the value passed

[gabfeudo]

Hi, I'm facing an easy problem with RLS.
I would like to create a policy which allow SELECT only for rows that matches the value passed from the request (for anon key).

For example I have this table called test_table:

create table
  public.test_table (
    id bigint generated by default as identity not null,
    test_id uuid not null,
    message text not null,
    constraint test_table_pkey primary key (id),
    constraint test_table_id_fkey foreign key (test_id) references second_table (id) on delete cascade
  ) tablespace pg_default;

I would like to enable queries like this:
supabase.from("test_table").select("*").eq("test_id", "hello")

but disallow
supabase.from("test_table").select("*")

in way that anon keys can not get all the table but only rows that match the specified test_id column.

Is this possibile?

Thanks in advance!

[GaryAustin1]

It is not possible with RLS. You can't see the filter values.
You would need to lock down RLS to prevent select.
Then use an rpc call to a security definer type postgres function to do the read of 1 row. You would pass it your filter value.
Security definer functions act as the owner (usually postgres role) and so bypass RLS you have on the table allowing it to read just the one row.

[jorgecamus24]

Hello, I am making a filter to find a record ( imei = 830675132116850), this filter as a GET method returns the entire table, what am I doing wrong.
This is the code that I put in Postman raw:

let { data: validacion_imei, error } = await supabase
.from('validacion_imei')
.select("imei")
.filter('imei', 'eq', '830675132116850')

[Radiergummi]

In case anyone else ends up here — I followed @GaryAustin1's suggestion and created a RLS policy with a definer function, and that works like a charm. In my case, I have a table called auth_challenges which stores anonymous challenges used for PassKey authentication. During the Webauthn request flow, a challenge will be generated in the first API call, and verified in the second. Therefore, I need to grant access to only the row with the matching challenge!

I created the function like so:

• Name: validate_challenge
• Arguments:
  • value: varchar
• Definition:

  BEGIN
    RETURN EXISTS (
      SELECT 1 FROM authentication.challenge c WHERE challenge = value
    );
  END;

And the policy:

CREATE POLICY "Allow reading own challenges"
  ON "authentication"."challenge"
  AS PERMISSIVE
  FOR SELECT
  TO public
  USING (
    validate_challenge(challenge::varchar)
  );

Maybe that helps someone!

[VV-EE]

For me this doesn't work since if you do a statement without a WHERE clause it sets your challenge::varchar on each row, so at the end SELECT * FROM "authentication"."challenge" returns all rows.