diff options
Diffstat (limited to 'database/postgresql/sql-schema.sql')
| -rw-r--r-- | database/postgresql/sql-schema.sql | 190 |
1 files changed, 190 insertions, 0 deletions
diff --git a/database/postgresql/sql-schema.sql b/database/postgresql/sql-schema.sql new file mode 100644 index 0000000..907d1d5 --- /dev/null +++ b/database/postgresql/sql-schema.sql @@ -0,0 +1,190 @@ +-- +-- 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 openvpn_users_uname ON openvpn_users(username); + +-- 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 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 SERIAL PRIMARY KEY -- Unique ID +); +CREATE INDEX openvpn_usercerts_uid ON openvpn_usercerts(uid); +CREATE INDEX openvpn_usercerts_certid ON openvpn_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 timestamp, + bytes_sent integer , + bytes_received integer , + llid SERIAL PRIMARY KEY +); +CREATE UNIQUE INDEX lastlog_sessionkey ON lastlog(sessionkey); + +-- 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(64) 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 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 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 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 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); + +-- 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 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); |