diff options
Diffstat (limited to 'database/postgresql/sql-schema.sql')
| -rw-r--r-- | database/postgresql/sql-schema.sql | 11 |
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 ( |