// session.go backs the browser half of the write gate. HTTP Basic works
// and is what the API uses, but it gives a person no way to log out and
// leaves the credential in the browser's keeping.
package oltp
import (
"crypto/rand"
"crypto/sha256"
"encoding/hex"
"errors"
"time"
)
const sessionSchema = `
CREATE TABLE IF NOT EXISTS session (
token TEXT PRIMARY KEY, -- SHA-256 of the cookie value, never the value
account TEXT NOT NULL,
expires TEXT NOT NULL, -- RFC3339
created TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS session_by_account ON session (account);`
// sessionMigrations: where a session was opened from and when it last
// spoke, so an admin can audit who is signed in from where and a
// person can recognise their own devices.
var sessionMigrations = []string{
`ALTER TABLE session ADD COLUMN ip TEXT NOT NULL DEFAULT ''`,
`ALTER TABLE session ADD COLUMN agent TEXT NOT NULL DEFAULT ''`,
`ALTER TABLE session ADD COLUMN last_seen TEXT NOT NULL DEFAULT ''`,
}
// Session is one signed-in device as the audit surfaces see it. ID is
// a prefix of the stored token HASH — enough to name a row, useless to
// forge a cookie.
type Session struct {
ID string
Account string
Created string
Expires string
LastSeen string
IP string
Agent string
}
// sessionSeenEvery bounds how often a request refreshes last_seen: an
// audit needs "this afternoon", not a write per page view.
const sessionSeenEvery = 5 * time.Minute
// SessionTTL is how long a login lasts. Long enough to be usable, short
// enough that a forgotten browser stops being a standing key.
const SessionTTL = 14 * 24 * time.Hour
// hashToken is what the database stores. A session table that can be
// read back into working cookies turns a backup, a log or a stray SELECT
// into a set of live logins; storing the digest means the cookie itself
// exists only in the browser.
func hashToken(token string) string {
sum := sha256.Sum256([]byte(token))
return hex.EncodeToString(sum[:])
}
// NewSession authenticates and issues a session token. The token is
// returned once, to be set as a cookie; it is not recoverable later.
func (d *DB) NewSession(name, password string) (token string, expires time.Time, ok bool) {
return d.NewSessionFrom(name, password, "", "")
}
// NewSessionFrom is NewSession that records where the sign-in came
// from. Several sessions per account are ordinary: a phone and a
// laptop are two rows, and ending one leaves the other signed in.
func (d *DB) NewSessionFrom(name, password, ip, agent string) (token string, expires time.Time, ok bool) {
acct, ok := d.Authenticate(name, password)
if !ok {
return "", time.Time{}, false
}
var buf [32]byte
if _, err := rand.Read(buf[:]); err != nil {
return "", time.Time{}, false
}
token = hex.EncodeToString(buf[:])
expires = time.Now().UTC().Add(SessionTTL)
now := time.Now().UTC().Format(time.RFC3339)
if _, err := d.db.Exec(`INSERT INTO session (token, account, expires, created, ip, agent, last_seen)
VALUES (?, ?, ?, ?, ?, ?, ?)`, hashToken(token), acct.Name,
expires.Format(time.RFC3339), now, ip, trimAgent(agent), now); err != nil {
return "", time.Time{}, false
}
d.db.Exec(`UPDATE account SET last_login = ? WHERE name = ?`, now, acct.Name)
return token, expires, true
}
func trimAgent(agent string) string {
if len(agent) > 200 {
return agent[:200]
}
return agent
}
// SessionAccount resolves a cookie to its account, or reports that it is
// unknown or expired. An expired row is deleted on sight rather than
// left to a sweeper: the cheapest place to notice is where it is read.
func (d *DB) SessionAccount(token string) (Account, bool) {
if token == "" {
return Account{}, false
}
h := hashToken(token)
var name, expires, seen string
if err := d.db.QueryRow(`SELECT account, expires, last_seen FROM session WHERE token = ?`,
h).Scan(&name, &expires, &seen); err != nil {
return Account{}, false
}
exp, err := time.Parse(time.RFC3339, expires)
if err != nil || time.Now().UTC().After(exp) {
d.db.Exec(`DELETE FROM session WHERE token = ?`, h)
return Account{}, false
}
var admin int
var created, disabled string
if err := d.db.QueryRow(`SELECT admin, created, disabled FROM account WHERE name = ?`,
name).Scan(&admin, &created, &disabled); err != nil {
d.db.Exec(`DELETE FROM session WHERE token = ?`, h)
return Account{}, false
}
// A shut account's sessions were ended when it was shut; this is
// the belt to that brace, for a row that raced the shutting.
if disabled != "" {
d.db.Exec(`DELETE FROM session WHERE token = ?`, h)
return Account{}, false
}
if last, perr := time.Parse(time.RFC3339, seen); perr != nil || time.Since(last) > sessionSeenEvery {
d.db.Exec(`UPDATE session SET last_seen = ? WHERE token = ?`, time.Now().UTC().Format(time.RFC3339), h)
}
return Account{Name: name, Admin: admin != 0, Created: created}, true
}
// EndSession logs one cookie out.
func (d *DB) EndSession(token string) {
if token != "" {
d.db.Exec(`DELETE FROM session WHERE token = ?`, hashToken(token))
}
}
// EndAccountSessions logs an account out everywhere — what a password
// change means, and the only way a stolen cookie stops working.
func (d *DB) EndAccountSessions(name string) error {
_, err := d.db.Exec(`DELETE FROM session WHERE account = ?`, name)
return err
}
// Sessions lists live sessions, newest first — one account's, or every
// account's when name is empty. Expired rows are swept on the way.
func (d *DB) Sessions(name string) ([]Session, error) {
now := time.Now().UTC().Format(time.RFC3339)
d.db.Exec(`DELETE FROM session WHERE expires < ?`, now)
q := `SELECT substr(token, 1, 12), account, created, expires, last_seen, ip, agent FROM session`
var args []any
if name != "" {
q += ` WHERE account = ?`
args = append(args, name)
}
rows, err := d.db.Query(q+` ORDER BY created DESC`, args...)
if err != nil {
return nil, err
}
defer rows.Close()
var out []Session
for rows.Next() {
var s Session
if err := rows.Scan(&s.ID, &s.Account, &s.Created, &s.Expires, &s.LastSeen, &s.IP, &s.Agent); err != nil {
return nil, err
}
out = append(out, s)
}
return out, rows.Err()
}
// EndSessionID ends one session by the ID Sessions reported. A
// non-empty account limits it to that account's own sessions, which is
// how a person signs out one of their devices without being able to
// sign out anybody else's.
func (d *DB) EndSessionID(id, account string) error {
if len(id) < 12 {
return errors.New("oltp: session id too short")
}
_, err := d.db.Exec(`DELETE FROM session WHERE substr(token, 1, 12) = ? AND (? = '' OR account = ?)`,
id[:12], account, account)
return err
}
// SessionID is the audit ID of the session behind a cookie token, so a
// person can tell "this device" apart from the rest of their list.
func SessionID(token string) string {
if token == "" {
return ""
}
return hashToken(token)[:12]
}