From 52497ab789ce706d6c726d347dfc8af7bedfee89 Mon Sep 17 00:00:00 2001 From: David Sommerseth Date: Sun, 8 Jan 2012 22:56:19 +0100 Subject: edb-pgsql: Add an updatable view for the lastlog table This allows the eurephia-auth user to only update the columns in the lastlog which it is supposed to update. Signed-off-by: David Sommerseth --- database/postgresql/sql-schema.sql | 15 +++++++++++++++ 1 file changed, 15 insertions(+) (limited to 'database/postgresql/sql-schema.sql') diff --git a/database/postgresql/sql-schema.sql b/database/postgresql/sql-schema.sql index dc75a82..6634344 100644 --- a/database/postgresql/sql-schema.sql +++ b/database/postgresql/sql-schema.sql @@ -95,6 +95,21 @@ CREATE TABLE lastlog ( ); 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 ( -- cgit