1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
|
--
-- eurephia database schema for SQLite3
--
-- GPLv2 only - Copyright (C) 2008 - 2012
-- 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,
vpnipv6addr varchar(48) ,
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_vpnaddr_history - This keeps an overview over which VPN client addresses
-- a session have used, in case the client changes the address.
CREATE TABLE openvpn_vpnaddr_history (
sessionkey varchar(64) NOT NULL,
macaddr varchar(20) NOT NULL,
ip4addr varchar(32) ,
ip6addr varchar(48) ,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
semaid integer PRIMARY KEY AUTOINCREMENT
);
CREATE INDEX openvpn_vpnaddr_hist_sessionkey ON openvpn_vpnaddr_history(sessionkey);
CREATE INDEX openvpn_vpnaddr_hist_macaddr ON openvpn_vpnaddr_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);
|
