Colin Watson has proposed merging lp:~cjwatson/launchpad/db-git-permissions into lp:launchpad/db-devel.
Commit message: Add GitRule and GitGrant tables. Requested reviews: Launchpad code reviewers (launchpad-reviewers): db Related bugs: Bug #1517559 in Launchpad itself: "git fine-grained permissions" https://bugs.launchpad.net/launchpad/+bug/1517559 For more details, see: https://code.launchpad.net/~cjwatson/launchpad/db-git-permissions/+merge/354200 Initial schema changes for fine-grained Git permissions. See https://docs.google.com/document/d/1JW_D_Tgo4X2-vPMZtShSbi3cm1iOsGcNIzeOpa5E_wA for the design. The GitRepository.rule_order array column isn't perfect, because PostgreSQL doesn't support arrays of foreign keys and so we lose some referential integrity. However, all the other options I can think of have worse downsides: * GitRule.position integer (rearranging rules requires writing to potentially many rows) * GitRule.position rational so that you can always insert a rule between two others (I've seen this seriously suggested on the internet as a solution to this class of problem, but good grief it seems unreasonably complex) * Store all rules and grants in a single JSON column on GitRepository (would become very wide and would require custom and probably slow person merging code) I therefore propose that we live with reduced referential integrity and enforce this constraint in the application instead. It's not great, but I think it's tolerable. -- Your team Launchpad code reviewers is requested to review the proposed merge of lp:~cjwatson/launchpad/db-git-permissions into lp:launchpad/db-devel.
=== added file 'database/schema/patch-2209-85-0.sql' --- database/schema/patch-2209-85-0.sql 1970-01-01 00:00:00 +0000 +++ database/schema/patch-2209-85-0.sql 2018-09-03 17:07:35 +0000 @@ -0,0 +1,63 @@ +-- Copyright 2018 Canonical Ltd. This software is licensed under the +-- GNU Affero General Public License version 3 (see the file LICENSE). + +SET client_min_messages=ERROR; + +CREATE TABLE GitRule ( + id serial PRIMARY KEY, + repository integer NOT NULL REFERENCES gitrepository ON DELETE CASCADE, + ref_pattern text NOT NULL, + creator integer NOT NULL REFERENCES person, + date_created timestamp without time zone DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') NOT NULL, + date_last_modified timestamp without time zone DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') NOT NULL +); + +CREATE UNIQUE INDEX gitrule__repository__ref_pattern__key + ON GitRule(repository, ref_pattern); + +COMMENT ON TABLE GitRule IS 'An access rule for a Git repository.'; +COMMENT ON COLUMN GitRule.repository IS 'The repository that this rule is for.'; +COMMENT ON COLUMN GitRule.ref_pattern IS 'The pattern of references matched by this rule.'; +COMMENT ON COLUMN GitRule.creator IS 'The user who created this rule.'; +COMMENT ON COLUMN GitRule.date_created IS 'The time when this rule was created.'; +COMMENT ON COLUMN GitRule.date_last_modified IS 'The time when this rule was last modified.'; + +ALTER TABLE GitRepository ADD COLUMN rule_order integer[]; + +COMMENT ON COLUMN GitRepository.rule_order IS 'An ordered array of access rule IDs in this repository.'; + +CREATE TABLE GitGrant ( + id serial PRIMARY KEY, + repository integer NOT NULL REFERENCES gitrepository ON DELETE CASCADE, + rule integer NOT NULL REFERENCES gitrule ON DELETE CASCADE, + grantee_type integer NOT NULL, + grantee integer REFERENCES person, + can_create boolean DEFAULT false NOT NULL, + can_push boolean DEFAULT false NOT NULL, + can_force_push boolean DEFAULT false NOT NULL, + grantor integer NOT NULL REFERENCES person, + date_created timestamp without time zone DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') NOT NULL, + date_last_modified timestamp without time zone DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') NOT NULL, + -- 2 == PERSON + CONSTRAINT has_grantee CHECK ((grantee_type = 2) = (grantee IS NOT NULL)), + CONSTRAINT force_push_implies_push CHECK (can_push OR NOT can_force_push) +); + +CREATE INDEX gitgrant__repository__idx + ON GitGrant(repository); +CREATE UNIQUE INDEX gitgrant__rule__grantee_type__grantee_key + ON GitGrant(rule, grantee_type, grantee); + +COMMENT ON TABLE GitGrant IS 'An access grant for a Git repository rule.'; +COMMENT ON COLUMN GitGrant.repository IS 'The repository that this grant is for.'; +COMMENT ON COLUMN GitGrant.rule IS 'The rule that this grant is for.'; +COMMENT ON COLUMN GitGrant.grantee_type IS 'The type of entity being granted access.'; +COMMENT ON COLUMN GitGrant.grantee IS 'The person or team being granted access.'; +COMMENT ON COLUMN GitGrant.can_create IS 'Whether creating references is allowed.'; +COMMENT ON COLUMN GitGrant.can_push IS 'Whether pushing references is allowed.'; +COMMENT ON COLUMN GitGrant.can_force_push IS 'Whether force-pushing references is allowed.'; +COMMENT ON COLUMN GitGrant.grantor IS 'The user who created this grant.'; +COMMENT ON COLUMN GitGrant.date_created IS 'The time when this grant was created.'; +COMMENT ON COLUMN GitGrant.date_last_modified IS 'The time when this grant was last modified.'; + +INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 85, 0);
_______________________________________________ Mailing list: https://launchpad.net/~launchpad-reviewers Post to : launchpad-reviewers@lists.launchpad.net Unsubscribe : https://launchpad.net/~launchpad-reviewers More help : https://help.launchpad.net/ListHelp
