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,
72 CREATE INDEX ON "emails" ("memid");
73 CREATE INDEX ON "emails" ("deleted");
74 CREATE INDEX ON "emails" ("email");
76 DROP TABLE IF EXISTS "emailPinglog";
77 DROP TABLE IF EXISTS "domainPinglog";
79 DROP TYPE IF EXISTS "emailPingType";
80 CREATE TYPE "emailPingType" AS ENUM ('fast', 'active');
81 DROP TYPE IF EXISTS "pingState";
82 CREATE TYPE "pingState" AS ENUM ('open', 'success', 'failed');
84 CREATE TABLE "emailPinglog" (
85 "when" timestamp NOT NULL,
87 "email" varchar(255) NOT NULL,
88 "type" "emailPingType" NOT NULL,
89 "status" "pingState" NOT NULL,
90 "result" varchar(255) NOT NULL,
91 "challenge" varchar(255) NULL DEFAULT NULL
94 DROP TABLE IF EXISTS "pingconfig";
96 DROP TYPE IF EXISTS "pingType";
97 CREATE TYPE "pingType" AS ENUM ('email', 'ssl', 'http', 'dns');
99 CREATE TABLE "pingconfig" (
100 "id" serial NOT NULL,
101 "domainid" int NOT NULL,
102 "type" "pingType" NOT NULL,
103 "info" varchar(255) NOT NULL,
104 "deleted" timestamp NULL DEFAULT NULL,
109 CREATE TABLE "domainPinglog" (
110 "when" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
111 "configId" int NOT NULL,
112 "state" "pingState" NOT NULL,
113 "challenge" varchar(16),
114 "result" varchar(255)
117 DROP TABLE IF EXISTS "baddomains";
118 CREATE TABLE "baddomains" (
119 "domain" varchar(255) NOT NULL DEFAULT ''
123 DROP TABLE IF EXISTS "alerts";
124 CREATE TABLE "alerts" (
125 "memid" int NOT NULL DEFAULT '0',
126 "general" boolean NOT NULL DEFAULT 'false',
127 "country" boolean NOT NULL DEFAULT 'false',
128 "regional" boolean NOT NULL DEFAULT 'false',
129 "radius" boolean NOT NULL DEFAULT 'false',
130 PRIMARY KEY ("memid")
133 DROP TABLE IF EXISTS "user_agreements";
134 CREATE TABLE "user_agreements" (
135 "id" serial NOT NULL,
136 "memid" int NOT NULL,
137 "secmemid" int DEFAULT NULL,
138 "document" varchar(50) DEFAULT NULL,
139 "date" timestamp DEFAULT NULL,
140 "active" boolean NOT NULL,
141 "method" varchar(100) NOT NULL,
142 "comment" varchar(100) DEFAULT NULL,
146 DROP TABLE IF EXISTS "certs";
148 DROP TYPE IF EXISTS "mdType";
149 CREATE TYPE "mdType" AS ENUM('md5','sha1','sha256','sha512');
151 DROP TYPE IF EXISTS "csrType";
152 CREATE TYPE "csrType" AS ENUM ('CSR', 'SPKAC');
154 CREATE TABLE "certs" (
155 "id" serial NOT NULL,
156 "memid" int NOT NULL DEFAULT '0',
157 "serial" varchar(50) NOT NULL DEFAULT '',
158 "keytype" char(2) NOT NULL DEFAULT 'NS',
159 "codesign" boolean NOT NULL DEFAULT 'false',
160 "md" "mdType" NOT NULL DEFAULT 'sha512',
161 "profile" int NOT NULL,
162 "caid" int NULL DEFAULT NULL,
164 "csr_name" varchar(255) NOT NULL DEFAULT '',
165 "csr_type" "csrType" NOT NULL,
166 "crt_name" varchar(255) NOT NULL DEFAULT '',
167 "created" timestamp NULL DEFAULT NULL,
168 "modified" timestamp NULL DEFAULT NULL,
169 "revoked" timestamp NULL DEFAULT NULL,
170 "expire" timestamp NULL DEFAULT NULL,
171 "renewed" boolean NOT NULL DEFAULT 'false',
172 "disablelogin" boolean NOT NULL DEFAULT 'false',
173 "pkhash" char(40) DEFAULT NULL,
174 "certhash" char(40) DEFAULT NULL,
175 "description" varchar(100) NOT NULL DEFAULT '',
178 CREATE INDEX ON "certs" ("pkhash");
179 CREATE INDEX ON "certs" ("revoked");
180 CREATE INDEX ON "certs" ("created");
181 CREATE INDEX ON "certs" ("memid");
182 CREATE INDEX ON "certs" ("serial");
183 CREATE INDEX ON "certs" ("expire");
184 CREATE INDEX ON "certs" ("crt_name");
188 DROP TABLE IF EXISTS "certAvas";
189 CREATE TABLE "certAvas" (
190 "certId" int NOT NULL,
191 "name" varchar(20) NOT NULL,
192 "value" varchar(255) NOT NULL,
194 PRIMARY KEY ("certId", "name")
197 DROP TABLE IF EXISTS "clientcerts";
198 CREATE TABLE "clientcerts" (
200 "disablelogin" boolean NOT NULL DEFAULT 'false',
205 DROP TABLE IF EXISTS "profiles";
206 CREATE TABLE "profiles" (
207 "id" serial NOT NULL,
208 "keyname" varchar(60) NOT NULL,
209 "include" varchar(200) NOT NULL,
210 "requires" varchar(200) NOT NULL,
211 "name" varchar(100) NOT NULL,
216 DROP TABLE IF EXISTS "subjectAlternativeNames";
218 DROP TYPE IF EXISTS "SANType";
219 CREATE TYPE "SANType" AS ENUM ('email', 'DNS');
221 CREATE TABLE "subjectAlternativeNames" (
222 "certId" int NOT NULL,
223 "contents" varchar(50) NOT NULL,
224 "type" "SANType" NOT NULL
227 DROP TABLE IF EXISTS "cacerts";
228 CREATE TABLE "cacerts" (
229 "id" serial NOT NULL,
230 "keyname" varchar(60) NOT NULL,
231 "link" varchar(160) NOT NULL,
232 "parentRoot" int NOT NULL,
233 "validFrom" timestamp NULL DEFAULT NULL,
234 "validTo" timestamp NULL DEFAULT NULL,
239 DROP TABLE IF EXISTS "jobs";
241 DROP TYPE IF EXISTS "jobType";
242 CREATE TYPE "jobType" AS ENUM ('sign', 'revoke');
243 DROP TYPE IF EXISTS "jobState";
244 CREATE TYPE "jobState" AS ENUM ('open', 'done', 'error');
247 CREATE TABLE "jobs" (
248 "id" serial NOT NULL,
249 "targetId" int NOT NULL,
250 "task" "jobType" NOT NULL,
251 "state" "jobState" NOT NULL DEFAULT 'open',
252 "warning" smallint NOT NULL DEFAULT '0',
254 "executeTo" VARCHAR(11),
258 CREATE INDEX ON "jobs" ("state");
260 DROP TABLE IF EXISTS "notary";
262 DROP TYPE IF EXISTS "notaryType";
263 CREATE TYPE "notaryType" AS enum('Face to Face Meeting', 'TOPUP', 'TTP-Assisted', 'Nucleus Bonus');
265 CREATE TABLE "notary" (
266 "id" serial NOT NULL,
267 "from" int NOT NULL DEFAULT '0',
268 "to" int NOT NULL DEFAULT '0',
269 # total points that have been entered
270 "points" int NOT NULL DEFAULT '0',
271 # awarded and the "experience points" are calculated virtually
272 # Face to Face is default, TOPUP is for the remaining 30Points after two TTP
273 # TTP is default ttp assurance
274 "method" "notaryType" NOT NULL DEFAULT 'Face to Face Meeting',
275 "location" varchar(255) NOT NULL DEFAULT '',
276 "date" varchar(255) NOT NULL DEFAULT '',
277 # date when assurance was entered
278 "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
280 "expire" timestamp NULL DEFAULT NULL,
282 "sponsor" int NOT NULL DEFAULT '0',
283 # date when assurance was deleted (or 0)
284 "deleted" timestamp NULL DEFAULT NULL,
288 CREATE INDEX ON "notary"("from");
289 CREATE INDEX ON "notary"("to");
290 CREATE INDEX ON "notary"("when");
291 CREATE INDEX ON "notary"("method");
294 DROP TABLE IF EXISTS "cats_passed";
295 CREATE TABLE "cats_passed" (
296 "id" serial NOT NULL,
297 "user_id" int NOT NULL,
298 "variant_id" int NOT NULL,
299 "pass_date" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
301 UNIQUE ("user_id","variant_id","pass_date")
304 # --------------------------------------------------------
307 # Table structure for table "cats_type"
310 DROP TABLE IF EXISTS "cats_type";
311 CREATE TABLE "cats_type" (
312 "id" serial NOT NULL,
313 "type_text" varchar(255) NOT NULL,
318 DROP TABLE IF EXISTS "arbitrations";
319 CREATE TABLE IF NOT EXISTS "arbitrations" (
321 "arbitration" varchar(20) NOT NULL,
322 PRIMARY KEY ("user","arbitration")
325 DROP TABLE IF EXISTS "user_groups";
327 DROP TYPE IF EXISTS "userGroup";
328 CREATE TYPE "userGroup" AS enum('supporter','arbitrator','blockedassuree','blockedassurer','blockedlogin','ttp-assurer','ttp-applicant', 'codesigning', 'orgassurer', 'blockedcert', 'nucleus-assurer');
330 CREATE TABLE IF NOT EXISTS "user_groups" (
331 "id" serial NOT NULL,
333 "permission" "userGroup" NOT NULL,
334 "granted" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
335 "deleted" timestamp NULL DEFAULT NULL,
336 "grantedby" int NOT NULL,
337 "revokedby" int DEFAULT NULL,
341 DROP TABLE IF EXISTS "org_admin";
343 DROP TYPE IF EXISTS "yesno";
344 CREATE TYPE "yesno" AS enum('y', 'n');
347 CREATE TABLE IF NOT EXISTS "org_admin" (
348 "orgid" int NOT NULL,
349 "memid" int NOT NULL,
350 "master" "yesno" NOT NULL,
351 "creator" int NOT NULL,
352 "created" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
353 "deleter" int NULL DEFAULT NULL,
354 "deleted" timestamp NULL DEFAULT NULL
356 CREATE INDEX ON "org_admin"("orgid", "memid");
359 DROP TABLE IF EXISTS "adminLog";
360 CREATE TABLE "adminLog" (
361 "when" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
363 "admin" int NOT NULL,
364 "type" varchar(100) NOT NULL DEFAULT '',
365 "information" varchar(50) NOT NULL DEFAULT ''
367 CREATE INDEX ON "adminLog"("when");
370 DROP TABLE IF EXISTS "schemeVersion";
371 CREATE TABLE "schemeVersion" (
372 "version" smallint NOT NULL,
373 PRIMARY KEY ("version")
375 INSERT INTO "schemeVersion" (version) VALUES(11);
377 DROP TABLE IF EXISTS `passwordResetTickets`;
378 CREATE TABLE `passwordResetTickets` (
379 `id` serial NOT NULL,
380 `memid` int NOT NULL,
381 `creator` int NOT NULL,
382 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
383 `used` timestamp NULL DEFAULT NULL,
384 `token` varchar(32) NOT NULL,
385 `private_token` varchar(255) NOT NULL,