future.sql (2867B)
1 2 -- See enum TALER_MERCHANT_MFA_Channel 3 CREATE TYPE tan_enum 4 AS ENUM ('sms', 'email', 'totp'); 5 6 -- See enum TALER_MERCHANT_MFA_CriticalOperation 7 CREATE TYPE op_enum 8 AS ENUM ('instance_provision', 'account_config', 'auth_config', 'instance_deletion', 'auth_token_creation'); 9 10 CREATE TABLE tan_challenges 11 (challenge_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE 12 ,h_body BYTEA NOT NULL CHECK (LENGTH(h_body)=64) 13 ,salt BYTEA NOT NULL CHECK (LENGTH(salt)=16) 14 ,op op_enum NOT NULL 15 ,code TEXT NOT NULL 16 ,creation_date INT8 NOT NULL 17 ,expiration_date INT8 NOT NULL 18 ,retransmission_date INT8 NOT NULL DEFAULT 0 19 ,confirmation_date INT8 DEFAULT NULL 20 ,retry_counter INT4 NOT NULL 21 ,merchant_serial INT8 NOT NULL 22 REFERENCES merchant_instances(merchant_serial) 23 ON DELETE CASCADE 24 ,tan_channel tan_enum NULL DEFAULT NULL 25 ,tan_info TEXT NULL DEFAULT NULL -- FIXME: when is this NULL? 26 ); 27 COMMENT ON TABLE tan_challenges 28 IS 'Stores multi-factor authorization (MFA) challenges'; 29 COMMENT ON COLUMN tan_challenges.challenge_id 30 IS 'Unique identifier for the challenge'; 31 COMMENT ON COLUMN tan_challenges.body 32 IS 'Request body of the original request that triggered the challenge, to be replayed once the challenge is satisfied. FIXME: probably should just store the hash of it.'; 33 COMMENT ON COLUMN tan_challenges.h_body 34 IS 'Salted hash of the body of the original request that triggered the challenge, to be replayed once the challenge is satisfied.'; 35 COMMENT ON COLUMN tan_challenges.salt 36 IS 'Salt used when hashing the original body.'; 37 COMMENT ON COLUMN tan_challenges.op 38 IS 'The protected operation to run after the challenge'; 39 COMMENT ON COLUMN tan_challenges.code 40 IS 'The pin code sent to the user and verified'; 41 COMMENT ON COLUMN tan_challenges.creation_date 42 IS 'Creation date of the code'; 43 COMMENT ON COLUMN tan_challenges.retransmission_date 44 IS 'When did we last transmit the challenge to the user'; 45 COMMENT ON COLUMN tan_challenges.expiration_date 46 IS 'When will the code expire'; 47 COMMENT ON COLUMN tan_challenges.confirmation_date 48 IS 'When was this challenge successfully verified, NULL if pending'; 49 COMMENT ON COLUMN tan_challenges.retry_counter 50 IS 'How many tries are left for this code; must be > 0'; 51 COMMENT ON COLUMN tan_challenges.tan_channel 52 IS 'TAN channel to use, if NULL use customer configured one'; 53 COMMENT ON COLUMN tan_challenges.tan_info 54 IS 'TAN information message to use to inform the user about where the challenge was sent'; 55 56 CREATE INDEX tan_challenges_lookup_index 57 ON tan_challenges (merchant_serial,op,expiration_date,creation_date DESC); 58 COMMENT ON INDEX tan_challenges_lookup_index 59 IS 'for lookup_mfa_challenge collection'; 60 61 CREATE INDEX tan_challenges_expiration_index 62 ON tan_challenges (expiration_date); 63 COMMENT ON INDEX tan_challenges_expiration_index 64 IS 'for garbage collection';