SUPERBASE: add ownership to data

functions_triggers.sql

--- for policies where the table doesnt have user association
create or replace function get_user_id(cond varchar) returns uuid as $$
declare
   id integer;
begin
  if cond <> 'no_profile' then
     select profile_id into id
       from profiles
       where user_id = auth.uid();
  end if;
  if cond <> 'no_user' then
     id = auth.uid();
  end if;
  return id;
end;
$$ language plqgsql;


create or replace function set_ownership() returns trigger as $$
begin
  IF TG_ARGV[0] <> 'no_profile' THEN
    select profile_id into NEW.profile_id
      from members
      where user_id = auth.uid();
  END IF;
  IF TG_ARGV[0] <> 'no_user' THEN
    NEW.user_id = auth.uid();
  END IF;
end;
$$ language plqgsql;

create or replace function keep_ownership() returns trigger as $$
begin
  if TG_ARGV[0] <> 'no_profile' THEN
    NEW.profile_id = OLD.profile_id;
  END IF;
  if TG_ARGV[0] <> 'no_user' THEN
    NEW.user_id = OLD.user_id;
  END IF;
end;
$$ language plqgsql;


create or replace function deny_change_ownership() returns trigger as $$
begin
  if TG_ARGV[0] <> 'no_profile' THEN
    if NEW.profile_id <> OLD.profile_id THEN
      RAISE EXCEPTION 'Cannot change data ownershipt: profile';
    END IF;
  END IF;
  if TG_ARGV[0] <> 'no_user' THEN
      if NEW.user_id <> OLD.user_id THEN
      RAISE EXCEPTION 'Cannot change data ownershipt: user';
      END IF;
    END IF;
  END IF;
end;
$$ language plqgsql;



--- triggers 

create trigger ensure_keep_ownership_TABLENAME_WITHOUT_USER_ID
BEFORE UPDATE ON TABLENAME_WITHOUT_USER_ID
FOR EACH ROW
EXECUTE PROCEDURE keep_ownership('no_user');

create trigger set_ownership_TABLENAME_WITHOUT_USER_ID
BEFORE INSERT ON TABLENAME_WITHOUT_USER_ID
FOR EACH ROW
EXECUTE PROCEDURE set_ownership('no_user');

create trigger deny_change_ownership_TABLENAME_WITHOUT_USER_ID
BEFORE UPDATE ON TABLENAME_WITHOUT_USER_ID
FOR EACH ROW
EXECUTE PROCEDURE deny_change_ownership('no_user');


ALTER TABLE TABLENAME_WITHOUT_USER_ID ENABLE ROW LEVEL SECURITY;

-- anyone can select, but just onwer can change
CREATE POLICY user_sel_policy ON TABLENAME_WITHOUT_USER_ID
    FOR SELECT
    USING (true);
CREATE POLICY user_mod_policy ON TABLENAME_WITHOUT_USER_ID
    USING (user_id = get_user_id('no_user'));