Added more comments to the SQLite3 database schema

1 files changed, 38 insertions, 7 deletions

diff --git a/database/sqlite/sql-schema.sql b/database/sqlite/sql-schema.sql
index a6c3997..c221a62 100644
--- a/database/sqlite/sql-schema.sql
+++ b/database/sqlite/sql-schema.sql
@@ -4,6 +4,8 @@
 -- GPLv2 only - Copyright 2008
 --              David Sommerseth
 
+
+-- openvpn_certificates - contains mainly X.509 information from SSL certificates
 CREATE TABLE openvpn_certificates (
        depth         integer      NOT NULL,
        digest        varchar(64)  NOT NULL,
@@ -15,6 +17,7 @@ CREATE TABLE openvpn_certificates (
 );
 CREATE UNIQUE INDEX opevpn_certificates_digest ON openvpn_certificates(digest);
 
+-- openvpn_users - contains user account information
 CREATE TABLE openvpn_users (
        username      varchar(32)  NOT NULL,
        password      varchar(128)  NOT NULL,
@@ -25,22 +28,26 @@ CREATE TABLE openvpn_users (
 );
 CREATE UNIQUE INDEX openvpn_users_uname ON openvpn_users(username);
 
+-- openvpn_usercerts - keeps the links of user accounts and certificates and
+-- which firewall access profile each access is granted
 CREATE TABLE openvpn_usercerts (
-       uid           integer      NOT NULL,
-       certid        integer      NOT NULL,
-       accessprofile integer      ,
+       uid           integer      NOT NULL,  -- Must be found in openvpn_users
+       certid        integer      NOT NULL,  -- Must be found in openvpn_certificates
+       accessprofile integer      ,          -- If not null, it must be found in openvpn_accesses
        registered    timestamp    DEFAULT CURRENT_TIMESTAMP,
-       uicid         integer      PRIMARY KEY AUTOINCREMENT
+       uicid         integer      PRIMARY KEY AUTOINCREMENT -- Unique ID
 );
 CREATE INDEX openvpn_usercerts_uid ON openvpn_usercerts(uid);
 CREATE INDEX openvpn_usercerts_certid ON openvpn_usercerts(certid);
 
+-- openvpn_accesses - all available firewall profiles must be registered here.
 CREATE TABLE openvpn_accesses (
-       access_descr   varchar(128) ,
-       fw_profile     varchar(64)  NOT NULL,
-       accessprofile  integer      PRIMARY KEY AUTOINCREMENT
+       access_descr   varchar(128) ,         -- A little description, used for the admin utilities
+       fw_profile     varchar(64)  NOT NULL, -- The destination "chain" in the firewall implementation
+       accessprofile  integer      PRIMARY KEY AUTOINCREMENT  -- Unique ID
 );
 
+-- openvpn_lastlog - This table keeps the session history of all granted user logins
 CREATE TABLE openvpn_lastlog (
        uid              integer      ,
        certid           integer      ,
@@ -62,6 +69,8 @@ CREATE TABLE openvpn_lastlog (
 );
 CREATE UNIQUE INDEX openvpn_lastlog_sessionkey ON openvpn_lastlog(sessionkey);
 
+-- openvpn_macaddr_history - This keeps an overview over which MAC addresses a session
+-- have used, in case the client changes the MAC address.
 CREATE TABLE openvpn_macaddr_history (
        sessionkey     varchar(64)  NOT NULL,
        macaddr        varchar(20)  NOT NULL,
@@ -71,6 +80,8 @@ CREATE TABLE openvpn_macaddr_history (
 CREATE INDEX openvpn_macaddr_hist_sessionkey ON openvpn_macaddr_history(sessionkey);
 CREATE INDEX openvpn_macaddr_hist_macaddr ON openvpn_macaddr_history(macaddr);
 
+-- openvpn_sessions - A little storage of variables needed by eurephia to keep track
+-- of all the sessions
 CREATE TABLE openvpn_sessions (
        sessionkey     varchar(128) NOT NULL,
        datakey        varchar(256) NOT NULL,
@@ -81,6 +92,9 @@ CREATE TABLE openvpn_sessions (
 CREATE INDEX openvpn_sessions_sessionkey ON openvpn_sessions(sessionkey);
 CREATE UNIQUE INDEX openvpn_sessions_sess_datakey ON openvpn_sessions(sessionkey, datakey);
 
+-- openvpn_blacklist - All blacklisted certificates (based on its SHA1 digest), username or
+-- IP address (remoteip) must be listed here.  Only one of the digest, username and remoteip
+-- fields should be used per record.
 CREATE TABLE openvpn_blacklist (
        digest         varchar(64)  ,
        username       varchar(32)  ,
@@ -93,6 +107,11 @@ CREATE UNIQUE INDEX openvpn_blacklist_digest ON openvpn_blacklist(digest);
 CREATE UNIQUE INDEX openvpn_blacklist_username ON openvpn_blacklist(username);
 CREATE UNIQUE INDEX openvpn_blacklist_remoteip ON openvpn_blacklist(remoteip);
 
+-- openvpn_attempts - Keeps an overview for eurephia on which certificates, usernames and
+-- IP addresses which have had unsuccessful login attempts.  This is used by the auto-blacklist
+-- feature in eurephia.  Of the attempts value reaches the configured blacklist threshold, it
+-- will be blacklisted.  Only one of the digest, username and IP address fields should be used
+-- per record.
 CREATE TABLE openvpn_attempts (
        username       varchar(32)  ,
        digest         varchar(64)  ,
@@ -106,6 +125,9 @@ CREATE UNIQUE INDEX openvpn_attempts_username ON openvpn_attempts(username);
 CREATE UNIQUE INDEX openvpn_attempts_digest ON openvpn_attempts(digest);
 CREATE UNIQUE INDEX openvpn_attempts_remoteip ON openvpn_attempts(remoteip);
 
+-- openvpn_sessionkeys - A sessionseed is a key which is only unique in a short time
+-- perspective, while a sessionkey is supposed to be unique for ever.  This table is
+-- a "translation" table between current session seeds and session keys.
 CREATE TABLE openvpn_sessionkeys (
        sessionseed      varchar(128)  NOT NULL,
        sessionkey       varchar(128)  NOT NULL,
@@ -113,6 +135,8 @@ CREATE TABLE openvpn_sessionkeys (
 );
 CREATE INDEX opevpn_sessionkeys_seed ON openvpn_sessionkeys(sessionseed);
 
+-- openvpn_config - This table keeps all the eurephia configuration parameters, and
+-- one record here is one configuration option.
 CREATE TABLE openvpn_config (
        datakey        varchar(64)  NOT NULL,
        dataval        text         ,
@@ -120,6 +144,10 @@ CREATE TABLE openvpn_config (
 );
 CREATE UNIQUE INDEX openvpn_config_key ON openvpn_config(datakey);
 
+-- eurephia_adminlog - Is the lastlog of the eurephia admin utilities.
+-- This also logs when the last action was performed, to make sure
+-- a user is automatically logged out if the session has been idle for
+-- too long.
 CREATE TABLE eurephia_adminlog (
        uid            integer      NOT NULL,
        interface      char         NOT NULL, -- C-onsole, W-eb
@@ -133,6 +161,9 @@ CREATE TABLE eurephia_adminlog (
 CREATE INDEX eurephia_adminlog_uid ON eurephia_adminlog(uid);
 CREATE INDEX eurephia_adminlog_sesskey ON eurephia_adminlog(sessionkey);
 
+-- eurephia_adminaccesss - Defines which modules eurephia users have access to.
+-- The access is defined per user account.  uid refers to the user account in the
+-- openvpn_users table.
 CREATE TABLE eurephia_adminaccess (
        uid            integer     NOT NULL,
        interface      char        NOT NULL,  -- C-onsole, W-eb