Debian Codegraph @

database/sqlite/sql-schema.sql

raw
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);