branch bow-v2-go updated (2b7707a -> d332ef4)
This is an automated email from the git hooks/post-receive script. New change to branch bow-v2-go in repository bow. See https://gitlab.nuiton.org/chorem/bow.git from 2b7707a ajout des groupes et des invitations (todo: probleme de droit) new d332ef4 ajout des groupes et des invitations (todo: probleme de droit) The 1 revisions listed above as "new" are entirely new to this repository and will be described in separate emails. The revisions listed as "adds" were already present in the repository and have only been added to this reference. Detailed log of new commits: 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) Summary of changes: migrate/008_create_group_invitation.sql | 212 ++++++++++++++++++++++++++++++++ 1 file changed, 212 insertions(+) create mode 100644 migrate/008_create_group_invitation.sql -- To stop receiving notification emails like this one, please contact chorem.org SCM administrator <admin+scm@chorem.org>.
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>.
participants (1)
-
chorem.org scm