Persistence and authentication
Persistence and authentication
What you will learn
- enforce durable invariants with constraints;
- place multi-step operations in transactions;
- store password verifiers and session-token digests safely;
- enforce ownership in queries;
- test repositories against an isolated real database.
Schema rules
CREATE TABLE users (
id uuid PRIMARY KEY,
email text NOT NULL,
password_hash bytea NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX users_email_unique ON users (lower(email));
CREATE TABLE tasks (
id uuid PRIMARY KEY,
owner_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title text NOT NULL CHECK (char_length(title) BETWEEN 1 AND 200),
completed boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
Application validation provides friendly errors; constraints preserve data when races or other paths bypass it. Test migrations against the same database family and major version used in deployment.
Transactions and identity
Registration may create both a user and session. Begin a transaction, defer tx.Rollback(), perform every operation through tx, and commit only after all succeed. Deferred rollback after commit harmlessly returns sql.ErrTxDone.
- Use a maintained password-hashing implementation with a reviewed cost; never a fast general hash alone.
- Generate session tokens with
crypto/rand, send raw tokens only over TLS, and store a cryptographic digest. - Define expiry, revocation, and rotation.
- Never log passwords, raw tokens, or authorization headers.
- Include ownership in protected queries:
WHERE id = $3 AND owner_id = $4. - Parameterize every untrusted SQL value.
Exact password parameters change with hardware and guidance. Follow the selected library's current documentation and benchmark on representative deployment hardware.
Practice
Why store a digest of a random session token but a slow password-specific hash of a password?
Summary
- Combine application validation with database constraints.
- Use one transaction for one all-or-nothing operation.
- Treat passwords and random tokens according to different threat models.
- Put ownership predicates inside protected data operations.
Ready to wrap up?
Mark this complete when you can explain the main idea and finish the practice task.