challenger

commit 0c5facedf8004347764b1b160eba0cdb6ad5b5d6
parent 63b304a689f3dc2b5c80e2642b45946a01d0cc4e
Author: Christian Grothoff <christian@grothoff.org>
Date:   Sun, 16 Apr 2023 20:12:39 +0200

work on schema

Diffstat:
M
src/challengerdb/challenger-0001.sql
 | 
32
++++++++++++++++++++++++--------

1 file changed, 24 insertions(+), 8 deletions(-)
diff --git a/src/challengerdb/challenger-0001.sql b/src/challengerdb/challenger-0001.sql
@@ -29,32 +29,45 @@ SET search_path TO challenger;
 CREATE TABLE IF NOT EXISTS clients
   (client_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
   ,url VARCHAR PRIMARY KEY
-  ,psk BYTEA PRIMARY KEY CHECK (length(psk)=32)
+  ,validation_counter INT8 NOT NULL DEFAULT(0)
+  ,client_secret VARCHAR NOT NULL
   );
 COMMENT ON TABLE clients
  IS 'Which clients are eligible to access the OAuth 2.0 client';
 COMMENT ON COLUMN clients.url
  IS 'URL of the clients where we would send data';
-COMMENT ON COLUMN clients.psk
- IS 'Pre-shared key with the client';
-
+COMMENT ON COLUMN clients.validation_counter
+ IS 'How many validations were initiated on behalf of this client (for accounting)';
+COMMENT ON COLUMN clients.client_secret
+ IS 'Client secret, used by the client to authorize the /login request';
 CREATE INDEX IF NOT EXISTS clients_serial
   ON clients (client_serial_id);
 
-
 CREATE TABLE IF NOT EXISTS validations
   (validation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
-  ,address VARCHAR PRIMARY KEY
+  ,client_serial_id INT8 NOT NULL REFERENCES clients (client_serial_id)
   ,last_pin INT4 NOT NULL
   ,attempts_left INT4 DEFAULT(3)
   ,last_tx_time INT8 NOT NULL
   ,expiration_time INT8 NOT NULL
+  ,address VARCHAR
+  ,client_scope VARCHAR NOT NULL
+  ,client_state VARCHAR NOT NULL
+  ,client_redirect_uri VARCHAR NOT NULL
  );
 
 COMMENT ON TABLE validations
   IS 'Active validations where we send a challenge to an address of a user';
+COMMENT ON COLUMN validations.client_serial_id
+ IS 'Which client initiated this validation';
+COMMENT ON COLUMN validations.client_scope
+ IS 'Client-specific scope value identifying the requested scope';
+COMMENT ON COLUMN validations.client_state
+ IS 'Client-specific state value identifying the purpose of the validation';
+COMMENT ON COLUMN validations.client_redirect_uri
+ IS 'Client-specific URI where to redirect the user-agent back once access is granted (or denied)';
 COMMENT ON COLUMN validations.address
-  IS 'Address we are validating';
+  IS 'Address we are validating; provided by the user-agent; usually a phone number or e-mail address (depends on the client_scope)';
 COMMENT ON COLUMN validations.last_pin
   IS 'Last PIN code send to the user';
 COMMENT ON COLUMN validations.attempts_left
@@ -72,14 +85,17 @@ CREATE INDEX IF NOT EXISTS validations_expiration
 
 CREATE TABLE IF NOT EXISTS grants
   (grant_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+  ,access_token BYTEA PRIMARY KEY CHECK (length(access_token)=32)
   ,address VARCHAR NOT NULL
   ,expiration_time INT8 NOT NULL
   );
 
 COMMENT ON TABLE grants
   IS 'Active grants where a client is authorized to access user data';
+COMMENT ON COLUMN grants.access_token
+  IS 'Token that grants access to the resource (the address)';
 COMMENT ON COLUMN grants.address
-  IS 'Address of the user';
+  IS 'Address of the user (the resource protected by the token)';
 COMMENT ON COLUMN grants.expiration_time
   IS 'When will the grant expire';