xmppoke/schema.pg.sql

DROP TABLE IF EXISTS test_results, srv_results, certificates, srv_certificates, certificate_subjects, tlsa_records, ciphers, srv_ciphers, srv_certificate_errors, public_servers, srv_mechanisms, certificate_sans, news_posts, dh_groups CASCADE;

CREATE TABLE test_results
( test_id SERIAL UNIQUE
, server_name TEXT
, test_date TIMESTAMP
, type TEXT CHECK(type IN ('server','client')) NOT NULL
, version TEXT
, srv_dnssec_good BOOLEAN
, srv_dnssec_bogus BOOLEAN
, error TEXT
);

CREATE TABLE srv_results
( srv_result_id SERIAL UNIQUE
, test_id INTEGER REFERENCES test_results(test_id)
, priority INTEGER
, weight INTEGER
, port INTEGER
, target TEXT
, requires_starttls BOOLEAN
, sslv2 BOOLEAN
, sslv3 BOOLEAN
, tlsv1 BOOLEAN
, tlsv1_1 BOOLEAN
, tlsv1_2 BOOLEAN
, compression TEXT
, reorders_ciphers BOOLEAN
, requires_peer_cert BOOLEAN
, trusted BOOLEAN
, valid_identity BOOLEAN
, cipher_score DECIMAL(7,4)
, certificate_score DECIMAL(7,4)
, keysize_score DECIMAL(7,4)
, protocol_score DECIMAL(7,4)
, total_score DECIMAL(7,4)
, grade TEXT
, done BOOLEAN
, tlsa_dnssec_good BOOLEAN
, tlsa_dnssec_bogus BOOLEAN
, a_aaaa_dnssec_good BOOLEAN
, a_aaaa_dnssec_bogus BOOLEAN
, warn_rc4_tls11 BOOLEAN
, warn_no_fs BOOLEAN
, warn_dh_2048 BOOLEAN
, error TEXT
);

CREATE TABLE certificates
( certificate_id SERIAL UNIQUE
, pem TEXT
, notbefore TIMESTAMP
, notafter TIMESTAMP
, digest_sha1 TEXT
, digest_sha256 TEXT
, digest_sha512 TEXT UNIQUE
, subject_key_info TEXT
, subject_key_info_sha256 TEXT
, subject_key_info_sha512 TEXT
, pubkey_bitsize INTEGER
, pubkey_type TEXT
, rsa_modulus TEXT
, debian_weak_key BOOLEAN
, sign_algorithm TEXT
, signed_by_id INTEGER
, trusted_root BOOLEAN
, crl_url TEXT
, ocsp_url TEXT
, private_key TEXT
);

CREATE TABLE srv_certificates
( srv_certificates_id SERIAL UNIQUE
, srv_result_id INTEGER REFERENCES srv_results(srv_result_id)
, certificate_id INTEGER REFERENCES certificates(certificate_id)
, chain_index INTEGER
);

CREATE TABLE srv_certificate_errors
( srv_certificates_id INTEGER REFERENCES srv_certificates(srv_certificates_id)
, message TEXT
);

CREATE TABLE certificate_subjects
( certificate_subject_id SERIAL UNIQUE
, certificate_id INTEGER REFERENCES certificates(certificate_id)
, name TEXT
, oid TEXT
, value TEXT
);

CREATE UNIQUE INDEX certificate_subjects_unique ON certificate_subjects (certificate_id, oid, value);

CREATE TABLE certificate_sans
( certificate_san_id SERIAL UNIQUE
, certificate_id INTEGER REFERENCES certificates(certificate_id)
, san_type TEXT CHECK(san_type IN ('DNSName','SRVName','XMPPAddr')) NOT NULL
, san_value TEXT
);

CREATE TABLE tlsa_records
( tlsa_record_id SERIAL UNIQUE
, srv_result_id INTEGER REFERENCES srv_results(srv_result_id)
, usage INTEGER
, selector INTEGER
, match INTEGER
, data BYTEA
, verified BOOLEAN
);

CREATE TABLE ciphers
( cipher_id SERIAL UNIQUE
, openssl_name TEXT
, official_name TEXT
, bitsize INTEGER
, key_exchange TEXT
, authentication TEXT
, symmetric_alg TEXT
, hash_alg TEXT
, forward_secret BOOLEAN
, export BOOLEAN
, tls_version TEXT
);

CREATE TABLE dh_groups
( dh_group_id SERIAL UNIQUE
, prime BYTEA NOT NULL
, generator BYTEA NOT NULL
, group_name TEXT
);

CREATE UNIQUE INDEX dh_groups_unique ON dh_groups (prime, generator);

CREATE TABLE srv_ciphers
( srv_result_id INTEGER REFERENCES srv_results(srv_result_id)
, cipher_id INTEGER  REFERENCES ciphers(cipher_id)
, cipher_index INTEGER
, ecdh_curve TEXT
, dh_bits INTEGER
, dh_group_id INTEGER REFERENCES dh_groups(dh_group_id)
);

CREATE TABLE public_servers
( public_server_id SERIAL UNIQUE
, server_name TEXT UNIQUE
, founded INTEGER
, country TEXT
, url TEXT
, description TEXT
, admin TEXT
, vcard_rest TEXT
);

INSERT INTO "ciphers" VALUES(1,'NULL-MD5','TLS_RSA_WITH_NULL_MD5',0,'RSA','RSA','None','MD5',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(2,'NULL-SHA','TLS_RSA_WITH_NULL_SHA',0,'RSA','RSA','None','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(4,'RC4-MD5','SSL_CK_RC4_128_WITH_MD5',128,'RSA','RSA','RC4','MD5',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(5,'RC4-SHA','TLS_RSA_WITH_RC4_128_SHA',128,'RSA','RSA','RC4','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(6,'EXP-RC2-CBC-MD5','TLS_RSA_EXPORT_WITH_RC2_CBC_40_MD5',40,'RSA','RSA','RC2','MD5',FALSE,TRUE,'SSLv3');
INSERT INTO "ciphers" VALUES(7,'IDEA-CBC-SHA','TLS_RSA_WITH_IDEA_CBC_SHA',128,'RSA','RSA','IDEA','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(8,'EXP-DES-CBC-SHA','TLS_RSA_EXPORT_WITH_DES40_CBC_SHA',40,'RSA','RSA','DES','SHA-1',FALSE,TRUE,'SSLv3');
INSERT INTO "ciphers" VALUES(9,'DES-CBC-SHA','TLS_RSA_WITH_DES_CBC_SHA',56,'RSA','RSA','DES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(10,'DES-CBC3-SHA','TLS_RSA_WITH_3DES_EDE_CBC_SHA',112,'RSA','RSA','3DES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(17,'EXP-EDH-DSS-DES-CBC-SHA','TLS_DHE_DSS_EXPORT_WITH_DES40_CBC_SHA',40,'DHE','DSS','DES','SHA-1',TRUE,TRUE,'SSLv3');
INSERT INTO "ciphers" VALUES(18,'EDH-DSS-DES-CBC-SHA','SSL_DHE_DSS_WITH_DES_CBC_SHA',56,'DHE','DSS','DES','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(19,'EDH-DSS-DES-CBC3-SHA','TLS_DHE_DSS_WITH_3DES_EDE_CBC_SHA',112,'DHE','DSS','3DES','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(20,'EXP-EDH-RSA-DES-CBC-SHA','TLS_DHE_RSA_EXPORT_WITH_DES40_CBC_SHA',40,'DHE','RSA','DES','SHA-1',TRUE,TRUE,'SSLv3');
INSERT INTO "ciphers" VALUES(21,'EDH-RSA-DES-CBC-SHA','TLS_DHE_RSA_WITH_DES_CBC_SHA',56,'DHE','RSA','DES','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(22,'EDH-RSA-DES-CBC3-SHA','TLS_DHE_RSA_WITH_3DES_EDE_CBC_SHA',112,'DHE','RSA','3DES','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(23,'EXP-RC4-MD5','SSL_DH_anon_EXPORT_WITH_RC4_40_MD5',40,'DH','None','RC4','MD5',FALSE,TRUE,'SSLv3');
INSERT INTO "ciphers" VALUES(24,'ADH-RC4-MD5','TLS_DH_anon_WITH_RC4_128_MD5',128,'DH','None','RC4','MD5',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(25,'EXP-ADH-DES-CBC-SHA','TLS_DH_anon_EXPORT_WITH_DES40_CBC_SHA',40,'DH','None','DES','SHA-1',FALSE,TRUE,'SSLv3');
INSERT INTO "ciphers" VALUES(26,'ADH-DES-CBC-SHA','TLS_DH_anon_WITH_DES_CBC_SHA',56,'DH','None','DES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(27,'ADH-DES-CBC3-SHA','TLS_DH_anon_WITH_3DES_EDE_CBC_SHA',112,'DH','None','3DES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(47,'AES128-SHA','TLS_RSA_WITH_AES_128_CBC_SHA',128,'RSA','RSA','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(50,'DHE-DSS-AES128-SHA','TLS_DHE_DSS_WITH_AES_128_CBC_SHA',128,'DHE','DSS','AES','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(51,'DHE-RSA-AES128-SHA','TLS_DHE_RSA_WITH_AES_128_CBC_SHA',128,'DHE','RSA','AES','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(52,'ADH-AES128-SHA','TLS_DH_anon_WITH_AES_128_CBC_SHA',128,'DH','None','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(53,'AES256-SHA','TLS_RSA_WITH_AES_256_CBC_SHA',256,'RSA','RSA','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(56,'DHE-DSS-AES256-SHA','TLS_DHE_DSS_WITH_AES_256_CBC_SHA',256,'DHE','DSS','AES','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(57,'DHE-RSA-AES256-SHA','TLS_DHE_RSA_WITH_AES_256_CBC_SHA',256,'DHE','RSA','AES','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(58,'ADH-AES256-SHA','TLS_DH_anon_WITH_AES_256_CBC_SHA',256,'DH','None','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(59,'NULL-SHA256','TLS_RSA_WITH_NULL_SHA256',0,'RSA','RSA','None','SHA-256',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(60,'AES128-SHA256','TLS_RSA_WITH_AES_128_CBC_SHA256',128,'RSA','RSA','AES','SHA-256',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(61,'AES256-SHA256','TLS_RSA_WITH_AES_256_CBC_SHA256',256,'RSA','RSA','AES','SHA-256',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(64,'DHE-DSS-AES128-SHA256','TLS_DHE_DSS_WITH_AES_128_CBC_SHA256',128,'DHE','DSS','AES','SHA-256',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(65,'CAMELLIA128-SHA','TLS_RSA_WITH_CAMELLIA_128_CBC_SHA',128,'RSA','RSA','CAMELLIA','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(68,'DHE-DSS-CAMELLIA128-SHA','TLS_DHE_DSS_WITH_CAMELLIA_128_CBC_SHA',128,'DHE','DSS','CAMELLIA','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(69,'DHE-RSA-CAMELLIA128-SHA','TLS_DHE_RSA_WITH_CAMELLIA_128_CBC_SHA',128,'DHE','RSA','CAMELLIA','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(70,'ADH-CAMELLIA128-SHA','TLS_DH_anon_WITH_CAMELLIA_128_CBC_SHA',128,'DH','None','CAMELLIA','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(103,'DHE-RSA-AES128-SHA256','TLS_DHE_RSA_WITH_AES_128_CBC_SHA256',128,'DHE','RSA','AES','SHA-256',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(106,'DHE-DSS-AES256-SHA256','TLS_DHE_DSS_WITH_AES_256_CBC_SHA256',256,'DHE','DSS','AES','SHA-256',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(107,'DHE-RSA-AES256-SHA256','TLS_DHE_RSA_WITH_AES_256_CBC_SHA256',256,'DHE','RSA','AES','SHA-256',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(108,'ADH-AES128-SHA256','TLS_DH_anon_WITH_AES_128_CBC_SHA256',128,'DH','None','AES','SHA-256',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(109,'ADH-AES256-SHA256','TLS_DH_anon_WITH_AES_256_CBC_SHA256',256,'DH','None','AES','SHA-256',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(132,'CAMELLIA256-SHA','TLS_RSA_WITH_CAMELLIA_256_CBC_SHA',256,'RSA','RSA','CAMELLIA','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(135,'DHE-DSS-CAMELLIA256-SHA','TLS_DHE_DSS_WITH_CAMELLIA_256_CBC_SHA',256,'DHE','DSS','CAMELLIA','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(136,'DHE-RSA-CAMELLIA256-SHA','TLS_DHE_RSA_WITH_CAMELLIA_256_CBC_SHA',256,'DHE','RSA','CAMELLIA','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(137,'ADH-CAMELLIA256-SHA','TLS_DH_anon_WITH_CAMELLIA_256_CBC_SHA',256,'DH','None','CAMELLIA','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(138,'PSK-RC4-SHA','TLS_PSK_WITH_RC4_128_SHA',128,'PSK','PSK','RC4','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(139,'PSK-3DES-EDE-CBC-SHA','TLS_PSK_WITH_3DES_EDE_CBC_SHA',112,'PSK','PSK','3DES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(140,'PSK-AES128-CBC-SHA','TLS_PSK_WITH_AES_128_CBC_SHA',128,'PSK','PSK','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(141,'PSK-AES256-CBC-SHA','TLS_PSK_WITH_AES_256_CBC_SHA',256,'PSK','PSK','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(150,'SEED-SHA','TLS_RSA_WITH_SEED_CBC_SHA',128,'RSA','RSA','SEED','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(153,'DHE-DSS-SEED-SHA','TLS_DHE_DSS_WITH_SEED_CBC_SHA',128,'DHE','DSS','SEED','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(154,'DHE-RSA-SEED-SHA','TLS_DHE_RSA_WITH_SEED_CBC_SHA',128,'DHE','RSA','SEED','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(155,'ADH-SEED-SHA','TLS_DH_anon_WITH_SEED_CBC_SHA',128,'DH','None','SEED','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(156,'AES128-GCM-SHA256','TLS_RSA_WITH_AES_128_GCM_SHA256',128,'RSA','RSA','AESGCM','AEAD',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(157,'AES256-GCM-SHA384','TLS_RSA_WITH_AES_256_GCM_SHA384',256,'RSA','RSA','AESGCM','AEAD',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(158,'DHE-RSA-AES128-GCM-SHA256','TLS_DHE_RSA_WITH_AES_128_GCM_SHA256',128,'DHE','RSA','AESGCM','AEAD',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(159,'DHE-RSA-AES256-GCM-SHA384','TLS_DHE_RSA_WITH_AES_256_GCM_SHA384',256,'DHE','RSA','AESGCM','AEAD',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(162,'DHE-DSS-AES128-GCM-SHA256','TLS_DHE_DSS_WITH_AES_128_GCM_SHA256',128,'DHE','DSS','AESGCM','AEAD',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(163,'DHE-DSS-AES256-GCM-SHA384','TLS_DHE_DSS_WITH_AES_256_GCM_SHA384',256,'DHE','DSS','AESGCM','AEAD',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(166,'ADH-AES128-GCM-SHA256','TLS_DH_anon_WITH_AES_128_GCM_SHA256',128,'DH','None','AESGCM','AEAD',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(167,'ADH-AES256-GCM-SHA384','TLS_DH_anon_WITH_AES_256_GCM_SHA384',256,'DH','None','AESGCM','AEAD',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49153,'ECDH-ECDSA-NULL-SHA','TLS_ECDH_ECDSA_WITH_NULL_SHA',0,'ECDH','ECDSA','None','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49154,'ECDH-ECDSA-RC4-SHA','TLS_ECDH_ECDSA_WITH_RC4_128_SHA',128,'ECDH','ECDSA','RC4','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49155,'ECDH-ECDSA-DES-CBC3-SHA','TLS_ECDH_ECDSA_WITH_3DES_EDE_CBC_SHA',112,'ECDH','ECDSA','3DES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49156,'ECDH-ECDSA-AES128-SHA','TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA',128,'ECDH','ECDSA','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49157,'ECDH-ECDSA-AES256-SHA','TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA',256,'ECDH','ECDSA','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49158,'ECDHE-ECDSA-NULL-SHA','TLS_ECDHE_ECDSA_WITH_NULL_SHA',0,'ECDHE','ECDSA','None','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49159,'ECDHE-ECDSA-RC4-SHA','TLS_ECDHE_ECDSA_WITH_RC4_128_SHA',128,'ECDHE','ECDSA','RC4','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49160,'ECDHE-ECDSA-DES-CBC3-SHA','TLS_ECDHE_ECDSA_WITH_3DES_EDE_CBC_SHA',112,'ECDHE','ECDSA','3DES','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49161,'ECDHE-ECDSA-AES128-SHA','TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA',128,'ECDHE','ECDSA','AES','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49162,'ECDHE-ECDSA-AES256-SHA','TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA',256,'ECDHE','ECDSA','AES','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49163,'ECDH-RSA-NULL-SHA','TLS_ECDH_RSA_WITH_NULL_SHA',0,'ECDH','RSA','None','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49164,'ECDH-RSA-RC4-SHA','TLS_ECDH_RSA_WITH_RC4_128_SHA',128,'ECDH','RSA','RC4','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49165,'ECDH-RSA-DES-CBC3-SHA','TLS_ECDH_RSA_WITH_3DES_EDE_CBC_SHA',112,'ECDH','RSA','3DES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49166,'ECDH-RSA-AES128-SHA','TLS_ECDH_RSA_WITH_AES_128_CBC_SHA',128,'ECDH','RSA','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49167,'ECDH-RSA-AES256-SHA','TLS_ECDH_RSA_WITH_AES_256_CBC_SHA',256,'ECDH','RSA','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49168,'ECDHE-RSA-NULL-SHA','TLS_ECDHE_RSA_WITH_NULL_SHA',0,'ECDHE','RSA','None','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49169,'ECDHE-RSA-RC4-SHA','TLS_ECDHE_RSA_WITH_RC4_128_SHA',128,'ECDHE','RSA','RC4','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49170,'ECDHE-RSA-DES-CBC3-SHA','TLS_ECDHE_RSA_WITH_3DES_EDE_CBC_SHA',112,'ECDHE','RSA','3DES','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49171,'ECDHE-RSA-AES128-SHA','TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA',128,'ECDHE','RSA','AES','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49172,'ECDHE-RSA-AES256-SHA','TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA',256,'ECDHE','RSA','AES','SHA-1',TRUE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49173,'AECDH-NULL-SHA','TLS_ECDH_anon_WITH_NULL_SHA',0,'ECDH','None','None','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49174,'AECDH-RC4-SHA','TLS_ECDH_anon_WITH_RC4_128_SHA',128,'ECDH','None','RC4','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49175,'AECDH-DES-CBC3-SHA','TLS_ECDH_anon_WITH_3DES_EDE_CBC_SHA',112,'ECDH','None','3DES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49176,'AECDH-AES128-SHA','TLS_ECDH_anon_WITH_AES_128_CBC_SHA',128,'ECDH','None','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49177,'AECDH-AES256-SHA','TLS_ECDH_anon_WITH_AES_256_CBC_SHA',256,'ECDH','None','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49178,'SRP-3DES-EDE-CBC-SHA','TLS_SRP_SHA_WITH_3DES_EDE_CBC_SHA',112,'SRP','None','3DES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49179,'SRP-RSA-3DES-EDE-CBC-SHA','TLS_SRP_SHA_RSA_WITH_3DES_EDE_CBC_SHA',112,'SRP','RSA','3DES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49180,'SRP-DSS-3DES-EDE-CBC-SHA','TLS_SRP_SHA_DSS_WITH_3DES_EDE_CBC_SHA',112,'SRP','DSS','3DES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49181,'SRP-AES-128-CBC-SHA','TLS_SRP_SHA_WITH_AES_128_CBC_SHA',128,'SRP','None','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49182,'SRP-RSA-AES-128-CBC-SHA','TLS_SRP_SHA_RSA_WITH_AES_128_CBC_SHA',128,'SRP','RSA','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49183,'SRP-DSS-AES-128-CBC-SHA','TLS_SRP_SHA_DSS_WITH_AES_128_CBC_SHA',128,'SRP','DSS','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49184,'SRP-AES-256-CBC-SHA','TLS_SRP_SHA_WITH_AES_256_CBC_SHA',256,'SRP','None','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49185,'SRP-RSA-AES-256-CBC-SHA','TLS_SRP_SHA_RSA_WITH_AES_256_CBC_SHA',256,'SRP','RSA','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49186,'SRP-DSS-AES-256-CBC-SHA','TLS_SRP_SHA_DSS_WITH_AES_256_CBC_SHA',256,'SRP','DSS','AES','SHA-1',FALSE,FALSE,'SSLv3');
INSERT INTO "ciphers" VALUES(49187,'ECDHE-ECDSA-AES128-SHA256','TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256',128,'ECDHE','ECDSA','AES','SHA-256',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49188,'ECDHE-ECDSA-AES256-SHA384','TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384',256,'ECDHE','ECDSA','AES','SHA-384',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49189,'ECDH-ECDSA-AES128-SHA256','TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256',128,'ECDH','ECDSA','AES','SHA-256',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49190,'ECDH-ECDSA-AES256-SHA384','TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384',256,'ECDH','ECDSA','AES','SHA-384',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49191,'ECDHE-RSA-AES128-SHA256','TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256',128,'ECDHE','RSA','AES','SHA-256',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49192,'ECDHE-RSA-AES256-SHA384','TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384',256,'ECDHE','RSA','AES','SHA-384',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49193,'ECDH-RSA-AES128-SHA256','TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256',128,'ECDH','RSA','AES','SHA-256',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49194,'ECDH-RSA-AES256-SHA384','TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384',256,'ECDH','RSA','AES','SHA-384',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49195,'ECDHE-ECDSA-AES128-GCM-SHA256','TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256',128,'ECDHE','ECDSA','AESGCM','AEAD',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49196,'ECDHE-ECDSA-AES256-GCM-SHA384','TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384',256,'ECDHE','ECDSA','AESGCM','AEAD',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49197,'ECDH-ECDSA-AES128-GCM-SHA256','TLS_ECDH_ECDSA_WITH_AES_128_GCM_SHA256',128,'ECDH','ECDSA','AESGCM','AEAD',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49198,'ECDH-ECDSA-AES256-GCM-SHA384','TLS_ECDH_ECDSA_WITH_AES_256_GCM_SHA384',256,'ECDH','ECDSA','AESGCM','AEAD',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49199,'ECDHE-RSA-AES128-GCM-SHA256','TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256',128,'ECDHE','RSA','AESGCM','AEAD',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49200,'ECDHE-RSA-AES256-GCM-SHA384','TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384',256,'ECDHE','RSA','AESGCM','AEAD',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49201,'ECDH-RSA-AES128-GCM-SHA256','TLS_ECDH_RSA_WITH_AES_128_GCM_SHA256',128,'ECDH','RSA','AESGCM','AEAD',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(49202,'ECDH-RSA-AES256-GCM-SHA384','TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384',256,'ECDH','RSA','AESGCM','AEAD',FALSE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(65664,'RC4-MD5','SSL_CK_RC4_128_WITH_MD5',128,'RSA','RSA','RC4','MD5',FALSE,FALSE,'SSLv2');
INSERT INTO "ciphers" VALUES(131200,'EXP-RC4-MD5','SSL_CK_RC4_128_EXPORT40_WITH_MD5',40,'RSA','RSA','RC4','MD5',FALSE,TRUE,'SSLv2');
INSERT INTO "ciphers" VALUES(196736,'RC2-CBC-MD5','SSL_CK_RC2_128_CBC_WITH_MD5',128,'RSA','RSA','RC2','MD5',FALSE,FALSE,'SSLv2');
INSERT INTO "ciphers" VALUES(262272,'EXP-RC2-CBC-MD5','TLS_RSA_EXPORT_WITH_RC2_CBC_40_MD5',40,'RSA','RSA','RC2','MD5',FALSE,TRUE,'SSLv2');
INSERT INTO "ciphers" VALUES(327808,'IDEA-CBC-MD5','SSL_CK_IDEA_128_CBC_WITH_MD5',128,'RSA','RSA','IDEA','MD5',FALSE,FALSE,'SSLv2');
INSERT INTO "ciphers" VALUES(393280,'DES-CBC-MD5','SSL_CK_DES_64_CBC_WITH_MD5',56,'RSA','RSA','DES','MD5',FALSE,FALSE,'SSLv2');
INSERT INTO "ciphers" VALUES(458944,'DES-CBC3-MD5','SSL_CK_DES_192_EDE3_CBC_WITH_MD5',112,'RSA','RSA','3DES','MD5',FALSE,FALSE,'SSLv2');

INSERT INTO "ciphers" VALUES(52243,'ECDHE-RSA-CHACHA20-POLY1305','TLS_ECDHE_RSA_WITH_CHACHA20_POLY1305_SHA256',256,'ECDHE','RSA','ChaCha20-Poly1305','AEAD',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(52244,'ECDHE-ECDSA-CHACHA20-POLY1305','TLS_ECDHE_ECDSA_WITH_CHACHA20_POLY1305_SHA256',256,'ECDHE','ECDSA','ChaCha20-Poly1305','AEAD',TRUE,FALSE,'TLSv1.2');
INSERT INTO "ciphers" VALUES(52245,'DHE-RSA-CHACHA20-POLY1305','TLS_DHE_RSA_WITH_CHACHA20_POLY1305_SHA25',256,'DHE','RSA','ChaCha20-Poly1305','AEAD',TRUE,FALSE,'TLSv1.2');

CREATE TABLE news_posts
( post_id SERIAL UNIQUE
, post_date TIMESTAMP
, message TEXT
, title TEXT
);

CREATE TABLE srv_mechanisms
( srv_mechanisms_id SERIAL UNIQUE
, srv_result_id INTEGER REFERENCES srv_results(srv_result_id)
, mechanism TEXT
, after_tls BOOLEAN
);


INSERT INTO dh_groups (group_name, generator, prime) VALUES ('RFC 2409 First Oakley Default Group', decode('02', 'hex'), decode(
       'FFFFFFFFFFFFFFFFC90FDAA22168C234C4C6628B80DC1CD1'
    || '29024E088A67CC74020BBEA63B139B22514A08798E3404DD'
    || 'EF9519B3CD3A431B302B0A6DF25F14374FE1356D6D51C245'
    || 'E485B576625E7EC6F44C42E9A63A3620FFFFFFFFFFFFFFFF', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('RFC 2409 First Oakley Default Group', decode('02', 'hex'), decode(
       'FFFFFFFFFFFFFFFFC90FDAA22168C234C4C6628B80DC1CD1'
    || '29024E088A67CC74020BBEA63B139B22514A08798E3404DD'
    || 'EF9519B3CD3A431B302B0A6DF25F14374FE1356D6D51C245'
    || 'E485B576625E7EC6F44C42E9A637ED6B0BFF5CB6F406B7ED'
    || 'EE386BFB5A899FA5AE9F24117C4B1FE649286651ECE65381'
    || 'FFFFFFFFFFFFFFFF', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('RFC 3526 1536-bit MODP Group', decode('02', 'hex'), decode(
       'FFFFFFFFFFFFFFFFC90FDAA22168C234C4C6628B80DC1CD1'
    || '29024E088A67CC74020BBEA63B139B22514A08798E3404DD'
    || 'EF9519B3CD3A431B302B0A6DF25F14374FE1356D6D51C245'
    || 'E485B576625E7EC6F44C42E9A637ED6B0BFF5CB6F406B7ED'
    || 'EE386BFB5A899FA5AE9F24117C4B1FE649286651ECE45B3D'
    || 'C2007CB8A163BF0598DA48361C55D39A69163FA8FD24CF5F'
    || '83655D23DCA3AD961C62F356208552BB9ED529077096966D'
    || '670C354E4ABC9804F1746C08CA237327FFFFFFFFFFFFFFFF', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('RFC 3526 2048-bit MODP Group', decode('02', 'hex'), decode(
       'FFFFFFFFFFFFFFFFC90FDAA22168C234C4C6628B80DC1CD1'
    || '29024E088A67CC74020BBEA63B139B22514A08798E3404DD'
    || 'EF9519B3CD3A431B302B0A6DF25F14374FE1356D6D51C245'
    || 'E485B576625E7EC6F44C42E9A637ED6B0BFF5CB6F406B7ED'
    || 'EE386BFB5A899FA5AE9F24117C4B1FE649286651ECE45B3D'
    || 'C2007CB8A163BF0598DA48361C55D39A69163FA8FD24CF5F'
    || '83655D23DCA3AD961C62F356208552BB9ED529077096966D'
    || '670C354E4ABC9804F1746C08CA18217C32905E462E36CE3B'
    || 'E39E772C180E86039B2783A2EC07A28FB5C55DF06F4C52C9'
    || 'DE2BCBF6955817183995497CEA956AE515D2261898FA0510'
    || '15728E5A8AACAA68FFFFFFFFFFFFFFFF', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('RFC 3526 3072-bit MODP Group', decode('02', 'hex'), decode(
       'FFFFFFFFFFFFFFFFC90FDAA22168C234C4C6628B80DC1CD1'
    || '29024E088A67CC74020BBEA63B139B22514A08798E3404DD'
    || 'EF9519B3CD3A431B302B0A6DF25F14374FE1356D6D51C245'
    || 'E485B576625E7EC6F44C42E9A637ED6B0BFF5CB6F406B7ED'
    || 'EE386BFB5A899FA5AE9F24117C4B1FE649286651ECE45B3D'
    || 'C2007CB8A163BF0598DA48361C55D39A69163FA8FD24CF5F'
    || '83655D23DCA3AD961C62F356208552BB9ED529077096966D'
    || '670C354E4ABC9804F1746C08CA18217C32905E462E36CE3B'
    || 'E39E772C180E86039B2783A2EC07A28FB5C55DF06F4C52C9'
    || 'DE2BCBF6955817183995497CEA956AE515D2261898FA0510'
    || '15728E5A8AAAC42DAD33170D04507A33A85521ABDF1CBA64'
    || 'ECFB850458DBEF0A8AEA71575D060C7DB3970F85A6E1E4C7'
    || 'ABF5AE8CDB0933D71E8C94E04A25619DCEE3D2261AD2EE6B'
    || 'F12FFA06D98A0864D87602733EC86A64521F2B18177B200C'
    || 'BBE117577A615D6C770988C0BAD946E208E24FA074E5AB31'
    || '43DB5BFCE0FD108E4B82D120A93AD2CAFFFFFFFFFFFFFFFF', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('RFC 3526 4069-bit MODP Group', decode('02', 'hex'), decode(
       'FFFFFFFFFFFFFFFFC90FDAA22168C234C4C6628B80DC1CD1'
    || '29024E088A67CC74020BBEA63B139B22514A08798E3404DD'
    || 'EF9519B3CD3A431B302B0A6DF25F14374FE1356D6D51C245'
    || 'E485B576625E7EC6F44C42E9A637ED6B0BFF5CB6F406B7ED'
    || 'EE386BFB5A899FA5AE9F24117C4B1FE649286651ECE45B3D'
    || 'C2007CB8A163BF0598DA48361C55D39A69163FA8FD24CF5F'
    || '83655D23DCA3AD961C62F356208552BB9ED529077096966D'
    || '670C354E4ABC9804F1746C08CA18217C32905E462E36CE3B'
    || 'E39E772C180E86039B2783A2EC07A28FB5C55DF06F4C52C9'
    || 'DE2BCBF6955817183995497CEA956AE515D2261898FA0510'
    || '15728E5A8AAAC42DAD33170D04507A33A85521ABDF1CBA64'
    || 'ECFB850458DBEF0A8AEA71575D060C7DB3970F85A6E1E4C7'
    || 'ABF5AE8CDB0933D71E8C94E04A25619DCEE3D2261AD2EE6B'
    || 'F12FFA06D98A0864D87602733EC86A64521F2B18177B200C'
    || 'BBE117577A615D6C770988C0BAD946E208E24FA074E5AB31'
    || '43DB5BFCE0FD108E4B82D120A92108011A723C12A787E6D7'
    || '88719A10BDBA5B2699C327186AF4E23C1A946834B6150BDA'
    || '2583E9CA2AD44CE8DBBBC2DB04DE8EF92E8EFC141FBECAA6'
    || '287C59474E6BC05D99B2964FA090C3A2233BA186515BE7ED'
    || '1F612970CEE2D7AFB81BDD762170481CD0069127D5B05AA9'
    || '93B4EA988D8FDDC186FFB7DC90A6C08F4DF435C934063199'
    || 'FFFFFFFFFFFFFFFF', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('RFC 3526 6144-bit MODP Group', decode('02', 'hex'), decode(
       'FFFFFFFFFFFFFFFFC90FDAA22168C234C4C6628B80DC1CD129024E08'
    || '8A67CC74020BBEA63B139B22514A08798E3404DDEF9519B3CD3A431B'
    || '302B0A6DF25F14374FE1356D6D51C245E485B576625E7EC6F44C42E9'
    || 'A637ED6B0BFF5CB6F406B7EDEE386BFB5A899FA5AE9F24117C4B1FE6'
    || '49286651ECE45B3DC2007CB8A163BF0598DA48361C55D39A69163FA8'
    || 'FD24CF5F83655D23DCA3AD961C62F356208552BB9ED529077096966D'
    || '670C354E4ABC9804F1746C08CA18217C32905E462E36CE3BE39E772C'
    || '180E86039B2783A2EC07A28FB5C55DF06F4C52C9DE2BCBF695581718'
    || '3995497CEA956AE515D2261898FA051015728E5A8AAAC42DAD33170D'
    || '04507A33A85521ABDF1CBA64ECFB850458DBEF0A8AEA71575D060C7D'
    || 'B3970F85A6E1E4C7ABF5AE8CDB0933D71E8C94E04A25619DCEE3D226'
    || '1AD2EE6BF12FFA06D98A0864D87602733EC86A64521F2B18177B200C'
    || 'BBE117577A615D6C770988C0BAD946E208E24FA074E5AB3143DB5BFC'
    || 'E0FD108E4B82D120A92108011A723C12A787E6D788719A10BDBA5B26'
    || '99C327186AF4E23C1A946834B6150BDA2583E9CA2AD44CE8DBBBC2DB'
    || '04DE8EF92E8EFC141FBECAA6287C59474E6BC05D99B2964FA090C3A2'
    || '233BA186515BE7ED1F612970CEE2D7AFB81BDD762170481CD0069127'
    || 'D5B05AA993B4EA988D8FDDC186FFB7DC90A6C08F4DF435C934028492'
    || '36C3FAB4D27C7026C1D4DCB2602646DEC9751E763DBA37BDF8FF9406'
    || 'AD9E530EE5DB382F413001AEB06A53ED9027D831179727B0865A8918'
    || 'DA3EDBEBCF9B14ED44CE6CBACED4BB1BDB7F1447E6CC254B33205151'
    || '2BD7AF426FB8F401378CD2BF5983CA01C64B92ECF032EA15D1721D03'
    || 'F482D7CE6E74FEF6D55E702F46980C82B5A84031900B1C9E59E7C97F'
    || 'BEC7E8F323A97A7E36CC88BE0F1D45B7FF585AC54BD407B22B4154AA'
    || 'CC8F6D7EBF48E1D814CC5ED20F8037E0A79715EEF29BE32806A1D58B'
    || 'B7C5DA76F550AA3D8A1FBFF0EB19CCB1A313D55CDA56C9EC2EF29632'
    || '387FE8D76E3C0468043E8F663F4860EE12BF2D5B0B7474D6E694F91E'
    || '6DCC4024FFFFFFFFFFFFFFFF', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('RFC 3526 8192-bit MODP Group', decode('02', 'hex'), decode(
       'FFFFFFFFFFFFFFFFC90FDAA22168C234C4C6628B80DC1CD1'
    || '29024E088A67CC74020BBEA63B139B22514A08798E3404DD'
    || 'EF9519B3CD3A431B302B0A6DF25F14374FE1356D6D51C245'
    || 'E485B576625E7EC6F44C42E9A637ED6B0BFF5CB6F406B7ED'
    || 'EE386BFB5A899FA5AE9F24117C4B1FE649286651ECE45B3D'
    || 'C2007CB8A163BF0598DA48361C55D39A69163FA8FD24CF5F'
    || '83655D23DCA3AD961C62F356208552BB9ED529077096966D'
    || '670C354E4ABC9804F1746C08CA18217C32905E462E36CE3B'
    || 'E39E772C180E86039B2783A2EC07A28FB5C55DF06F4C52C9'
    || 'DE2BCBF6955817183995497CEA956AE515D2261898FA0510'
    || '15728E5A8AAAC42DAD33170D04507A33A85521ABDF1CBA64'
    || 'ECFB850458DBEF0A8AEA71575D060C7DB3970F85A6E1E4C7'
    || 'ABF5AE8CDB0933D71E8C94E04A25619DCEE3D2261AD2EE6B'
    || 'F12FFA06D98A0864D87602733EC86A64521F2B18177B200C'
    || 'BBE117577A615D6C770988C0BAD946E208E24FA074E5AB31'
    || '43DB5BFCE0FD108E4B82D120A92108011A723C12A787E6D7'
    || '88719A10BDBA5B2699C327186AF4E23C1A946834B6150BDA'
    || '2583E9CA2AD44CE8DBBBC2DB04DE8EF92E8EFC141FBECAA6'
    || '287C59474E6BC05D99B2964FA090C3A2233BA186515BE7ED'
    || '1F612970CEE2D7AFB81BDD762170481CD0069127D5B05AA9'
    || '93B4EA988D8FDDC186FFB7DC90A6C08F4DF435C934028492'
    || '36C3FAB4D27C7026C1D4DCB2602646DEC9751E763DBA37BD'
    || 'F8FF9406AD9E530EE5DB382F413001AEB06A53ED9027D831'
    || '179727B0865A8918DA3EDBEBCF9B14ED44CE6CBACED4BB1B'
    || 'DB7F1447E6CC254B332051512BD7AF426FB8F401378CD2BF'
    || '5983CA01C64B92ECF032EA15D1721D03F482D7CE6E74FEF6'
    || 'D55E702F46980C82B5A84031900B1C9E59E7C97FBEC7E8F3'
    || '23A97A7E36CC88BE0F1D45B7FF585AC54BD407B22B4154AA'
    || 'CC8F6D7EBF48E1D814CC5ED20F8037E0A79715EEF29BE328'
    || '06A1D58BB7C5DA76F550AA3D8A1FBFF0EB19CCB1A313D55C'
    || 'DA56C9EC2EF29632387FE8D76E3C0468043E8F663F4860EE'
    || '12BF2D5B0B7474D6E694F91E6DBE115974A3926F12FEE5E4'
    || '38777CB6A932DF8CD8BEC4D073B931BA3BC832B68D9DD300'
    || '741FA7BF8AFC47ED2576F6936BA424663AAB639C5AE4F568'
    || '3423B4742BF1C978238F16CBE39D652DE3FDB8BEFC848AD9'
    || '22222E04A4037C0713EB57A81A23F0C73473FC646CEA306B'
    || '4BCBC8862F8385DDFA9D4B7FA2C087E879683303ED5BDD3A'
    || '062B3CF5B3A278A66D2A13F83F44F82DDF310EE074AB6A36'
    || '4597E899A0255DC164F31CC50846851DF9AB48195DED7EA1'
    || 'B1D510BD7EE74D73FAF36BC31ECFA268359046F4EB879F92'
    || '4009438B481C6CD7889A002ED5EE382BC9190DA6FC026E47'
    || '9558E4475677E9AA9E3050E2765694DFC81F56E880B96E71'
    || '60C980DD98EDD3DFFFFFFFFFFFFFFFFF', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('Java sun.security.provider default 512-bit prime',
    decode('678471b27a9cf44ee91a49c5147db1a9aaf244f05a43'
        || '4d6486931d2d14271b9e35030b71fd73da179069b32e'
        || '2935630e1c2062354d0da20a6c416e50be794ca4', 'hex'),
    decode('fca682ce8e12caba26efccf7110e526db078b05edecb' 
        || 'cd1eb4a208f3ae1617ae01f35b91a47e6df63413c5e1'
        || '2ed0899bcd132acd50d99151bdc43ee737592e17', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('Java sun.security.provider default 768-bit prime',
    decode('30470ad5a005fb14ce2d9dcd87e38bc7d1b1c5fac'
        || 'baecbe95f190aa7a31d23c4dbbcbe06174544401a'
        || '5b2c020965d8c2bd2171d3668445771f74ba084d2'
        || '029d83c1c158547f3a9f1a2715be23d51ae4d3e5a'
        || '1f6a7064f316933a346d3f529252', 'hex'),
    decode('e9e642599d355f37c97ffd3567120b8e25c9cd43e'
        || '927b3a9670fbec5d890141922d2c3b3ad24800937'
        || '99869d1e846aab49fab0ad26d2ce6a22219d470bc'
        || 'e7d777d4a21fbe9c270b57f607002f3cef8393694'
        || 'cf45ee3688c11a8c56ab127a3daf', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('Java sun.security.provider default 1024-bit prime',
    decode('f7e1a085d69b3ddecbbcab5c36b857b97994afbbfa'
        || '3aea82f9574c0b3d0782675159578ebad4594fe671'
        || '07108180b449167123e84c281613b7cf09328cc8a6'
        || 'e13c167a8b547c8d28e0a3ae1e2bb3a675916ea37f'
        || '0bfa213562f1fb627a01243bcca4f1bea8519089a8'
        || '83dfe15ae59f06928b665e807b552564014c3bfecf'
        || '492a', 'hex'),
    decode('fd7f53811d75122952df4a9c2eece4e7f611b7523c'
        || 'ef4400c31e3f80b6512669455d402251fb593d8d58'
        || 'fabfc5f5ba30f6cb9b556cd7813b801d346ff26660'
        || 'b76b9950a5a49f9fe8047b1022c24fbba9d7feb7c6'
        || '1bf83b57e7c6a8a6150f04fb83f6d3c51ec3023554'
        || '135a169132f675f3ae2b61d72aeff22203199dd148'
        || '01c7', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('RFC 5114 1024-bit MODP Group with 160-bit Prime Order Subgroup',
    decode('A4D1CBD5C3FD34126765A442EFB99905F8104DD258AC507F'
        || 'D6406CFF14266D31266FEA1E5C41564B777E690F5504F213'
        || '160217B4B01B886A5E91547F9E2749F4D7FBD7D3B9A92EE1'
        || '909D0D2263F80A76A6A24C087A091F531DBF0A0169B6A28A'
        || 'D662A4D18E73AFA32D779D5918D08BC8858F4DCEF97C2A24'
        || '855E6EEB22B3B2E5', 'hex'),
    decode('B10B8F96A080E01DDE92DE5EAE5D54EC52C99FBCFB06A3C6'
        || '9A6A9DCA52D23B616073E28675A23D189838EF1E2EE652C0'
        || '13ECB4AEA906112324975C3CD49B83BFACCBDD7D90C4BD70'
        || '98488E9C219A73724EFFD6FAE5644738FAA31A4FF55BCCC0'
        || 'A151AF5F0DC8B4BD45BF37DF365C1A65E68CFDA76D4DA708'
        || 'DF1FB2BC2E4A4371', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('RFC 5114 2048-bit MODP Group with 224-bit Prime Order Subgroup',
    decode('AC4032EF4F2D9AE39DF30B5C8FFDAC506CDEBE7B89998CAF'
        || '74866A08CFE4FFE3A6824A4E10B9A6F0DD921F01A70C4AFA'
        || 'AB739D7700C29F52C57DB17C620A8652BE5E9001A8D66AD7'
        || 'C17669101999024AF4D027275AC1348BB8A762D0521BC98A'
        || 'E247150422EA1ED409939D54DA7460CDB5F6C6B250717CBE'
        || 'F180EB34118E98D119529A45D6F834566E3025E316A330EF'
        || 'BB77A86F0C1AB15B051AE3D428C8F8ACB70A8137150B8EEB'
        || '10E183EDD19963DDD9E263E4770589EF6AA21E7F5F2FF381'
        || 'B539CCE3409D13CD566AFBB48D6C019181E1BCFE94B30269'
        || 'EDFE72FE9B6AA4BD7B5A0F1C71CFFF4C19C418E1F6EC0179'
        || '81BC087F2A7065B384B890D3191F2BFA', 'hex'),
    decode('AD107E1E9123A9D0D660FAA79559C51FA20D64E5683B9FD1'
        || 'B54B1597B61D0A75E6FA141DF95A56DBAF9A3C407BA1DF15'
        || 'EB3D688A309C180E1DE6B85A1274A0A66D3F8152AD6AC212'
        || '9037C9EDEFDA4DF8D91E8FEF55B7394B7AD5B7D0B6C12207'
        || 'C9F98D11ED34DBF6C6BA0B2C8BBC27BE6A00E0A0B9C49708'
        || 'B3BF8A317091883681286130BC8985DB1602E714415D9330'
        || '278273C7DE31EFDC7310F7121FD5A07415987D9ADC0A486D'
        || 'CDF93ACC44328387315D75E198C641A480CD86A1B9E587E8'
        || 'BE60E69CC928B2B9C52172E413042E9B23F10B0E16E79763'
        || 'C9B53DCF4BA80A29E3FB73C16B8E75B97EF363E2FFA31F71'
        || 'CF9DE5384E71B81C0AC4DFFE0C10E64F', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('RFC 5114 2048-bit MODP Group with 256-bit Prime Order Subgroup',
    decode('3FB32C9B73134D0B2E77506660EDBD484CA7B18F21EF2054'
        || '07F4793A1A0BA12510DBC15077BE463FFF4FED4AAC0BB555'
        || 'BE3A6C1B0C6B47B1BC3773BF7E8C6F62901228F8C28CBB18'
        || 'A55AE31341000A650196F931C77A57F2DDF463E5E9EC144B'
        || '777DE62AAAB8A8628AC376D282D6ED3864E67982428EBC83'
        || '1D14348F6F2F9193B5045AF2767164E1DFC967C1FB3F2E55'
        || 'A4BD1BFFE83B9C80D052B985D182EA0ADB2A3B7313D3FE14'
        || 'C8484B1E052588B9B7D2BBD2DF016199ECD06E1557CD0915'
        || 'B3353BBB64E0EC377FD028370DF92B52C7891428CDC67EB6'
        || '184B523D1DB246C32F63078490F00EF8D647D148D4795451'
        || '5E2327CFEF98C582664B4C0F6CC41659', 'hex'),
    decode('87A8E61DB4B6663CFFBBD19C651959998CEEF608660DD0F2'
        || '5D2CEED4435E3B00E00DF8F1D61957D4FAF7DF4561B2AA30'
        || '16C3D91134096FAA3BF4296D830E9A7C209E0C6497517ABD'
        || '5A8A9D306BCF67ED91F9E6725B4758C022E0B1EF4275BF7B'
        || '6C5BFC11D45F9088B941F54EB1E59BB8BC39A0BF12307F5C'
        || '4FDB70C581B23F76B63ACAE1CAA6B7902D52526735488A0E'
        || 'F13C6D9A51BFA4AB3AD8347796524D8EF6A167B5A41825D9'
        || '67E144E5140564251CCACB83E6B486F6B3CA3F7971506026'
        || 'C0B857F689962856DED4010ABD0BE621C3A3960A54E710C3'
        || '75F26375D7014103A4B54330C198AF126116D2276E11715F'
        || '693877FAD7EF09CADB094AE91E1A1597', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('draft-ietf-tls-negotiated-ff-dhe-10 ffdhe2048',
    decode('02', 'hex'),
    decode('FFFFFFFFFFFFFFFFADF85458A2BB4A9AAFDC5620273D3CF1'
        || 'D8B9C583CE2D3695A9E13641146433FBCC939DCE249B3EF9'
        || '7D2FE363630C75D8F681B202AEC4617AD3DF1ED5D5FD6561'
        || '2433F51F5F066ED0856365553DED1AF3B557135E7F57C935'
        || '984F0C70E0E68B77E2A689DAF3EFE8721DF158A136ADE735'
        || '30ACCA4F483A797ABC0AB182B324FB61D108A94BB2C8E3FB'
        || 'B96ADAB760D7F4681D4F42A3DE394DF4AE56EDE76372BB19'
        || '0B07A7C8EE0A6D709E02FCE1CDF7E2ECC03404CD28342F61'
        || '9172FE9CE98583FF8E4F1232EEF28183C3FE3B1B4C6FAD73'
        || '3BB5FCBC2EC22005C58EF1837D1683B2C6F34A26C1B2EFFA'
        || '886B423861285C97FFFFFFFFFFFFFFFF', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('draft-ietf-tls-negotiated-ff-dhe-10 ffdhe3072',
    decode('02', 'hex'),
    decode('FFFFFFFFFFFFFFFFADF85458A2BB4A9AAFDC5620273D3CF1'
        || 'D8B9C583CE2D3695A9E13641146433FBCC939DCE249B3EF9'
        || '7D2FE363630C75D8F681B202AEC4617AD3DF1ED5D5FD6561'
        || '2433F51F5F066ED0856365553DED1AF3B557135E7F57C935'
        || '984F0C70E0E68B77E2A689DAF3EFE8721DF158A136ADE735'
        || '30ACCA4F483A797ABC0AB182B324FB61D108A94BB2C8E3FB'
        || 'B96ADAB760D7F4681D4F42A3DE394DF4AE56EDE76372BB19'
        || '0B07A7C8EE0A6D709E02FCE1CDF7E2ECC03404CD28342F61'
        || '9172FE9CE98583FF8E4F1232EEF28183C3FE3B1B4C6FAD73'
        || '3BB5FCBC2EC22005C58EF1837D1683B2C6F34A26C1B2EFFA'
        || '886B4238611FCFDCDE355B3B6519035BBC34F4DEF99C0238'
        || '61B46FC9D6E6C9077AD91D2691F7F7EE598CB0FAC186D91C'
        || 'AEFE130985139270B4130C93BC437944F4FD4452E2D74DD3'
        || '64F2E21E71F54BFF5CAE82AB9C9DF69EE86D2BC522363A0D'
        || 'ABC521979B0DEADA1DBF9A42D5C4484E0ABCD06BFA53DDEF'
        || '3C1B20EE3FD59D7C25E41D2B66C62E37FFFFFFFFFFFFFFFF', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('draft-ietf-tls-negotiated-ff-dhe-10 ffdhe4096',
    decode('02', 'hex'),
    decode('FFFFFFFFFFFFFFFFADF85458A2BB4A9AAFDC5620273D3CF1'
        || 'D8B9C583CE2D3695A9E13641146433FBCC939DCE249B3EF9'
        || '7D2FE363630C75D8F681B202AEC4617AD3DF1ED5D5FD6561'
        || '2433F51F5F066ED0856365553DED1AF3B557135E7F57C935'
        || '984F0C70E0E68B77E2A689DAF3EFE8721DF158A136ADE735'
        || '30ACCA4F483A797ABC0AB182B324FB61D108A94BB2C8E3FB'
        || 'B96ADAB760D7F4681D4F42A3DE394DF4AE56EDE76372BB19'
        || '0B07A7C8EE0A6D709E02FCE1CDF7E2ECC03404CD28342F61'
        || '9172FE9CE98583FF8E4F1232EEF28183C3FE3B1B4C6FAD73'
        || '3BB5FCBC2EC22005C58EF1837D1683B2C6F34A26C1B2EFFA'
        || '886B4238611FCFDCDE355B3B6519035BBC34F4DEF99C0238'
        || '61B46FC9D6E6C9077AD91D2691F7F7EE598CB0FAC186D91C'
        || 'AEFE130985139270B4130C93BC437944F4FD4452E2D74DD3'
        || '64F2E21E71F54BFF5CAE82AB9C9DF69EE86D2BC522363A0D'
        || 'ABC521979B0DEADA1DBF9A42D5C4484E0ABCD06BFA53DDEF'
        || '3C1B20EE3FD59D7C25E41D2B669E1EF16E6F52C3164DF4FB'
        || '7930E9E4E58857B6AC7D5F42D69F6D187763CF1D55034004'
        || '87F55BA57E31CC7A7135C886EFB4318AED6A1E012D9E6832'
        || 'A907600A918130C46DC778F971AD0038092999A333CB8B7A'
        || '1A1DB93D7140003C2A4ECEA9F98D0ACC0A8291CDCEC97DCF'
        || '8EC9B55A7F88A46B4DB5A851F44182E1C68A007E5E655F6A'
        || 'FFFFFFFFFFFFFFFF', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('draft-ietf-tls-negotiated-ff-dhe-10 ffdhe6144',
    decode('02', 'hex'),
    decode('FFFFFFFFFFFFFFFFADF85458A2BB4A9AAFDC5620273D3CF1'
        || 'D8B9C583CE2D3695A9E13641146433FBCC939DCE249B3EF9'
        || '7D2FE363630C75D8F681B202AEC4617AD3DF1ED5D5FD6561'
        || '2433F51F5F066ED0856365553DED1AF3B557135E7F57C935'
        || '984F0C70E0E68B77E2A689DAF3EFE8721DF158A136ADE735'
        || '30ACCA4F483A797ABC0AB182B324FB61D108A94BB2C8E3FB'
        || 'B96ADAB760D7F4681D4F42A3DE394DF4AE56EDE76372BB19'
        || '0B07A7C8EE0A6D709E02FCE1CDF7E2ECC03404CD28342F61'
        || '9172FE9CE98583FF8E4F1232EEF28183C3FE3B1B4C6FAD73'
        || '3BB5FCBC2EC22005C58EF1837D1683B2C6F34A26C1B2EFFA'
        || '886B4238611FCFDCDE355B3B6519035BBC34F4DEF99C0238'
        || '61B46FC9D6E6C9077AD91D2691F7F7EE598CB0FAC186D91C'
        || 'AEFE130985139270B4130C93BC437944F4FD4452E2D74DD3'
        || '64F2E21E71F54BFF5CAE82AB9C9DF69EE86D2BC522363A0D'
        || 'ABC521979B0DEADA1DBF9A42D5C4484E0ABCD06BFA53DDEF'
        || '3C1B20EE3FD59D7C25E41D2B669E1EF16E6F52C3164DF4FB'
        || '7930E9E4E58857B6AC7D5F42D69F6D187763CF1D55034004'
        || '87F55BA57E31CC7A7135C886EFB4318AED6A1E012D9E6832'
        || 'A907600A918130C46DC778F971AD0038092999A333CB8B7A'
        || '1A1DB93D7140003C2A4ECEA9F98D0ACC0A8291CDCEC97DCF'
        || '8EC9B55A7F88A46B4DB5A851F44182E1C68A007E5E0DD902'
        || '0BFD64B645036C7A4E677D2C38532A3A23BA4442CAF53EA6'
        || '3BB454329B7624C8917BDD64B1C0FD4CB38E8C334C701C3A'
        || 'CDAD0657FCCFEC719B1F5C3E4E46041F388147FB4CFDB477'
        || 'A52471F7A9A96910B855322EDB6340D8A00EF092350511E3'
        || '0ABEC1FFF9E3A26E7FB29F8C183023C3587E38DA0077D9B4'
        || '763E4E4B94B2BBC194C6651E77CAF992EEAAC0232A281BF6'
        || 'B3A739C1226116820AE8DB5847A67CBEF9C9091B462D538C'
        || 'D72B03746AE77F5E62292C311562A846505DC82DB854338A'
        || 'E49F5235C95B91178CCF2DD5CACEF403EC9D1810C6272B04'
        || '5B3B71F9DC6B80D63FDD4A8E9ADB1E6962A69526D43161C1'
        || 'A41D570D7938DAD4A40E329CD0E40E65FFFFFFFFFFFFFFFF', 'hex'));

INSERT INTO dh_groups (group_name, generator, prime) VALUES ('draft-ietf-tls-negotiated-ff-dhe-10 ffdhe8192',
    decode('02', 'hex'),
    decode('FFFFFFFFFFFFFFFFADF85458A2BB4A9AAFDC5620273D3CF1'
        || 'D8B9C583CE2D3695A9E13641146433FBCC939DCE249B3EF9'
        || '7D2FE363630C75D8F681B202AEC4617AD3DF1ED5D5FD6561'
        || '2433F51F5F066ED0856365553DED1AF3B557135E7F57C935'
        || '984F0C70E0E68B77E2A689DAF3EFE8721DF158A136ADE735'
        || '30ACCA4F483A797ABC0AB182B324FB61D108A94BB2C8E3FB'
        || 'B96ADAB760D7F4681D4F42A3DE394DF4AE56EDE76372BB19'
        || '0B07A7C8EE0A6D709E02FCE1CDF7E2ECC03404CD28342F61'
        || '9172FE9CE98583FF8E4F1232EEF28183C3FE3B1B4C6FAD73'
        || '3BB5FCBC2EC22005C58EF1837D1683B2C6F34A26C1B2EFFA'
        || '886B4238611FCFDCDE355B3B6519035BBC34F4DEF99C0238'
        || '61B46FC9D6E6C9077AD91D2691F7F7EE598CB0FAC186D91C'
        || 'AEFE130985139270B4130C93BC437944F4FD4452E2D74DD3'
        || '64F2E21E71F54BFF5CAE82AB9C9DF69EE86D2BC522363A0D'
        || 'ABC521979B0DEADA1DBF9A42D5C4484E0ABCD06BFA53DDEF'
        || '3C1B20EE3FD59D7C25E41D2B669E1EF16E6F52C3164DF4FB'
        || '7930E9E4E58857B6AC7D5F42D69F6D187763CF1D55034004'
        || '87F55BA57E31CC7A7135C886EFB4318AED6A1E012D9E6832'
        || 'A907600A918130C46DC778F971AD0038092999A333CB8B7A'
        || '1A1DB93D7140003C2A4ECEA9F98D0ACC0A8291CDCEC97DCF'
        || '8EC9B55A7F88A46B4DB5A851F44182E1C68A007E5E0DD902'
        || '0BFD64B645036C7A4E677D2C38532A3A23BA4442CAF53EA6'
        || '3BB454329B7624C8917BDD64B1C0FD4CB38E8C334C701C3A'
        || 'CDAD0657FCCFEC719B1F5C3E4E46041F388147FB4CFDB477'
        || 'A52471F7A9A96910B855322EDB6340D8A00EF092350511E3'
        || '0ABEC1FFF9E3A26E7FB29F8C183023C3587E38DA0077D9B4'
        || '763E4E4B94B2BBC194C6651E77CAF992EEAAC0232A281BF6'
        || 'B3A739C1226116820AE8DB5847A67CBEF9C9091B462D538C'
        || 'D72B03746AE77F5E62292C311562A846505DC82DB854338A'
        || 'E49F5235C95B91178CCF2DD5CACEF403EC9D1810C6272B04'
        || '5B3B71F9DC6B80D63FDD4A8E9ADB1E6962A69526D43161C1'
        || 'A41D570D7938DAD4A40E329CCFF46AAA36AD004CF600C838'
        || '1E425A31D951AE64FDB23FCEC9509D43687FEB69EDD1CC5E'
        || '0B8CC3BDF64B10EF86B63142A3AB8829555B2F747C932665'
        || 'CB2C0F1CC01BD70229388839D2AF05E454504AC78B758282'
        || '2846C0BA35C35F5C59160CC046FD8251541FC68C9C86B022'
        || 'BB7099876A460E7451A8A93109703FEE1C217E6C3826E52C'
        || '51AA691E0E423CFC99E9E31650C1217B624816CDAD9A95F9'
        || 'D5B8019488D9C0A0A1FE3075A577E23183F81D4A3F2FA457'
        || '1EFC8CE0BA8A4FE8B6855DFE72B0A66EDED2FBABFBE58A30'
        || 'FAFABE1C5D71A87E2F741EF8C1FE86FEA6BBFDE530677F0D'
        || '97D11D49F7A8443D0822E506A9F4614E011E2A94838FF88C'
        || 'D68C8BB7C5C6424CFFFFFFFFFFFFFFFF', 'hex'));

GRANT ALL PRIVILEGES ON TABLE test_results, srv_results, certificates, srv_certificates, certificate_subjects, tlsa_records, ciphers, srv_ciphers, srv_certificate_errors, public_servers, certificate_sans, news_posts, srv_mechanisms, dh_groups TO xmppoke;

GRANT ALL PRIVILEGES ON SEQUENCE test_results_test_id_seq, srv_results_srv_result_id_seq, srv_results_srv_result_id_seq, tlsa_records_tlsa_record_id_seq, certificates_certificate_id_seq, certificate_subjects_certificate_subject_id_seq, srv_certificates_srv_certificates_id_seq, certificate_sans_certificate_san_id_seq, srv_mechanisms_srv_mechanisms_id_seq, dh_groups_dh_group_id_seq, public_servers_public_server_id_seq TO xmppoke;