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>

1 files changed, 11 insertions, 0 deletions

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 (