Something went wrong. Try again.
A community based topic aggregation platform built on atproto
Something went wrong. Try again.
4.8 kB · 70 lines
SQL
at main
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071-- +goose Up-- The literal 1 below is credentialcipher.Version, the leading byte of every-- value the application cipher writes; pgcrypto output starts with 0xC3, so a-- first byte other than 1 (or an empty value) is legacy data. The AppView's-- startup pass (postgres.ReencryptLegacyCredentials) runs before goose and-- converts such rows; this guard is the independent backstop so the key is-- never dropped while something still needs it. It assumes the single-writer-- deployment Coves runs: one AppView, replaced on deploy, so no older binary-- can write pgcrypto ciphertext between this check and the DROP.-- +goose StatementBeginDO $$BEGIN IF EXISTS ( SELECT 1 FROM communities WHERE (pds_password_encrypted IS NOT NULL AND (octet_length(pds_password_encrypted) = 0 OR get_byte(pds_password_encrypted, 0) <> 1)) OR (pds_access_token_encrypted IS NOT NULL AND (octet_length(pds_access_token_encrypted) = 0 OR get_byte(pds_access_token_encrypted, 0) <> 1)) OR (pds_refresh_token_encrypted IS NOT NULL AND (octet_length(pds_refresh_token_encrypted) = 0 OR get_byte(pds_refresh_token_encrypted, 0) <> 1)) UNION ALL SELECT 1 FROM aggregators WHERE (oauth_access_token_encrypted IS NOT NULL AND (octet_length(oauth_access_token_encrypted) = 0 OR get_byte(oauth_access_token_encrypted, 0) <> 1)) OR (oauth_refresh_token_encrypted IS NOT NULL AND (octet_length(oauth_refresh_token_encrypted) = 0 OR get_byte(oauth_refresh_token_encrypted, 0) <> 1)) OR (oauth_dpop_private_key_encrypted IS NOT NULL AND (octet_length(oauth_dpop_private_key_encrypted) = 0 OR get_byte(oauth_dpop_private_key_encrypted, 0) <> 1)) ) THEN RAISE EXCEPTION 'legacy pgcrypto ciphertext remains; the AppView must re-encrypt credentials with ENCRYPTION_KEY before migration 046 can drop encryption_keys'; END IF;END$$;-- +goose StatementEnd
COMMENT ON COLUMN communities.pds_password_encrypted IS 'SENSITIVE: AES-256-GCM ciphertext sealed by the application with the key from ENCRYPTION_KEY; required for session recovery when tokens expire';COMMENT ON COLUMN communities.pds_access_token_encrypted IS 'SENSITIVE: AES-256-GCM ciphertext sealed by the application with the key from ENCRYPTION_KEY for community PDS access';COMMENT ON COLUMN communities.pds_refresh_token_encrypted IS 'SENSITIVE: AES-256-GCM ciphertext sealed by the application with the key from ENCRYPTION_KEY for community PDS session renewal';COMMENT ON COLUMN aggregators.oauth_access_token_encrypted IS 'SENSITIVE: AES-256-GCM ciphertext sealed by the application with the key from ENCRYPTION_KEY for PDS operations';COMMENT ON COLUMN aggregators.oauth_refresh_token_encrypted IS 'SENSITIVE: AES-256-GCM ciphertext sealed by the application with the key from ENCRYPTION_KEY for session renewal';COMMENT ON COLUMN aggregators.oauth_dpop_private_key_encrypted IS 'SENSITIVE: AES-256-GCM ciphertext sealed by the application with the key from ENCRYPTION_KEY for token refresh';
DROP TABLE encryption_keys;
-- +goose Down-- Schema-only rollback. The table comes back with a FRESH random key and the-- credential columns keep their AES-256-GCM values, so a pre-046 binary cannot-- read any stored credential after this runs. There is no reverse conversion:-- to actually roll back, restore a pre-cutover backup or NULL the credential-- columns and re-provision the affected communities and aggregators.CREATE TABLE encryption_keys ( id INTEGER PRIMARY KEY CHECK (id = 1), key_data BYTEA NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, rotated_at TIMESTAMP WITH TIME ZONE);
INSERT INTO encryption_keys (id, key_data)VALUES (1, gen_random_bytes(32))ON CONFLICT (id) DO NOTHING;
COMMENT ON TABLE encryption_keys IS 'Encryption keys for sensitive data - RESTRICT ACCESS';COMMENT ON COLUMN communities.pds_password_encrypted IS 'Encrypted community PDS password (pgp_sym_encrypt) - required for session recovery when tokens expire';COMMENT ON COLUMN communities.pds_access_token_encrypted IS 'Encrypted JWT - decrypt with pgp_sym_decrypt';COMMENT ON COLUMN communities.pds_refresh_token_encrypted IS 'Encrypted refresh token - decrypt with pgp_sym_decrypt';COMMENT ON COLUMN aggregators.oauth_access_token_encrypted IS 'SENSITIVE: Encrypted OAuth access token (pgp_sym_encrypt) for PDS operations';COMMENT ON COLUMN aggregators.oauth_refresh_token_encrypted IS 'SENSITIVE: Encrypted OAuth refresh token (pgp_sym_encrypt) for session renewal';COMMENT ON COLUMN aggregators.oauth_dpop_private_key_encrypted IS 'SENSITIVE: Encrypted DPoP private key (pgp_sym_encrypt) for token refresh';