summaryrefslogtreecommitdiffstats
path: root/database/postgresql/sql-schema.sql
blob: 67e88413461ecd0728057d05870588de108aa2c7 (plain)
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);