bretton.dev
A community based topic aggregation platform built on atproto
1-- +goose Up
2-- Encrypt aggregator OAuth tokens at rest using pgp_sym_encrypt
3-- This addresses the security issue where OAuth tokens were stored in plaintext
4-- despite migration 024 claiming "encrypted at application layer before storage"
5
6-- +goose StatementBegin
7
8-- Step 1: Add new encrypted columns for OAuth tokens and DPoP private key
9ALTER TABLE aggregators
10 ADD COLUMN oauth_access_token_encrypted BYTEA,
11 ADD COLUMN oauth_refresh_token_encrypted BYTEA,
12 ADD COLUMN oauth_dpop_private_key_encrypted BYTEA;
13
14-- Step 2: Migrate existing plaintext data to encrypted columns
15-- Uses the same encryption key table as community credentials (migration 006)
16UPDATE aggregators
17SET
18 oauth_access_token_encrypted = CASE
19 WHEN oauth_access_token IS NOT NULL AND oauth_access_token != ''
20 THEN pgp_sym_encrypt(oauth_access_token, (SELECT encode(key_data, 'hex') FROM encryption_keys WHERE id = 1))
21 ELSE NULL
22 END,
23 oauth_refresh_token_encrypted = CASE
24 WHEN oauth_refresh_token IS NOT NULL AND oauth_refresh_token != ''
25 THEN pgp_sym_encrypt(oauth_refresh_token, (SELECT encode(key_data, 'hex') FROM encryption_keys WHERE id = 1))
26 ELSE NULL
27 END,
28 oauth_dpop_private_key_encrypted = CASE
29 WHEN oauth_dpop_private_key_multibase IS NOT NULL AND oauth_dpop_private_key_multibase != ''
30 THEN pgp_sym_encrypt(oauth_dpop_private_key_multibase, (SELECT encode(key_data, 'hex') FROM encryption_keys WHERE id = 1))
31 ELSE NULL
32 END
33WHERE oauth_access_token IS NOT NULL
34 OR oauth_refresh_token IS NOT NULL
35 OR oauth_dpop_private_key_multibase IS NOT NULL;
36
37-- Step 3: Drop the old plaintext columns
38ALTER TABLE aggregators
39 DROP COLUMN oauth_access_token,
40 DROP COLUMN oauth_refresh_token,
41 DROP COLUMN oauth_dpop_private_key_multibase;
42
43-- Step 4: Add security comments
44COMMENT ON COLUMN aggregators.oauth_access_token_encrypted IS 'SENSITIVE: Encrypted OAuth access token (pgp_sym_encrypt) for PDS operations';
45COMMENT ON COLUMN aggregators.oauth_refresh_token_encrypted IS 'SENSITIVE: Encrypted OAuth refresh token (pgp_sym_encrypt) for session renewal';
46COMMENT ON COLUMN aggregators.oauth_dpop_private_key_encrypted IS 'SENSITIVE: Encrypted DPoP private key (pgp_sym_encrypt) for token refresh';
47
48-- +goose StatementEnd
49
50-- +goose Down
51-- +goose StatementBegin
52
53-- Restore plaintext columns
54ALTER TABLE aggregators
55 ADD COLUMN oauth_access_token TEXT,
56 ADD COLUMN oauth_refresh_token TEXT,
57 ADD COLUMN oauth_dpop_private_key_multibase TEXT;
58
59-- Decrypt data back to plaintext (for rollback)
60UPDATE aggregators
61SET
62 oauth_access_token = CASE
63 WHEN oauth_access_token_encrypted IS NOT NULL
64 THEN pgp_sym_decrypt(oauth_access_token_encrypted, (SELECT encode(key_data, 'hex') FROM encryption_keys WHERE id = 1))
65 ELSE NULL
66 END,
67 oauth_refresh_token = CASE
68 WHEN oauth_refresh_token_encrypted IS NOT NULL
69 THEN pgp_sym_decrypt(oauth_refresh_token_encrypted, (SELECT encode(key_data, 'hex') FROM encryption_keys WHERE id = 1))
70 ELSE NULL
71 END,
72 oauth_dpop_private_key_multibase = CASE
73 WHEN oauth_dpop_private_key_encrypted IS NOT NULL
74 THEN pgp_sym_decrypt(oauth_dpop_private_key_encrypted, (SELECT encode(key_data, 'hex') FROM encryption_keys WHERE id = 1))
75 ELSE NULL
76 END
77WHERE oauth_access_token_encrypted IS NOT NULL
78 OR oauth_refresh_token_encrypted IS NOT NULL
79 OR oauth_dpop_private_key_encrypted IS NOT NULL;
80
81-- Drop encrypted columns
82ALTER TABLE aggregators
83 DROP COLUMN oauth_access_token_encrypted,
84 DROP COLUMN oauth_refresh_token_encrypted,
85 DROP COLUMN oauth_dpop_private_key_encrypted;
86
87-- Restore comments
88COMMENT ON COLUMN aggregators.oauth_access_token IS 'SENSITIVE: OAuth access token for PDS operations';
89COMMENT ON COLUMN aggregators.oauth_refresh_token IS 'SENSITIVE: OAuth refresh token for session renewal';
90COMMENT ON COLUMN aggregators.oauth_dpop_private_key_multibase IS 'SENSITIVE: DPoP private key in multibase format for token refresh';
91
92-- +goose StatementEnd