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

Postgresql – Supabase Policy: How to allow update only a specific column after triggering a function

I have 2 tables:

  1. Events
  2. Attendance

I wrote a function that gets triggered on every row insert or update in the Attendance table, to update "Event"."attendees_count" column by incrementing by +1 if it’s an insert, decrement by -1 if attendance status was updated to "cancel" (means he will not join event).

How do I write a policy to allow update of "events"."attendees_count" for all authenticated users even if he’s not the one who created the event, but at the same time prevent other column changes if he’s not the one who create the event

For example this for self event update policy =>

create policy "Enable update activity" on "public"."events" as permissive
for update
  to authenticated using ((auth.uid () = created_by))
with
  check ((auth.uid () = created_by));
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    How i make it.

    Explaining the problem

    I want to increment a column on every INSERT or UPDATE to another table Attendance. so I permitted user to update his event details but I couldn't find a way to allow only one column update for the rest of the users. even from UI I prevent updating others events records still exposing a vulnerability by any authenticated can update any event

    The hack

    I keep policy for authenticated role to update only the events you create:

    create policy "Enable update event" on "public"."events" as permissive
for update to authenticated using ((auth.uid() = created_by))
with check ((auth.uid() = created_by));

    on top of that, I move my function from the scoop of authenticated users to postgres (every time a function is triggered it comes with the current user scoop)

    1. Add new Policy in schema public for the table Events to allow UPDATE with a role of postgres:

    with SQL Editor run

    create policy "Enable triggers to update" on "public"."Events" as permissive
for update
  to postgres using (true);

    1. Create a new schema I call it Daemon

    2. Create function update_event_attendees_count in schema Daemon that will be triggered on every INSERT or UPDATE in Attendance Table

    Using the supabase UI. the function scoop is the following

    BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE public.Events
    SET attendees_count = attendees_count + 1
    WHERE id = NEW.event_id;
  ELSIF TG_OP = 'UPDATE' THEN
    IF NEW.status = 'accept' AND OLD.status <> 'accept' THEN
      UPDATE public.Events
      SET attendees_count = attendees_count + 1
      WHERE id = NEW.event_id;
    ELSE
      IF NEW.status <> 'accept' AND OLD.status = 'accept' THEN
          UPDATE public.Events
          SET attendees_count = CASE 
                WHEN attendees_count - 1 < 0 THEN 0
                ELSE attendees_count - 1
            END
          WHERE id = NEW.activity_id;
      END IF;
    END IF;
  END IF;
  RETURN NEW;
END;

    In my case the function increment and decrements attendees_count column on every insert or status change of the attendance (make sure to change the scoop of the function according to your case)

    1. Create a trigger in the public schema and select update_event_attendees_count to be triggered

    You can do that by the supabase UI

    1. the tricky part to move the ownership of update_event_attendees_count to postgres so it gets the preveleges of postgres that you already create a policy to permit UPDATES on Events Table by running:
    grant execute on function daemon.update_event_attendees_count () to postgres;

ALTER FUNCTION daemon.update_event_attendees_count () OWNER TO postgres;

ALTER FUNCTION daemon.update_event_attendees_count () SECURITY DEFINER;

  2. 0

    You can define The owner of the Trigger. And Set the privilleges for the triggers separately.

    Details are well defined in this answer

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

Back To Top
Search