1-- 2-- PostgreSQL database dump 3-- 4 5SET statement_timeout = 0; 6SET lock_timeout = 0; 7SET client_encoding = 'UTF8'; 8SET standard_conforming_strings = on; 9SET check_function_bodies = false; 10SET client_min_messages = warning; 11 12-- 13-- Name: plpgsql; Type: EXTENSION; Schema: - 14-- 15 16CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; 17 18-- 19-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: - 20-- 21 22COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; 23 24SET search_path = public, pg_catalog; 25 26SET default_tablespace = ''; 27 28SET default_with_oids = false; 29 30-- 31-- Name: aliases; Type: TABLE; Schema: public; Tablespace: 32-- 33 34CREATE TABLE aliases ( 35 identifier text, 36 pki_realm text NOT NULL, 37 alias text NOT NULL, 38 group_id text, 39 generation numeric(49,0), 40 notafter numeric(49,0), 41 notbefore numeric(49,0) 42); 43 44-- 45-- Name: application_log; Type: TABLE; Schema: public; Tablespace: 46-- 47 48CREATE TABLE application_log ( 49 application_log_id numeric(49,0) NOT NULL, 50 logtimestamp numeric(20,5), 51 workflow_id numeric(49,0), 52 category text, 53 priority numeric(49,0), 54 message text 55); 56 57-- 58-- Name: audittrail; Type: TABLE; Schema: public; Tablespace: 59-- 60 61CREATE TABLE audittrail ( 62 audittrail_key integer NOT NULL, 63 logtimestamp numeric(49,0), 64 category text, 65 loglevel text, 66 message text 67); 68 69-- 70-- Name: certificate; Type: TABLE; Schema: public; Tablespace: 71-- 72 73CREATE TABLE certificate ( 74 pki_realm text, 75 issuer_dn text, 76 cert_key numeric(49,0) NOT NULL, 77 issuer_identifier text NOT NULL, 78 identifier text, 79 subject text, 80 status text, 81 subject_key_identifier text, 82 authority_key_identifier text, 83 notbefore numeric(49,0), 84 notafter numeric(49,0), 85 revocation_time numeric(49,0), 86 invalidity_time numeric(49,0), 87 reason_code text, 88 hold_instruction_code text, 89 revocation_id numeric(49,0), 90 req_key numeric(49,0), 91 data text 92); 93 94-- 95-- Name: certificate_attributes; Type: TABLE; Schema: public; Tablespace: 96-- 97 98CREATE TABLE certificate_attributes ( 99 attribute_key numeric(49,0) NOT NULL, 100 identifier text NOT NULL, 101 attribute_contentkey text, 102 attribute_value text 103); 104 105-- 106-- Name: crl; Type: TABLE; Schema: public; Tablespace: 107-- 108 109CREATE TABLE crl ( 110 pki_realm text NOT NULL, 111 issuer_identifier text NOT NULL, 112 profile text, 113 crl_key numeric(49,0) NOT NULL, 114 crl_number numeric(49,0), 115 items integer, 116 max_revocation_id numeric(49,0), 117 data text, 118 last_update numeric(49,0), 119 next_update numeric(49,0), 120 publication_date numeric(49,0) 121); 122 123 124-- 125-- Name: csr; Type: TABLE; Schema: public; Tablespace: 126-- 127 128CREATE TABLE csr ( 129 pki_realm text NOT NULL, 130 req_key numeric(49,0) NOT NULL, 131 format text, 132 data text, 133 profile text, 134 subject text 135); 136 137-- 138-- Name: csr_attributes; Type: TABLE; Schema: public; Tablespace: 139-- 140 141CREATE TABLE csr_attributes ( 142 attribute_key numeric(49,0) NOT NULL, 143 pki_realm text NOT NULL, 144 req_key numeric(49,0) NOT NULL, 145 attribute_contentkey text, 146 attribute_value text, 147 attribute_source text 148); 149 150-- 151-- Name: datapool; Type: TABLE; Schema: public; Tablespace: 152-- 153 154CREATE TABLE datapool ( 155 pki_realm text NOT NULL, 156 namespace text NOT NULL, 157 datapool_key text NOT NULL, 158 datapool_value text, 159 encryption_key text, 160 access_key text, 161 notafter numeric(49,0), 162 last_update numeric(49,0) 163); 164 165-- 166-- Name: report; Type: TABLE; Schema: public; Tablespace: 167-- 168 169CREATE TABLE report ( 170 report_name text NOT NULL, 171 pki_realm text NOT NULL, 172 created numeric(49,0), 173 mime_type text NOT NULL, 174 description text NOT NULL, 175 report_value bytea NOT NULL 176); 177 178-- 179-- Name: secret; Type: TABLE; Schema: public; Tablespace: 180-- 181 182CREATE TABLE secret ( 183 pki_realm text NOT NULL, 184 group_id text NOT NULL, 185 data text 186); 187 188-- 189-- Name: backend_session; Type: TABLE; Schema: public; Tablespace: 190-- 191 192CREATE TABLE backend_session ( 193 session_id text NOT NULL, 194 data text, 195 created numeric(49,0) NOT NULL, 196 modified numeric(49,0) NOT NULL, 197 ip_address text 198); 199 200-- 201-- Name: frontend_session; Type: TABLE; Schema: public; Tablespace: 202-- 203 204CREATE TABLE frontend_session ( 205 session_id text NOT NULL, 206 data text, 207 created numeric(49,0) NOT NULL, 208 modified numeric(49,0) NOT NULL, 209 ip_address text 210); 211 212-- 213-- Name: seq_application_log; Type: SEQUENCE; Schema: public; 214-- 215 216CREATE SEQUENCE seq_application_log 217 START WITH 0 218 INCREMENT BY 1 219 MINVALUE 0 220 NO MAXVALUE 221 CACHE 1; 222 223-- 224-- Name: seq_audittrail; Type: SEQUENCE; Schema: public; 225-- 226 227CREATE SEQUENCE seq_audittrail 228 START WITH 0 229 INCREMENT BY 1 230 MINVALUE 0 231 NO MAXVALUE 232 CACHE 1; 233 234-- 235-- Name: audittrail_audittrail_key_seq; Type: SEQUENCE OWNED BY; Schema: public; 236-- 237 238ALTER SEQUENCE seq_audittrail OWNED BY audittrail.audittrail_key; 239 240-- 241-- Name: seq_certificate; Type: SEQUENCE; Schema: public; 242-- 243 244CREATE SEQUENCE seq_certificate 245 START WITH 0 246 INCREMENT BY 1 247 MINVALUE 0 248 NO MAXVALUE 249 CACHE 1; 250 251-- 252-- Name: seq_certificate_attributes; Type: SEQUENCE; Schema: public; 253-- 254 255CREATE SEQUENCE seq_certificate_attributes 256 START WITH 0 257 INCREMENT BY 1 258 MINVALUE 0 259 NO MAXVALUE 260 CACHE 1; 261 262-- 263-- Name: seq_crl; Type: SEQUENCE; Schema: public; 264-- 265 266CREATE SEQUENCE seq_crl 267 START WITH 0 268 INCREMENT BY 1 269 MINVALUE 0 270 NO MAXVALUE 271 CACHE 1; 272 273-- 274-- Name: seq_csr; Type: SEQUENCE; Schema: public; 275-- 276 277CREATE SEQUENCE seq_csr 278 START WITH 0 279 INCREMENT BY 1 280 MINVALUE 0 281 NO MAXVALUE 282 CACHE 1; 283 284-- 285-- Name: seq_csr_attributes; Type: SEQUENCE; Schema: public; 286-- 287 288CREATE SEQUENCE seq_csr_attributes 289 START WITH 0 290 INCREMENT BY 1 291 MINVALUE 0 292 NO MAXVALUE 293 CACHE 1; 294 295-- 296-- Name: seq_global_id; Type: SEQUENCE; Schema: public; 297-- 298 299CREATE SEQUENCE seq_secret 300 START WITH 0 301 INCREMENT BY 1 302 MINVALUE 0 303 NO MAXVALUE 304 CACHE 1; 305 306-- 307-- Name: seq_workflow; Type: SEQUENCE; Schema: public; 308-- 309 310CREATE SEQUENCE seq_workflow 311 START WITH 0 312 INCREMENT BY 1 313 MINVALUE 0 314 NO MAXVALUE 315 CACHE 1; 316 317-- 318-- Name: seq_workflow_history; Type: SEQUENCE; Schema: public; 319-- 320 321CREATE SEQUENCE seq_workflow_history 322 START WITH 0 323 INCREMENT BY 1 324 MINVALUE 0 325 NO MAXVALUE 326 CACHE 1; 327 328-- 329-- Name: workflow; Type: TABLE; Schema: public; Tablespace: 330-- 331 332CREATE TABLE workflow ( 333 workflow_id numeric(49,0) NOT NULL, 334 pki_realm text, 335 workflow_type text, 336 workflow_state text, 337 workflow_last_update timestamp without time zone, 338 workflow_proc_state text, 339 workflow_wakeup_at numeric(49,0), 340 workflow_count_try numeric(49,0), 341 workflow_reap_at numeric(49,0), 342 workflow_archive_at numeric(49,0), 343 workflow_session text, 344 watchdog_key text 345); 346 347-- 348-- Name: workflow_attributes; Type: TABLE; Schema: public; Tablespace: 349-- 350 351CREATE TABLE workflow_attributes ( 352 workflow_id numeric(49,0) NOT NULL, 353 attribute_contentkey text NOT NULL, 354 attribute_value text 355); 356 357-- 358-- Name: workflow_context; Type: TABLE; Schema: public; Tablespace: 359-- 360 361CREATE TABLE workflow_context ( 362 workflow_id numeric(49,0) NOT NULL, 363 workflow_context_key text NOT NULL, 364 workflow_context_value text 365); 366 367-- 368-- Name: workflow_history; Type: TABLE; Schema: public; Tablespace: 369-- 370 371CREATE TABLE workflow_history ( 372 workflow_hist_id numeric(49,0) NOT NULL, 373 workflow_id numeric(49,0), 374 workflow_action text, 375 workflow_description text, 376 workflow_state text, 377 workflow_user text, 378 workflow_node text, 379 workflow_history_date timestamp without time zone 380); 381 382-- 383-- Name: ocsp_responses; Type: TABLE; Schema: public; Tablespace: 384-- 385 386CREATE TABLE ocsp_responses ( 387 identifier text, 388 serial_number bytea NOT NULL, 389 authority_key_identifier bytea NOT NULL, 390 body bytea NOT NULL, 391 expiry timestamp with time zone 392); 393 394CREATE TABLE users ( 395 username text NOT NULL, 396 password text, 397 pki_realm text, 398 mail text NOT NULL, 399 realname text, 400 role text 401); 402-- 403-- Name: workflow_history; Type: TABLE; Schema: public; Tablespace: 404-- 405 406ALTER TABLE ONLY ocsp_responses 407 ADD CONSTRAINT ocsp_responses_pkey PRIMARY KEY (serial_number, authority_key_identifier); 408 409ALTER TABLE ONLY users 410 ADD CONSTRAINT users_pkey PRIMARY KEY (username, pki_realm), 411 ADD CONSTRAINT users_mail UNIQUE (mail, pki_realm); 412 413-- 414-- Name: audittrail_key; Type: DEFAULT; Schema: public; 415-- 416 417ALTER TABLE ONLY audittrail ALTER COLUMN audittrail_key SET DEFAULT nextval('seq_audittrail'::regclass); 418 419-- 420-- Name: aliases_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 421-- 422 423ALTER TABLE ONLY aliases 424 ADD CONSTRAINT aliases_pkey PRIMARY KEY (pki_realm, alias); 425 426-- 427-- Name: application_log_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 428-- 429 430ALTER TABLE ONLY application_log 431 ADD CONSTRAINT application_log_pkey PRIMARY KEY (application_log_id); 432 433-- 434-- Name: audittrail_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 435-- 436 437ALTER TABLE ONLY audittrail 438 ADD CONSTRAINT audittrail_pkey PRIMARY KEY (audittrail_key); 439 440-- 441-- Name: certificate_attributes_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 442-- 443 444ALTER TABLE ONLY certificate_attributes 445 ADD CONSTRAINT certificate_attributes_pkey PRIMARY KEY (attribute_key, identifier); 446 447-- 448-- Name: certificate_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 449-- 450 451ALTER TABLE ONLY certificate 452 ADD CONSTRAINT certificate_pkey PRIMARY KEY (issuer_identifier, cert_key); 453 454-- 455-- Name: crl_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 456-- 457 458ALTER TABLE ONLY crl 459 ADD CONSTRAINT crl_pkey PRIMARY KEY (pki_realm, issuer_identifier, crl_key); 460 461-- 462-- Name: csr_attributes_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 463-- 464 465ALTER TABLE ONLY csr_attributes 466 ADD CONSTRAINT csr_attributes_pkey PRIMARY KEY (attribute_key, pki_realm, req_key); 467 468-- 469-- Name: csr_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 470-- 471 472ALTER TABLE ONLY csr 473 ADD CONSTRAINT csr_pkey PRIMARY KEY (pki_realm, req_key); 474 475-- 476-- Name: datapool_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 477-- 478 479ALTER TABLE ONLY datapool 480 ADD CONSTRAINT datapool_pkey PRIMARY KEY (pki_realm, namespace, datapool_key); 481 482-- 483-- Name: secret_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 484-- 485 486ALTER TABLE ONLY secret 487 ADD CONSTRAINT secret_pkey PRIMARY KEY (pki_realm, group_id); 488 489-- 490-- Name: backend_session_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 491-- 492 493ALTER TABLE ONLY backend_session 494 ADD CONSTRAINT backend_session_pkey PRIMARY KEY (session_id); 495 496-- 497-- Name: frontend_session_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 498-- 499 500ALTER TABLE ONLY frontend_session 501 ADD CONSTRAINT frontend_session_pkey PRIMARY KEY (session_id); 502 503-- 504-- Name: workflow_attributes_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 505-- 506 507ALTER TABLE ONLY workflow_attributes 508 ADD CONSTRAINT workflow_attributes_pkey PRIMARY KEY (workflow_id, attribute_contentkey); 509 510-- 511-- Name: workflow_context_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 512-- 513 514ALTER TABLE ONLY workflow_context 515 ADD CONSTRAINT workflow_context_pkey PRIMARY KEY (workflow_id, workflow_context_key); 516 517-- 518-- Name: workflow_history_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 519-- 520 521ALTER TABLE ONLY workflow_history 522 ADD CONSTRAINT workflow_history_pkey PRIMARY KEY (workflow_hist_id); 523 524-- 525-- Name: workflow_pkey; Type: CONSTRAINT; Schema: public; Tablespace: 526-- 527 528ALTER TABLE ONLY workflow 529 ADD CONSTRAINT workflow_pkey PRIMARY KEY (workflow_id); 530 531 532CREATE INDEX aliases_realm_group ON aliases USING btree (pki_realm, group_id); 533 534CREATE INDEX application_log_id ON application_log USING btree (workflow_id); 535CREATE INDEX application_log_filter ON application_log USING btree (workflow_id,category,priority); 536 537CREATE INDEX cert_csr_serial_index ON certificate USING btree (req_key); 538CREATE UNIQUE INDEX cert_identifier_index ON certificate USING btree (identifier); 539CREATE INDEX cert_issuer_identifier_index ON certificate USING btree (issuer_identifier); 540CREATE INDEX cert_realm_req_index ON certificate USING btree (pki_realm, req_key); 541CREATE INDEX cert_realm_index ON certificate USING btree (pki_realm); 542CREATE INDEX cert_status_index ON certificate USING btree (status); 543CREATE INDEX cert_subject_index ON certificate USING btree (subject); 544CREATE INDEX cert_notbefore_index ON certificate USING btree (notbefore); 545CREATE INDEX cert_notafter_index ON certificate USING btree (notafter); 546CREATE INDEX cert_revocation_time_index ON certificate USING btree (revocation_time); 547CREATE INDEX cert_invalidity_time_index ON certificate USING btree (invalidity_time); 548CREATE INDEX cert_reason_code_index ON certificate USING btree (reason_code); 549CREATE INDEX cert_hold_index ON certificate USING btree (hold_instruction_code); 550CREATE UNIQUE INDEX cert_revocation_id ON certificate USING btree (revocation_id); 551 552CREATE INDEX cert_attributes_key_index ON certificate_attributes USING btree (attribute_contentkey); 553CREATE INDEX cert_attributes_value_index ON certificate_attributes USING btree (attribute_value); 554CREATE INDEX cert_attributes_identifier_index ON certificate_attributes USING btree (identifier); 555CREATE INDEX cert_attributes_keyid_index ON certificate_attributes USING btree (identifier,attribute_contentkey); 556CREATE INDEX cert_attributes_keyvalue_index ON certificate_attributes USING btree (attribute_contentkey,attribute_value); 557 558 559CREATE INDEX crl_issuer_index ON crl USING btree (issuer_identifier); 560CREATE INDEX crl_profile ON crl USING btree (profile); 561CREATE INDEX crl_realm_index ON crl USING btree (pki_realm); 562CREATE INDEX crl_issuer_update_index ON crl USING btree (issuer_identifier, last_update); 563CREATE INDEX crl_issuer_number_index ON crl USING btree (issuer_identifier, crl_number); 564CREATE INDEX crl_revocation_id ON crl USING btree (max_revocation_id); 565 566CREATE INDEX csr_subject_index ON csr USING btree (subject); 567CREATE INDEX csr_realm_index ON csr USING btree (pki_realm); 568CREATE INDEX csr_realm_profile_index ON csr USING btree (pki_realm, profile); 569 570CREATE INDEX csr_attributes_req_key_index ON csr_attributes USING btree (req_key); 571CREATE INDEX csr_attributes_pki_realm_req_key_index ON csr_attributes USING btree (pki_realm, req_key); 572 573CREATE INDEX datapool_namespace_index ON datapool USING btree (pki_realm, namespace); 574CREATE INDEX datapool_notafter_index ON datapool USING btree (notafter); 575 576CREATE INDEX backend_session_modified_index ON backend_session USING btree (modified); 577 578CREATE INDEX frontend_session_modified_index ON frontend_session USING btree (modified); 579 580CREATE INDEX workflow_pki_realm_index ON workflow USING btree (pki_realm); 581CREATE INDEX workflow_realm_type_index ON workflow USING btree (pki_realm, workflow_type); 582CREATE INDEX workflow_state_index ON workflow USING btree (pki_realm, workflow_state); 583CREATE INDEX workflow_proc_state_index ON workflow USING btree (pki_realm, workflow_proc_state); 584CREATE INDEX workflow_wakeup_index ON workflow USING btree (workflow_proc_state, watchdog_key, workflow_wakeup_at); 585CREATE INDEX workflow_reapat_index ON workflow USING btree (workflow_proc_state, watchdog_key, workflow_reap_at); 586CREATE INDEX workflow_archive_index ON workflow USING btree (workflow_proc_state, watchdog_key, workflow_archive_at); 587 588CREATE INDEX wfl_attributes_id_index ON workflow_attributes USING btree (workflow_id); 589CREATE INDEX wfl_attributes_key_index ON workflow_attributes USING btree (attribute_contentkey); 590CREATE INDEX wfl_attributes_value_index ON workflow_attributes USING btree (attribute_value); 591CREATE INDEX wfl_attributes_keyvalue_index ON workflow_attributes USING btree (attribute_contentkey,attribute_value); 592 593CREATE INDEX wf_hist_wfserial_index ON workflow_history USING btree (workflow_id); 594 595CREATE INDEX ocsp_responses_index ON ocsp_responses USING btree (identifier); 596 597-- 598-- PostgreSQL database dump complete 599-- 600