This is an automated email from the git hooks/post-receive script. New commit to branch bow-v2-go in repository bow. See https://gitlab.nuiton.org/chorem/bow.git commit d332ef4f1a76b55dee3a7b58ae95fcfe59f66f5e Author: Benjamin <poussin@codelutin.com> Date: Sat Feb 13 01:03:01 2021 +0100 ajout des groupes et des invitations (todo: probleme de droit) --- migrate/008_create_group_invitation.sql | 212 ++++++++++++++++++++++++++++++++ 1 file changed, 212 insertions(+) diff --git a/migrate/008_create_group_invitation.sql b/migrate/008_create_group_invitation.sql new file mode 100644 index 0000000..43fdaba --- /dev/null +++ b/migrate/008_create_group_invitation.sql @@ -0,0 +1,212 @@ +-- Creation des groupes et des invitations +-- refactoring des groupes pour que la sécu se fasse dans la base + +CREATE TYPE memberRole AS enum ( + 'admin', + 'writer', + 'adder', + 'reader' +); + +CREATE TABLE bowgroupMember ( + id UUID PRIMARY KEY DEFAULT gen_random_uuid(), + bowgroup UUID REFERENCES bowgroup ON DELETE CASCADE, + creationDate TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp, + updateDate TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp, + + role memberRole NOT NULL, + member UUID NOT NULL REFERENCES bowuser ON DELETE CASCADE +); + +CREATE INDEX bowgroupMember_group_role_idx ON bowgroupMember (bowgroup, role); +CREATE INDEX bowgroupMember_member_idx ON bowgroupMember (member); +CREATE TRIGGER update_bowgroupMember_updateDate BEFORE UPDATE ON bowgroupMember FOR EACH ROW EXECUTE PROCEDURE update_updateDate_column(); + +CREATE TABLE bowgroupInvitation ( + id UUID PRIMARY KEY DEFAULT gen_random_uuid(), + creationDate TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp, + validityDate TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp, + initiator UUID NOT NULL REFERENCES bowuser ON DELETE CASCADE, -- la personne qui a invité + guest UUID NOT NULL REFERENCES bowuser ON DELETE CASCADE, -- la personne que est invité à rejoindre le groupe + bowgroup UUID NOT NULL REFERENCES bowgroup ON DELETE CASCADE, -- le groupe pour lequel l'invitation est faite + role memberRole NOT NULL, -- le role qu'aura l'invité dans le groupe s'il accepte + message TEXT, -- un message que l'initiateur de l'invitation veut transmettre a l'invité + + CONSTRAINT guest_group_unique UNIQUE (guest, bowgroup) +); + +CREATE INDEX bowgroupInvitation_guest_idx ON bowgroupInvitation (guest); +CREATE INDEX bowgroupInvitation_group_idx ON bowgroupInvitation (bowgroup); +CREATE TRIGGER update_bowgroupInvitation_updateDate BEFORE UPDATE ON bowgroupInvitation FOR EACH ROW EXECUTE PROCEDURE update_updateDate_column(); + +-- -------------- MODIFICATION ET MIGRATION GROUP -------------- -- + +ALTER TABLE bowgroup ADD owner UUID REFERENCES bowuser ON DELETE CASCADE; +UPDATE bowgroup set owner = admin[1]; +ALTER TABLE bowgroup ALTER owner SET NOT NULL; +ALTER TABLE bowgroup ADD visible BOOLEAN DEFAULT false; -- indique que le groupe est publiquement visible (sinon seuls les membres peuvent le voir) +ALTER TABLE bowgroup ADD lang regconfig NOT NULL DEFAULT 'english'::regconfig; + +CREATE INDEX bowgroup_owner_idx ON bowgroup (owner); +CREATE INDEX bowgroup_visible_idx ON bowgroup (visible); +CREATE INDEX bowgroup_name_description_ft_idx ON bowgroup USING GIN (to_tsvector(lang, text(coalesce(name, '')) || ' ' || coalesce(description, ''))); + +insert into bowgroupMember (bowgroup, role, member) select id, 'admin', unnest(admin) from bowgroup; +insert into bowgroupMember (bowgroup, role, member) select id, 'writer', unnest(writer) from bowgroup; +insert into bowgroupMember (bowgroup, role, member) select id, 'reader', unnest(reader) from bowgroup; + +DROP POLICY bookmark_access ON bookmark; +DROP POLICY bookmark_access_group on bookmark; +DROP POLICY bowgroup_access_insert ON bowgroup; +DROP POLICY bowgroup_access_update ON bowgroup; +DROP POLICY bowgroup_access_select ON bowgroup; + +ALTER TABLE bowgroup DROP tokens; +ALTER TABLE bowgroup DROP admin; +ALTER TABLE bowgroup DROP writer; +ALTER TABLE bowgroup DROP reader; + +-- -------------- S E C U R I T Y -------------- -- + +GRANT SELECT, INSERT, DELETE, TRIGGER ON bowgroup TO person; +GRANT SELECT, INSERT, DELETE, TRIGGER ON bowgroupMember TO person; +GRANT SELECT, INSERT, DELETE, TRIGGER ON bowgroupInvitation TO person; +GRANT UPDATE(updateDate, description) ON bowgroup TO person; +GRANT UPDATE(updateDate, role) ON bowgroupMember TO person; + +ALTER TABLE bowgroup ENABLE ROW LEVEL SECURITY; +ALTER TABLE bowgroupMember ENABLE ROW LEVEL SECURITY; +ALTER TABLE bowgroupInvitation ENABLE ROW LEVEL SECURITY; + +-- tous les utilisateurs peuvent créer un groupe s'il finisse owner du groupe +CREATE POLICY bowgroup_access_insert ON bowgroup + FOR INSERT + WITH CHECK (owner = current_user::uuid); + +-- tout le monde peut lire les groupes s'il est visible ou qu'on est dans les utilisateurs du groupes +CREATE POLICY bowgroup_access_select ON bowgroup + FOR SELECT + USING (visible OR current_user::uuid = owner OR id in (SELECT bowgroup FROM bowgroupMember WHERE member = current_user::uuid)); + +-- seuls les admins peuvent modifier un groupe +CREATE POLICY bowgroup_access_update ON bowgroup + FOR UPDATE + USING (current_user::uuid = owner OR id in (SELECT bowgroup FROM bowgroupMember WHERE member = current_user::uuid AND role = 'admin')); + +-- seuls les admins peuvent retirer des utilisateurs d'un groupe +CREATE POLICY bowgroupMember_delete ON bowgroupMember + FOR DELETE + USING ( + EXISTS(SELECT * FROM bowgroup WHERE bowgroup.id = bowgroupMember.bowgroup AND bowgroup.owner = current_user::uuid) + OR + EXISTS(SELECT * FROM bowgroupMember a WHERE a.bowgroup = bowgroupMember.bowgroup AND a.member = current_user::uuid AND a.role = 'admin') + ); + + +-- si un bookmark a comme tag '@toto' et proprietaire '1234' alors il est visible de toutes les utilisateurs +-- membre du group @toto dont l'utilisateur '1234' est owner, admin, writer ou adder +CREATE POLICY bookmark_access_group ON bookmark + FOR SELECT + TO person + USING ( + -- si c'est le proprietaire du bookmark il peut le lire + current_user::uuid = owner + OR + EXISTS(WITH _groupWithName AS (SELECT id FROM bowgroup WHERE bookmark.tags @> ('{@' || bowgroup.name || '}')::text[]), + -- tout les groups dont le nom est convenable et qui on le proprietaire du bookmark comme ADDER (owner, admin, writer, adder) + _groupAdder AS ((SELECT bowgroup.id FROM bowgroup, _groupWithName WHERE _groupWithName.id = bowgroup.id AND bowgroup.owner = bookmark.owner) + UNION ALL + (SELECT bowgroup AS id FROM bowgroupMember, _groupWithName WHERE _groupWithName.id = bowgroupMember.bowgroup AND member = bookmark.owner AND role in ('admin', 'writer', 'adder'))), + -- tout les groups dont le nom est convenable et qui on le current_user du bookmark comme membre + _groupReader AS ((SELECT bowgroup.id FROM bowgroup, _groupWithName WHERE _groupWithName.id = bowgroup.id AND bowgroup.owner = current_user::uuid) + UNION ALL + (SELECT bowgroup AS id FROM bowgroupMember, _groupWithName WHERE _groupWithName.id = bowgroupMember.bowgroup AND member = current_user::uuid)) + -- on retourne les groupes en commun entre ADDER et READER + SELECT _groupReader.id FROM _groupAdder, _groupReader WHERE _groupAdder.id = _groupReader.id + ) +); + +ALTER TABLE bowgroupInvitation ENABLE ROW LEVEL SECURITY; +-- les admin d'un groupe peuvent ajouter/retirer des entrées pour ce groupe +CREATE POLICY invitation_insert_admin ON bowgroupInvitation + FOR INSERT + WITH CHECK (EXISTS( + (SELECT bowgroup.id FROM bowgroup WHERE bowgroup.id = bowgroupInvitation.bowgroup AND bowgroup.owner = current_user::uuid) + UNION ALL + (SELECT bowgroupMember.id FROM bowgroupMember WHERE bowgroupInvitation.bowgroup = bowgroupMember.bowgroup AND member = current_user::uuid AND bowgroupMember.role = 'admin') + ) + ); +CREATE POLICY invitation_select_admin ON bowgroupInvitation + FOR SELECT + USING (EXISTS( + (SELECT bowgroup.id FROM bowgroup WHERE bowgroup.id = bowgroupInvitation.bowgroup AND bowgroup.owner = current_user::uuid) + UNION ALL + (SELECT bowgroupMember.id FROM bowgroupMember WHERE bowgroupInvitation.bowgroup = bowgroupMember.bowgroup AND member = current_user::uuid AND bowgroupMember.role = 'admin') + ) + ); +CREATE POLICY invitation_delete_admin ON bowgroupInvitation + FOR DELETE + USING (EXISTS( + (SELECT bowgroup.id FROM bowgroup WHERE bowgroup.id = bowgroupInvitation.bowgroup AND bowgroup.owner = current_user::uuid) + UNION ALL + (SELECT bowgroupMember.id FROM bowgroupMember WHERE bowgroupInvitation.bowgroup = bowgroupMember.bowgroup AND member = current_user::uuid AND bowgroupMember.role = 'admin') + ) + ); + +-- les guests peuvent s'ajouter au groupe s'ils sont dans les invités puis se retirer des invitations +CREATE POLICY bowgroupMember_guest_insert ON bowgroupMember + FOR INSERT + WITH CHECK (EXISTS(SELECT * FROM bowgroupInvitation i WHERE bowgroupMember.bowgroup = i.bowgroup AND bowgroupMember.role = i.role AND i.guest = current_user::uuid AND member = current_user::uuid AND i.validityDate > current_timestamp)); + +CREATE POLICY invitation_guest_select ON bowgroupInvitation + FOR SELECT + USING (guest = current_user::uuid); + +CREATE POLICY invitation_guest_delete ON bowgroupInvitation + FOR DELETE + USING (guest = current_user::uuid); + + +---- create above / drop below ---- + +DROP POLICY bookmark_access_group ON bookmark; + +DROP POLICY bowgroup_access_insert ON bowgroup; +DROP POLICY bowgroup_access_update ON bowgroup; +DROP POLICY bowgroup_access_select ON bowgroup; + +DROP POLICY bowgroupMember_guest_insert ON bowgroupMember; +DROP POLICY bowgroupMember_delete ON bowgroupMember; + +DROP POLICY invitation_insert_admin ON bowgroupInvitation; +DROP POLICY invitation_select_admin ON bowgroupInvitation; +DROP POLICY invitation_delete_admin ON bowgroupInvitation; +DROP POLICY invitation_guest_select ON bowgroupInvitation; +DROP POLICY invitation_guest_delete ON bowgroupInvitation; + +ALTER TABLE bowgroup ADD tokens jsonb; +ALTER TABLE bowgroup ADD admin UUID[]; +ALTER TABLE bowgroup ADD writer UUID[]; +ALTER TABLE bowgroup ADD reader UUID[]; + +update bowgroup SET admin=(select owner || array_agg(member) from bowgroupMember where bowgroupMember.bowgroup = bowgroup.id AND role = 'admin'); +update bowgroup SET writer=(select array_agg(member) from bowgroupMember where bowgroupMember.bowgroup = bowgroup.id AND role = 'writer'); +update bowgroup SET reader=(select array_agg(member) from bowgroupMember where bowgroupMember.bowgroup = bowgroup.id AND role = 'reader'); + +CREATE INDEX bowgroup_token_idx ON bowgroup USING gin (tokens); +CREATE INDEX bowgroup_admin_idx ON bowgroup USING gin (admin); +CREATE INDEX bowgroup_writer_idx ON bowgroup USING gin (writer); +CREATE INDEX bowgroup_reader_idx ON bowgroup USING gin (reader); + +ALTER TABLE bowgroup DROP owner; +ALTER TABLE bowgroup DROP visible; +ALTER TABLE bowgroup DROP lang; + +DROP INDEX bowgroup_name_description_ft_idx; + +DROP TABLE bowgroupMember; +DROP TABLE bowgroupInvitation; + +DROP TYPE memberRole; + +GRANT UPDATE(updateDate, description, tokens, admin, writer, reader) ON bowgroup TO person; -- To stop receiving notification emails like this one, please contact chorem.org SCM administrator <admin+scm@chorem.org>.