simple-group-membership.sql

create type user_role as enum ('hatch_anonymous', 'hatch_contributor', 'hatch_admin');
create type group_member_role as enum ('group_person', 'group_admin');

create table person (
	id serial not null
		constraint person_pkey
			primary key
);

create table "group" (
	id serial not null
		constraint group_pkey
			primary key,
	name varchar(255) default ''::character varying not null
		constraint group_name_unique
			unique,
	person_id integer
		constraint group_person_id_foreign
			references person
);

create table group_member (
	id serial not null
		constraint group_member_pkey
			primary key,
	group_id integer
		constraint group_member_group_id_foreign
			references "group"
				on delete cascade,
	person_id integer
		constraint group_member_person_id_foreign
			references person
				on delete cascade,
	role group_member_role default 'group_person'::group_member_role not null
);

CREATE FUNCTION public.is_owner_of_group(group_id INTEGER, pid INTEGER) RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT * FROM "group" WHERE "group".id = group_id AND "group".person_id = pid
  )
$$ LANGUAGE sql STRICT STABLE SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION public.is_owner_of_group(INTEGER, INTEGER) TO hatch_contributor, hatch_admin;

ALTER TABLE public.person ENABLE ROW LEVEL SECURITY;
ALTER TABLE public."group" ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.group_member ENABLE ROW LEVEL SECURITY;

GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public."person" TO hatch_anonymous, hatch_contributor, hatch_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public."group" TO hatch_anonymous, hatch_contributor, hatch_admin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public."group_member" TO hatch_anonymous, hatch_contributor, hatch_admin;

GRANT USAGE ON SEQUENCE public.person_id_seq TO hatch_anonymous, hatch_contributor, hatch_admin;
GRANT USAGE ON SEQUENCE public.group_id_seq TO hatch_anonymous, hatch_contributor, hatch_admin;
GRANT USAGE ON SEQUENCE public.group_member_id_seq TO hatch_anonymous, hatch_contributor, hatch_admin;

CREATE POLICY person_policy ON person FOR ALL TO hatch_contributor USING (true) WITH CHECK (true);
CREATE POLICY group_policy ON "group" FOR ALL TO hatch_contributor USING (true) WITH CHECK (true);
CREATE POLICY select_group_member ON group_member FOR SELECT TO hatch_contributor USING (
	is_owner_of_group(group_id, current_setting('jwt.claims.person_id')::integer)::boolean
);
CREATE POLICY insert_group_member ON group_member FOR INSERT TO hatch_contributor WITH CHECK (
	is_owner_of_group(group_id, current_setting('jwt.claims.person_id')::integer)::boolean
);

-- INSERT INTO person (id) VALUES (5);
-- -- INSERT INTO person (id) VALUES (2);
-- -- INSERT INTO "group" (name, person_id) VALUES ('test', 1);
-- BEGIN;
-- set local jwt.claims.person_id to 1;
-- set role hatch_contributor;
-- INSERT INTO "group_member" (group_id, person_id) VALUES (2, 5);
-- COMMIT;