CRUD example policy

[Zyles]

Can you provide CRUD example policies for a custom table?

I saw the examples for group tables and tried to apply them but am running into the following problem:

new row violates row-level security policy for table "cities"

I have a cities table:

Foreign key group_id to groups.id

And policy:

create policy "Enable insert for authenticated users only"
on "public"."cities"
to authenticated
with check (
  user_is_group_member(group_id)
);

I also tried this policy:

create policy "Authenticated can insert"
on "public"."cities"
as permissive
for insert
to authenticated
with check (true);

Error:

insert or update on table "cities" violates foreign key constraint "cities_group_id_fkey"

JS code:

  const { data, error } = await supabase.auth.signInWithPassword({
      email: '[email protected]',
      password: 'xxxxx',
    })

const { error } = await supabase.from(table).insert({ name: "City name" })

  if (error) {
    throw new Error(error.message)
  }

[Zyles]

Read policy works correctly:

create policy "Can only read group rows"
on "public"."cities"
to authenticated
using (
  user_is_group_member(group_id)
);

[jziggas]

provide the group ID

[Zyles]

Hmm okay. I was hoping for a better automated solution.

If you forget to pass the group_id for example in code.

Just like the user_id is autopopulated with auth.uid()

[point-source]

You could add a before trigger that could maybe do something like this but the only way for it to know which group_id to use in the absence of one would be in the case of an update rather than an insert. Are you trying to update or upsert/insert? If it's an update, set a trigger to carry the group_id value of the old record over to the new one. Other than that, you logically must include the group_id in the submitted data so that the db knows what group the incoming data belongs to.