| 1 |
-- |
| 2 |
-- eurephia database schema for SQLite3 |
| 3 |
-- |
| 4 |
-- GPLv2 only - Copyright (C) 2008 - 2012 |
| 5 |
-- David Sommerseth <[email protected]> |
| 6 |
-- |
| 7 |
-- This program is free software; you can redistribute it and/or |
| 8 |
-- modify it under the terms of the GNU General Public License |
| 9 |
-- as published by the Free Software Foundation; version 2 |
| 10 |
-- of the License. |
| 11 |
-- |
| 12 |
-- This program is distributed in the hope that it will be useful, |
| 13 |
-- but WITHOUT ANY WARRANTY; without even the implied warranty of |
| 14 |
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
| 15 |
-- GNU General Public License for more details. |
| 16 |
-- |
| 17 |
-- You should have received a copy of the GNU General Public License |
| 18 |
-- along with this program; if not, write to the Free Software |
| 19 |
-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. |
| 20 |
-- |
| 21 |
|
| 22 |
|
| 23 |
-- openvpn_certificates - contains mainly X.509 information from SSL certificates |
| 24 |
CREATE TABLE openvpn_certificates ( |
| 25 |
depth integer NOT NULL, |
| 26 |
digest varchar(64) NOT NULL, |
| 27 |
common_name varchar(64) NOT NULL, |
| 28 |
organisation varchar(64) NOT NULL, |
| 29 |
email varchar(256) NOT NULL, |
| 30 |
registered timestamp DEFAULT CURRENT_TIMESTAMP, |
| 31 |
certid integer PRIMARY KEY AUTOINCREMENT |
| 32 |
); |
| 33 |
CREATE UNIQUE INDEX opevpn_certificates_digest ON openvpn_certificates(digest); |
| 34 |
|
| 35 |
-- openvpn_users - contains user account information |
| 36 |
CREATE TABLE openvpn_users ( |
| 37 |
username varchar(32) NOT NULL, |
| 38 |
password varchar(128) NOT NULL, |
| 39 |
activated timestamp , |
| 40 |
deactivated timestamp , |
| 41 |
last_accessed timestamp , |
| 42 |
uid integer PRIMARY KEY AUTOINCREMENT |
| 43 |
); |
| 44 |
CREATE UNIQUE INDEX openvpn_users_uname ON openvpn_users(username); |
| 45 |
|
| 46 |
-- openvpn_usercerts - keeps the links of user accounts and certificates and |
| 47 |
-- which firewall access profile each access is granted |
| 48 |
CREATE TABLE openvpn_usercerts ( |
| 49 |
uid integer NOT NULL, -- Must be found in openvpn_users |
| 50 |
certid integer NOT NULL, -- Must be found in openvpn_certificates |
| 51 |
accessprofile integer , -- If not null, it must be found in openvpn_accesses |
| 52 |
registered timestamp DEFAULT CURRENT_TIMESTAMP, |
| 53 |
uicid integer PRIMARY KEY AUTOINCREMENT -- Unique ID |
| 54 |
); |
| 55 |
CREATE INDEX openvpn_usercerts_uid ON openvpn_usercerts(uid); |
| 56 |
CREATE INDEX openvpn_usercerts_certid ON openvpn_usercerts(certid); |
| 57 |
|
| 58 |
-- openvpn_accesses - all available firewall profiles must be registered here. |
| 59 |
CREATE TABLE openvpn_accesses ( |
| 60 |
access_descr varchar(128) , -- A little description, used for the admin utilities |
| 61 |
fw_profile varchar(64) NOT NULL, -- The destination "chain" in the firewall implementation |
| 62 |
accessprofile integer PRIMARY KEY AUTOINCREMENT -- Unique ID |
| 63 |
); |
| 64 |
|
| 65 |
-- openvpn_lastlog - This table keeps the session history of all granted user logins |
| 66 |
CREATE TABLE openvpn_lastlog ( |
| 67 |
uid integer , |
| 68 |
certid integer , |
| 69 |
protocol varchar(4) NOT NULL, |
| 70 |
remotehost varchar(128) NOT NULL, |
| 71 |
remoteport integer NOT NULL, |
| 72 |
macaddr varchar(20) , |
| 73 |
vpnipaddr varchar(32) NOT NULL, |
| 74 |
vpnipmask varchar(32) NOT NULL, |
| 75 |
vpnipv6addr varchar(48) , |
| 76 |
sessionstatus integer NOT NULL DEFAULT 0, |
| 77 |
sessionkey varchar(128) , |
| 78 |
login timestamp , |
| 79 |
logout timestamp , |
| 80 |
session_deleted timestamp , |
| 81 |
session_duration timestamp, |
| 82 |
bytes_sent integer , |
| 83 |
bytes_received integer , |
| 84 |
llid integer PRIMARY KEY AUTOINCREMENT |
| 85 |
); |
| 86 |
CREATE UNIQUE INDEX openvpn_lastlog_sessionkey ON openvpn_lastlog(sessionkey); |
| 87 |
|
| 88 |
-- openvpn_vpnaddr_history - This keeps an overview over which VPN client addresses |
| 89 |
-- a session have used, in case the client changes the address. |
| 90 |
CREATE TABLE openvpn_vpnaddr_history ( |
| 91 |
sessionkey varchar(64) NOT NULL, |
| 92 |
macaddr varchar(20) NOT NULL, |
| 93 |
ip4addr varchar(32) , |
| 94 |
ip6addr varchar(48) , |
| 95 |
registered timestamp DEFAULT CURRENT_TIMESTAMP, |
| 96 |
semaid integer PRIMARY KEY AUTOINCREMENT |
| 97 |
); |
| 98 |
CREATE INDEX openvpn_vpnaddr_hist_sessionkey ON openvpn_vpnaddr_history(sessionkey); |
| 99 |
CREATE INDEX openvpn_vpnaddr_hist_macaddr ON openvpn_vpnaddr_history(macaddr); |
| 100 |
|
| 101 |
-- openvpn_sessions - A little storage of variables needed by eurephia to keep track |
| 102 |
-- of all the sessions |
| 103 |
CREATE TABLE openvpn_sessions ( |
| 104 |
sessionkey varchar(128) NOT NULL, |
| 105 |
datakey varchar(256) NOT NULL, |
| 106 |
dataval text , |
| 107 |
registered timestamp DEFAULT CURRENT_TIMESTAMP, |
| 108 |
sessid integer PRIMARY KEY AUTOINCREMENT |
| 109 |
); |
| 110 |
CREATE INDEX openvpn_sessions_sessionkey ON openvpn_sessions(sessionkey); |
| 111 |
CREATE UNIQUE INDEX openvpn_sessions_sess_datakey ON openvpn_sessions(sessionkey, datakey); |
| 112 |
|
| 113 |
-- openvpn_blacklist - All blacklisted certificates (based on its SHA1 digest), username or |
| 114 |
-- IP address (remoteip) must be listed here. Only one of the digest, username and remoteip |
| 115 |
-- fields should be used per record. |
| 116 |
CREATE TABLE openvpn_blacklist ( |
| 117 |
digest varchar(64) , |
| 118 |
username varchar(32) , |
| 119 |
remoteip varchar(32) , |
| 120 |
registered timestamp DEFAULT CURRENT_TIMESTAMP, |
| 121 |
last_accessed timestamp , |
| 122 |
blid integer PRIMARY KEY AUTOINCREMENT |
| 123 |
); |
| 124 |
CREATE UNIQUE INDEX openvpn_blacklist_digest ON openvpn_blacklist(digest); |
| 125 |
CREATE UNIQUE INDEX openvpn_blacklist_username ON openvpn_blacklist(username); |
| 126 |
CREATE UNIQUE INDEX openvpn_blacklist_remoteip ON openvpn_blacklist(remoteip); |
| 127 |
|
| 128 |
-- openvpn_attempts - Keeps an overview for eurephia on which certificates, usernames and |
| 129 |
-- IP addresses which have had unsuccessful login attempts. This is used by the auto-blacklist |
| 130 |
-- feature in eurephia. Of the attempts value reaches the configured blacklist threshold, it |
| 131 |
-- will be blacklisted. Only one of the digest, username and IP address fields should be used |
| 132 |
-- per record. |
| 133 |
CREATE TABLE openvpn_attempts ( |
| 134 |
username varchar(32) , |
| 135 |
digest varchar(64) , |
| 136 |
remoteip varchar(32) , |
| 137 |
attempts integer DEFAULT 0, |
| 138 |
registered timestamp DEFAULT CURRENT_TIMESTAMP, |
| 139 |
last_attempt timestamp , |
| 140 |
atpid integer PRIMARY KEY AUTOINCREMENT |
| 141 |
); |
| 142 |
CREATE UNIQUE INDEX openvpn_attempts_username ON openvpn_attempts(username); |
| 143 |
CREATE UNIQUE INDEX openvpn_attempts_digest ON openvpn_attempts(digest); |
| 144 |
CREATE UNIQUE INDEX openvpn_attempts_remoteip ON openvpn_attempts(remoteip); |
| 145 |
|
| 146 |
-- openvpn_sessionkeys - A sessionseed is a key which is only unique in a short time |
| 147 |
-- perspective, while a sessionkey is supposed to be unique for ever. This table is |
| 148 |
-- a "translation" table between current session seeds and session keys. |
| 149 |
CREATE TABLE openvpn_sessionkeys ( |
| 150 |
sessionseed varchar(128) NOT NULL, |
| 151 |
sessionkey varchar(128) NOT NULL, |
| 152 |
PRIMARY KEY(sessionkey) |
| 153 |
); |
| 154 |
CREATE INDEX opevpn_sessionkeys_seed ON openvpn_sessionkeys(sessionseed); |
| 155 |
|
| 156 |
-- openvpn_config - This table keeps all the eurephia configuration parameters, and |
| 157 |
-- one record here is one configuration option. |
| 158 |
CREATE TABLE openvpn_config ( |
| 159 |
datakey varchar(64) NOT NULL, |
| 160 |
dataval text , |
| 161 |
cfgid integer PRIMARY KEY AUTOINCREMENT |
| 162 |
); |
| 163 |
CREATE UNIQUE INDEX openvpn_config_key ON openvpn_config(datakey); |
| 164 |
|
| 165 |
-- eurephia_adminlog - Is the lastlog of the eurephia admin utilities. |
| 166 |
-- This also logs when the last action was performed, to make sure |
| 167 |
-- a user is automatically logged out if the session has been idle for |
| 168 |
-- too long. |
| 169 |
CREATE TABLE eurephia_adminlog ( |
| 170 |
uid integer NOT NULL, |
| 171 |
interface char NOT NULL, -- C-onsole, W-eb |
| 172 |
status integer NOT NULL, |
| 173 |
login timestamp NOT NULL, |
| 174 |
last_action timestamp NOT NULL, |
| 175 |
logout timestamp , |
| 176 |
sessionkey varchar(128) NOT NULL, |
| 177 |
ealid integer PRIMARY KEY AUTOINCREMENT |
| 178 |
); |
| 179 |
CREATE INDEX eurephia_adminlog_uid ON eurephia_adminlog(uid); |
| 180 |
CREATE INDEX eurephia_adminlog_sesskey ON eurephia_adminlog(sessionkey); |
| 181 |
|
| 182 |
-- eurephia_adminaccesss - Defines which modules eurephia users have access to. |
| 183 |
-- The access is defined per user account. uid refers to the user account in the |
| 184 |
-- openvpn_users table. |
| 185 |
CREATE TABLE eurephia_adminaccess ( |
| 186 |
uid integer NOT NULL, |
| 187 |
interface char NOT NULL, -- C-onsole, W-eb |
| 188 |
access varchar(64) NOT NULL |
| 189 |
); |
| 190 |
CREATE INDEX eurephia_adminacc_uid ON eurephia_adminaccess (uid); |
| 191 |
CREATE INDEX eurephia_adminacc_uid_intf ON eurephia_adminaccess (uid,interface); |