aboutsummaryrefslogtreecommitdiffstats
path: root/src/libcharon/plugins/sql
diff options
context:
space:
mode:
Diffstat (limited to 'src/libcharon/plugins/sql')
-rw-r--r--src/libcharon/plugins/sql/Makefile.am17
-rw-r--r--src/libcharon/plugins/sql/cred.sql24
-rw-r--r--src/libcharon/plugins/sql/mysql.sql226
-rw-r--r--src/libcharon/plugins/sql/sql_config.c535
-rw-r--r--src/libcharon/plugins/sql/sql_config.h53
-rw-r--r--src/libcharon/plugins/sql/sql_cred.c365
-rw-r--r--src/libcharon/plugins/sql/sql_cred.h53
-rw-r--r--src/libcharon/plugins/sql/sql_logger.c145
-rw-r--r--src/libcharon/plugins/sql/sql_logger.h53
-rw-r--r--src/libcharon/plugins/sql/sql_plugin.c107
-rw-r--r--src/libcharon/plugins/sql/sql_plugin.h42
-rw-r--r--src/libcharon/plugins/sql/sqlite.sql227
-rw-r--r--src/libcharon/plugins/sql/test.sql126
13 files changed, 1973 insertions, 0 deletions
diff --git a/src/libcharon/plugins/sql/Makefile.am b/src/libcharon/plugins/sql/Makefile.am
new file mode 100644
index 000000000..ece07f538
--- /dev/null
+++ b/src/libcharon/plugins/sql/Makefile.am
@@ -0,0 +1,17 @@
+
+INCLUDES = -I$(top_srcdir)/src/libstrongswan -I$(top_srcdir)/src/charon
+
+AM_CFLAGS = -rdynamic \
+ -DPLUGINS=\""${libstrongswan_plugins}\""
+
+if MONOLITHIC
+noinst_LTLIBRARIES = libstrongswan-sql.la
+else
+plugin_LTLIBRARIES = libstrongswan-sql.la
+endif
+
+libstrongswan_sql_la_SOURCES = \
+ sql_plugin.h sql_plugin.c sql_config.h sql_config.c \
+ sql_cred.h sql_cred.c sql_logger.h sql_logger.c
+
+libstrongswan_sql_la_LDFLAGS = -module -avoid-version
diff --git a/src/libcharon/plugins/sql/cred.sql b/src/libcharon/plugins/sql/cred.sql
new file mode 100644
index 000000000..4b53e4e4b
--- /dev/null
+++ b/src/libcharon/plugins/sql/cred.sql
@@ -0,0 +1,24 @@
+
+DROP TABLE IF EXISTS shared_secrets;
+CREATE TABLE shared_secrets (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ type INTEGER,
+ local TEXT,
+ remote TEXT
+);
+
+DROP TABLE IF EXISTS certificates;
+CREATE TABLE certificates (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ type INTEGER,
+ subject TEXT,
+ data BLOB,
+);
+
+DROP TABLE IF EXISTS private_keys;
+CREATE TABLE private_keys (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ type INTEGER,
+ keyid BLOB,
+ data BLOB,
+);
diff --git a/src/libcharon/plugins/sql/mysql.sql b/src/libcharon/plugins/sql/mysql.sql
new file mode 100644
index 000000000..1a01394b6
--- /dev/null
+++ b/src/libcharon/plugins/sql/mysql.sql
@@ -0,0 +1,226 @@
+
+
+DROP TABLE IF EXISTS `identities`;
+CREATE TABLE `identities` (
+ `id` int(10) unsigned NOT NULL auto_increment,
+ `type` tinyint(4) unsigned NOT NULL,
+ `data` varbinary(64) NOT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE (`type`, `data`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `child_configs`;
+CREATE TABLE `child_configs` (
+ `id` int(10) unsigned NOT NULL auto_increment,
+ `name` varchar(32) collate utf8_unicode_ci NOT NULL,
+ `lifetime` mediumint(8) unsigned NOT NULL default '1500',
+ `rekeytime` mediumint(8) unsigned NOT NULL default '1200',
+ `jitter` mediumint(8) unsigned NOT NULL default '60',
+ `updown` varchar(128) collate utf8_unicode_ci default NULL,
+ `hostaccess` tinyint(1) unsigned NOT NULL default '0',
+ `mode` tinyint(4) unsigned NOT NULL default '1',
+ `dpd_action` tinyint(4) unsigned NOT NULL default '0',
+ `close_action` tinyint(4) unsigned NOT NULL default '0',
+ `ipcomp` tinyint(4) unsigned NOT NULL default '0',
+ PRIMARY KEY (`id`),
+ INDEX (`name`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `child_config_traffic_selector`;
+CREATE TABLE `child_config_traffic_selector` (
+ `child_cfg` int(10) unsigned NOT NULL,
+ `traffic_selector` int(10) unsigned NOT NULL,
+ `kind` tinyint(3) unsigned NOT NULL,
+ INDEX (`child_cfg`, `traffic_selector`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `ike_configs`;
+CREATE TABLE `ike_configs` (
+ `id` int(10) unsigned NOT NULL auto_increment,
+ `certreq` tinyint(3) unsigned NOT NULL default '1',
+ `force_encap` tinyint(1) NOT NULL default '0',
+ `local` varchar(128) collate utf8_unicode_ci NOT NULL,
+ `remote` varchar(128) collate utf8_unicode_ci NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `peer_configs`;
+CREATE TABLE `peer_configs` (
+ `id` int(10) unsigned NOT NULL auto_increment,
+ `name` varchar(32) collate utf8_unicode_ci NOT NULL,
+ `ike_version` tinyint(3) unsigned NOT NULL default '2',
+ `ike_cfg` int(10) unsigned NOT NULL,
+ `local_id` varchar(64) collate utf8_unicode_ci NOT NULL,
+ `remote_id` varchar(64) collate utf8_unicode_ci NOT NULL,
+ `cert_policy` tinyint(3) unsigned NOT NULL default '1',
+ `uniqueid` tinyint(3) unsigned NOT NULL default '0',
+ `auth_method` tinyint(3) unsigned NOT NULL default '1',
+ `eap_type` tinyint(3) unsigned NOT NULL default '0',
+ `eap_vendor` smallint(5) unsigned NOT NULL default '0',
+ `keyingtries` tinyint(3) unsigned NOT NULL default '3',
+ `rekeytime` mediumint(8) unsigned NOT NULL default '7200',
+ `reauthtime` mediumint(8) unsigned NOT NULL default '0',
+ `jitter` mediumint(8) unsigned NOT NULL default '180',
+ `overtime` mediumint(8) unsigned NOT NULL default '300',
+ `mobike` tinyint(1) NOT NULL default '1',
+ `dpd_delay` mediumint(8) unsigned NOT NULL default '120',
+ `virtual` varchar(40) default NULL,
+ `pool` varchar(32) default NULL,
+ `mediation` tinyint(1) NOT NULL default '0',
+ `mediated_by` int(10) unsigned NOT NULL default '0',
+ `peer_id` int(10) unsigned NOT NULL default '0',
+ PRIMARY KEY (`id`),
+ INDEX (`name`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `peer_config_child_config`;
+CREATE TABLE `peer_config_child_config` (
+ `peer_cfg` int(10) unsigned NOT NULL,
+ `child_cfg` int(10) unsigned NOT NULL,
+ PRIMARY KEY (`peer_cfg`, `child_cfg`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `traffic_selectors`;
+CREATE TABLE `traffic_selectors` (
+ `id` int(10) unsigned NOT NULL auto_increment,
+ `type` tinyint(3) unsigned NOT NULL default '7',
+ `protocol` smallint(5) unsigned NOT NULL default '0',
+ `start_addr` varbinary(16) default NULL,
+ `end_addr` varbinary(16) default NULL,
+ `start_port` smallint(5) unsigned NOT NULL default '0',
+ `end_port` smallint(5) unsigned NOT NULL default '65535',
+ PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS certificates;
+CREATE TABLE certificates (
+ `id` int(10) unsigned NOT NULL auto_increment,
+ `type` tinyint(3) unsigned NOT NULL,
+ `keytype` tinyint(3) unsigned NOT NULL,
+ `data` BLOB NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS certificate_identity;
+CREATE TABLE certificate_identity (
+ `certificate` int(10) unsigned NOT NULL,
+ `identity` int(10) unsigned NOT NULL,
+ PRIMARY KEY (`certificate`, `identity`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS private_keys;
+CREATE TABLE private_keys (
+ `id` int(10) unsigned NOT NULL auto_increment,
+ `type` tinyint(3) unsigned NOT NULL,
+ `data` BLOB NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS private_key_identity;
+CREATE TABLE private_key_identity (
+ `private_key` int(10) unsigned NOT NULL,
+ `identity` int(10) unsigned NOT NULL,
+ PRIMARY KEY (`private_key`, `identity`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS shared_secrets;
+CREATE TABLE shared_secrets (
+ `id` int(10) unsigned NOT NULL auto_increment,
+ `type` tinyint(3) unsigned NOT NULL,
+ `data` varbinary(256) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS shared_secret_identity;
+CREATE TABLE shared_secret_identity (
+ `shared_secret` int(10) unsigned NOT NULL,
+ `identity` int(10) unsigned NOT NULL,
+ PRIMARY KEY (`shared_secret`, `identity`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS pools;
+CREATE TABLE pools (
+ `id` int(10) unsigned NOT NULL auto_increment,
+ `name` varchar(32) NOT NULL,
+ `start` varbinary(16) NOT NULL,
+ `end` varbinary(16) NOT NULL,
+ `timeout` int(10) unsigned NOT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE (`name`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS addresses;
+CREATE TABLE addresses (
+ `id` int(10) unsigned NOT NULL auto_increment,
+ `pool` int(10) unsigned NOT NULL,
+ `address` varbinary(16) NOT NULL,
+ `identity` int(10) unsigned NOT NULL,
+ `acquired` int(10) unsigned NOT NULL,
+ `released` int(10) unsigned DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ INDEX (`pool`),
+ INDEX (`identity`),
+ INDEX (`address`)
+);
+
+DROP TABLE IF EXISTS leases;
+CREATE TABLE leases (
+ `id` int(10) unsigned NOT NULL auto_increment,
+ `address` int(10) unsigned NOT NULL,
+ `identity` int(10) unsigned NOT NULL,
+ `acquired` int(10) unsigned NOT NULL,
+ `released` int(10) unsigned DEFAULT NULL,
+ PRIMARY KEY (`id`)
+);
+
+DROP TABLE IF EXISTS attributes;
+CREATE TABLE attributes (
+ `id` int(10) unsigned NOT NULL auto_increment,
+ `type` int(10) unsigned NOT NULL,
+ `value` varbinary(16) NOT NULL
+);
+
+DROP TABLE IF EXISTS ike_sas;
+CREATE TABLE ike_sas (
+ `local_spi` varbinary(8) NOT NULL,
+ `remote_spi` varbinary(8) NOT NULL,
+ `id` int(10) unsigned NOT NULL,
+ `initiator` tinyint(1) NOT NULL,
+ `local_id_type` tinyint(3) NOT NULL,
+ `local_id_data` varbinary(64) NOT NULL,
+ `remote_id_type` tinyint(3) NOT NULL,
+ `remote_id_data` varbinary(64) NOT NULL,
+ `host_family` tinyint(3) NOT NULL,
+ `local_host_data` varbinary(16) NOT NULL,
+ `remote_host_data` varbinary(16) NOT NULL,
+ `lastuse` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`local_spi`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS logs;
+CREATE TABLE logs (
+ `id` int(10) unsigned NOT NULL auto_increment,
+ `local_spi` varbinary(8) NOT NULL,
+ `signal` tinyint(3) NOT NULL,
+ `level` tinyint(3) NOT NULL,
+ `msg` varchar(256) NOT NULL,
+ `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ PRIMARY KEY (`id`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
diff --git a/src/libcharon/plugins/sql/sql_config.c b/src/libcharon/plugins/sql/sql_config.c
new file mode 100644
index 000000000..23366898a
--- /dev/null
+++ b/src/libcharon/plugins/sql/sql_config.c
@@ -0,0 +1,535 @@
+/*
+ * Copyright (C) 2006-2008 Martin Willi
+ * Hochschule fuer Technik Rapperswil
+ *
+ * This program is free software; you can redistribute it and/or modify it
+ * under the terms of the GNU General Public License as published by the
+ * Free Software Foundation; either version 2 of the License, or (at your
+ * option) any later version. See <http://www.fsf.org/copyleft/gpl.txt>.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
+ * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
+ * for more details.
+ */
+
+#include <string.h>
+
+#include "sql_config.h"
+
+#include <daemon.h>
+
+typedef struct private_sql_config_t private_sql_config_t;
+
+/**
+ * Private data of an sql_config_t object
+ */
+struct private_sql_config_t {
+
+ /**
+ * Public part
+ */
+ sql_config_t public;
+
+ /**
+ * database connection
+ */
+ database_t *db;
+};
+
+/**
+ * forward declaration
+ */
+static peer_cfg_t *build_peer_cfg(private_sql_config_t *this, enumerator_t *e,
+ identification_t *me, identification_t *other);
+
+/**
+ * build a traffic selector from a SQL query
+ */
+static traffic_selector_t *build_traffic_selector(private_sql_config_t *this,
+ enumerator_t *e, bool *local)
+{
+ int type, protocol, start_port, end_port;
+ chunk_t start_addr, end_addr;
+ traffic_selector_t *ts;
+ enum {
+ TS_LOCAL = 0,
+ TS_REMOTE = 1,
+ TS_LOCAL_DYNAMIC = 2,
+ TS_REMOTE_DYNAMIC = 3,
+ } kind;
+
+ while (e->enumerate(e, &kind, &type, &protocol,
+ &start_addr, &end_addr, &start_port, &end_port))
+ {
+ *local = FALSE;
+ switch (kind)
+ {
+ case TS_LOCAL:
+ *local = TRUE;
+ /* FALL */
+ case TS_REMOTE:
+ ts = traffic_selector_create_from_bytes(protocol, type,
+ start_addr, start_port, end_addr, end_port);
+ break;
+ case TS_LOCAL_DYNAMIC:
+ *local = TRUE;
+ /* FALL */
+ case TS_REMOTE_DYNAMIC:
+ ts = traffic_selector_create_dynamic(protocol,
+ start_port, end_port);
+ break;
+ default:
+ continue;
+ }
+ if (ts)
+ {
+ return ts;
+ }
+ }
+ return NULL;
+}
+
+/**
+ * Add traffic selectors to a child config
+ */
+static void add_traffic_selectors(private_sql_config_t *this,
+ child_cfg_t *child, int id)
+{
+ enumerator_t *e;
+ traffic_selector_t *ts;
+ bool local;
+
+ e = this->db->query(this->db,
+ "SELECT kind, type, protocol, "
+ "start_addr, end_addr, start_port, end_port "
+ "FROM traffic_selectors JOIN child_config_traffic_selector "
+ "ON id = traffic_selector WHERE child_cfg = ?",
+ DB_INT, id,
+ DB_INT, DB_INT, DB_INT,
+ DB_BLOB, DB_BLOB, DB_INT, DB_INT);
+ if (e)
+ {
+ while ((ts = build_traffic_selector(this, e, &local)))
+ {
+ child->add_traffic_selector(child, local, ts);
+ }
+ e->destroy(e);
+ }
+}
+
+/**
+ * build a Child configuration from a SQL query
+ */
+static child_cfg_t *build_child_cfg(private_sql_config_t *this, enumerator_t *e)
+{
+ int id, lifetime, rekeytime, jitter, hostaccess, mode, dpd, close, ipcomp;
+ char *name, *updown;
+ child_cfg_t *child_cfg;
+
+ if (e->enumerate(e, &id, &name, &lifetime, &rekeytime, &jitter,
+ &updown, &hostaccess, &mode, &dpd, &close, &ipcomp))
+ {
+ lifetime_cfg_t lft = {
+ .time = { .life = lifetime, .rekey = rekeytime, .jitter = jitter }
+ };
+ child_cfg = child_cfg_create(name, &lft, updown, hostaccess, mode,
+ dpd, close, ipcomp, 0);
+ /* TODO: read proposal from db */
+ child_cfg->add_proposal(child_cfg, proposal_create_default(PROTO_ESP));
+ add_traffic_selectors(this, child_cfg, id);
+ return child_cfg;
+ }
+ return NULL;
+}
+
+/**
+ * Add child configs to peer config
+ */
+static void add_child_cfgs(private_sql_config_t *this, peer_cfg_t *peer, int id)
+{
+ enumerator_t *e;
+ child_cfg_t *child_cfg;
+
+ e = this->db->query(this->db,
+ "SELECT id, name, lifetime, rekeytime, jitter, "
+ "updown, hostaccess, mode, dpd_action, close_action, ipcomp "
+ "FROM child_configs JOIN peer_config_child_config ON id = child_cfg "
+ "WHERE peer_cfg = ?",
+ DB_INT, id,
+ DB_INT, DB_TEXT, DB_INT, DB_INT, DB_INT,
+ DB_TEXT, DB_INT, DB_INT, DB_INT, DB_INT, DB_INT);
+ if (e)
+ {
+ while ((child_cfg = build_child_cfg(this, e)))
+ {
+ peer->add_child_cfg(peer, child_cfg);
+ }
+ e->destroy(e);
+ }
+}
+
+/**
+ * build a ike configuration from a SQL query
+ */
+static ike_cfg_t *build_ike_cfg(private_sql_config_t *this, enumerator_t *e,
+ host_t *my_host, host_t *other_host)
+{
+ int certreq, force_encap;
+ char *local, *remote;
+
+ while (e->enumerate(e, &certreq, &force_encap, &local, &remote))
+ {
+ ike_cfg_t *ike_cfg;
+
+ ike_cfg = ike_cfg_create(certreq, force_encap,
+ local, IKEV2_UDP_PORT, remote, IKEV2_UDP_PORT);
+ /* TODO: read proposal from db */
+ ike_cfg->add_proposal(ike_cfg, proposal_create_default(PROTO_IKE));
+ return ike_cfg;
+ }
+ return NULL;
+}
+
+/**
+ * Query a IKE config by its id
+ */
+static ike_cfg_t* get_ike_cfg_by_id(private_sql_config_t *this, int id)
+{
+ enumerator_t *e;
+ ike_cfg_t *ike_cfg = NULL;
+
+ e = this->db->query(this->db,
+ "SELECT certreq, force_encap, local, remote "
+ "FROM ike_configs WHERE id = ?",
+ DB_INT, id,
+ DB_INT, DB_INT, DB_TEXT, DB_TEXT);
+ if (e)
+ {
+ ike_cfg = build_ike_cfg(this, e, NULL, NULL);
+ e->destroy(e);
+ }
+ return ike_cfg;
+}
+
+/**
+ * Query a peer config by its id
+ */
+static peer_cfg_t *get_peer_cfg_by_id(private_sql_config_t *this, int id)
+{
+ enumerator_t *e;
+ peer_cfg_t *peer_cfg = NULL;
+
+ e = this->db->query(this->db,
+ "SELECT c.id, name, ike_cfg, l.type, l.data, r.type, r.data, "
+ "cert_policy, uniqueid, auth_method, eap_type, eap_vendor, "
+ "keyingtries, rekeytime, reauthtime, jitter, overtime, mobike, "
+ "dpd_delay, virtual, pool, "
+ "mediation, mediated_by, COALESCE(p.type, 0), p.data "
+ "FROM peer_configs AS c "
+ "JOIN identities AS l ON local_id = l.id "
+ "JOIN identities AS r ON remote_id = r.id "
+ "LEFT JOIN identities AS p ON peer_id = p.id "
+ "WHERE id = ?",
+ DB_INT, id,
+ DB_INT, DB_TEXT, DB_INT, DB_INT, DB_BLOB, DB_INT, DB_BLOB,
+ DB_INT, DB_INT, DB_INT, DB_INT, DB_INT,
+ DB_INT, DB_INT, DB_INT, DB_INT, DB_INT, DB_INT,
+ DB_INT, DB_TEXT, DB_TEXT,
+ DB_INT, DB_INT, DB_INT, DB_BLOB);
+ if (e)
+ {
+ peer_cfg = build_peer_cfg(this, e, NULL, NULL);
+ e->destroy(e);
+ }
+ return peer_cfg;
+}
+
+/**
+ * build a peer configuration from a SQL query
+ */
+static peer_cfg_t *build_peer_cfg(private_sql_config_t *this, enumerator_t *e,
+ identification_t *me, identification_t *other)
+{
+ int id, ike_cfg, l_type, r_type,
+ cert_policy, uniqueid, auth_method, eap_type, eap_vendor, keyingtries,
+ rekeytime, reauthtime, jitter, overtime, mobike, dpd_delay,
+ mediation, mediated_by, p_type;
+ chunk_t l_data, r_data, p_data;
+ char *name, *virtual, *pool;
+
+ while (e->enumerate(e,
+ &id, &name, &ike_cfg, &l_type, &l_data, &r_type, &r_data,
+ &cert_policy, &uniqueid, &auth_method, &eap_type, &eap_vendor,
+ &keyingtries, &rekeytime, &reauthtime, &jitter, &overtime, &mobike,
+ &dpd_delay, &virtual, &pool,
+ &mediation, &mediated_by, &p_type, &p_data))
+ {
+ identification_t *local_id, *remote_id, *peer_id = NULL;
+ peer_cfg_t *peer_cfg, *mediated_cfg;
+ ike_cfg_t *ike;
+ host_t *vip = NULL;
+ auth_cfg_t *auth;
+
+ local_id = identification_create_from_encoding(l_type, l_data);
+ remote_id = identification_create_from_encoding(r_type, r_data);
+ if ((me && !me->matches(me, local_id)) ||
+ (other && !other->matches(other, remote_id)))
+ {
+ local_id->destroy(local_id);
+ remote_id->destroy(remote_id);
+ continue;
+ }
+ ike = get_ike_cfg_by_id(this, ike_cfg);
+ mediated_cfg = mediated_by ? get_peer_cfg_by_id(this, mediated_by) : NULL;
+ if (p_type)
+ {
+ peer_id = identification_create_from_encoding(p_type, p_data);
+ }
+ if (virtual)
+ {
+ vip = host_create_from_string(virtual, 0);
+ }
+ if (ike)
+ {
+ peer_cfg = peer_cfg_create(
+ name, 2, ike, cert_policy, uniqueid,
+ keyingtries, rekeytime, reauthtime, jitter, overtime,
+ mobike, dpd_delay, vip, pool,
+ mediation, mediated_cfg, peer_id);
+ auth = auth_cfg_create();
+ auth->add(auth, AUTH_RULE_AUTH_CLASS, auth_method);
+ auth->add(auth, AUTH_RULE_IDENTITY, local_id);
+ peer_cfg->add_auth_cfg(peer_cfg, auth, TRUE);
+ auth = auth_cfg_create();
+ auth->add(auth, AUTH_RULE_IDENTITY, remote_id);
+ if (eap_type)
+ {
+ auth->add(auth, AUTH_RULE_AUTH_CLASS, AUTH_CLASS_EAP);
+ auth->add(auth, AUTH_RULE_EAP_TYPE, eap_type);
+ if (eap_vendor)
+ {
+ auth->add(auth, AUTH_RULE_EAP_VENDOR, eap_vendor);
+ }
+ }
+ peer_cfg->add_auth_cfg(peer_cfg, auth, FALSE);
+ add_child_cfgs(this, peer_cfg, id);
+ return peer_cfg;
+ }
+ DESTROY_IF(ike);
+ DESTROY_IF(mediated_cfg);
+ DESTROY_IF(peer_id);
+ DESTROY_IF(local_id);
+ DESTROY_IF(remote_id);
+ }
+ return NULL;
+}
+
+/**
+ * implements backend_t.get_peer_cfg_by_name.
+ */
+static peer_cfg_t *get_peer_cfg_by_name(private_sql_config_t *this, char *name)
+{
+ enumerator_t *e;
+ peer_cfg_t *peer_cfg = NULL;
+
+ e = this->db->query(this->db,
+ "SELECT c.id, name, ike_cfg, l.type, l.data, r.type, r.data, "
+ "cert_policy, uniqueid, auth_method, eap_type, eap_vendor, "
+ "keyingtries, rekeytime, reauthtime, jitter, overtime, mobike, "
+ "dpd_delay, virtual, pool, "
+ "mediation, mediated_by, COALESCE(p.type, 0), p.data "
+ "FROM peer_configs AS c "
+ "JOIN identities AS l ON local_id = l.id "
+ "JOIN identities AS r ON remote_id = r.id "
+ "LEFT JOIN identities AS p ON peer_id = p.id "
+ "WHERE ike_version = ? AND name = ?",
+ DB_INT, 2, DB_TEXT, name,
+ DB_INT, DB_TEXT, DB_INT, DB_INT, DB_BLOB, DB_INT, DB_BLOB,
+ DB_INT, DB_INT, DB_INT, DB_INT, DB_INT,
+ DB_INT, DB_INT, DB_INT, DB_INT, DB_INT, DB_INT,
+ DB_INT, DB_TEXT, DB_TEXT,
+ DB_INT, DB_INT, DB_INT, DB_BLOB);
+ if (e)
+ {
+ peer_cfg = build_peer_cfg(this, e, NULL, NULL);
+ e->destroy(e);
+ }
+ return peer_cfg;
+}
+
+typedef struct {
+ /** implements enumerator */
+ enumerator_t public;
+ /** reference to context */
+ private_sql_config_t *this;
+ /** filtering own host */
+ host_t *me;
+ /** filtering remote host */
+ host_t *other;
+ /** inner SQL enumerator */
+ enumerator_t *inner;
+ /** currently enumerated peer config */
+ ike_cfg_t *current;
+} ike_enumerator_t;
+
+/**
+ * Implementation of ike_enumerator_t.public.enumerate
+ */
+static bool ike_enumerator_enumerate(ike_enumerator_t *this, ike_cfg_t **cfg)
+{
+ DESTROY_IF(this->current);
+ this->current = build_ike_cfg(this->this, this->inner, this->me, this->other);
+ if (this->current)
+ {
+ *cfg = this->current;
+ return TRUE;
+ }
+ return FALSE;
+}
+
+/**
+ * Implementation of ike_enumerator_t.public.destroy
+ */
+static void ike_enumerator_destroy(ike_enumerator_t *this)
+{
+ DESTROY_IF(this->current);
+ this->inner->destroy(this->inner);
+ free(this);
+}
+
+/**
+ * Implementation of backend_t.create_ike_cfg_enumerator.
+ */
+static enumerator_t* create_ike_cfg_enumerator(private_sql_config_t *this,
+ host_t *me, host_t *other)
+{
+ ike_enumerator_t *e = malloc_thing(ike_enumerator_t);
+
+ e->this = this;
+ e->me = me;
+ e->other = other;
+ e->current = NULL;
+ e->public.enumerate = (void*)ike_enumerator_enumerate;
+ e->public.destroy = (void*)ike_enumerator_destroy;
+
+ e->inner = this->db->query(this->db,
+ "SELECT certreq, force_encap, local, remote "
+ "FROM ike_configs",
+ DB_INT, DB_INT, DB_TEXT, DB_TEXT);
+ if (!e->inner)
+ {
+ free(e);
+ return NULL;
+ }
+ return &e->public;
+}
+
+
+typedef struct {
+ /** implements enumerator */
+ enumerator_t public;
+ /** reference to context */
+ private_sql_config_t *this;
+ /** filtering own identity */
+ identification_t *me;
+ /** filtering remote identity */
+ identification_t *other;
+ /** inner SQL enumerator */
+ enumerator_t *inner;
+ /** currently enumerated peer config */
+ peer_cfg_t *current;
+} peer_enumerator_t;
+
+/**
+ * Implementation of peer_enumerator_t.public.enumerate
+ */
+static bool peer_enumerator_enumerate(peer_enumerator_t *this, peer_cfg_t **cfg)
+{
+ DESTROY_IF(this->current);
+ this->current = build_peer_cfg(this->this, this->inner, this->me, this->other);
+ if (this->current)
+ {
+ *cfg = this->current;
+ return TRUE;
+ }
+ return FALSE;
+}
+
+/**
+ * Implementation of peer_enumerator_t.public.destroy
+ */
+static void peer_enumerator_destroy(peer_enumerator_t *this)
+{
+ DESTROY_IF(this->current);
+ this->inner->destroy(this->inner);
+ free(this);
+}
+
+/**
+ * Implementation of backend_t.create_peer_cfg_enumerator.
+ */
+static enumerator_t* create_peer_cfg_enumerator(private_sql_config_t *this,
+ identification_t *me,
+ identification_t *other)
+{
+ peer_enumerator_t *e = malloc_thing(peer_enumerator_t);
+
+ e->this = this;
+ e->me = me;
+ e->other = other;
+ e->current = NULL;
+ e->public.enumerate = (void*)peer_enumerator_enumerate;
+ e->public.destroy = (void*)peer_enumerator_destroy;
+
+ /* TODO: only get configs whose IDs match exactly or contain wildcards */
+ e->inner = this->db->query(this->db,
+ "SELECT c.id, name, ike_cfg, l.type, l.data, r.type, r.data, "
+ "cert_policy, uniqueid, auth_method, eap_type, eap_vendor, "
+ "keyingtries, rekeytime, reauthtime, jitter, overtime, mobike, "
+ "dpd_delay, virtual, pool, "
+ "mediation, mediated_by, COALESCE(p.type, 0), p.data "
+ "FROM peer_configs AS c "
+ "JOIN identities AS l ON local_id = l.id "
+ "JOIN identities AS r ON remote_id = r.id "
+ "LEFT JOIN identities AS p ON peer_id = p.id "
+ "WHERE ike_version = ?",
+ DB_INT, 2,
+ DB_INT, DB_TEXT, DB_INT, DB_INT, DB_BLOB, DB_INT, DB_BLOB,
+ DB_INT, DB_INT, DB_INT, DB_INT, DB_INT,
+ DB_INT, DB_INT, DB_INT, DB_INT, DB_INT, DB_INT,
+ DB_INT, DB_TEXT, DB_TEXT,
+ DB_INT, DB_INT, DB_INT, DB_BLOB);
+ if (!e->inner)
+ {
+ free(e);
+ return NULL;
+ }
+ return &e->public;
+}
+
+/**
+ * Implementation of sql_config_t.destroy.
+ */
+static void destroy(private_sql_config_t *this)
+{
+ free(this);
+}
+
+/**
+ * Described in header.
+ */
+sql_config_t *sql_config_create(database_t *db)
+{
+ private_sql_config_t *this = malloc_thing(private_sql_config_t);
+
+ this->public.backend.create_peer_cfg_enumerator = (enumerator_t*(*)(backend_t*, identification_t *me, identification_t *other))create_peer_cfg_enumerator;
+ this->public.backend.create_ike_cfg_enumerator = (enumerator_t*(*)(backend_t*, host_t *me, host_t *other))create_ike_cfg_enumerator;
+ this->public.backend.get_peer_cfg_by_name = (peer_cfg_t* (*)(backend_t*,char*))get_peer_cfg_by_name;
+ this->public.destroy = (void(*)(sql_config_t*))destroy;
+
+ this->db = db;
+
+ return &this->public;
+}
+
diff --git a/src/libcharon/plugins/sql/sql_config.h b/src/libcharon/plugins/sql/sql_config.h
new file mode 100644
index 000000000..700d00a97
--- /dev/null
+++ b/src/libcharon/plugins/sql/sql_config.h
@@ -0,0 +1,53 @@
+/*
+ * Copyright (C) 2008 Martin Willi
+ * Hochschule fuer Technik Rapperswil
+ *
+ * This program is free software; you can redistribute it and/or modify it
+ * under the terms of the GNU General Public License as published by the
+ * Free Software Foundation; either version 2 of the License, or (at your
+ * option) any later version. See <http://www.fsf.org/copyleft/gpl.txt>.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
+ * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
+ * for more details.
+ */
+
+/**
+ * @defgroup sql_config_i sql_config
+ * @{ @ingroup sql
+ */
+
+#ifndef SQL_CONFIG_H_
+#define SQL_CONFIG_H_
+
+#include <config/backend.h>
+#include <database/database.h>
+
+typedef struct sql_config_t sql_config_t;
+
+/**
+ * SQL database configuration backend.
+ */
+struct sql_config_t {
+
+ /**
+ * Implements backend_t interface
+ */
+ backend_t backend;
+
+ /**
+ * Destry the backend.
+ */
+ void (*destroy)(sql_config_t *this);
+};
+
+/**
+ * Create a sql_config backend instance.
+ *
+ * @param db underlying database
+ * @return backend instance
+ */
+sql_config_t *sql_config_create(database_t *db);
+
+#endif /** SQL_CONFIG_H_ @}*/
diff --git a/src/libcharon/plugins/sql/sql_cred.c b/src/libcharon/plugins/sql/sql_cred.c
new file mode 100644
index 000000000..12f4ab045
--- /dev/null
+++ b/src/libcharon/plugins/sql/sql_cred.c
@@ -0,0 +1,365 @@
+/*
+ * Copyright (C) 2008 Martin Willi
+ * Hochschule fuer Technik Rapperswil
+ *
+ * This program is free software; you can redistribute it and/or modify it
+ * under the terms of the GNU General Public License as published by the
+ * Free Software Foundation; either version 2 of the License, or (at your
+ * option) any later version. See <http://www.fsf.org/copyleft/gpl.txt>.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
+ * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
+ * for more details.
+ */
+
+#include <string.h>
+
+#include "sql_cred.h"
+
+#include <daemon.h>
+
+typedef struct private_sql_cred_t private_sql_cred_t;
+
+/**
+ * Private data of an sql_cred_t object
+ */
+struct private_sql_cred_t {
+
+ /**
+ * Public part
+ */
+ sql_cred_t public;
+
+ /**
+ * database connection
+ */
+ database_t *db;
+};
+
+/**
+ * enumerator over private keys
+ */
+typedef struct {
+ /** implements enumerator */
+ enumerator_t public;
+ /** inner SQL enumerator */
+ enumerator_t *inner;
+ /** currently enumerated private key */
+ private_key_t *current;
+} private_enumerator_t;
+
+/**
+ * Implementation of private_enumerator_t.public.enumerate
+ */
+static bool private_enumerator_enumerate(private_enumerator_t *this,
+ private_key_t **key)
+{
+ chunk_t blob;
+ int type;
+
+ DESTROY_IF(this->current);
+ while (this->inner->enumerate(this->inner, &type, &blob))
+ {
+ this->current = lib->creds->create(lib->creds, CRED_PRIVATE_KEY, type,
+ BUILD_BLOB_ASN1_DER, blob,
+ BUILD_END);
+ if (this->current)
+ {
+ *key = this->current;
+ return TRUE;
+ }
+ }
+ this->current = NULL;
+ return FALSE;
+}
+
+/**
+ * Implementation of private_enumerator_t.public.destroy
+ */
+static void private_enumerator_destroy(private_enumerator_t *this)
+{
+ DESTROY_IF(this->current);
+ this->inner->destroy(this->inner);
+ free(this);
+}
+
+/**
+ * Implementation of credential_set_t.create_private_enumerator.
+ */
+static enumerator_t* create_private_enumerator(private_sql_cred_t *this,
+ key_type_t type,
+ identification_t *id)
+{
+ private_enumerator_t *e;
+
+ e = malloc_thing(private_enumerator_t);
+ e->current = NULL;
+ e->public.enumerate = (void*)private_enumerator_enumerate;
+ e->public.destroy = (void*)private_enumerator_destroy;
+ if (id && id->get_type(id) != ID_ANY)
+ {
+ e->inner = this->db->query(this->db,
+ "SELECT p.type, p.data FROM private_keys AS p "
+ "JOIN private_key_identity AS pi ON p.id = pi.private_key "
+ "JOIN identities AS i ON pi.identity = i.id "
+ "WHERE i.type = ? AND i.data = ? AND (? OR p.type = ?)",
+ DB_INT, id->get_type(id), DB_BLOB, id->get_encoding(id),
+ DB_INT, type == KEY_ANY, DB_INT, type,
+ DB_INT, DB_BLOB);
+ }
+ else
+ {
+ e->inner = this->db->query(this->db,
+ "SELECT type, data FROM private_keys WHERE (? OR type = ?)",
+ DB_INT, type == KEY_ANY, DB_INT, type,
+ DB_INT, DB_BLOB);
+ }
+ if (!e->inner)
+ {
+ free(e);
+ return NULL;
+ }
+ return &e->public;
+}
+
+/**
+ * enumerator over certificates
+ */
+typedef struct {
+ /** implements enumerator */
+ enumerator_t public;
+ /** inner SQL enumerator */
+ enumerator_t *inner;
+ /** currently enumerated cert */
+ certificate_t *current;
+} cert_enumerator_t;
+
+/**
+ * Implementation of cert_enumerator_t.public.enumerate
+ */
+static bool cert_enumerator_enumerate(cert_enumerator_t *this,
+ certificate_t **cert)
+{
+ chunk_t blob;
+ int type;
+
+ DESTROY_IF(this->current);
+ while (this->inner->enumerate(this->inner, &type, &blob))
+ {
+ this->current = lib->creds->create(lib->creds, CRED_CERTIFICATE, type,
+ BUILD_BLOB_ASN1_DER, blob,
+ BUILD_END);
+ if (this->current)
+ {
+ *cert = this->current;
+ return TRUE;
+ }
+ }
+ this->current = NULL;
+ return FALSE;
+}
+
+/**
+ * Implementation of cert_enumerator_t.public.destroy
+ */
+static void cert_enumerator_destroy(cert_enumerator_t *this)
+{
+ DESTROY_IF(this->current);
+ this->inner->destroy(this->inner);
+ free(this);
+}
+
+/**
+ * Implementation of credential_set_t.create_cert_enumerator.
+ */
+static enumerator_t* create_cert_enumerator(private_sql_cred_t *this,
+ certificate_type_t cert, key_type_t key,
+ identification_t *id, bool trusted)
+{
+ cert_enumerator_t *e;
+
+ e = malloc_thing(cert_enumerator_t);
+ e->current = NULL;
+ e->public.enumerate = (void*)cert_enumerator_enumerate;
+ e->public.destroy = (void*)cert_enumerator_destroy;
+ if (id && id->get_type(id) != ID_ANY)
+ {
+ e->inner = this->db->query(this->db,
+ "SELECT c.type, c.data FROM certificates AS c "
+ "JOIN certificate_identity AS ci ON c.id = ci.certificate "
+ "JOIN identities AS i ON ci.identity = i.id "
+ "WHERE i.type = ? AND i.data = ? AND "
+ "(? OR c.type = ?) AND (? OR c.keytype = ?)",
+ DB_INT, id->get_type(id), DB_BLOB, id->get_encoding(id),
+ DB_INT, cert == CERT_ANY, DB_INT, cert,
+ DB_INT, key == KEY_ANY, DB_INT, key,
+ DB_INT, DB_BLOB);
+ }
+ else
+ {
+ e->inner = this->db->query(this->db,
+ "SELECT type, data FROM certificates WHERE "
+ "(? OR type = ?) AND (? OR keytype = ?)",
+ DB_INT, cert == CERT_ANY, DB_INT, cert,
+ DB_INT, key == KEY_ANY, DB_INT, key,
+ DB_INT, DB_BLOB);
+ }
+ if (!e->inner)
+ {
+ free(e);
+ return NULL;
+ }
+ return &e->public;
+}
+
+/**
+ * enumerator over shared keys
+ */
+typedef struct {
+ /** implements enumerator */
+ enumerator_t public;
+ /** inner SQL enumerator */
+ enumerator_t *inner;
+ /** own identity */
+ identification_t *me;
+ /** remote identity */
+ identification_t *other;
+ /** currently enumerated private key */
+ shared_key_t *current;
+} shared_enumerator_t;
+
+/**
+ * Implementation of shared_enumerator_t.public.enumerate
+ */
+static bool shared_enumerator_enumerate(shared_enumerator_t *this,
+ shared_key_t **shared,
+ id_match_t *me, id_match_t *other)
+{
+ chunk_t blob;
+ int type;
+
+ DESTROY_IF(this->current);
+ while (this->inner->enumerate(this->inner, &type, &blob))
+ {
+ this->current = shared_key_create(type, chunk_clone(blob));
+ if (this->current)
+ {
+ *shared = this->current;
+ if (me)
+ {
+ *me = this->me ? ID_MATCH_PERFECT : ID_MATCH_ANY;
+ }
+ if (other)
+ {
+ *other = this->other ? ID_MATCH_PERFECT : ID_MATCH_ANY;
+ }
+ return TRUE;
+ }
+ }
+ this->current = NULL;
+ return FALSE;
+}
+
+/**
+ * Implementation of shared_enumerator_t.public.destroy
+ */
+static void shared_enumerator_destroy(shared_enumerator_t *this)
+{
+ DESTROY_IF(this->current);
+ this->inner->destroy(this->inner);
+ free(this);
+}
+
+/**
+ * Implementation of credential_set_t.create_shared_enumerator.
+ */
+static enumerator_t* create_shared_enumerator(private_sql_cred_t *this,
+ shared_key_type_t type,
+ identification_t *me, identification_t *other)
+{
+ shared_enumerator_t *e;
+
+ e = malloc_thing(shared_enumerator_t);
+ e->me = me;
+ e->other = other;
+ e->current = NULL;
+ e->public.enumerate = (void*)shared_enumerator_enumerate;
+ e->public.destroy = (void*)shared_enumerator_destroy;
+ if (!me && !other)
+ {
+ e->inner = this->db->query(this->db,
+ "SELECT type, data FROM shared_secrets WHERE (? OR type = ?)",
+ DB_INT, type == SHARED_ANY, DB_INT, type,
+ DB_INT, DB_BLOB);
+ }
+ else if (me && other)
+ {
+ e->inner = this->db->query(this->db,
+ "SELECT s.type, s.data FROM shared_secrets AS s "
+ "JOIN shared_secret_identity AS sm ON s.id = sm.shared_secret "
+ "JOIN identities AS m ON sm.identity = m.id "
+ "JOIN shared_secret_identity AS so ON s.id = so.shared_secret "
+ "JOIN identities AS o ON so.identity = o.id "
+ "WHERE m.type = ? AND m.data = ? AND o.type = ? AND o.data = ? "
+ "AND (? OR s.type = ?)",
+ DB_INT, me->get_type(me), DB_BLOB, me->get_encoding(me),
+ DB_INT, other->get_type(other), DB_BLOB, other->get_encoding(other),
+ DB_INT, type == SHARED_ANY, DB_INT, type,
+ DB_INT, DB_BLOB);
+ }
+ else
+ {
+ identification_t *id = me ? me : other;
+
+ e->inner = this->db->query(this->db,
+ "SELECT s.type, s.data FROM shared_secrets AS s "
+ "JOIN shared_secret_identity AS si ON s.id = si.shared_secret "
+ "JOIN identities AS i ON si.identity = i.id "
+ "WHERE i.type = ? AND i.data = ? AND (? OR s.type = ?)",
+ DB_INT, id->get_type(id), DB_BLOB, id->get_encoding(id),
+ DB_INT, type == SHARED_ANY, DB_INT, type,
+ DB_INT, DB_BLOB);
+ }
+ if (!e->inner)
+ {
+ free(e);
+ return NULL;
+ }
+ return &e->public;
+}
+
+/**
+ * Implementation of credential_set_t.cache_cert.
+ */
+static void cache_cert(private_sql_cred_t *this, certificate_t *cert)
+{
+ /* TODO: implement CRL caching to database */
+}
+
+/**
+ * Implementation of sql_cred_t.destroy.
+ */
+static void destroy(private_sql_cred_t *this)
+{
+ free(this);
+}
+/**
+ * Described in header.
+ */
+sql_cred_t *sql_cred_create(database_t *db)
+{
+ private_sql_cred_t *this = malloc_thing(private_sql_cred_t);
+
+ this->public.set.create_private_enumerator = (void*)create_private_enumerator;
+ this->public.set.create_cert_enumerator = (void*)create_cert_enumerator;
+ this->public.set.create_shared_enumerator = (void*)create_shared_enumerator;
+ this->public.set.create_cdp_enumerator = (void*)return_null;
+ this->public.set.cache_cert = (void*)cache_cert;
+ this->public.destroy = (void(*)(sql_cred_t*))destroy;
+
+ this->db = db;
+
+ return &this->public;
+}
+
diff --git a/src/libcharon/plugins/sql/sql_cred.h b/src/libcharon/plugins/sql/sql_cred.h
new file mode 100644
index 000000000..7f387398e
--- /dev/null
+++ b/src/libcharon/plugins/sql/sql_cred.h
@@ -0,0 +1,53 @@
+/*
+ * Copyright (C) 2008 Martin Willi
+ * Hochschule fuer Technik Rapperswil
+ *
+ * This program is free software; you can redistribute it and/or modify it
+ * under the terms of the GNU General Public License as published by the
+ * Free Software Foundation; either version 2 of the License, or (at your
+ * option) any later version. See <http://www.fsf.org/copyleft/gpl.txt>.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
+ * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
+ * for more details.
+ */
+
+/**
+ * @defgroup sql_cred_i sql_cred
+ * @{ @ingroup sql
+ */
+
+#ifndef SQL_CRED_H_
+#define SQL_CRED_H_
+
+#include <credentials/credential_set.h>
+#include <database/database.h>
+
+typedef struct sql_cred_t sql_cred_t;
+
+/**
+ * SQL database credential set.
+ */
+struct sql_cred_t {
+
+ /**
+ * Implements credential_set_t interface
+ */
+ credential_set_t set;
+
+ /**
+ * Destry the backend.
+ */
+ void (*destroy)(sql_cred_t *this);
+};
+
+/**
+ * Create a sql_cred backend instance.
+ *
+ * @param db underlying database
+ * @return credential set
+ */
+sql_cred_t *sql_cred_create(database_t *db);
+
+#endif /** SQL_CRED_H_ @}*/
diff --git a/src/libcharon/plugins/sql/sql_logger.c b/src/libcharon/plugins/sql/sql_logger.c
new file mode 100644
index 000000000..d350c4c3d
--- /dev/null
+++ b/src/libcharon/plugins/sql/sql_logger.c
@@ -0,0 +1,145 @@
+/*
+ * Copyright (C) 2008 Martin Willi
+ * Hochschule fuer Technik Rapperswil
+ *
+ * This program is free software; you can redistribute it and/or modify it
+ * under the terms of the GNU General Public License as published by the
+ * Free Software Foundation; either version 2 of the License, or (at your
+ * option) any later version. See <http://www.fsf.org/copyleft/gpl.txt>.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
+ * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
+ * for more details.
+ */
+
+#include <string.h>
+
+#include "sql_logger.h"
+
+#include <daemon.h>
+
+typedef struct private_sql_logger_t private_sql_logger_t;
+
+/**
+ * Private data of an sql_logger_t object
+ */
+struct private_sql_logger_t {
+
+ /**
+ * Public part
+ */
+ sql_logger_t public;
+
+ /**
+ * database connection
+ */
+ database_t *db;
+
+ /**
+ * logging level
+ */
+ int level;
+
+ /**
+ * avoid recursive logging
+ */
+ bool recursive;
+};
+
+/**
+ * Implementation of bus_listener_t.log.
+ */
+static bool log_(private_sql_logger_t *this, debug_t group, level_t level,
+ int thread, ike_sa_t* ike_sa, char *format, va_list args)
+{
+ if (this->recursive)
+ {
+ return TRUE;
+ }
+ this->recursive = TRUE;
+
+ if (ike_sa && level <= this->level)
+ {
+ char buffer[8192];
+ chunk_t local_spi, remote_spi;
+ host_t *local_host, *remote_host;
+ identification_t *local_id, *remote_id;
+ u_int64_t ispi, rspi;
+ ike_sa_id_t *id;
+
+ id = ike_sa->get_id(ike_sa);
+ ispi = id->get_initiator_spi(id);
+ rspi = id->get_responder_spi(id);
+ if (id->is_initiator(id))
+ {
+ local_spi.ptr = (char*)&ispi;
+ remote_spi.ptr = (char*)&rspi;
+ }
+ else
+ {
+ local_spi.ptr = (char*)&rspi;
+ remote_spi.ptr = (char*)&ispi;
+ }
+ local_spi.len = remote_spi.len = sizeof(ispi);
+ local_id = ike_sa->get_my_id(ike_sa);
+ remote_id = ike_sa->get_other_id(ike_sa);
+ local_host = ike_sa->get_my_host(ike_sa);
+ remote_host = ike_sa->get_other_host(ike_sa);
+
+ vsnprintf(buffer, sizeof(buffer), format, args);
+
+ this->db->execute(this->db, NULL, "REPLACE INTO ike_sas ("
+ "local_spi, remote_spi, id, initiator, "
+ "local_id_type, local_id_data, "
+ "remote_id_type, remote_id_data, "
+ "host_family, local_host_data, remote_host_data) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
+ DB_BLOB, local_spi, DB_BLOB, remote_spi,
+ DB_INT, ike_sa->get_unique_id(ike_sa),
+ DB_INT, id->is_initiator(id),
+ DB_INT, local_id->get_type(local_id),
+ DB_BLOB, local_id->get_encoding(local_id),
+ DB_INT, remote_id->get_type(remote_id),
+ DB_BLOB, remote_id->get_encoding(remote_id),
+ DB_INT, local_host->get_family(local_host),
+ DB_BLOB, local_host->get_address(local_host),
+ DB_BLOB, remote_host->get_address(remote_host));
+ this->db->execute(this->db, NULL, "INSERT INTO logs ("
+ "local_spi, signal, level, msg) VALUES (?, ?, ?, ?)",
+ DB_BLOB, local_spi, DB_INT, group, DB_INT, level,
+ DB_TEXT, buffer);
+ }
+ this->recursive = FALSE;
+ /* always stay registered */
+ return TRUE;
+}
+
+/**
+ * Implementation of sql_logger_t.destroy.
+ */
+static void destroy(private_sql_logger_t *this)
+{
+ free(this);
+}
+
+/**
+ * Described in header.
+ */
+sql_logger_t *sql_logger_create(database_t *db)
+{
+ private_sql_logger_t *this = malloc_thing(private_sql_logger_t);
+
+ memset(&this->public.listener, 0, sizeof(listener_t));
+ this->public.listener.log = (bool(*)(listener_t*,debug_t,level_t,int,ike_sa_t*,char*,va_list))log_;
+ this->public.destroy = (void(*)(sql_logger_t*))destroy;
+
+ this->db = db;
+ this->recursive = FALSE;
+
+ this->level = lib->settings->get_int(lib->settings,
+ "charon.plugins.sql.loglevel", -1);
+
+ return &this->public;
+}
+
diff --git a/src/libcharon/plugins/sql/sql_logger.h b/src/libcharon/plugins/sql/sql_logger.h
new file mode 100644
index 000000000..a933705da
--- /dev/null
+++ b/src/libcharon/plugins/sql/sql_logger.h
@@ -0,0 +1,53 @@
+/*
+ * Copyright (C) 2008 Martin Willi
+ * Hochschule fuer Technik Rapperswil
+ *
+ * This program is free software; you can redistribute it and/or modify it
+ * under the terms of the GNU General Public License as published by the
+ * Free Software Foundation; either version 2 of the License, or (at your
+ * option) any later version. See <http://www.fsf.org/copyleft/gpl.txt>.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
+ * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
+ * for more details.
+ */
+
+/**
+ * @defgroup sql_logger_i sql_logger
+ * @{ @ingroup sql
+ */
+
+#ifndef SQL_LOGGER_H_
+#define SQL_LOGGER_H_
+
+#include <bus/bus.h>
+#include <database/database.h>
+
+typedef struct sql_logger_t sql_logger_t;
+
+/**
+ * SQL database logger.
+ */
+struct sql_logger_t {
+
+ /**
+ * Implements bus_listener_t interface
+ */
+ listener_t listener;
+
+ /**
+ * Destry the backend.
+ */
+ void (*destroy)(sql_logger_t *this);
+};
+
+/**
+ * Create a sql_logger instance.
+ *
+ * @param db underlying database
+ * @return logger instance
+ */
+sql_logger_t *sql_logger_create(database_t *db);
+
+#endif /** SQL_LOGGER_H_ @}*/
diff --git a/src/libcharon/plugins/sql/sql_plugin.c b/src/libcharon/plugins/sql/sql_plugin.c
new file mode 100644
index 000000000..e2d2d63b3
--- /dev/null
+++ b/src/libcharon/plugins/sql/sql_plugin.c
@@ -0,0 +1,107 @@
+/*
+ * Copyright (C) 2008 Martin Willi
+ * Hochschule fuer Technik Rapperswil
+ *
+ * This program is free software; you can redistribute it and/or modify it
+ * under the terms of the GNU General Public License as published by the
+ * Free Software Foundation; either version 2 of the License, or (at your
+ * option) any later version. See <http://www.fsf.org/copyleft/gpl.txt>.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
+ * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
+ * for more details.
+ */
+
+#include "sql_plugin.h"
+
+#include <daemon.h>
+#include "sql_config.h"
+#include "sql_cred.h"
+#include "sql_logger.h"
+
+typedef struct private_sql_plugin_t private_sql_plugin_t;
+
+/**
+ * private data of sql plugin
+ */
+struct private_sql_plugin_t {
+
+ /**
+ * implements plugin interface
+ */
+ sql_plugin_t public;
+
+ /**
+ * database connection instance
+ */
+ database_t *db;
+
+ /**
+ * configuration backend
+ */
+ sql_config_t *config;
+
+ /**
+ * credential set
+ */
+ sql_cred_t *cred;
+
+ /**
+ * bus listener/logger
+ */
+ sql_logger_t *logger;
+};
+
+/**
+ * Implementation of plugin_t.destroy
+ */
+static void destroy(private_sql_plugin_t *this)
+{
+ charon->backends->remove_backend(charon->backends, &this->config->backend);
+ charon->credentials->remove_set(charon->credentials, &this->cred->set);
+ charon->bus->remove_listener(charon->bus, &this->logger->listener);
+ this->config->destroy(this->config);
+ this->cred->destroy(this->cred);
+ this->logger->destroy(this->logger);
+ this->db->destroy(this->db);
+ free(this);
+}
+
+/*
+ * see header file
+ */
+plugin_t *sql_plugin_create()
+{
+ char *uri;
+ private_sql_plugin_t *this;
+
+ uri = lib->settings->get_str(lib->settings, "charon.plugins.sql.database", NULL);
+ if (!uri)
+ {
+ DBG1(DBG_CFG, "sql plugin: database URI not set");
+ return NULL;
+ }
+
+ this = malloc_thing(private_sql_plugin_t);
+
+ this->public.plugin.destroy = (void(*)(plugin_t*))destroy;
+
+ this->db = lib->db->create(lib->db, uri);
+ if (!this->db)
+ {
+ DBG1(DBG_CFG, "sql plugin failed to connect to database");
+ free(this);
+ return NULL;
+ }
+ this->config = sql_config_create(this->db);
+ this->cred = sql_cred_create(this->db);
+ this->logger = sql_logger_create(this->db);
+
+ charon->backends->add_backend(charon->backends, &this->config->backend);
+ charon->credentials->add_set(charon->credentials, &this->cred->set);
+ charon->bus->add_listener(charon->bus, &this->logger->listener);
+
+ return &this->public.plugin;
+}
+
diff --git a/src/libcharon/plugins/sql/sql_plugin.h b/src/libcharon/plugins/sql/sql_plugin.h
new file mode 100644
index 000000000..c6f9ba905
--- /dev/null
+++ b/src/libcharon/plugins/sql/sql_plugin.h
@@ -0,0 +1,42 @@
+/*
+ * Copyright (C) 2008 Martin Willi
+ * Hochschule fuer Technik Rapperswil
+ *
+ * This program is free software; you can redistribute it and/or modify it
+ * under the terms of the GNU General Public License as published by the
+ * Free Software Foundation; either version 2 of the License, or (at your
+ * option) any later version. See <http://www.fsf.org/copyleft/gpl.txt>.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
+ * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
+ * for more details.
+ */
+
+/**
+ * @defgroup sql sql
+ * @ingroup cplugins
+ *
+ * @defgroup sql_plugin sql_plugin
+ * @{ @ingroup sql
+ */
+
+#ifndef SQL_PLUGIN_H_
+#define SQL_PLUGIN_H_
+
+#include <plugins/plugin.h>
+
+typedef struct sql_plugin_t sql_plugin_t;
+
+/**
+ * SQL database configuration plugin
+ */
+struct sql_plugin_t {
+
+ /**
+ * implements plugin interface
+ */
+ plugin_t plugin;
+};
+
+#endif /** SQL_PLUGIN_H_ @}*/
diff --git a/src/libcharon/plugins/sql/sqlite.sql b/src/libcharon/plugins/sql/sqlite.sql
new file mode 100644
index 000000000..53d64dc34
--- /dev/null
+++ b/src/libcharon/plugins/sql/sqlite.sql
@@ -0,0 +1,227 @@
+
+
+DROP TABLE IF EXISTS identities;
+CREATE TABLE identities (
+ id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+ type INTEGER NOT NULL,
+ data BLOB NOT NULL,
+ UNIQUE (type, data)
+);
+
+
+DROP TABLE IF EXISTS child_configs;
+CREATE TABLE child_configs (
+ id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+ name TEXT NOT NULL,
+ lifetime INTEGER NOT NULL DEFAULT '1500',
+ rekeytime INTEGER NOT NULL DEFAULT '1200',
+ jitter INTEGER NOT NULL DEFAULT '60',
+ updown TEXT DEFAULT NULL,
+ hostaccess INTEGER NOT NULL DEFAULT '0',
+ mode INTEGER NOT NULL DEFAULT '1',
+ dpd_action INTEGER NOT NULL DEFAULT '0',
+ close_action INTEGER NOT NULL DEFAULT '0',
+ ipcomp INTEGER NOT NULL DEFAULT '0'
+);
+DROP INDEX IF EXISTS child_configs_name;
+CREATE INDEX child_configs_name ON child_configs (
+ name
+);
+
+
+DROP TABLE IF EXISTS child_config_traffic_selector;
+CREATE TABLE child_config_traffic_selector (
+ child_cfg INTEGER NOT NULL,
+ traffic_selector INTEGER NOT NULL,
+ kind INTEGER NOT NULL
+);
+DROP INDEX IF EXISTS child_config_traffic_selector;
+CREATE INDEX child_config_traffic_selector_all ON child_config_traffic_selector (
+ child_cfg, traffic_selector
+);
+
+
+DROP TABLE IF EXISTS ike_configs;
+CREATE TABLE ike_configs (
+ id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+ certreq INTEGER NOT NULL DEFAULT '1',
+ force_encap INTEGER NOT NULL DEFAULT '0',
+ local TEXT NOT NULL,
+ remote TEXT NOT NULL
+);
+
+
+DROP TABLE IF EXISTS peer_configs;
+CREATE TABLE peer_configs (
+ id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+ name TEXT NOT NULL,
+ ike_version INTEGER NOT NULL DEFAULT '2',
+ ike_cfg INTEGER NOT NULL,
+ local_id TEXT NOT NULL,
+ remote_id TEXT NOT NULL,
+ cert_policy INTEGER NOT NULL DEFAULT '1',
+ uniqueid INTEGER NOT NULL DEFAULT '0',
+ auth_method INTEGER NOT NULL DEFAULT '1',
+ eap_type INTEGER NOT NULL DEFAULT '0',
+ eap_vendor INTEGER NOT NULL DEFAULT '0',
+ keyingtries INTEGER NOT NULL DEFAULT '3',
+ rekeytime INTEGER NOT NULL DEFAULT '7200',
+ reauthtime INTEGER NOT NULL DEFAULT '0',
+ jitter INTEGER NOT NULL DEFAULT '180',
+ overtime INTEGER NOT NULL DEFAULT '300',
+ mobike INTEGER NOT NULL DEFAULT '1',
+ dpd_delay INTEGER NOT NULL DEFAULT '120',
+ virtual TEXT DEFAULT NULL,
+ pool TEXT DEFAULT NULL,
+ mediation INTEGER NOT NULL DEFAULT '0',
+ mediated_by INTEGER NOT NULL DEFAULT '0',
+ peer_id INTEGER NOT NULL DEFAULT '0'
+);
+DROP INDEX IF EXISTS peer_configs_name;
+CREATE INDEX peer_configs_name ON peer_configs (
+ name
+);
+
+
+DROP TABLE IF EXISTS peer_config_child_config;
+CREATE TABLE peer_config_child_config (
+ peer_cfg INTEGER NOT NULL,
+ child_cfg INTEGER NOT NULL,
+ PRIMARY KEY (peer_cfg, child_cfg)
+);
+
+
+DROP TABLE IF EXISTS traffic_selectors;
+CREATE TABLE traffic_selectors (
+ id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+ type INTEGER NOT NULL DEFAULT '7',
+ protocol INTEGER NOT NULL DEFAULT '0',
+ start_addr BLOB DEFAULT NULL,
+ end_addr BLOB DEFAULT NULL,
+ start_port INTEGER NOT NULL DEFAULT '0',
+ end_port INTEGER NOT NULL DEFAULT '65535'
+);
+
+
+DROP TABLE IF EXISTS certificates;
+CREATE TABLE certificates (
+ id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+ type INTEGER NOT NULL,
+ keytype INTEGER NOT NULL,
+ data BLOB NOT NULL
+);
+
+
+DROP TABLE IF EXISTS certificate_identity;
+CREATE TABLE certificate_identity (
+ certificate INTEGER NOT NULL,
+ identity INTEGER NOT NULL,
+ PRIMARY KEY (certificate, identity)
+);
+
+
+DROP TABLE IF EXISTS private_keys;
+CREATE TABLE private_keys (
+ id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+ type INTEGER NOT NULL,
+ data BLOB NOT NULL
+);
+
+
+DROP TABLE IF EXISTS private_key_identity;
+CREATE TABLE private_key_identity (
+ private_key INTEGER NOT NULL,
+ identity INTEGER NOT NULL,
+ PRIMARY KEY (private_key, identity)
+);
+
+
+DROP TABLE IF EXISTS shared_secrets;
+CREATE TABLE shared_secrets (
+ id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+ type INTEGER NOT NULL,
+ data BLOB NOT NULL
+);
+
+
+DROP TABLE IF EXISTS shared_secret_identity;
+CREATE TABLE shared_secret_identity (
+ shared_secret INTEGER NOT NULL,
+ identity INTEGER NOT NULL,
+ PRIMARY KEY (shared_secret, identity)
+);
+
+
+DROP TABLE IF EXISTS pools;
+CREATE TABLE pools (
+ id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+ name TEXT NOT NULL UNIQUE,
+ start BLOB NOT NULL,
+ end BLOB NOT NULL,
+ timeout INTEGER NOT NULL
+);
+
+DROP TABLE IF EXISTS addresses;
+CREATE TABLE addresses (
+ id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+ pool INTEGER NOT NULL,
+ address BLOB NOT NULL,
+ identity INTEGER NOT NULL,
+ acquired INTEGER NOT NULL,
+ released INTEGER NOT NULL
+);
+DROP INDEX IF EXISTS addresses_pool;
+CREATE INDEX addresses_pool ON addresses (
+ pool
+);
+DROP INDEX IF EXISTS addresses_address;
+CREATE INDEX addresses_address ON addresses (
+ address
+);
+DROP INDEX IF EXISTS addresses_identity;
+CREATE INDEX addresses_identity ON addresses (
+ identity
+);
+
+DROP TABLE IF EXISTS leases;
+CREATE TABLE leases (
+ id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+ address INTEGER NOT NULL,
+ identity INTEGER NOT NULL,
+ acquired INTEGER NOT NULL,
+ released INTEGER NOT NULL
+);
+
+DROP TABLE IF EXISTS attributes;
+CREATE TABLE attributes (
+ id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+ type INTEGER NOT NULL,
+ value BLOB NOT NULL
+);
+
+DROP TABLE IF EXISTS ike_sas;
+CREATE TABLE ike_sas (
+ local_spi BLOB NOT NULL PRIMARY KEY,
+ remote_spi BLOB NOT NULL,
+ id INTEGER NOT NULL,
+ initiator INTEGER NOT NULL,
+ local_id_type INTEGER NOT NULL,
+ local_id_data BLOB NOT NULL,
+ remote_id_type INTEGER NOT NULL,
+ remote_id_data BLOB NOT NULL,
+ host_family INTEGER NOT NULL,
+ local_host_data BLOB NOT NULL,
+ remote_host_data BLOB NOT NULL,
+ created INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+
+DROP TABLE IF EXISTS logs;
+CREATE TABLE logs (
+ id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+ local_spi BLOB NOT NULL,
+ signal INTEGER NOT NULL,
+ level INTEGER NOT NULL,
+ msg TEXT NOT NULL,
+ time INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+
diff --git a/src/libcharon/plugins/sql/test.sql b/src/libcharon/plugins/sql/test.sql
new file mode 100644
index 000000000..f3cc0e8d4
--- /dev/null
+++ b/src/libcharon/plugins/sql/test.sql
@@ -0,0 +1,126 @@
+
+INSERT INTO identities (
+ type, data
+) VALUES (
+ 9, X'3039310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e310f300d060355040313066d617274696e' /* C=CH, O=Linux strongSwan, CN=martin */
+);
+INSERT INTO identities (
+ type, data
+) VALUES (
+ 2, X'73696476303135302e6873722e6368' /* sidv0150.hsr.ch */
+
+);
+INSERT INTO identities (
+ type, data
+) VALUES (
+ 202, X'5d735be540d27e858bbc56d7b73766d859bac953'
+);
+INSERT INTO identities (
+ type, data
+) VALUES (
+ 202, X'65c7bb4351a284794e4bf3bf60f4df70dc822b21'
+);
+INSERT INTO identities (
+ type, data
+) VALUES (
+ 9, X'3046310b3009060355040613024348310c300a060355040a1303485352310f300d060355040b1306496e74536563311830160603550403130f73696476303135302e6873722e6368' /* C=CH, O=HSR, OU=IntSec, CN=sidv0150.hsr.ch */
+);
+
+INSERT INTO ike_configs (
+ local, remote
+) VALUES (
+ '0.0.0.0', '152.96.52.150'
+);
+
+INSERT INTO peer_configs (
+ name, ike_cfg, local_id, remote_id
+) VALUES (
+ 'sqltest', 1, 1, 2
+);
+
+INSERT INTO child_configs (
+ name
+) VALUES (
+ 'sqltest'
+);
+
+INSERT INTO peer_config_child_config (
+ peer_cfg, child_cfg
+) VALUES (
+ 1, 1
+);
+
+INSERT INTO traffic_selectors (
+ type
+) VALUES (
+ 7
+);
+
+INSERT INTO child_config_traffic_selector (
+ child_cfg, traffic_selector, kind
+) VALUES (
+ 1, 1, 2
+);
+INSERT INTO child_config_traffic_selector (
+ child_cfg, traffic_selector, kind
+) VALUES (
+ 1, 1, 3
+);
+
+INSERT INTO shared_secrets (
+ type, data
+) VALUES (
+ 1, 'martintestpsk'
+);
+
+INSERT INTO shared_secret_identity (
+ shared_secret, identity
+) VALUES (
+ 1, 1
+);
+
+INSERT INTO shared_secret_identity (
+ shared_secret, identity
+) VALUES (
+ 1, 2
+);
+
+INSERT INTO certificates (
+ type, keytype, data
+) VALUES (
+ 1, 1, X'308202fa308201e2a00302010202105af265ae78ff23def7a6a3948c3fa0c1300d06092a864886f70d01010505003039310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e310f300d060355040313066d617274696e301e170d3037303432373037313432365a170d3132303432353037313432365a3039310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e310f300d060355040313066d617274696e30820122300d06092a864886f70d01010105000382010f003082010a0282010100d7b9ba4de23b3d357a3f886795e7fd9fe90a0d793a9e218fcbe46724ae0cdab3ccec36b4a84df13dade48c639254b7b202a200628b04aca017ad179a050dd7b30802c526cfdd0542fc136d9fb1f34f821def01c991ea371b7928fabf9fb3eb824f10c64ba408f78ef200ea0497809f6586de6bc7da83fcad4aaf528b4d33ee49872f3b6045668fe689ccb19202172b7b8e90478484599581d8e0f387e00409fdcc3a2134faecbef59ccf55807be3759d3668ab83e3ad01530d8a9aa6b015c9c5f89b5132cf976cfe4a563cc88f4a70234ff6f7e69f09cd8fea207d34c0c5c034066f8beb04543f0ecde285ab943e916c186f965df28b10e99043b06152accf750203010001300d06092a864886f70d01010505000382010100096342ade5a3f6c95d08f2787beb8aef5000c8ebe92694cb84107e426b863857a602985a2c8f44321b978c7e4bd8e8e80f4ab9319ff69f0e6726052a99143541479afa12940be9277c7120d78d3b97192d15ffa4f3898d295ff63f93af7861e4e12e75c12cc4769519f837dcd8007a3c0f492e880916b39233df77834fb59e308c481dd884fbf1b9a0be25ff4cebef2bcdfa0b94663b28083f3ada41d06bab5ebb8a9fdc983e593748be69de8582f2538be444e4719114850e1e79dd62f5dc2589ab505baaaee3646a2334d730e22ac8810cecd231c61eb6c057d9e114069bf8516947f09ccd69ef8e5f62da10f73c6d0f33ec6ffd940716413206a4e1083187'
+);
+INSERT INTO certificates (
+ type, keytype, data
+) VALUES (
+ 1, 1, X'30820503308202eba003020102020149300d06092a864886f70d01010505003045310b3009060355040613024348310c300a060355040a1303485352310f300d060355040b1306496e74536563311730150603550403130e496e745365632032303037204341301e170d3037303131363135323634385a170d3131303131353135323634385a3046310b3009060355040613024348310c300a060355040a1303485352310f300d060355040b1306496e74536563311830160603550403130f73696476303135302e6873722e636830820122300d06092a864886f70d01010105000382010f003082010a0282010100cd946c229f7d52b21da1cb5384e7dcaf6529f760534a56355efd49e87a9c6f1ddd5ff303bd7eb49c23de03adc487456f41eb5f92947bdc8ff8dbe443f8d112e0da2c98145e7c4d1cd15cddd08577f4d4f3d0a2e1da3c08c94cd819758751931e7a9724cc43d73a11b8e176a268b4cdbbf3995cb09723abc9bfc477c25e714a4661a84c078be7404d8986be55f20437e3a6b278a3cc89aec085941f1a1aafaf4b22ae146fe4684d5567dc30658a32087d01b98515070cb1653311cb6102f82a83c638c2a79985dbb9600752e9cbc272014a5c547b4ab59130c3a948658bff794b6f202cf95939ffa73b10521f05c060cecb15f8597ed95d72b9e405ee31f1b5d90203010001a381fc3081f930090603551d1304023000300b0603551d0f0404030203a8301d0603551d0e04160414ed90e64feca21f4b6897992422e0de21b9d6262930750603551d23046e306c80140ab8ed865c4ded8c83a7c681fafb49292d451f43a149a4473045310b3009060355040613024348310c300a060355040a1303485352310f300d060355040b1306496e74536563311730150603550403130e496e745365632032303037204341820900df8d6b00b2efccd8301a0603551d1104133011820f73696476303135302e6873722e6368302d0603551d1f042630243022a020a01e861c687474703a2f2f696e747365632e6873722e63682f6873722e63726c300d06092a864886f70d01010505000382020100a4d7ca2d8e49943102104f0b4aec0c80ab18b2499bc3ac582f1df13e34a11a664ec5ac92df47de5c6ecad1f931f7226a038e65764cc97eb01f6ff022e0745f134824316ca5912e6a648924e1932301b71e95ee00390a1de90ca8f0eb91c5cc781e22828ec1e9a4b3040c193cd5ece18acdf97d0bc0c99c04433301cfe06c29bbd15b18133266c76056cf0c184aaafd25cc4fde60cb02d12bc9f96de41521d4cd1a57d28cb4da80493e39b3f2fe2a1f52c5e787f63837f406999b40fa77928fdd9d97bdb68828a096bc98275c7d6b40c4be020a3787ac9078a8471283a7e7fa2001b65e4558eb9c13e3883d0858f6a56f1260f35edff5953a4a28e785c2f853ab29b8ccee245e7799ab372c2a6e05b75de593be238df7a6c95792d9c67bf33bbe0edbdcda48373ed1097a76324950d1dd3f5f3046b04dc150ee38c69bbd1ceb8421db05bb39f16ec309f5b97aed81b45b04da2603bb0cc8ecff96a627cb8a73d700309fd4dde266196fb51799ca46c7d885da6eee1e300b7d6a4f2c701f8ae3b061f576e8e3028070e8662c120198d8df5993c8c41f3465217ebbcdfcad0706f608c6492ffcac5a69c1e9b610e1a41534ca3f10631ddc045edeba46e35804eb43629241e036acdd701eaf8a634a05250443648bb619ae5192ea281b0ae40ac94e9e38afd14b212b0c09e4a92ca64b61ea520e828273c39a529169d09d555ccf21'
+);
+
+INSERT INTO certificate_identity (
+ certificate, identity
+) VALUES (
+ 1, 3
+);
+INSERT INTO certificate_identity (
+ certificate, identity
+) VALUES (
+ 1, 1
+);
+INSERT INTO certificate_identity (
+ certificate, identity
+) VALUES (
+ 2, 4
+);
+INSERT INTO certificate_identity (
+ certificate, identity
+) VALUES (
+ 2, 5
+);
+
+INSERT INTO pools (
+ name, start, end, next
+) VALUES (
+ 'a', X'0a050000', X'0a05ffff', X'0a050000'
+);
+
+
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-22 21:26:52 +0000