65 lines
2.8 KiB
SQL
65 lines
2.8 KiB
SQL
-- Access control for the API and web UI: users, groups, and glob-based
|
|
-- topic access rules (allow/deny; see shared/access.py for semantics).
|
|
|
|
-- NOTE: user/group administration happens in the web UI (or SQL). Rule
|
|
-- patterns are globs matched against the full topic/tag name, where
|
|
-- '*' matches any character sequence (including '.') and everything
|
|
-- else is literal. Example: '11001.*_Value' grants everything under
|
|
-- '11001.' ending in '_Value'; user rules can override group rules via
|
|
-- deny, which always wins over allow.
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'user'
|
|
CHECK (role IN ('user', 'admin')),
|
|
disabled_at TIMESTAMPTZ,
|
|
must_change_password BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS groups (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_groups (
|
|
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
group_id BIGINT NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (user_id, group_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS access_rules (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
|
|
group_id BIGINT REFERENCES groups(id) ON DELETE CASCADE,
|
|
kind TEXT NOT NULL CHECK (kind IN ('allow', 'deny')),
|
|
pattern TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CHECK ((user_id IS NULL) <> (group_id IS NULL)) -- exactly one owner
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_access_rules_user ON access_rules (user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_access_rules_group ON access_rules (group_id);
|
|
|
|
-- Sessions issued by the web UI; the API validates the same table, so a
|
|
-- web login is also an API login. Only a SHA-256 of the token is stored.
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
token_hash TEXT PRIMARY KEY,
|
|
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
csrf_token TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
seen_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions (user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_expiry ON sessions (expires_at);
|
|
|
|
-- Login attempt throttling for the web UI.
|
|
CREATE TABLE IF NOT EXISTS login_attempts (
|
|
ip TEXT PRIMARY KEY,
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
last_seen TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
); |