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

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