------------------------
-- IHE TLS Test Cases Import
-- ceoche, 2015-2016
------------------------

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

--
-- /!\ BEFORE RUNNING THE SCRIPT /!\
--
-- 1. make a dump of the targeted database.
--
-- 2. Generate the set of certificates used for testing (in gazelle-ATNA tool, admin section) (note which certificate authority was used)
--
-- 3. Update in this script (line 32) the certificate authority id used to generate the testing certificate
--    (marqued with XXX in example : CREATE FUNCTION cert_CA_id() RETURNS integer AS 'SELECT XXX AS id;' LANGUAGE SQL ;)
--
-- 4. Run this script :
--        $ psql -U [user-db] [database name] < [sql script name] (example : $ psql -U gazelle tls < sript.sql)
--
-- 5. Execute updateIndexes
--
--
-- Certificate Authority ID :

CREATE FUNCTION cert_CA_id() RETURNS integer AS 'SELECT 643 AS id;' LANGUAGE SQL ;

--
-- List of certificates ids
--
CREATE FUNCTION cert_valid_id() RETURNS integer AS 'SELECT Max(id) FROM tls_certificate WHERE Right(subject, 5)=''valid''' LANGUAGE SQL ;
CREATE FUNCTION cert_selfsigned_id() RETURNS integer AS 'SELECT Max(id) FROM tls_certificate WHERE Right(subject, 11)=''self-signed''' LANGUAGE SQL ;
CREATE FUNCTION cert_expired_id() RETURNS integer AS 'SELECT Max(id) FROM tls_certificate WHERE Right(subject, 7)=''expired''' LANGUAGE SQL ;
CREATE FUNCTION cert_revoked_id() RETURNS integer AS 'SELECT Max(id) FROM tls_certificate WHERE Right(subject, 7)=''revoked''' LANGUAGE SQL ;
CREATE FUNCTION cert_unknown_id() RETURNS integer AS 'SELECT Max(id) FROM tls_certificate WHERE Right(subject, 7)=''unknown''' LANGUAGE SQL ;
CREATE FUNCTION cert_corrupted_id() RETURNS integer AS 'SELECT Max(id) FROM tls_certificate WHERE Right(subject, 9)=''corrupted''' LANGUAGE SQL ;
CREATE FUNCTION cert_wrongkey_id() RETURNS integer AS 'SELECT Max(id) FROM tls_certificate WHERE Right(subject, 8)=''wrongKey''' LANGUAGE SQL ;

--
-- IHE ATNA Authenticate (SUT : server)
--

-- data set
INSERT INTO tls_test_data_set (id, last_changed, last_modifier_id, is_check_revocation, context, name, is_need_client_auth, is_use_sys_ca_certs, certificate_id)
VALUES (nextval('tls_test_data_set_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', TRUE, 2, 'IHE_Valid', TRUE, FALSE, cert_valid_id()) ;

INSERT INTO tls_test_data_set_tls_certificate (tls_test_data_set_id, trustedissuers_id) VALUES (currval('tls_test_data_set_id_seq'), cert_CA_id()) ;

INSERT INTO tls_test_data_set_tls_test_ciphersuite (tls_test_data_set_id, ciphersuite) VALUES (currval('tls_test_data_set_id_seq'), 2) ;
INSERT INTO tls_test_data_set_tls_test_protocol (tls_test_data_set_id, protocol) VALUES (currval('tls_test_data_set_id_seq'), 2) ;

-- test case
INSERT INTO tls_test_case (id, last_changed, last_modifier_id, context, description, expected_alert_level, expected_handshake_success, mandatory_alert_description, mandatory_alert_level, mandatory_handshake_success, name, suttype, actor_id, dataset_id)
VALUES (nextval('tls_test_case_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', 2, 'This test automates the "ATNA_Authenticate_with_TLS_Tools" mechanism for system that act as server. A simulated client with a valid certificate will attempts to connect to your system. The secured connection must succeed.', NULL, TRUE, FALSE, FALSE, TRUE, 'IHE_ATNA_Authenticate (for server systems)', 1, NULL, currval('tls_test_data_set_id_seq')) ;

--
-- IHE Error Self Signed
--

-- data set
INSERT INTO tls_test_data_set (id, last_changed, last_modifier_id, is_check_revocation, context, name, is_need_client_auth, is_use_sys_ca_certs, certificate_id)
VALUES (nextval('tls_test_data_set_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', TRUE, 2, 'IHE_Error_Self-Signed', TRUE, FALSE, cert_selfsigned_id()) ;

INSERT INTO tls_test_data_set_tls_certificate (tls_test_data_set_id, trustedissuers_id) VALUES (currval('tls_test_data_set_id_seq'), cert_CA_id()) ;

INSERT INTO tls_test_data_set_tls_test_ciphersuite (tls_test_data_set_id, ciphersuite) VALUES (currval('tls_test_data_set_id_seq'), 2) ;
INSERT INTO tls_test_data_set_tls_test_protocol (tls_test_data_set_id, protocol) VALUES (currval('tls_test_data_set_id_seq'), 2) ;

-- test case
INSERT INTO tls_test_case (id, last_changed, last_modifier_id, context, description, expected_alert_level, expected_handshake_success, mandatory_alert_description, mandatory_alert_level, mandatory_handshake_success, name, suttype, actor_id, dataset_id)
VALUES (nextval('tls_test_case_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', 2, 'In a large scale network, Consumers with self-signed certificate must not be able to connect to secured servers. A simulated "self-signed client" will attempt to connect to your system. It must be rejected. The reception of a close_notify or a fatal/warning alert is expected (see RFC 2246 section 7.2.1 and 7.2.2).', 1, FALSE, FALSE, FALSE, TRUE, 'IHE_ErrorCase_Self-Signed', 1, NULL, currval('tls_test_data_set_id_seq')) ;

INSERT INTO tls_test_case_expectedalertdescriptions (tls_test_case_id, element) VALUES (currval('tls_test_case_id_seq'), 11) ;
INSERT INTO tls_test_case_expectedalertdescriptions (tls_test_case_id, element) VALUES (currval('tls_test_case_id_seq'), 13) ;

--
-- IHE Error Expired
--

-- data set
INSERT INTO tls_test_data_set (id, last_changed, last_modifier_id, is_check_revocation, context, name, is_need_client_auth, is_use_sys_ca_certs, certificate_id)
VALUES (nextval('tls_test_data_set_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', TRUE, 2, 'IHE_Error_Expired', TRUE, FALSE, cert_expired_id()) ;

INSERT INTO tls_test_data_set_tls_certificate (tls_test_data_set_id, trustedissuers_id) VALUES (currval('tls_test_data_set_id_seq'), cert_CA_id()) ;

INSERT INTO tls_test_data_set_tls_test_ciphersuite (tls_test_data_set_id, ciphersuite) VALUES (currval('tls_test_data_set_id_seq'), 2) ;
INSERT INTO tls_test_data_set_tls_test_protocol (tls_test_data_set_id, protocol) VALUES (currval('tls_test_data_set_id_seq'), 2) ;

-- test case
INSERT INTO tls_test_case (id, last_changed, last_modifier_id, context, description, expected_alert_level, expected_handshake_success, mandatory_alert_description, mandatory_alert_level, mandatory_handshake_success, name, suttype, actor_id, dataset_id)
VALUES (nextval('tls_test_case_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', 2, 'A simulated client with an expired certificate will attempts to connect to a system that act as a secured server. The system must reject the connection. The reception of a close_notify or a fatal/warning alert is expected (see RFC 2246 section 7.2.1 and 7.2.2).', 1, FALSE, FALSE, FALSE, TRUE, 'IHE_ErrorCase_Expired', 1, NULL, currval('tls_test_data_set_id_seq')) ;

INSERT INTO tls_test_case_expectedalertdescriptions (tls_test_case_id, element) VALUES (currval('tls_test_case_id_seq'), 10) ;

--
-- IHE Error Revoked
--

-- data set
INSERT INTO tls_test_data_set (id, last_changed, last_modifier_id, is_check_revocation, context, name, is_need_client_auth, is_use_sys_ca_certs, certificate_id)
VALUES (nextval('tls_test_data_set_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', TRUE, 2, 'IHE_Error_Revoked', TRUE, FALSE, cert_revoked_id()) ;

INSERT INTO tls_test_data_set_tls_certificate (tls_test_data_set_id, trustedissuers_id) VALUES (currval('tls_test_data_set_id_seq'), cert_CA_id()) ;

INSERT INTO tls_test_data_set_tls_test_ciphersuite (tls_test_data_set_id, ciphersuite) VALUES (currval('tls_test_data_set_id_seq'), 2) ;
INSERT INTO tls_test_data_set_tls_test_protocol (tls_test_data_set_id, protocol) VALUES (currval('tls_test_data_set_id_seq'), 2) ;

-- test case
INSERT INTO tls_test_case (id, last_changed, last_modifier_id, context, description, expected_alert_level, expected_handshake_success, mandatory_alert_description, mandatory_alert_level, mandatory_handshake_success, name, suttype, actor_id, dataset_id)
VALUES (nextval('tls_test_case_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', 2, 'A simulated client with a revoked certificate will attempt to connect to a system that act as a secured server. The system must reject the connection. The reception of a close_notify or a fatal/warning alert is expected (see RFC 2246 section 7.2.1 and 7.2.2).', 1, FALSE, FALSE, FALSE, TRUE, 'IHE_ErrorCase_Revoked', 1, NULL, currval('tls_test_data_set_id_seq')) ;

INSERT INTO tls_test_case_expectedalertdescriptions (tls_test_case_id, element) VALUES (currval('tls_test_case_id_seq'), 9) ;

--
-- IHE Error Unknown
--

-- data set
INSERT INTO tls_test_data_set (id, last_changed, last_modifier_id, is_check_revocation, context, name, is_need_client_auth, is_use_sys_ca_certs, certificate_id)
VALUES (nextval('tls_test_data_set_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', TRUE, 2, 'IHE_Error_Unknown', TRUE, FALSE, cert_unknown_id()) ;

INSERT INTO tls_test_data_set_tls_certificate (tls_test_data_set_id, trustedissuers_id) VALUES (currval('tls_test_data_set_id_seq'), cert_CA_id()) ;

INSERT INTO tls_test_data_set_tls_test_ciphersuite (tls_test_data_set_id, ciphersuite) VALUES (currval('tls_test_data_set_id_seq'), 2) ;
INSERT INTO tls_test_data_set_tls_test_protocol (tls_test_data_set_id, protocol) VALUES (currval('tls_test_data_set_id_seq'), 2) ;

-- test case
INSERT INTO tls_test_case (id, last_changed, last_modifier_id, context, description, expected_alert_level, expected_handshake_success, mandatory_alert_description, mandatory_alert_level, mandatory_handshake_success, name, suttype, actor_id, dataset_id)
VALUES (nextval('tls_test_case_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', 2, 'A simulated client with an untrusted certificate will attempt to connect to a system that act as a secured server. The system must reject the connection. The reception of a close_notify or a fatal/warning alert is expected (see RFC 2246 section 7.2.1 and 7.2.2).', 1, FALSE, FALSE, FALSE, TRUE, 'IHE_ErrorCase_Unknown', 1, NULL, currval('tls_test_data_set_id_seq')) ;

INSERT INTO tls_test_case_expectedalertdescriptions (tls_test_case_id, element) VALUES (currval('tls_test_case_id_seq'), 13) ;

--
-- IHE Error Corrupted
--

-- data set
INSERT INTO tls_test_data_set (id, last_changed, last_modifier_id, is_check_revocation, context, name, is_need_client_auth, is_use_sys_ca_certs, certificate_id)
VALUES (nextval('tls_test_data_set_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', TRUE, 2, 'IHE_Error_Corrupted', TRUE, FALSE, cert_corrupted_id()) ;

INSERT INTO tls_test_data_set_tls_certificate (tls_test_data_set_id, trustedissuers_id) VALUES (currval('tls_test_data_set_id_seq'), cert_CA_id()) ;

INSERT INTO tls_test_data_set_tls_test_ciphersuite (tls_test_data_set_id, ciphersuite) VALUES (currval('tls_test_data_set_id_seq'), 2) ;
INSERT INTO tls_test_data_set_tls_test_protocol (tls_test_data_set_id, protocol) VALUES (currval('tls_test_data_set_id_seq'), 2) ;

-- test case
INSERT INTO tls_test_case (id, last_changed, last_modifier_id, context, description, expected_alert_level, expected_handshake_success, mandatory_alert_description, mandatory_alert_level, mandatory_handshake_success, name, suttype, actor_id, dataset_id)
VALUES (nextval('tls_test_case_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', 2, 'A simulated client with an corrupted certificate will attempts to connect to a system that act as a secured server. The system must reject the connection. The reception of a close_notify or a fatal/warning alert is expected (see RFC 2246 section 7.2.1 and 7.2.2).', 1, FALSE, FALSE, FALSE, TRUE, 'IHE_ErrorCase_Corrupted', 1, NULL, currval('tls_test_data_set_id_seq')) ;

INSERT INTO tls_test_case_expectedalertdescriptions (tls_test_case_id, element) VALUES (currval('tls_test_case_id_seq'), 7) ;
INSERT INTO tls_test_case_expectedalertdescriptions (tls_test_case_id, element) VALUES (currval('tls_test_case_id_seq'), 16) ;

--
-- IHE Error Without Authentication
--

-- data set
INSERT INTO tls_test_data_set (id, last_changed, last_modifier_id, is_check_revocation, context, name, is_need_client_auth, is_use_sys_ca_certs, certificate_id)
VALUES (nextval('tls_test_data_set_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', TRUE, 2, 'IHE_Error_Without_Authentication', FALSE, FALSE, cert_valid_id()) ;

INSERT INTO tls_test_data_set_tls_certificate (tls_test_data_set_id, trustedissuers_id) VALUES (currval('tls_test_data_set_id_seq'), cert_CA_id()) ;

INSERT INTO tls_test_data_set_tls_test_ciphersuite (tls_test_data_set_id, ciphersuite) VALUES (currval('tls_test_data_set_id_seq'), 2) ;
INSERT INTO tls_test_data_set_tls_test_protocol (tls_test_data_set_id, protocol) VALUES (currval('tls_test_data_set_id_seq'), 2) ;

-- test case
INSERT INTO tls_test_case (id, last_changed, last_modifier_id, context, description, expected_alert_level, expected_handshake_success, mandatory_alert_description, mandatory_alert_level, mandatory_handshake_success, name, suttype, actor_id, dataset_id)
VALUES (nextval('tls_test_case_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', 2, 'A simulated client without any certificate will attempt to connect to a system that act as a secured server. The system must reject the connection. The reception of a close_notify or a fatal/warning alert is expected (see RFC 2246 section 7.2.1 and 7.2.2).', 1, FALSE, FALSE, FALSE, TRUE, 'IHE_ErrorCase_Without_Authentication', 1, NULL, currval('tls_test_data_set_id_seq')) ;

INSERT INTO tls_test_case_expectedalertdescriptions (tls_test_case_id, element) VALUES (currval('tls_test_case_id_seq'), 7) ;
INSERT INTO tls_test_case_expectedalertdescriptions (tls_test_case_id, element) VALUES (currval('tls_test_case_id_seq'), 6) ;

--
-- IHE Error Wrong Key
--

-- data set
INSERT INTO tls_test_data_set (id, last_changed, last_modifier_id, is_check_revocation, context, name, is_need_client_auth, is_use_sys_ca_certs, certificate_id)
VALUES (nextval('tls_test_data_set_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', TRUE, 2, 'IHE_Error_Wrong_Key', TRUE, FALSE, cert_wrongkey_id()) ;

INSERT INTO tls_test_data_set_tls_certificate (tls_test_data_set_id, trustedissuers_id) VALUES (currval('tls_test_data_set_id_seq'), cert_CA_id()) ;

INSERT INTO tls_test_data_set_tls_test_ciphersuite (tls_test_data_set_id, ciphersuite) VALUES (currval('tls_test_data_set_id_seq'), 2) ;
INSERT INTO tls_test_data_set_tls_test_protocol (tls_test_data_set_id, protocol) VALUES (currval('tls_test_data_set_id_seq'), 2) ;

-- test case
INSERT INTO tls_test_case (id, last_changed, last_modifier_id, context, description, expected_alert_level, expected_handshake_success, mandatory_alert_description, mandatory_alert_level, mandatory_handshake_success, name, suttype, actor_id, dataset_id)
VALUES (nextval('tls_test_case_id_seq'), '2015-03-26 11:30:00.000000+01', 'ceoche', 2, 'A simulated client with a certificate, which do not match with its private key, will attempts to connect to a system that act as a secured server. The system must reject the connection. The reception of a close_notify or a fatal/warning alert is expected (see RFC 2246 section 7.2.1 and 7.2.2).', 1, FALSE, FALSE, FALSE, TRUE, 'IHE_ErrorCase_Wrong_Key', 1, NULL, currval('tls_test_data_set_id_seq')) ;

INSERT INTO tls_test_case_expectedalertdescriptions (tls_test_case_id, element) VALUES (currval('tls_test_case_id_seq'), 16) ;
INSERT INTO tls_test_case_expectedalertdescriptions (tls_test_case_id, element) VALUES (currval('tls_test_case_id_seq'), 7) ;

--
-- Clear
--

DROP FUNCTION cert_CA_id() ;
DROP FUNCTION cert_valid_id() ;
DROP FUNCTION cert_selfsigned_id() ;
DROP FUNCTION cert_expired_id() ;
DROP FUNCTION cert_revoked_id() ;
DROP FUNCTION cert_unknown_id() ;
DROP FUNCTION cert_corrupted_id() ;
DROP FUNCTION cert_wrongkey_id() ;

--
-- End of script
--
