max / makenotwork

732 B · 20 lines History Blame Raw

1	-- Hash SyncKit API keys: store SHA-256 hash + prefix instead of plaintext.
2	-- Existing keys are hashed in place. New keys are hashed before storage.
3
4	CREATE EXTENSION IF NOT EXISTS pgcrypto;
5
6	ALTER TABLE sync_apps ADD COLUMN api_key_hash VARCHAR(64);
7	ALTER TABLE sync_apps ADD COLUMN api_key_prefix VARCHAR(8);
8
9	UPDATE sync_apps SET
10	api_key_hash = encode(digest(api_key::bytea, 'sha256'), 'hex'),
11	api_key_prefix = LEFT(api_key, 8);
12
13	ALTER TABLE sync_apps ALTER COLUMN api_key_hash SET NOT NULL;
14	ALTER TABLE sync_apps ALTER COLUMN api_key_prefix SET NOT NULL;
15
16	DROP INDEX IF EXISTS idx_sync_apps_api_key;
17	CREATE UNIQUE INDEX idx_sync_apps_api_key_hash ON sync_apps(api_key_hash);
18
19	ALTER TABLE sync_apps DROP COLUMN api_key;
20