-- -- eurephia database schema for PostgreSQL -- -- GPLv2 only - Copyright (C) 2011 -- David Sommerseth -- -- 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);