--
-- eurephia database schema for SQLite3
--
-- GPLv2 only - Copyright (C) 2008 - 2010
--              David Sommerseth <dazo@users.sourceforge.net>
--
--  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; version 2
--  of the License.
--
--  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.
--
--  You should have received a copy of the GNU General Public License
--  along with this program; if not, write to the Free Software
--  Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
--


-- openvpn_certificates - contains mainly X.509 information from SSL certificates
CREATE TABLE openvpn_certificates (
       depth         integer      NOT NULL,
       digest        varchar(64)  NOT NULL,
       common_name   varchar(64)  NOT NULL,
       organisation  varchar(64)  NOT NULL,
       email         varchar(256) NOT NULL,
       registered    timestamp    DEFAULT CURRENT_TIMESTAMP,
       certid        integer      PRIMARY KEY AUTOINCREMENT
);
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,
       activated     timestamp    ,
       deactivated   timestamp    ,
       last_accessed timestamp    ,
       uid           integer      PRIMARY KEY AUTOINCREMENT
);
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,  -- 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 -- 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) ,         -- 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      ,
       protocol         varchar(4)   NOT NULL,
       remotehost       varchar(128) NOT NULL,
       remoteport       integer      NOT NULL,
       macaddr          varchar(20)  ,
       vpnipaddr        varchar(32)  NOT NULL,
       vpnipmask        varchar(32)  NOT NULL,
       sessionstatus    integer      NOT NULL DEFAULT 0,
       sessionkey       varchar(128) ,
       login            timestamp    ,
       logout           timestamp    ,
       session_deleted  timestamp    ,
       session_duration timestamp,
       bytes_sent       integer      ,
       bytes_received   integer      ,
       llid             integer      PRIMARY KEY AUTOINCREMENT
);
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,
       registered     timestamp    DEFAULT CURRENT_TIMESTAMP,
       semaid         integer      PRIMARY KEY AUTOINCREMENT
);
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,
       dataval        text         ,
       registered     timestamp    DEFAULT CURRENT_TIMESTAMP,
       sessid         integer      PRIMARY KEY AUTOINCREMENT
);
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)  ,
       remoteip       varchar(32)  ,
       registered     timestamp    DEFAULT CURRENT_TIMESTAMP,
       last_accessed  timestamp    ,
       blid           integer      PRIMARY KEY AUTOINCREMENT
);
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)  ,
       remoteip       varchar(32)  ,
       attempts       integer      DEFAULT 0,
       registered     timestamp    DEFAULT CURRENT_TIMESTAMP,
       last_attempt   timestamp    ,
       atpid          integer      PRIMARY KEY AUTOINCREMENT
);
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,
       PRIMARY KEY(sessionkey)
);
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         ,
       cfgid          integer      PRIMARY KEY AUTOINCREMENT
);
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
       status         integer      NOT NULL,
       login          timestamp    NOT NULL,
       last_action    timestamp    NOT NULL,
       logout         timestamp    ,
       sessionkey     varchar(128) NOT NULL,
       ealid          integer      PRIMARY KEY AUTOINCREMENT
);
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
       access         varchar(64) NOT NULL
);
CREATE INDEX eurephia_adminacc_uid ON eurephia_adminaccess (uid);
CREATE INDEX eurephia_adminacc_uid_intf ON eurephia_adminaccess (uid,interface);
