challenger-0001.sql (4888B)
1 -- 2 -- This file is part of Challenger 3 -- Copyright (C) 2023 Taler Systems SA 4 -- 5 -- Challenger is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- Challenger is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- Challenger; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 -- Everything in one big transaction 18 BEGIN; 19 20 -- Check patch versioning is in place. 21 SELECT _v.register_patch('challenger-0001', NULL, NULL); 22 23 CREATE SCHEMA challenger; 24 COMMENT ON SCHEMA challenger IS 'challenger data'; 25 26 SET search_path TO challenger; 27 28 29 CREATE TABLE IF NOT EXISTS clients 30 (client_serial_id BIGINT UNIQUE GENERATED BY DEFAULT AS IDENTITY 31 ,uri VARCHAR NOT NULL UNIQUE 32 ,validation_counter INT8 NOT NULL DEFAULT(0) 33 ,client_secret VARCHAR NOT NULL 34 ); 35 COMMENT ON TABLE clients 36 IS 'Which clients are eligible to access the OAuth 2.0 client'; 37 COMMENT ON COLUMN clients.client_serial_id 38 IS 'Unique ID for the client'; 39 COMMENT ON COLUMN clients.uri 40 IS 'Client redirection URI of the clients, where we would redirect to for authorization'; 41 COMMENT ON COLUMN clients.validation_counter 42 IS 'How many validations were initiated on behalf of this client (for accounting)'; 43 COMMENT ON COLUMN clients.client_secret 44 IS 'Client secret, used by the client to authorize the /login request'; 45 CREATE INDEX IF NOT EXISTS clients_serial 46 ON clients (client_serial_id); 47 48 CREATE TABLE IF NOT EXISTS validations 49 (validation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE 50 ,client_serial_id INT8 NOT NULL REFERENCES clients (client_serial_id) 51 ,nonce BYTEA CHECK (length(nonce)=32) UNIQUE 52 ,expiration_time INT8 NOT NULL 53 ,last_tx_time INT8 NOT NULL DEFAULT (0) 54 ,address_attempts_left INT4 DEFAULT(3) 55 ,last_pin INT4 56 ,pin_transmissions_left INT4 DEFAULT(3) 57 ,auth_attempts_left INT4 DEFAULT(0) 58 ,address VARCHAR 59 ,client_scope VARCHAR 60 ,client_state VARCHAR 61 ,client_redirect_uri VARCHAR 62 ); 63 64 COMMENT ON TABLE validations 65 IS 'Active validations where we send a challenge to an address of a user'; 66 COMMENT ON COLUMN validations.client_serial_id 67 IS 'Which client initiated this validation'; 68 COMMENT ON COLUMN validations.nonce 69 IS 'Unguessable validation identifier'; 70 COMMENT ON COLUMN validations.client_scope 71 IS 'Client-specific scope value identifying the requested scope'; 72 COMMENT ON COLUMN validations.client_state 73 IS 'Client-specific state value identifying the purpose of the validation'; 74 COMMENT ON COLUMN validations.client_redirect_uri 75 IS 'Client-specific URI where to redirect the user-agent back once access is granted (or denied)'; 76 COMMENT ON COLUMN validations.address 77 IS 'Address we are validating; provided by the user-agent; usually a phone number or e-mail address (depends on the client_scope)'; 78 COMMENT ON COLUMN validations.last_pin 79 IS 'Last PIN code send to the user'; 80 COMMENT ON COLUMN validations.address_attempts_left 81 IS 'How many more address changes is the user allowed to make (guard against DoS and brute-forcing)'; 82 COMMENT ON COLUMN validations.pin_transmissions_left 83 IS 'How many more PIN transmission attempts do we permit (guard against DoS and brute-forcing)'; 84 COMMENT ON COLUMN validations.auth_attempts_left 85 IS 'How many more authentication attempts do we permit (guard against brute-forcing)'; 86 COMMENT ON COLUMN validations.last_tx_time 87 IS 'When did we last sent the challenge (guard against DDoS)'; 88 COMMENT ON COLUMN validations.expiration_time 89 IS 'When will the challenge expire'; 90 91 CREATE INDEX IF NOT EXISTS validations_serial 92 ON validations (validation_serial_id); 93 CREATE INDEX IF NOT EXISTS validations_expiration 94 ON validations (expiration_time); 95 96 97 CREATE TABLE IF NOT EXISTS tokens 98 (grant_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY 99 ,access_token BYTEA PRIMARY KEY CHECK (length(access_token)=32) 100 ,address VARCHAR NOT NULL 101 ,address_expiration_time INT8 NOT NULL 102 ,token_expiration_time INT8 NOT NULL 103 ); 104 105 COMMENT ON TABLE tokens 106 IS 'Active tokens where a client is authorized to access user data'; 107 COMMENT ON COLUMN tokens.access_token 108 IS 'Token that tokens access to the resource (the address)'; 109 COMMENT ON COLUMN tokens.address 110 IS 'Address of the user (the resource protected by the token)'; 111 COMMENT ON COLUMN tokens.address_expiration_time 112 IS 'Timestamp until when we consider the address to be valid'; 113 COMMENT ON COLUMN tokens.token_expiration_time 114 IS 'Time until when we consider the grant to be valid'; 115 116 -- Complete transaction 117 COMMIT;