Restrict PostgreSQL row edits by username

Minimal Examples

A minimal example on how to limit row edits on a PostgreSQL table to a user matching a username value in one of the row’s columns.

CREATE OR REPLACE FUNCTION my_schema.edit_owned()
   RETURNS trigger
   LANGUAGE plpgsql
   AS $function$
BEGIN
   IF OLD."allowed_user" != current_user 
   AND current_user NOT IN ('postgres','other_privileged_user')  
      THEN RAISE EXCEPTION 'You are not allowed modify this row'; 
   ELSIF (TG_OP = 'UPDATE') 
      THEN RETURN NEW; 
   ELSIF (TG_OP = 'DELETE')  
      THEN RETURN OLD;
   END IF;
END;
$function$;

CREATE TRIGGER MyTable__edit_owned BEFORE DELETE OR UPDATE ON my_schema."MyTable" FOR EACH ROW EXECUTE PROCEDURE my_schema.edit_owned();