summaryrefslogtreecommitdiff
path: root/src/stasis/stasis-0001.sql
blob: 87dde94d42f14524996d1cb16a54d76199d335a1 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
--
-- This file is part of Anastasis
-- Copyright (C) 2020, 2021 Anastasis SARL SA
--
-- ANASTASIS is free software; you can redistribute it and/or modify it under the
-- terms of the GNU General Public License as published by the Free Software
-- Foundation; either version 3, or (at your option) any later version.
--
-- ANASTASIS is distributed in the hope that it will be useful, but WITHOUT ANY
-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
-- A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License along with
-- ANASTASIS; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
--

-- Everything in one big transaction
BEGIN;

-- Check patch versioning is in place.
SELECT _v.register_patch('stasis-0001', NULL, NULL);


CREATE TABLE IF NOT EXISTS anastasis_truth_payment
  (truth_uuid BYTEA PRIMARY KEY CHECK(LENGTH(truth_uuid)=32),
   amount_val INT8 NOT NULL,
   amount_frac INT4 NOT NULL,
   expiration INT8 NOT NULL);
COMMENT ON TABLE anastasis_truth_payment
  IS 'Records about payments for truth uploads';
COMMENT ON COLUMN anastasis_truth_payment.truth_uuid
  IS 'Identifier of the truth';
COMMENT ON COLUMN anastasis_truth_payment.amount_val
  IS 'Amount we were paid';
COMMENT ON COLUMN anastasis_truth_payment.amount_frac
  IS 'Amount we were paid fraction';
COMMENT ON COLUMN anastasis_truth_payment.expiration
  IS 'At which date will the truth payment expire';


CREATE TABLE IF NOT EXISTS anastasis_truth
  (truth_uuid BYTEA PRIMARY KEY CHECK(LENGTH(truth_uuid)=32),
   key_share_data BYTEA CHECK(LENGTH(key_share_data)=80) NOT NULL,
   method_name VARCHAR NOT NULL,
   encrypted_truth BYTEA NOT NULL,
   truth_mime VARCHAR NOT NULL,
   expiration INT8 NOT NULL);
COMMENT ON TABLE anastasis_truth
  IS 'Truth data is needed to authenticate clients during recovery';
COMMENT ON COLUMN anastasis_truth.truth_uuid
  IS 'The truth UUID uniquely identifies this truth record. Not a foreign key as we may offer storing truth for free.';
COMMENT ON COLUMN anastasis_truth.key_share_data
  IS 'Stores the encrypted key share used to recover the key (nonce, tag and keyshare)';
COMMENT ON COLUMN anastasis_truth.method_name
  IS 'Defines the authentication method (SMS, E-Mail, Question..)';
COMMENT ON COLUMN anastasis_truth.encrypted_truth
  IS 'Stores the encrypted authentication data';
COMMENT ON COLUMN anastasis_truth.truth_mime
  IS 'Defines the mime type of the stored authentcation data';
COMMENT ON COLUMN anastasis_truth.expiration
  IS 'At which date will the truth record expire';


CREATE TABLE IF NOT EXISTS anastasis_user
  (user_id BYTEA PRIMARY KEY CHECK(LENGTH(user_id)=32),
   expiration_date INT8 NOT NULL);
COMMENT ON TABLE anastasis_user
  IS 'Saves a user which is using Anastasis';
COMMENT ON COLUMN anastasis_user.user_id
  IS 'Identifier of the user account';
COMMENT ON COLUMN anastasis_user.expiration_date
  IS 'At which date will the user record expire';


CREATE TABLE IF NOT EXISTS anastasis_recdoc_payment
  (payment_id BIGSERIAL PRIMARY KEY,
   user_id BYTEA NOT NULL REFERENCES anastasis_user(user_id),
   post_counter INT4 NOT NULL DEFAULT 0 CHECK(post_counter >= 0),
   amount_val INT8 NOT NULL,
   amount_frac INT4 NOT NULL,
   payment_identifier BYTEA NOT NULL CHECK(LENGTH(payment_identifier)=32),
   creation_date INT8 NOT NULL,
   paid BOOLEAN NOT NULL DEFAULT FALSE);
COMMENT ON TABLE anastasis_recdoc_payment
  IS 'Records a payment for a recovery document';
COMMENT ON COLUMN anastasis_recdoc_payment.payment_id
  IS 'Serial number which identifies the payment';
COMMENT ON COLUMN anastasis_recdoc_payment.user_id
  IS 'Link to the corresponding user who paid';
COMMENT ON COLUMN anastasis_recdoc_payment.post_counter
  IS 'For how many posts does the user pay';
COMMENT ON COLUMN anastasis_recdoc_payment.amount_val
  IS 'Amount we were paid';
COMMENT ON COLUMN anastasis_recdoc_payment.amount_frac
  IS 'Amount we were paid fraction';
COMMENT ON COLUMN anastasis_recdoc_payment.payment_identifier
  IS 'Payment identifier which the user has to provide';
COMMENT ON COLUMN anastasis_recdoc_payment.creation_date
  IS 'Creation date of the payment';
COMMENT ON COLUMN anastasis_recdoc_payment.paid
  IS 'Is the payment finished';


CREATE TABLE IF NOT EXISTS anastasis_challenge_payment
  (payment_id BIGSERIAL PRIMARY KEY,
   truth_uuid BYTEA CHECK(LENGTH(truth_uuid)=32) NOT NULL,
   amount_val INT8 NOT NULL,
   amount_frac INT4 NOT NULL,
   payment_identifier BYTEA NOT NULL CHECK(LENGTH(payment_identifier)=32),
   creation_date INT8 NOT NULL,
   counter INT4 NOT NULL DEFAULT 3,
   paid BOOLEAN NOT NULL DEFAULT FALSE,
   refunded BOOLEAN NOT NULL DEFAULT FALSE
  );
COMMENT ON TABLE anastasis_recdoc_payment
  IS 'Records a payment for a challenge';
COMMENT ON COLUMN anastasis_challenge_payment.payment_id
  IS 'Serial number which identifies the payment';
COMMENT ON COLUMN anastasis_challenge_payment.truth_uuid
  IS 'Link to the corresponding challenge which is paid';
COMMENT ON COLUMN anastasis_challenge_payment.amount_val
  IS 'Amount we were paid';
COMMENT ON COLUMN anastasis_challenge_payment.amount_frac
  IS 'Amount we were paid fraction';
COMMENT ON COLUMN anastasis_challenge_payment.payment_identifier
  IS 'Payment identifier which the user has to provide';
COMMENT ON COLUMN anastasis_challenge_payment.counter
  IS 'How many more times will we issue the challenge for the given payment';
COMMENT ON COLUMN anastasis_challenge_payment.creation_date
  IS 'Creation date of the payment';
COMMENT ON COLUMN anastasis_challenge_payment.paid
  IS 'Is the payment finished';
COMMENT ON COLUMN anastasis_challenge_payment.refunded
  IS 'Was the payment refunded';


CREATE TABLE IF NOT EXISTS anastasis_recoverydocument
  (user_id BYTEA NOT NULL REFERENCES anastasis_user(user_id),
   version INT4 NOT NULL,
   account_sig BYTEA NOT NULL CHECK(LENGTH(account_sig)=64),
   recovery_data_hash BYTEA NOT NULL CHECK(length(recovery_data_hash)=64),
   recovery_data BYTEA NOT NULL,
   PRIMARY KEY (user_id, version));
COMMENT ON TABLE anastasis_recoverydocument
  IS 'Stores a recovery document which contains the policy and the encrypted core secret';
COMMENT ON COLUMN anastasis_recoverydocument.user_id
  IS 'Link to the owner of this recovery document';
COMMENT ON COLUMN anastasis_recoverydocument.version
  IS 'The version of this recovery document';
COMMENT ON COLUMN anastasis_recoverydocument.account_sig
  IS 'Signature of the recovery document';
COMMENT ON COLUMN anastasis_recoverydocument.recovery_data_hash
  IS 'Hash of the recovery document to prevent unnecessary uploads';
COMMENT ON COLUMN anastasis_recoverydocument.recovery_data
  IS 'Contains the encrypted policy and core secret';


CREATE TABLE IF NOT EXISTS anastasis_challengecode
  (truth_uuid BYTEA CHECK(LENGTH(truth_uuid)=32) NOT NULL,
   code INT8 NOT NULL,
   creation_date INT8 NOT NULL,
   expiration_date INT8 NOT NULL,
   retransmission_date INT8 NOT NULL DEFAULT 0,
   retry_counter INT4 NOT NULL,
   satisfied BOOLEAN NOT NULL DEFAULT FALSE);
COMMENT ON TABLE anastasis_challengecode
  IS 'Stores a code which is checked for the authentication by SMS, E-Mail..';
COMMENT ON COLUMN anastasis_challengecode.truth_uuid
  IS 'Link to the corresponding challenge which is solved';
COMMENT ON COLUMN anastasis_challengecode.code
  IS 'The pin code which is sent to the user and verified';
COMMENT ON COLUMN anastasis_challengecode.creation_date
  IS 'Creation date of the code';
COMMENT ON COLUMN anastasis_challengecode.retransmission_date
  IS 'When did we last transmit the challenge to the user';
COMMENT ON COLUMN anastasis_challengecode.expiration_date
  IS 'When will the code expire';
COMMENT ON COLUMN anastasis_challengecode.retry_counter
  IS 'How many tries are left for this code must be > 0';
COMMENT ON COLUMN anastasis_challengecode.satisfied
  IS 'Has this challenge been satisfied by the user, used if it is not enough for the user to know the code (like for video identification or SEPA authentication). For SMS/E-mail/Post verification, this field being FALSE does not imply that the user did not meet the challenge.';

CREATE INDEX IF NOT EXISTS anastasis_challengecode_uuid_index
  ON anastasis_challengecode
  (truth_uuid,expiration_date);
COMMENT ON INDEX anastasis_challengecode_uuid_index
  IS 'for challenge lookup';

CREATE INDEX IF NOT EXISTS anastasis_challengecode_expiration_index
  ON anastasis_challengecode
  (truth_uuid,expiration_date);
COMMENT ON INDEX anastasis_challengecode_expiration_index
  IS 'for challenge garbage collection';


-- Complete transaction
COMMIT;