Using policy for "only user can update, but anyone can read" logic?

[kopi-cloud]

Started trying to figure out the policy-based security stuff (I'm new to row-level-security stuff, never tried it before).

I made a private_user_info table and public_user_info table (both in the public schema). The idea being that I would store stuff a user wouldn't want other users to see in the "private" table, and the "public" table would be for stuff anybody can read, but only the user themselves can change.

My schema definition policies looks like:

create policy "users can view/update only own private_user_info"
  on public.private_user_info
  using (auth.uid() = uuid);

create policy "users can insert only own  public_user_info"
  on public.public_user_info
  for insert
  with check (auth.uid() = uuid);
create policy "users can update only own  public_user_info"
  on public.public_user_info
  for update
  with check (auth.uid() = uuid);
create policy "users can delete only own public_user_info"
  on public.public_user_info
  for delete
  using (auth.uid() = uuid);

The private table policy seems to have worked, but the public table policy won't let me insert a row, it fails with new row violates row-level security policy for table "public_user_info".

The code for inserting into the table looks like:

export async function saveDisplayName(
  db: SupabaseClient, newValue: string
): Promise<string|ErrorInfo>{
  const result = await db.from<public_user_info>(Tables.public_user_info).
    insert({uuid: db.auth.user()?.id, display_name: newValue}, {upsert: true});

  log.debug("result of save", result);
  if( result.error ){
    return { problem: result.error, message: result.error.message};
  }
  if( result.data?.length !== 1 || !result.data[0].display_name){
    return { problem: result, message: "unexpectec data returned from db"};
  }
  return result.data[0].display_name;
}

Is the RLS policy stuff the right approach for this idea of "readable by anyone, only writable for the user"? Am I doing something wrong?

It occurs to me, maybe this is better done by moving the "display name" column to the private table and having a view that other users can query but not update (I guess by revoking privileges, never done it before)?

As well as the code being availabe, you can see or try out the app here (including the failing result from updating the display name).

[shorn]

This was my mistake - I was not defining the policies correctly.

It's worth noting the original policy configuration I had didn't work for select queries either. The select query wasn't failing but it wasn't returning rows from the DB either (as confirmed by manually adding a row using the editor).

I thought I didn't need a for select policy since I wanted any user to be able to read it. Turns out you need a select policy as soon as you enable row level security - so I added a select policy of using ( auth.role() = 'authenticated'). That worked for select queries, but my insert/update statement still didn't work.

So I added a full "for all" type policy as below, and now selects and insert/update seem to be working:

create policy "only authn users can read public_user_info"
  on public.public_user_info
  using ( auth.role() = 'authenticated' );

Edit:
Turns out the "for all" policy is not a good idea, because if multiple policies are present, they're "OR"ed together - meaning the above allows any authenticated user to update the value for any other user - which is what I'm trying to avoid.

So now I'm trying to figure out why my upsert doesn't work.

Edit 2:
The problem was the update policy, if you're going to be using "upsert" functionality, you need to specify a using clause on the update policy, like:

create policy "users can update only own  public_user_info"
  on public.public_user_info
  for update
  using ( auth.role() = 'authenticated' )
  with check (auth.uid() = uuid);