1 DROP TABLE IF EXISTS "certOwners";
2 CREATE TABLE "certOwners" (
4 "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
5 "modified" timestamp NULL DEFAULT NULL,
6 "deleted" timestamp NULL DEFAULT NULL,
10 DROP TABLE IF EXISTS "users";
11 CREATE TABLE "users" (
13 "email" varchar(255) NOT NULL DEFAULT '',
14 "password" varchar(255) NOT NULL DEFAULT '',
15 "fname" varchar(255) NOT NULL DEFAULT '',
16 "mname" varchar(255) NOT NULL DEFAULT '',
17 "lname" varchar(255) NOT NULL DEFAULT '',
18 "suffix" varchar(50) NOT NULL DEFAULT '',
20 "verified" boolean NOT NULL DEFAULT 'false',
21 "ccid" int NOT NULL DEFAULT '0',
22 "regid" int NOT NULL DEFAULT '0',
23 "locid" int NOT NULL DEFAULT '0',
24 "listme" boolean NOT NULL DEFAULT 'false',
25 "contactinfo" varchar(255) NOT NULL DEFAULT '',
26 "language" varchar(5) NOT NULL DEFAULT '',
29 CREATE INDEX ON "users" ("ccid");
30 CREATE INDEX ON "users" ("regid");
31 CREATE INDEX ON "users" ("locid");
32 CREATE INDEX ON "users" ("email");
33 CREATE INDEX ON "users" ("verified");
36 DROP TABLE IF EXISTS "organisations";
37 CREATE TABLE IF NOT EXISTS "organisations" (
39 "name" varchar(100) NOT NULL,
40 "state" varchar(2) NOT NULL,
41 "province" varchar(100) NOT NULL,
42 "city" varchar(100) NOT NULL,
43 "contactEmail" varchar(100) NOT NULL,
44 "creator" int NOT NULL,
48 DROP TABLE IF EXISTS "domains";
49 CREATE TABLE "domains" (
52 "domain" varchar(255) NOT NULL,
53 "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
54 "modified" timestamp NULL DEFAULT NULL,
55 "deleted" timestamp NULL DEFAULT NULL,
58 CREATE INDEX ON "domains" ("memid");
59 CREATE INDEX ON "domains" ("domain");
60 CREATE INDEX ON "domains" ("deleted");
62 DROP TABLE IF EXISTS "emails";
63 CREATE TABLE "emails" (
65 "memid" int NOT NULL DEFAULT '0',
66 "email" varchar(255) NOT NULL DEFAULT '',
67 "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
68 "modified" timestamp NULL DEFAULT NULL,
69 "deleted" timestamp NULL DEFAULT NULL,
70 "hash" varchar(50) NOT NULL DEFAULT '',
71 "attempts" smallint NOT NULL DEFAULT '0',
74 CREATE INDEX ON "emails" ("memid");
75 CREATE INDEX ON "emails" ("hash");
76 CREATE INDEX ON "emails" ("deleted");
77 CREATE INDEX ON "emails" ("email");
79 DROP TABLE IF EXISTS "emailPinglog";
80 DROP TABLE IF EXISTS "domainPinglog";
82 DROP TYPE IF EXISTS "emailPingType";
83 CREATE TYPE "emailPingType" AS ENUM ('fast', 'active');
84 DROP TYPE IF EXISTS "pingState";
85 CREATE TYPE "pingState" AS ENUM ('open', 'success', 'failed');
87 CREATE TABLE "emailPinglog" (
88 "when" timestamp NOT NULL,
90 "email" varchar(255) NOT NULL,
91 "type" "emailPingType" NOT NULL,
92 "status" "pingState" NOT NULL,
93 "result" varchar(255) NOT NULL
96 DROP TABLE IF EXISTS "pingconfig";
98 DROP TYPE IF EXISTS "pingType";
99 CREATE TYPE "pingType" AS ENUM ('email', 'ssl', 'http', 'dns');
101 CREATE TABLE "pingconfig" (
102 "id" serial NOT NULL,
103 "domainid" int NOT NULL,
104 "type" "pingType" NOT NULL,
105 "info" varchar(255) NOT NULL,
106 "deleted" timestamp NULL DEFAULT NULL,
111 CREATE TABLE "domainPinglog" (
112 "when" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
113 "configId" int NOT NULL,
114 "state" "pingState" NOT NULL,
115 "challenge" varchar(16),
116 "result" varchar(255)
119 DROP TABLE IF EXISTS "baddomains";
120 CREATE TABLE "baddomains" (
121 "domain" varchar(255) NOT NULL DEFAULT ''
125 DROP TABLE IF EXISTS "alerts";
126 CREATE TABLE "alerts" (
127 "memid" int NOT NULL DEFAULT '0',
128 "general" boolean NOT NULL DEFAULT 'false',
129 "country" boolean NOT NULL DEFAULT 'false',
130 "regional" boolean NOT NULL DEFAULT 'false',
131 "radius" boolean NOT NULL DEFAULT 'false',
132 PRIMARY KEY ("memid")
135 DROP TABLE IF EXISTS "user_agreements";
136 CREATE TABLE "user_agreements" (
137 "id" serial NOT NULL,
138 "memid" int NOT NULL,
139 "secmemid" int DEFAULT NULL,
140 "document" varchar(50) DEFAULT NULL,
141 "date" timestamp DEFAULT NULL,
142 "active" boolean NOT NULL,
143 "method" varchar(100) NOT NULL,
144 "comment" varchar(100) DEFAULT NULL,
148 DROP TABLE IF EXISTS "certs";
150 DROP TYPE IF EXISTS "mdType";
151 CREATE TYPE "mdType" AS ENUM('md5','sha1','sha256','sha512');
153 DROP TYPE IF EXISTS "csrType";
154 CREATE TYPE "csrType" AS ENUM ('CSR', 'SPKAC');
156 CREATE TABLE "certs" (
157 "id" serial NOT NULL,
158 "memid" int NOT NULL DEFAULT '0',
159 "serial" varchar(50) NOT NULL DEFAULT '',
160 "keytype" char(2) NOT NULL DEFAULT 'NS',
161 "codesign" boolean NOT NULL DEFAULT 'false',
162 "md" "mdType" NOT NULL DEFAULT 'sha512',
163 "profile" int NOT NULL,
164 "caid" int NULL DEFAULT NULL,
166 "csr_name" varchar(255) NOT NULL DEFAULT '',
167 "csr_type" "csrType" NOT NULL,
168 "crt_name" varchar(255) NOT NULL DEFAULT '',
169 "created" timestamp NULL DEFAULT NULL,
170 "modified" timestamp NULL DEFAULT NULL,
171 "revoked" timestamp NULL DEFAULT NULL,
172 "expire" timestamp NULL DEFAULT NULL,
173 "renewed" boolean NOT NULL DEFAULT 'false',
174 "disablelogin" boolean NOT NULL DEFAULT 'false',
175 "pkhash" char(40) DEFAULT NULL,
176 "certhash" char(40) DEFAULT NULL,
177 "description" varchar(100) NOT NULL DEFAULT '',
180 CREATE INDEX ON "certs" ("pkhash");
181 CREATE INDEX ON "certs" ("revoked");
182 CREATE INDEX ON "certs" ("created");
183 CREATE INDEX ON "certs" ("memid");
184 CREATE INDEX ON "certs" ("serial");
185 CREATE INDEX ON "certs" ("expire");
186 CREATE INDEX ON "certs" ("crt_name");
190 DROP TABLE IF EXISTS "certAvas";
191 CREATE TABLE "certAvas" (
192 "certId" int NOT NULL,
193 "name" varchar(20) NOT NULL,
194 "value" varchar(255) NOT NULL,
196 PRIMARY KEY ("certId", "name")
199 DROP TABLE IF EXISTS "clientcerts";
200 CREATE TABLE "clientcerts" (
202 "disablelogin" boolean NOT NULL DEFAULT 'false',
207 DROP TABLE IF EXISTS "profiles";
208 CREATE TABLE "profiles" (
209 "id" serial NOT NULL,
210 "keyname" varchar(60) NOT NULL,
211 "include" varchar(200) NOT NULL,
212 "requires" varchar(200) NOT NULL,
213 "name" varchar(100) NOT NULL,
218 DROP TABLE IF EXISTS "subjectAlternativeNames";
220 DROP TYPE IF EXISTS "SANType";
221 CREATE TYPE "SANType" AS ENUM ('email', 'DNS');
223 CREATE TABLE "subjectAlternativeNames" (
224 "certId" int NOT NULL,
225 "contents" varchar(50) NOT NULL,
226 "type" "SANType" NOT NULL
229 DROP TABLE IF EXISTS "cacerts";
230 CREATE TABLE "cacerts" (
231 "id" serial NOT NULL,
232 "keyname" varchar(60) NOT NULL,
233 "link" varchar(160) NOT NULL,
234 "parentRoot" int NOT NULL,
235 "validFrom" timestamp NULL DEFAULT NULL,
236 "validTo" timestamp NULL DEFAULT NULL,
241 DROP TABLE IF EXISTS "jobs";
243 DROP TYPE IF EXISTS "jobType";
244 CREATE TYPE "jobType" AS ENUM ('sign', 'revoke');
245 DROP TYPE IF EXISTS "jobState";
246 CREATE TYPE "jobState" AS ENUM ('open', 'done', 'error');
249 CREATE TABLE "jobs" (
250 "id" serial NOT NULL,
251 "targetId" int NOT NULL,
252 "task" "jobType" NOT NULL,
253 "state" "jobState" NOT NULL DEFAULT 'open',
254 "warning" smallint NOT NULL DEFAULT '0',
256 "executeTo" VARCHAR(11),
260 CREATE INDEX ON "jobs" ("state");
262 DROP TABLE IF EXISTS "notary";
264 DROP TYPE IF EXISTS "notaryType";
265 CREATE TYPE "notaryType" AS enum('Face to Face Meeting', 'TOPUP', 'TTP-Assisted');
267 CREATE TABLE "notary" (
268 "id" serial NOT NULL,
269 "from" int NOT NULL DEFAULT '0',
270 "to" int NOT NULL DEFAULT '0',
271 # total points that have been entered
272 "points" int NOT NULL DEFAULT '0',
273 # awarded and the "experience points" are calculated virtually
274 # Face to Face is default, TOPUP is for the remaining 30Points after two TTP
275 # TTP is default ttp assurance
276 "method" "notaryType" NOT NULL DEFAULT 'Face to Face Meeting',
277 "location" varchar(255) NOT NULL DEFAULT '',
278 "date" varchar(255) NOT NULL DEFAULT '',
279 # date when assurance was entered
280 "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
282 "expire" timestamp NULL DEFAULT NULL,
284 "sponsor" int NOT NULL DEFAULT '0',
285 # date when assurance was deleted (or 0)
286 "deleted" timestamp NULL DEFAULT NULL,
290 CREATE INDEX ON "notary"("from");
291 CREATE INDEX ON "notary"("to");
292 CREATE INDEX ON "notary"("when");
293 CREATE INDEX ON "notary"("method");
296 DROP TABLE IF EXISTS "cats_passed";
297 CREATE TABLE "cats_passed" (
298 "id" serial NOT NULL,
299 "user_id" int NOT NULL,
300 "variant_id" int NOT NULL,
301 "pass_date" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
303 UNIQUE ("user_id","variant_id","pass_date")
306 # --------------------------------------------------------
309 # Table structure for table "cats_type"
312 DROP TABLE IF EXISTS "cats_type";
313 CREATE TABLE "cats_type" (
314 "id" serial NOT NULL,
315 "type_text" varchar(255) NOT NULL,
320 DROP TABLE IF EXISTS "arbitrations";
321 CREATE TABLE IF NOT EXISTS "arbitrations" (
323 "arbitration" varchar(20) NOT NULL,
324 PRIMARY KEY ("user","arbitration")
327 DROP TABLE IF EXISTS "user_groups";
329 DROP TYPE IF EXISTS "userGroup";
330 CREATE TYPE "userGroup" AS enum('supporter','arbitrator','blockedassuree','blockedassurer','blockedlogin','ttp-assurer','ttp-applicant', 'codesigning', 'orgassurer', 'blockedcert');
332 CREATE TABLE IF NOT EXISTS "user_groups" (
333 "id" serial NOT NULL,
335 "permission" "userGroup" NOT NULL,
336 "granted" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
337 "deleted" timestamp NULL DEFAULT NULL,
338 "grantedby" int NOT NULL,
339 "revokedby" int DEFAULT NULL,
343 DROP TABLE IF EXISTS "org_admin";
345 DROP TYPE IF EXISTS "yesno";
346 CREATE TYPE "yesno" AS enum('y', 'n');
349 CREATE TABLE IF NOT EXISTS "org_admin" (
350 "orgid" int NOT NULL,
351 "memid" int NOT NULL,
352 "master" "yesno" NOT NULL,
353 "creator" int NOT NULL,
354 "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
355 "deleter" int NULL DEFAULT NULL,
356 "deleted" timestamp NULL DEFAULT NULL
358 CREATE INDEX ON "org_admin"("orgid", "memid");
361 DROP TABLE IF EXISTS "adminLog";
362 CREATE TABLE "adminLog" (
363 "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
365 "admin" int NOT NULL,
366 "type" varchar(100) NOT NULL DEFAULT '',
367 "information" varchar(50) NOT NULL DEFAULT ''
369 CREATE INDEX ON "adminLog"("when");
372 DROP TABLE IF EXISTS "schemeVersion";
373 CREATE TABLE "schemeVersion" (
374 "version" smallint NOT NULL,
375 PRIMARY KEY ("version")
377 INSERT INTO "schemeVersion" (version) VALUES(8);
379 DROP TABLE IF EXISTS `passwordResetTickets`;
380 CREATE TABLE `passwordResetTickets` (
381 `id` serial NOT NULL,
382 `memid` int NOT NULL,
383 `creator` int NOT NULL,
384 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
385 `used` timestamp NULL DEFAULT NULL,
386 `token` varchar(32) NOT NULL,
387 `private_token` varchar(255) NOT NULL,