SQLite Database Schema

The database schema for SQLite as used by the sql and attr-sql plugins is defined in src/pool/sqlite.sql.

TABLE identities

type defined in src/libstrongswan/utils/identification.h

0

ID_ANY

matches any id

id = %any

1

ID_IPV4_ADDR

IPv4 address

id = 192.168.0.1

2

ID_FQDN

fully qualified domain name

id = moon.strongswan.org

3

ID_RFC822_ADDR

RFC822 email address

id = carol@strongswan.org

5

ID_IPV6_ADDR

IPv6 address

id = fec0::1

9

ID_DER_ASN1_DN

ASN.1 distinguished name

id = "C=CH, O=Linux strongSwan, CN=moon.strongswan.org"

11

ID_KEY_ID

opaque octet string

id = @#e5e410876c2ac4bead854942a6de7658303a9fc1

ID_ANY: scripts/id2sql "%any"

INSERT INTO identities (
  type, data
) VALUES (
  0, X_
);

ID_IPV4_ADDR: scripts/id2sql "192.168.0.1"

INSERT INTO identities (
  type, data
) VALUES (
  1, X'c0a80001'
);

ID_FQDN: scripts/id2sql "moon.strongswan.org"

INSERT INTO identities (
  type, data
) VALUES (
  2, X'6d6f6f6e2e7374726f6e677377616e2e6f7267'
);

ID_RFC822_ADDR: scripts/id2sql "carol@strongswan.org"

INSERT INTO identities (
  type, data
) VALUES (
  3, X'6361726f6c407374726f6e677377616e2e6f7267'
);

ID_IPV6_ADDR: scripts/id2sql "fec0::1"

INSERT INTO identities (
  type, data
) VALUES (
  5, X'fec00000000000000000000000000001'
);

ID_DER_ASN1_DN: scripts/id2sql "C=CH, O=Linux strongSwan, CN=moon.strongswan.org"

INSERT INTO identities (
  type, data
) VALUES (
  9, X'3046310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e311c301a060355040313136d6f6f6e2e7374726f6e677377616e2e6f7267'
);

ID_KEY_ID: scripts/id2sql "@#e5:e4:10:87:6c:2a:c4:be:ad:85:49:42:a6:de:76:58:30:3a:9f:c1"

INSERT INTO identities (
  type, data
) VALUES (
  11, X'e5e410876c2ac4bead854942a6de7658303a9fc1'
);

ID_PUBKEY_INFO_SHA1: scripts/key2keyid < moonKey.der

INSERT INTO identities (
  type, data
) VALUES (
  11, X'd70dbd46d5133519064f12f100525ead0802ca95'
);

ID_PUBKEY_SHA1: scripts/key2keyid < moonKey.der

INSERT INTO identities (
  type, data
) VALUES (
  11, X'e5e410876c2ac4bead854942a6de7658303a9fc1'
);

TABLE private_keys

type defined in src/libstrongswan/credentials/keys/public_key.h

1

KEY_RSA

RSA key in PKCS#1 format

2

KEY_ECDSA

ECDSA key in ANSI X9.62 format

TABLE certificates

type defined in src/libstrongswan/credentials/certificates/certificate.h

1

CERT_X509

X.509 certificate

2

CERT_X509_CRL

X.509 certificate revocation list

5

CERT_X509_AC

X.509 attribute certificate

6

CERT_TRUSTED_PUBKEY

Trusted public key

Certificate Encoding

The bin2sql script in scripts/bin2sql.c can be used to convert X.509 certificates in binary ASN.1-encoded der format to the hex blob format needed by the SQL certificates table.

The bin2sql utility can be compiled by changing via the cd command into the scripts subdirectory and executing the make command there.

Example:

  • Certificate myCert.der in binary der format

od -t x1 myCert.der
0000000 30 82 05 55 30 82 03 3d a0 03 02 01 02 02 08 7c
0000020 24 43 4b b7 dc ef 7e 30 0d 06 09 2a 86 48 86 f7
...
0002500 d6 03 54 9c 77 71 de 4c 15 29 03 2f fb 07 b6 e4
0002520 fe 81 c7 7f a6 3f dc 27 61

  • Convert the binary content of the certificate file to SQL hex blob

cat myCert.der | ./bin2sql
X'308205553082033da00302010202087c24434bb7dcef7e300d06092a864886f7...d603549c7771de4c1529032ffb07b6e4fe81c77fa63fdc2761'

TABLE shared_secrets

type defined in src/libstrongswan/credentials/keys/shared_key.h

1

SHARED_IKE

secrets.ike<suffix>.secret

2

SHARED_EAP

secrets.eap<suffix>.secret

3

SHARED_PRIVATE_KEY_PASS

secrets.rsa<suffix>.secret

4

SHARED_PIN

secrets.token<suffix>.pin

TABLE peer_configs

auth_method defined in src/libstrongswan/credentials/auth_cfg.h

0

AUTH_CLASS_ANY

Any or no authentication method

1

AUTH_CLASS_PUBKEY

Authentication using public key

auth = pubkey

2

AUTH_CLASS_PSK

Authentication using pre-shared secret

auth = psk

3

AUTH_CLASS_EAP

Authentication using EAP

auth = eap

4

AUTH_CLASS_XAUTH

authentication using XAuth

auth = xauth

eap_type defined in src/libstrongswan/eap/eap.h

4

EAP_MD5

6

EAP_GTC

13

EAP_TLS

18

EAP_SIM

21

EAP_TTLS

23

EAP_AKA

25

EAP_PEAP

26

EAP_MSCHAPV2

38

EAP_TNC

253

EAP_RADIUS

TABLE child_configs

start_action, dpd_action, and close_action defined in src/libcharon/config/child_cfg.h

0

ACTION_NONE

No action or clear

1

ACTION_ROUTE

Install or retain an IPsec policy

2

ACTION_RESTART

Start or restart a CHILD_SA

IPsec mode defined in src/libstrongswan/ipsec/ipsec_types.h

1

TRANSPORT

IPsec transport mode

2

TUNNEL

IPsec tunnel mode

3

BEET

IPsec beet mode

4

PASS

Shunt PASS policy

5

DROP

Shunt DROP policy

TABLE traffic_selectors

type defined in src/libstrongswan/selectors/traffic_selector.h

7

TS_IPV4_ADDR_RANGE

8

TS_IPV6_ADDR_RANGE

kind defined in src/libcharon/plugins/sql/sql_config.c

0

Local TS

1

Remote TS

2

Local Dynamic TS

3

Remote Dynamic TS

Examples