73 lines
3.2 KiB
SQL
73 lines
3.2 KiB
SQL
-- +goose Up
|
|
|
|
-- Sign-in through OIDC (P-6). The provider supplies the EVENT of a login and nothing else: the
|
|
-- session that follows is ours, revocable in one row.
|
|
|
|
-- Identity is the pair (provider, subject) and nothing else, so the address stops being a key.
|
|
--
|
|
-- Google states it outright: an address can change hands and must not be a primary identifier.
|
|
-- Rules that follow:
|
|
-- * an unknown (provider, subject) always creates a NEW user, whatever address it arrives with;
|
|
-- * a second provider is attached to an existing account by an authenticated ACTION;
|
|
-- * users.email is refreshed only from a verified address.
|
|
-- The cost is two accounts for one person who signs in with two providers — a duplicate, which a
|
|
-- person can merge. The cost of the alternative is an account taken over by whoever inherits an
|
|
-- address, which nobody can undo.
|
|
alter table users alter column email drop not null;
|
|
drop index users_email_key;
|
|
|
|
create table identities (
|
|
provider text not null,
|
|
subject text not null,
|
|
user_id text not null references users (id) on delete cascade,
|
|
email text,
|
|
email_verified boolean not null default false,
|
|
created_at timestamptz not null default now(),
|
|
last_login_at timestamptz not null default now(),
|
|
primary key (provider, subject)
|
|
);
|
|
|
|
create index identities_user_idx on identities (user_id);
|
|
|
|
-- The in-flight half of a login: one row per authorization request, single-use, minutes long.
|
|
-- The state travels in a URL and in a cookie, so it is stored as a digest like any other
|
|
-- credential. The verifier is the PKCE secret; it never leaves this server.
|
|
create table auth_states (
|
|
state_sha256 bytea primary key,
|
|
provider text not null,
|
|
nonce text not null,
|
|
code_verifier text not null,
|
|
return_to text not null default '',
|
|
created_at timestamptz not null default now(),
|
|
expires_at timestamptz not null
|
|
);
|
|
|
|
create index auth_states_expiry_idx on auth_states (expires_at);
|
|
|
|
-- The login journal. The sessions table is swept and is not an audit: "when did I last sign in,
|
|
-- and from what" has to survive the sweep, and it is the evidence behind "sign out everywhere".
|
|
--
|
|
-- Coarse on purpose: an address prefix and a client class, never a full IP or user agent.
|
|
-- SET NULL rather than cascade: deleting an account must not erase the evidence of how it was
|
|
-- accessed — the row is anonymised, not destroyed.
|
|
create table login_events (
|
|
id bigint generated always as identity primary key,
|
|
user_id text references users (id) on delete set null,
|
|
provider text not null,
|
|
outcome text not null check (outcome in ('success', 'denied')),
|
|
reason text not null default '',
|
|
ip_prefix text not null default '',
|
|
client text not null default '',
|
|
at timestamptz not null default now()
|
|
);
|
|
|
|
create index login_events_user_idx on login_events (user_id, at desc);
|
|
-- The retention sweep deletes by age; a login journal that only grows is a liability.
|
|
create index login_events_at_idx on login_events (at);
|
|
|
|
-- +goose Down
|
|
drop table login_events;
|
|
drop table auth_states;
|
|
drop table identities;
|
|
create unique index users_email_key on users (lower(email));
|
|
alter table users alter column email set not null;
|