From f3c6505df85d22706e8a8a849ead71bb10246cbe Mon Sep 17 00:00:00 2001
From: David Sommerseth <dazo@users.sourceforge.net>
Date: Thu, 5 Jan 2012 19:49:17 +0100
Subject: Add an SQL VIEW for updating users.last_accessed more safely

It's not ideal to let the eurephia-auth user have write access to the
users table.  This view will allow the eurephia-auth user only to touch
users.last_accessed; and this value will be enforced to be CURRENT_TIMESTAMP.

Signed-off-by: David Sommerseth <dazo@users.sourceforge.net>
---
 database/postgresql/sql-schema.sql | 11 +++++++++++
 1 file changed, 11 insertions(+)

(limited to 'database/postgresql/sql-schema.sql')

diff --git a/database/postgresql/sql-schema.sql b/database/postgresql/sql-schema.sql
index 907d1d5..11b5c7d 100644
--- a/database/postgresql/sql-schema.sql
+++ b/database/postgresql/sql-schema.sql
@@ -43,6 +43,17 @@ CREATE TABLE users (
 );
 CREATE UNIQUE INDEX openvpn_users_uname ON openvpn_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 (
-- 
cgit 

