commit f7343271475afeb77d58192090163b743028da2d
from: Oliver Lowe <o@olowe.co>
date: Mon Apr 25 09:27:31 2022 UTC

mailmux: store last modified time of users, aliases

This is useful information that could be used for auditing people
abusing the service, some basic non-invasive system metrics, and
of course it's useful for users to see in the interface.

commit - 4fb0d63962ea2b720c4ad42eef17c8aecd716fb1
commit + f7343271475afeb77d58192090163b743028da2d
blob - 8c734a57ded5058303addc5eba09334d439a07cc
blob + a3aff8f8efcfa8adc7aadb3b6a76af020240e542
--- alias.go
+++ alias.go
@@ -24,6 +24,8 @@ type Alias struct {
 	// Note contains user-defined text that can be used to,
 	// for example, identify the alias.
 	Note string
+	// modTime contains the time the alias was last modified.
+	modTime time.Time
 }
 
 // Equal reports whether a and b represent the same Alias.
@@ -71,7 +73,8 @@ CREATE TABLE IF NOT EXISTS aliases (
 	recipient TEXT PRIMARY KEY,
 	destination TEXT NOT NULL,
 	expiry INTEGER NOT NULL,
-	note TEXT NOT NULL
+	note TEXT NOT NULL,
+	modtime INTEGER NOT NULL
 );`
 	_, err = db.Exec(stmt)
 	if err != nil {
@@ -106,11 +109,11 @@ func (db *AliasDB) Put(a Alias) error {
 	}
 	var q string
 	if errors.Is(err, errRecipientNotExist) {
-		q = "INSERT INTO aliases (recipient, destination, expiry, note) VALUES (?, ?, ?, ?)"
-		_, err = db.Exec(q, a.Recipient, a.Destination, a.Expiry.Unix(), a.Note)
+		q = "INSERT INTO aliases (recipient, destination, expiry, note, modtime) VALUES (?, ?, ?, ?, ?)"
+		_, err = db.Exec(q, a.Recipient, a.Destination, a.Expiry.Unix(), a.Note, time.Now().Unix())
 	} else if err == nil {
-		q = "UPDATE aliases SET recipient = ?, destination = ?, expiry = ?, note = ? WHERE recipient = ?"
-		_, err = db.Exec(q, a.Recipient, a.Destination, a.Expiry.Unix(), a.Note, a.Recipient)
+		q = "UPDATE aliases SET recipient = ?, destination = ?, expiry = ?, note = ?, modtime = ? WHERE recipient = ?"
+		_, err = db.Exec(q, a.Recipient, a.Destination, a.Expiry.Unix(), a.Note, time.Now().Unix(), a.Recipient)
 	}
 	return err
 }
@@ -118,21 +121,24 @@ func (db *AliasDB) Put(a Alias) error {
 // Lookup returns the Alias with the recipient rcpt. If no alias exists, an error is returned.
 func (db *AliasDB) Lookup(rcpt string) (Alias, error) {
 	var a Alias
-	q := "SELECT recipient, destination, expiry, note FROM ALIASES WHERE recipient = ?"
+	q := "SELECT recipient, destination, expiry, note, modtime FROM ALIASES WHERE recipient = ?"
 	var expiry int64
-	err := db.QueryRow(q, rcpt).Scan(&a.Recipient, &a.Destination, &expiry, &a.Note)
+	var modtime int64
+	err := db.QueryRow(q, rcpt).Scan(&a.Recipient, &a.Destination, &expiry, &a.Note, &modtime)
 	if errors.Is(err, sql.ErrNoRows) {
 		return Alias{}, errRecipientNotExist
 	} else if err != nil {
 		return Alias{}, err
 	}
 	a.Expiry = time.Unix(expiry, 0)
+	a.modTime = time.Unix(modtime, 0)
 	return a, nil
 }
 
 // Aliases returns all aliases who have their destination address as dest.
 func (db *AliasDB) Aliases(dest string) ([]Alias, error) {
-	rows, err := db.Query("SELECT recipient, destination, expiry, note FROM aliases WHERE destination = ?", dest)
+	q := "SELECT recipient, destination, expiry, note FROM aliases WHERE destination = ?"
+	rows, err := db.Query(q, dest)
 	if err != nil {
 		return nil, err
 	}
blob - 5df1f44e95df5012ac0d24147c449c696aa34808
blob + 6fc8bb0f4d22cd170287c17377a96dfd1ee42cdb
--- userdb.go
+++ userdb.go
@@ -7,6 +7,7 @@ import (
 	"net/mail"
 	"os"
 	"path"
+	"time"
 
 	_ "github.com/mattn/go-sqlite3"
 	"golang.org/x/crypto/bcrypt"
@@ -58,7 +59,8 @@ func OpenUserDB(name, dir string) (*UserDB, error) {
 func initialiseUserDB(db *sql.DB) error {
 	stmt := `CREATE TABLE IF NOT EXISTS users (
 	username TEXT PRIMARY KEY,
-	password BLOB NOT NULL
+	password BLOB NOT NULL,
+	modtime INTEGER NOT NULL
 );`
 	_, err := db.Exec(stmt)
 	return err
@@ -92,7 +94,7 @@ func (db *UserDB) add(username string, pw Password) er
 	if err != nil {
 		return fmt.Errorf("add %s: %w", username, err)
 	}
-	_, err = db.Exec("INSERT INTO users (username, password) VALUES (?, ?)", username, hashed)
+	_, err = db.Exec("INSERT INTO users (username, password, modtime) VALUES (?, ?, ?)", username, hashed, time.Now().Unix())
 	if err != nil {
 		return fmt.Errorf("add %s: %w", username, err)
 	}
@@ -115,7 +117,7 @@ func (db *UserDB) Change(name string, new Password) er
 	if err != nil {
 		return fmt.Errorf("generate hash: %w", err)
 	}
-	_, err = db.Exec("UPDATE users SET password = ? WHERE username = ?", hashed, name)
+	_, err = db.Exec("UPDATE users SET password = ?, modtime = ? WHERE username = ?", hashed, time.Now().Unix(), name)
 	return err
 }