95 lines
4.1 KiB
PL/PgSQL
95 lines
4.1 KiB
PL/PgSQL
-- 0002: give single-use tokens a purpose.
|
|
--
|
|
-- 0001 reused the control plane's existing, unused `bootstrap_tokens` table for
|
|
-- email verification. That table has no notion of what a token is for, so with
|
|
-- only one kind of token in play the omission was invisible.
|
|
--
|
|
-- It stops being invisible the moment a second kind exists. A password-reset
|
|
-- token and an email-verification token would be interchangeable: a reset link
|
|
-- clicked by anyone holding it would confirm an address, and a verification link
|
|
-- would set a password. Neither is acceptable, and the table as it stands cannot
|
|
-- tell them apart.
|
|
--
|
|
-- The column defaults to the existing purpose, so every row already written stays
|
|
-- valid and the verification flow is unchanged. No existing row is rewritten, and
|
|
-- the verification code continues to work without modification.
|
|
--
|
|
-- `0002_password_reset_purpose.sql`, additive, and safe to apply more than once.
|
|
|
|
BEGIN;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 1. Purpose
|
|
-- ---------------------------------------------------------------------
|
|
|
|
-- A default, not a null, so the column can be NOT NULL and an insert that
|
|
-- forgets to name a purpose is still valid and still safe: it lands as
|
|
-- email_verification, which is the only purpose that existed before this
|
|
-- migration and therefore the conservative choice.
|
|
ALTER TABLE public.bootstrap_tokens
|
|
ADD COLUMN IF NOT EXISTS purpose text NOT NULL DEFAULT 'email_verification';
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint c
|
|
JOIN pg_class t ON t.oid = c.conrelid
|
|
JOIN pg_namespace n ON n.oid = t.relnamespace
|
|
WHERE n.nspname = 'public'
|
|
AND t.relname = 'bootstrap_tokens'
|
|
AND c.conname = 'bootstrap_tokens_purpose_check'
|
|
) THEN
|
|
ALTER TABLE public.bootstrap_tokens
|
|
ADD CONSTRAINT bootstrap_tokens_purpose_check
|
|
CHECK (purpose IN ('email_verification', 'password_reset'));
|
|
RAISE NOTICE 'attached bootstrap_tokens_purpose_check';
|
|
ELSE
|
|
RAISE NOTICE 'bootstrap_tokens_purpose_check already present; leaving it alone';
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
COMMENT ON COLUMN public.bootstrap_tokens.purpose IS
|
|
'What this token authorises: email_verification confirms an address, password_reset sets a new password. They are deliberately not interchangeable, because each would otherwise grant the other''s capability.';
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 2. Lookup
|
|
-- ---------------------------------------------------------------------
|
|
|
|
-- Every token read filters on user and purpose together, and an unconsumed token
|
|
-- for one purpose is looked up on every reset and every verification. The index
|
|
-- covers that; without it each of those is a sequential scan of the table, which
|
|
-- on a busy deployment is also a cheap way for an unauthenticated endpoint to
|
|
-- generate load.
|
|
CREATE INDEX IF NOT EXISTS bootstrap_tokens_user_purpose_idx
|
|
ON public.bootstrap_tokens (user_id, purpose, expires_at)
|
|
WHERE consumed_at IS NULL;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 3. Rollback
|
|
-- ---------------------------------------------------------------------
|
|
-- Additive, so a rollback is dropping what was added. The column is dropped
|
|
-- before the index, since the index depends on it. Any token of a purpose other
|
|
-- than email_verification must be deleted first, or the drop will leave rows the
|
|
-- column no longer describes; the guarded delete below handles that, and the
|
|
-- NOTICE says how many rows it removed rather than passing over them.
|
|
--
|
|
-- DROP INDEX IF EXISTS public.bootstrap_tokens_user_purpose_idx;
|
|
--
|
|
-- DO $$
|
|
-- BEGIN
|
|
-- IF EXISTS (
|
|
-- SELECT 1 FROM information_schema.columns
|
|
-- WHERE table_schema = 'public'
|
|
-- AND table_name = 'bootstrap_tokens'
|
|
-- AND column_name = 'purpose'
|
|
-- ) THEN
|
|
-- DELETE FROM public.bootstrap_tokens WHERE purpose <> 'email_verification';
|
|
-- ALTER TABLE public.bootstrap_tokens DROP COLUMN purpose;
|
|
-- END IF;
|
|
-- END
|
|
-- $$;
|
|
|
|
COMMIT;
|