sonda/migrations/postgres/0004_admin_refresh.sql
tgiachi dbe9338af3 feat(auth): add refresh tokens, admin role and user management
- feat(auth): short-lived access tokens plus rotating refresh tokens stored as
  SHA-256 hashes; replaying a spent token revokes every session of that user
- feat(auth): is_admin flag; the first registered user is the admin and the
  last admin can be neither demoted nor deleted
- feat(api): POST /auth/refresh, /auth/logout, /auth/password and GET /auth/me
- feat(api): admin-only GET/POST /users, PATCH /users/{id}, PUT
  /users/{id}/password and DELETE /users/{id}; the admin check reads the role
  from the database on every call
- feat(api): starting a library scan is now admin-only
- feat(storage): users and refresh token repositories for SQLite and
  PostgreSQL (migration 0004; the oldest existing account becomes admin)
- feat(config): auth.access_ttl_minutes and auth.refresh_ttl_hours replace
  auth.token_ttl_hours
- test: rotation, reuse detection, concurrent rotation with a single winner,
  admin rules, role changes taking effect immediately
2026-11-11 12:38:42 +01:00

18 lines
808 B
SQL

-- Admin flag and rotating refresh tokens.
ALTER TABLE users ADD COLUMN is_admin BOOLEAN NOT NULL DEFAULT FALSE;
-- Accounts that already exist: the oldest one becomes the admin.
UPDATE users SET is_admin = TRUE WHERE id = (SELECT MIN(id) FROM users);
-- Only a hash of the token is stored, never the token itself.
CREATE TABLE refresh_tokens (
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
token_hash TEXT NOT NULL UNIQUE, -- SHA-256 of the token, hex
created_at BIGINT NOT NULL, -- unix seconds
expires_at BIGINT NOT NULL, -- unix seconds
revoked_at BIGINT -- unix seconds, NULL while active
);
CREATE INDEX idx_refresh_tokens_user ON refresh_tokens(user_id);