377 lines
14 KiB
Go
377 lines
14 KiB
Go
package auth
|
|
|
|
import (
|
|
"context"
|
|
"sort"
|
|
"strings"
|
|
"testing"
|
|
|
|
"github.com/jackc/pgx/v5/pgxpool"
|
|
|
|
"github.com/krow/krow-backend/go-api/internal/testutil"
|
|
)
|
|
|
|
// These tests drive migration 000004 itself: what it creates, that it can be
|
|
// rolled back, and that rolling it back and re-applying it leaves the schema
|
|
// where it started. They also cover the one change 000004 makes to an existing
|
|
// table — the global unique index on users.email.
|
|
//
|
|
// Every one runs in its own throwaway database. Nothing here can reach the
|
|
// development database: testutil builds the name from its own prefix.
|
|
|
|
const migration4Up = "000004_auth_sessions.up.sql"
|
|
const migration4Down = "000004_auth_sessions.down.sql"
|
|
|
|
// 14. Email is unique across the whole table, not merely within one
|
|
// organization. This is what makes "log in with your email" answerable.
|
|
func TestUsersEmailIsGloballyUnique(t *testing.T) {
|
|
f := newFixture(t, "schema_email_unique")
|
|
|
|
// A second organization. Under 000001's (org_id, email) key alone, the
|
|
// insert below would have been perfectly legal.
|
|
var otherOrg string
|
|
if err := f.pool.QueryRow(f.ctx,
|
|
`INSERT INTO organizations (name, slug) VALUES ($1, $2) RETURNING id::text`,
|
|
"Second Org", "second-org").Scan(&otherOrg); err != nil {
|
|
t.Fatalf("create the second organization: %v", err)
|
|
}
|
|
|
|
_, err := f.pool.Exec(f.ctx,
|
|
`INSERT INTO users (org_id, email, full_name) VALUES ($1::uuid, $2::citext, $3)`,
|
|
otherOrg, "session-owner@example.test", "Impostor")
|
|
if err == nil {
|
|
t.Fatal("the same email was accepted in a second organization; login would be ambiguous")
|
|
}
|
|
if !strings.Contains(err.Error(), "users_email_global_key") {
|
|
t.Errorf("the rejection came from %v, want a users_email_global_key violation", err)
|
|
}
|
|
|
|
// citext makes the index case-insensitive, which is what a login form
|
|
// needs: nobody should be able to register Demo@… beside demo@….
|
|
if _, err := f.pool.Exec(f.ctx,
|
|
`INSERT INTO users (org_id, email, full_name) VALUES ($1::uuid, $2::citext, $3)`,
|
|
otherOrg, "SESSION-OWNER@EXAMPLE.TEST", "Impostor"); err == nil {
|
|
t.Error("a case-variant of an existing email was accepted")
|
|
}
|
|
|
|
// A genuinely different email in the second organization is still fine —
|
|
// the index constrains duplicates, not multi-tenancy.
|
|
if _, err := f.pool.Exec(f.ctx,
|
|
`INSERT INTO users (org_id, email, full_name) VALUES ($1::uuid, $2::citext, $3)`,
|
|
otherOrg, "someone-else@example.test", "Colleague"); err != nil {
|
|
t.Errorf("a distinct email in a second organization was rejected: %v", err)
|
|
}
|
|
|
|
// The index is unique and covers exactly users(email).
|
|
var isUnique bool
|
|
var definition string
|
|
if err := f.pool.QueryRow(f.ctx,
|
|
`SELECT i.indisunique, pg_get_indexdef(i.indexrelid)
|
|
FROM pg_index i
|
|
JOIN pg_class c ON c.oid = i.indexrelid
|
|
WHERE c.relname = 'users_email_global_key'`).Scan(&isUnique, &definition); err != nil {
|
|
t.Fatalf("read users_email_global_key: %v", err)
|
|
}
|
|
if !isUnique {
|
|
t.Error("users_email_global_key is not a unique index")
|
|
}
|
|
if !strings.Contains(definition, "(email)") {
|
|
t.Errorf("users_email_global_key covers %q, want (email)", definition)
|
|
}
|
|
}
|
|
|
|
// 15 and 17. Applying every migration in order produces the sessions table
|
|
// this package needs, and leaves everything the earlier migrations built.
|
|
func TestMigrationUpBuildsTheSessionsSchema(t *testing.T) {
|
|
ctx := context.Background()
|
|
pool := testutil.Sandbox(t, "schema_up")
|
|
testutil.ApplyAllMigrations(ctx, t, pool)
|
|
|
|
// Columns and types.
|
|
want := map[string]string{
|
|
"id": "uuid",
|
|
"user_id": "uuid",
|
|
"token_hash": "text",
|
|
"expires_at": "timestamp with time zone",
|
|
"absolute_expires_at": "timestamp with time zone",
|
|
"created_date": "timestamp with time zone",
|
|
"last_seen_at": "timestamp with time zone",
|
|
}
|
|
rows, err := pool.Query(ctx,
|
|
`SELECT column_name, data_type, is_nullable
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'public' AND table_name = 'sessions'`)
|
|
if err != nil {
|
|
t.Fatalf("read the sessions columns: %v", err)
|
|
}
|
|
got := map[string]string{}
|
|
for rows.Next() {
|
|
var name, dataType, nullable string
|
|
if err := rows.Scan(&name, &dataType, &nullable); err != nil {
|
|
t.Fatalf("scan: %v", err)
|
|
}
|
|
got[name] = dataType
|
|
// Every column is required. A nullable expires_at would be a session
|
|
// with no deadline at all.
|
|
if nullable != "NO" {
|
|
t.Errorf("sessions.%s is nullable; every session column is required", name)
|
|
}
|
|
}
|
|
rows.Close()
|
|
if err := rows.Err(); err != nil {
|
|
t.Fatalf("read the sessions columns: %v", err)
|
|
}
|
|
if len(got) == 0 {
|
|
t.Fatal("the sessions table does not exist after the migrations")
|
|
}
|
|
for name, wantType := range want {
|
|
if gotType, ok := got[name]; !ok {
|
|
t.Errorf("sessions.%s is missing", name)
|
|
} else if gotType != wantType {
|
|
t.Errorf("sessions.%s is %s, want %s", name, gotType, wantType)
|
|
}
|
|
}
|
|
for name := range got {
|
|
if _, expected := want[name]; !expected {
|
|
t.Errorf("sessions has an unexpected column %q", name)
|
|
}
|
|
}
|
|
|
|
// Constraints: the primary key, the uniqueness that makes a token identify
|
|
// one session, and the checks that keep a raw token and an immortal
|
|
// session out of the table.
|
|
for _, c := range []struct{ name, kind string }{
|
|
{"sessions_pkey", "p"},
|
|
{"sessions_token_hash_key", "u"},
|
|
{"sessions_token_hash_sha256", "c"},
|
|
{"sessions_absolute_after_created", "c"},
|
|
{"sessions_within_absolute", "c"},
|
|
} {
|
|
var kind string
|
|
if err := pool.QueryRow(ctx,
|
|
`SELECT contype::text FROM pg_constraint
|
|
WHERE conrelid = 'public.sessions'::regclass AND conname = $1`, c.name).Scan(&kind); err != nil {
|
|
t.Errorf("constraint %s is missing: %v", c.name, err)
|
|
continue
|
|
}
|
|
if kind != c.kind {
|
|
t.Errorf("constraint %s is of type %q, want %q", c.name, kind, c.kind)
|
|
}
|
|
}
|
|
|
|
// The foreign key, and that it cascades. ON DELETE NO ACTION here would
|
|
// mean a deleted user keeps a working session.
|
|
var fkTarget, onDelete string
|
|
if err := pool.QueryRow(ctx,
|
|
`SELECT confrelid::regclass::text, confdeltype::text
|
|
FROM pg_constraint
|
|
WHERE conrelid = 'public.sessions'::regclass AND contype = 'f'`).Scan(&fkTarget, &onDelete); err != nil {
|
|
t.Fatalf("read the sessions foreign key: %v", err)
|
|
}
|
|
if fkTarget != "users" {
|
|
t.Errorf("the foreign key points at %s, want users", fkTarget)
|
|
}
|
|
if onDelete != "c" {
|
|
t.Errorf("the foreign key is ON DELETE %q, want \"c\" (CASCADE)", onDelete)
|
|
}
|
|
|
|
// Indexes. token_hash is indexed by its UNIQUE constraint — that index is
|
|
// the lookup path — plus the two the sweep and per-user revocation need.
|
|
indexes := indexNames(ctx, t, pool, "sessions")
|
|
for _, name := range []string{"sessions_pkey", "sessions_token_hash_key", "sessions_user_idx", "sessions_expires_idx"} {
|
|
if !contains(indexes, name) {
|
|
t.Errorf("index %s is missing; sessions has %v", name, indexes)
|
|
}
|
|
}
|
|
|
|
// 17. The earlier migrations are untouched: the tables they built are all
|
|
// still here, and so is the org-scoped uniqueness 000001 declared.
|
|
//
|
|
// Named rather than counted. This was a count of 18 — the 17 tables from
|
|
// 000001 plus sessions — which asserted the right thing in the wrong way:
|
|
// it broke when 000005 added two unrelated tables, and it would have stayed
|
|
// green if 000004 had dropped one table and added another. Listing them is
|
|
// both more precise and stable across later migrations.
|
|
for _, table := range []string{
|
|
"organizations", "users", "user_preferences", "role_categories",
|
|
"certifications", "badges", "courses", "learning_paths", "job_postings",
|
|
"worker_profiles", "job_applications", "ai_interviews", "staff",
|
|
"assignments", "shift_records", "evidence", "user_activity",
|
|
"sessions",
|
|
} {
|
|
if !tableExists(ctx, t, pool, table) {
|
|
t.Errorf("%s is missing after the migrations", table)
|
|
}
|
|
}
|
|
var orgScoped int
|
|
if err := pool.QueryRow(ctx,
|
|
`SELECT count(*)::int FROM pg_constraint
|
|
WHERE conrelid = 'public.users'::regclass AND conname = 'users_org_email_key'`).Scan(&orgScoped); err != nil {
|
|
t.Fatalf("read users_org_email_key: %v", err)
|
|
}
|
|
if orgScoped != 1 {
|
|
t.Error("000004 removed users_org_email_key; it should leave the existing table alone")
|
|
}
|
|
}
|
|
|
|
// 16. 000004 rolls back cleanly, and re-applies afterwards. A migration that
|
|
// cannot be reversed is a migration nobody can safely deploy.
|
|
func TestMigration000004IsReversible(t *testing.T) {
|
|
ctx := context.Background()
|
|
pool := testutil.Sandbox(t, "schema_down")
|
|
testutil.ApplyAllMigrations(ctx, t, pool)
|
|
|
|
if !tableExists(ctx, t, pool, "sessions") {
|
|
t.Fatal("sessions does not exist before the rollback")
|
|
}
|
|
|
|
if err := testutil.ApplyMigration(ctx, t, pool, migration4Down); err != nil {
|
|
t.Fatalf("apply %s: %v", migration4Down, err)
|
|
}
|
|
|
|
if tableExists(ctx, t, pool, "sessions") {
|
|
t.Error("sessions survived the rollback")
|
|
}
|
|
if indexExists(ctx, t, pool, "users_email_global_key") {
|
|
t.Error("users_email_global_key survived the rollback")
|
|
}
|
|
|
|
// The rollback must touch nothing else. users is still here, still has the
|
|
// constraint that predates 000004, and still has its rows.
|
|
if !tableExists(ctx, t, pool, "users") {
|
|
t.Fatal("the rollback dropped the users table")
|
|
}
|
|
var orgScoped int
|
|
if err := pool.QueryRow(ctx,
|
|
`SELECT count(*)::int FROM pg_constraint
|
|
WHERE conrelid = 'public.users'::regclass AND conname = 'users_org_email_key'`).Scan(&orgScoped); err != nil {
|
|
t.Fatalf("read users_org_email_key: %v", err)
|
|
}
|
|
if orgScoped != 1 {
|
|
t.Error("the rollback removed users_org_email_key, which it did not create")
|
|
}
|
|
// With the global index gone, the pre-000004 rule is back in force: the
|
|
// same email in two organizations is legal again.
|
|
var orgA, orgB string
|
|
if err := pool.QueryRow(ctx, `INSERT INTO organizations (name, slug) VALUES ('A','a') RETURNING id::text`).Scan(&orgA); err != nil {
|
|
t.Fatalf("create org A: %v", err)
|
|
}
|
|
if err := pool.QueryRow(ctx, `INSERT INTO organizations (name, slug) VALUES ('B','b') RETURNING id::text`).Scan(&orgB); err != nil {
|
|
t.Fatalf("create org B: %v", err)
|
|
}
|
|
for _, org := range []string{orgA, orgB} {
|
|
if _, err := pool.Exec(ctx,
|
|
`INSERT INTO users (org_id, email, full_name) VALUES ($1::uuid, $2::citext, '')`,
|
|
org, "shared@example.test"); err != nil {
|
|
t.Fatalf("insert after the rollback: %v", err)
|
|
}
|
|
}
|
|
|
|
// Re-applying now must fail, because the data violates the uniqueness the
|
|
// migration is about to declare — and it must fail without half-applying.
|
|
// That is the honest behaviour: the operator has duplicates to resolve.
|
|
if err := testutil.ApplyMigration(ctx, t, pool, migration4Up); err == nil {
|
|
t.Fatal("000004 applied over duplicate emails; the index would not be unique")
|
|
}
|
|
if tableExists(ctx, t, pool, "sessions") {
|
|
t.Error("the failed migration left the sessions table behind; it is not atomic")
|
|
}
|
|
|
|
// Resolve the duplicate and it applies cleanly, restoring exactly what the
|
|
// rollback removed.
|
|
if _, err := pool.Exec(ctx, `DELETE FROM users WHERE org_id = $1::uuid`, orgB); err != nil {
|
|
t.Fatalf("remove the duplicate: %v", err)
|
|
}
|
|
if err := testutil.ApplyMigration(ctx, t, pool, migration4Up); err != nil {
|
|
t.Fatalf("re-apply %s: %v", migration4Up, err)
|
|
}
|
|
if !tableExists(ctx, t, pool, "sessions") {
|
|
t.Error("sessions did not come back")
|
|
}
|
|
if !indexExists(ctx, t, pool, "users_email_global_key") {
|
|
t.Error("users_email_global_key did not come back")
|
|
}
|
|
}
|
|
|
|
// Every migration has a matching down file, so any of them can be reversed.
|
|
func TestEveryMigrationHasADownFile(t *testing.T) {
|
|
ups := testutil.MigrationFiles(t, ".up.sql")
|
|
downs := testutil.MigrationFiles(t, ".down.sql")
|
|
if len(ups) != len(downs) {
|
|
t.Fatalf("%d up migrations and %d down migrations", len(ups), len(downs))
|
|
}
|
|
for i, up := range ups {
|
|
want := strings.TrimSuffix(up, ".up.sql") + ".down.sql"
|
|
if downs[i] != want {
|
|
t.Errorf("%s has no matching down migration (found %s)", up, downs[i])
|
|
}
|
|
}
|
|
// 000004 is still present, with its pair. This used to also assert that it
|
|
// was the NEWEST and that there were exactly four migrations — a snapshot
|
|
// that this test's own comment predicted would need revisiting, and which
|
|
// 000005 duly broke. The count belongs to whichever phase added the newest
|
|
// migration (see TestMigrationPairsIncluding000005), so it is asserted
|
|
// there and not here. What this phase cares about — that the migration it
|
|
// added is intact and reversible — is unchanged and still checked.
|
|
if !contains(ups, migration4Up) {
|
|
t.Errorf("%s is missing from the migrations directory", migration4Up)
|
|
}
|
|
if !contains(downs, migration4Down) {
|
|
t.Errorf("%s is missing from the migrations directory", migration4Down)
|
|
}
|
|
}
|
|
|
|
/* ── introspection helpers ──────────────────────────────────────────────── */
|
|
|
|
func tableExists(ctx context.Context, t *testing.T, pool *pgxpool.Pool, name string) bool {
|
|
t.Helper()
|
|
var reg *string
|
|
if err := pool.QueryRow(ctx, `SELECT to_regclass('public.' || $1)::text`, name).Scan(®); err != nil {
|
|
t.Fatalf("to_regclass(%s): %v", name, err)
|
|
}
|
|
return reg != nil
|
|
}
|
|
|
|
func indexExists(ctx context.Context, t *testing.T, pool *pgxpool.Pool, name string) bool {
|
|
t.Helper()
|
|
var n int
|
|
if err := pool.QueryRow(ctx,
|
|
`SELECT count(*)::int FROM pg_indexes WHERE schemaname = 'public' AND indexname = $1`,
|
|
name).Scan(&n); err != nil {
|
|
t.Fatalf("look up index %s: %v", name, err)
|
|
}
|
|
return n > 0
|
|
}
|
|
|
|
func indexNames(ctx context.Context, t *testing.T, pool *pgxpool.Pool, table string) []string {
|
|
t.Helper()
|
|
rows, err := pool.Query(ctx,
|
|
`SELECT indexname FROM pg_indexes WHERE schemaname = 'public' AND tablename = $1`, table)
|
|
if err != nil {
|
|
t.Fatalf("list the indexes on %s: %v", table, err)
|
|
}
|
|
defer rows.Close()
|
|
var names []string
|
|
for rows.Next() {
|
|
var name string
|
|
if err := rows.Scan(&name); err != nil {
|
|
t.Fatalf("scan: %v", err)
|
|
}
|
|
names = append(names, name)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
t.Fatalf("list the indexes on %s: %v", table, err)
|
|
}
|
|
sort.Strings(names)
|
|
return names
|
|
}
|
|
|
|
func contains(haystack []string, needle string) bool {
|
|
for _, s := range haystack {
|
|
if s == needle {
|
|
return true
|
|
}
|
|
}
|
|
return false
|
|
}
|