-- -- eurephia database schema for SQLite3 -- -- GPLv2 only - Copyright (C) 2008 - 2010 -- David Sommerseth -- -- 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);