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
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
|
--
-- eurephia database schema for PostgreSQL
--
-- GPLv2 only - Copyright (C) 2011
-- 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.
--
-- Table certificates: contains mainly X.509 information from SSL certificates
CREATE TABLE 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 SERIAL PRIMARY KEY
);
CREATE UNIQUE INDEX certificates_digest ON certificates(digest);
-- Table users: contains user account information
CREATE TABLE users (
username varchar(32) NOT NULL,
password varchar(128) NOT NULL,
activated timestamp ,
deactivated timestamp ,
last_accessed timestamp ,
uid SERIAL PRIMARY KEY
);
CREATE UNIQUE INDEX users_uname ON users(username);
-- View users_last_access: A view which allows the eurephia-auth user to update
-- the last_access column in users table
--
-- The "touch" rule will enforce CURRENT_TIMESTAMP, not trusting the value provided
-- by the user updating this view.
--
CREATE VIEW users_last_access AS SELECT uid, last_accessed FROM users;
CREATE RULE users_last_access_touch
AS ON UPDATE TO users_last_access
DO INSTEAD UPDATE users SET last_accessed = CURRENT_TIMESTAMP WHERE uid = NEW.uid;
-- Table usercerts: keeps the links of user accounts and certificates and
-- which firewall access profile each access is granted
CREATE TABLE usercerts (
uid integer NOT NULL, -- Must be found in 'users'
certid integer NOT NULL, -- Must be found in 'certificates'
accessprofile integer , -- If not null, it must be found in 'accesses'
registered timestamp DEFAULT CURRENT_TIMESTAMP,
uicid SERIAL PRIMARY KEY -- Unique ID
);
CREATE INDEX usercerts_uid ON usercerts(uid);
CREATE INDEX usercerts_certid ON usercerts(certid);
-- Table accesses: all available firewall profiles must be registered here.
CREATE TABLE 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 SERIAL PRIMARY KEY -- Unique ID
);
-- Table lastlog: This table keeps the session history of all granted user logins
CREATE TABLE 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 interval ,
bytes_sent integer ,
bytes_received integer ,
llid SERIAL PRIMARY KEY
);
CREATE UNIQUE INDEX lastlog_sessionkey ON lastlog(sessionkey);
-- View lastlog_update: A view with columns which is allowed to be updated
CREATE VIEW lastlog_update AS
SELECT sessionkey, sessionstatus, macaddr, logout, session_deleted, session_duration,
bytes_sent, bytes_received
FROM lastlog;
CREATE RULE lastlog_allow_update
AS ON UPDATE TO lastlog_update
DO INSTEAD UPDATE lastlog
SET sessionstatus = NEW.sessionstatus, macaddr = NEW.macaddr,
logout = NEW.logout, session_deleted = NEW.session_deleted,
session_duration = NEW.session_duration,
bytes_sent = NEW.bytes_sent, bytes_received = NEW.bytes_received
WHERE sessionkey = OLD.sessionkey
AND sessionstatus = OLD.sessionstatus;
-- Table macaddr_history: This keeps an overview over which MAC addresses a session
-- have used, in case the client changes the MAC address.
CREATE TABLE macaddr_history (
sessionkey varchar(128) NOT NULL,
macaddr varchar(20) NOT NULL,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
semaid SERIAL PRIMARY KEY
);
CREATE INDEX macaddr_hist_sessionkey ON macaddr_history(sessionkey);
CREATE INDEX macaddr_hist_macaddr ON macaddr_history(macaddr);
-- TABLE sessions: A little storage of variables needed by eurephia to keep track
-- of all the sessions
CREATE TABLE sessions (
sessionkey varchar(128) NOT NULL,
datakey varchar(256) NOT NULL,
dataval text ,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
sessid SERIAL PRIMARY KEY
);
CREATE INDEX sessions_sessionkey ON sessions(sessionkey);
CREATE UNIQUE INDEX sessions_sess_datakey ON sessions(sessionkey, datakey);
-- Table 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 blacklist (
digest varchar(64) ,
username varchar(32) ,
remoteip varchar(32) ,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
last_accessed timestamp ,
blid SERIAL PRIMARY KEY
);
CREATE UNIQUE INDEX blacklist_digest ON blacklist(digest);
CREATE UNIQUE INDEX blacklist_username ON blacklist(username);
CREATE UNIQUE INDEX blacklist_remoteip ON blacklist(remoteip);
-- Table 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 attempts (
username varchar(32) ,
digest varchar(64) ,
remoteip varchar(32) ,
attempts integer DEFAULT 0,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
last_attempt timestamp ,
atpid SERIAL PRIMARY KEY
);
CREATE UNIQUE INDEX attempts_username ON attempts(username);
CREATE UNIQUE INDEX attempts_digest ON attempts(digest);
CREATE UNIQUE INDEX attempts_remoteip ON attempts(remoteip);
-- Table 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 sessionkeys (
sessionseed varchar(128) NOT NULL,
sessionkey varchar(128) NOT NULL,
PRIMARY KEY(sessionkey)
);
CREATE INDEX sessionkeys_seed ON sessionkeys(sessionseed);
-- Table configuration: This table keeps all the eurephia configuration parameters, and
-- one record here is one configuration option.
CREATE TABLE configuration (
datakey varchar(64) NOT NULL,
dataval text ,
cfgid SERIAL PRIMARY KEY
);
CREATE UNIQUE INDEX configuration_key ON configuration(datakey);
-- Table 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 SERIAL PRIMARY KEY
);
CREATE INDEX eurephia_adminlog_uid ON eurephia_adminlog(uid);
CREATE INDEX eurephia_adminlog_sesskey ON eurephia_adminlog(sessionkey);
CREATE VIEW eurephia_adminlog_update AS
SELECT sessionkey, status, last_action, logout
FROM eurephia_adminlog;
CREATE RULE eurephia_adminlog_allow_update
AS ON UPDATE TO eurephia_adminlog_update
DO INSTEAD UPDATE eurephia_adminlog
SET status = NEW.status, last_action = NEW.last_action,
logout = NEW.logout
WHERE sessionkey = OLD.sessionkey;
-- Table 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 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);
|
