max / makenotwork

3.3 KB · 66 lines History Blame Raw

1	-- SyncKit Groups: keep every GCK generation, not just the current one.
2	--
3	-- Rotation (member removal, or a re-key after a suspected compromise) mints a new
4	-- Group Content Key. Until now each member had exactly one grant row, overwritten
5	-- on rotation, and sync_group_log carried no generation at all: its schema comment
6	-- asserted "generation = sync_groups.gck_version", which only holds if every
7	-- existing entry is re-encrypted under the new key at rotation time. The server
8	-- cannot do that, because it never sees plaintext. So a rotation would have left
9	-- every pre-rotation entry sealed under a key no member could obtain any more.
10	--
11	-- Two changes make history survive a rotation:
12	--
13	-- 1. Entries record the generation they were sealed under. A pull returns it and
14	-- the client resolves that generation's key.
15	-- 2. Grants are keyed by generation, so a member keeps the grants for every
16	-- generation they were a member during. Rotation adds rows rather than
17	-- replacing them.
18	--
19	-- A removed member's grants are deleted outright: they lose access to everything,
20	-- including entries they could have read before. Whatever they already pulled is
21	-- in their hands regardless, which is the standard, documented limitation.
22	--
23	-- Design: wiki synckit-groups-design.
24
25	-- Grants, one row per (group, member, generation).
26	CREATE TABLE IF NOT EXISTS sync_group_grants (
27	group_id UUID NOT NULL REFERENCES sync_groups(id) ON DELETE CASCADE,
28	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
29	-- The GCK generation this grant opens. Not a foreign key: generations are a
30	-- counter on sync_groups, not rows.
31	gck_version INT NOT NULL,
32	-- The GCK sealed to this member's X25519 public key (base64), opaque to the
33	-- server. Produced by the admin via seal_gck_to_member (synckit-client).
34	sealed_gck TEXT NOT NULL,
35	granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
36	PRIMARY KEY (group_id, user_id, gck_version)
37	);
38
39	-- Carry the existing single grant per member across as that member's grant for
40	-- the generation it was sealed under.
41	INSERT INTO sync_group_grants (group_id, user_id, gck_version, sealed_gck, granted_at)
42	SELECT group_id, user_id, gck_version, sealed_gck, added_at
43	FROM sync_group_members
44	ON CONFLICT (group_id, user_id, gck_version) DO NOTHING;
45
46	-- sync_group_members keeps membership: role, the public key rotation re-seals to,
47	-- and when they joined. The grant columns now live in sync_group_grants, which is
48	-- the only place a generation's sealed key is recorded.
49	ALTER TABLE sync_group_members DROP COLUMN IF EXISTS sealed_gck;
50	ALTER TABLE sync_group_members DROP COLUMN IF EXISTS gck_version;
51
52	-- Every group entry records the generation its ciphertext was sealed under, so a
53	-- pull can hand the client the right key generation per row. Existing rows
54	-- predate any rotation, so they belong to their group's current generation.
55	ALTER TABLE sync_group_log ADD COLUMN IF NOT EXISTS gck_version INT;
56
57	UPDATE sync_group_log l
58	SET gck_version = g.gck_version
59	FROM sync_groups g
60	WHERE l.group_id = g.id AND l.gck_version IS NULL;
61
62	-- Groups with no entries leave nothing to backfill; the default covers a row
63	-- inserted by an older binary mid-deploy.
64	ALTER TABLE sync_group_log ALTER COLUMN gck_version SET DEFAULT 1;
65	ALTER TABLE sync_group_log ALTER COLUMN gck_version SET NOT NULL;
66