ca5525d4bd
Sessions had an absolute cap (created_at + SessionAbsoluteTTL) but the JWT path only had per-token TTL on the refresh row, letting a well-behaved client refresh indefinitely. Add chain_started_at to authkit_tokens, copy it forward on every rotation, and reject in RefreshJWT when now > chainStartedAt + RefreshChainAbsoluteTTL. Default 30d, mirroring SessionAbsoluteTTL. Schema, verifier, queries, model, and integration test updated. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
249 lines
11 KiB
Go
249 lines
11 KiB
Go
package authkit
|
|
|
|
import (
|
|
"fmt"
|
|
"strings"
|
|
)
|
|
|
|
// queries holds every SQL string the store issues, with table identifiers
|
|
// already substituted from a validated Schema. Built once at New() to avoid
|
|
// per-call concatenation. Identifiers are interpolated via concatenation,
|
|
// safe because Schema.Validate gated them through identifierRE.
|
|
type queries struct {
|
|
// users
|
|
createUser string
|
|
getUserByID string
|
|
getUserByEmail string
|
|
updateUser string
|
|
deleteUser string
|
|
setPassword string
|
|
setEmailVerified string
|
|
bumpSessionVersion string
|
|
|
|
// sessions
|
|
createSession string
|
|
getSession string
|
|
touchSession string
|
|
deleteSession string
|
|
deleteUserSessions string
|
|
deleteExpiredSessions string
|
|
|
|
// tokens
|
|
createToken string
|
|
consumeToken string
|
|
getToken string
|
|
getOTPForUser string
|
|
decrementOTPAttempt string
|
|
consumeOTPByID string
|
|
deleteByChain string
|
|
deleteExpiredTokens string
|
|
|
|
// service keys
|
|
createServiceKey string
|
|
getServiceKey string
|
|
listServiceKeys string
|
|
touchServiceKey string
|
|
revokeServiceKey string
|
|
getServiceKeyAbilities string
|
|
insertServiceKeyAbil string
|
|
|
|
// roles
|
|
createRole string
|
|
getRoleByID string
|
|
getRoleBySlug string
|
|
listRoles string
|
|
deleteRole string
|
|
assignRoleToUser string
|
|
removeRoleFromUser string
|
|
getUserRoles string
|
|
hasAnyRolePrefix string
|
|
|
|
// permissions
|
|
createPermission string
|
|
getPermissionByID string
|
|
getPermissionBySlug string
|
|
listPermissions string
|
|
deletePermission string
|
|
assignPermissionToRole string
|
|
removePermissionFromRole string
|
|
getRolePermissions string
|
|
getUserPermissions string
|
|
|
|
// direct user permissions
|
|
grantPermissionToUser string
|
|
revokePermissionFromUser string
|
|
|
|
// abilities
|
|
createAbility string
|
|
getAbilityByID string
|
|
getAbilityBySlug string
|
|
listAbilities string
|
|
deleteAbility string
|
|
|
|
// migrations
|
|
createMigrationsTable string
|
|
selectAppliedVersions string
|
|
}
|
|
|
|
func buildQueries(t Tables) queries {
|
|
return queries{
|
|
// users
|
|
createUser: `INSERT INTO ` + t.Users + `
|
|
(id, email, email_normalized, email_verified_at, password_hash,
|
|
session_version, last_login_at, created_at, updated_at)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
|
|
getUserByID: `SELECT id, email, email_normalized, email_verified_at,
|
|
password_hash, session_version, last_login_at, created_at, updated_at
|
|
FROM ` + t.Users + ` WHERE id = $1`,
|
|
getUserByEmail: `SELECT id, email, email_normalized, email_verified_at,
|
|
password_hash, session_version, last_login_at, created_at, updated_at
|
|
FROM ` + t.Users + ` WHERE email_normalized = $1`,
|
|
updateUser: `UPDATE ` + t.Users + ` SET
|
|
email = $1, email_normalized = $2, email_verified_at = $3,
|
|
password_hash = $4, session_version = $5,
|
|
last_login_at = $6, updated_at = $7
|
|
WHERE id = $8`,
|
|
deleteUser: `DELETE FROM ` + t.Users + ` WHERE id = $1`,
|
|
setPassword: `UPDATE ` + t.Users + ` SET password_hash = $1, updated_at = $2 WHERE id = $3`,
|
|
setEmailVerified: `UPDATE ` + t.Users + ` SET email_verified_at = $1, updated_at = $2 WHERE id = $3`,
|
|
bumpSessionVersion: `UPDATE ` + t.Users + ` SET session_version = session_version + 1, updated_at = $1 WHERE id = $2 RETURNING session_version`,
|
|
|
|
// sessions
|
|
createSession: `INSERT INTO ` + t.Sessions + `
|
|
(id_hash, user_id, user_agent, ip, created_at, last_seen_at, expires_at)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7)`,
|
|
getSession: `SELECT id_hash, user_id, user_agent, ip, created_at, last_seen_at, expires_at
|
|
FROM ` + t.Sessions + ` WHERE id_hash = $1`,
|
|
touchSession: `UPDATE ` + t.Sessions + ` SET last_seen_at = $1, expires_at = $2 WHERE id_hash = $3`,
|
|
deleteSession: `DELETE FROM ` + t.Sessions + ` WHERE id_hash = $1`,
|
|
deleteUserSessions: `DELETE FROM ` + t.Sessions + ` WHERE user_id = $1`,
|
|
deleteExpiredSessions: `DELETE FROM ` + t.Sessions + ` WHERE expires_at <= $1`,
|
|
|
|
// tokens
|
|
createToken: `INSERT INTO ` + t.Tokens + `
|
|
(hash, kind, user_id, chain_id, chain_started_at, consumed_at, attempts_remaining, created_at, expires_at)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
|
|
consumeToken: `UPDATE ` + t.Tokens + `
|
|
SET consumed_at = $1
|
|
WHERE kind = $2 AND hash = $3 AND consumed_at IS NULL AND expires_at > $4
|
|
RETURNING hash, kind, user_id, chain_id, chain_started_at, consumed_at, attempts_remaining, created_at, expires_at`,
|
|
getToken: `SELECT hash, kind, user_id, chain_id, chain_started_at, consumed_at, attempts_remaining, created_at, expires_at
|
|
FROM ` + t.Tokens + ` WHERE kind = $1 AND hash = $2`,
|
|
// getOTPForUser returns the most recent unconsumed, unexpired OTP for
|
|
// the user, used to verify a code by hash-comparing client input.
|
|
getOTPForUser: `SELECT hash, kind, user_id, chain_id, chain_started_at, consumed_at, attempts_remaining, created_at, expires_at
|
|
FROM ` + t.Tokens + `
|
|
WHERE kind = $1 AND user_id = $2 AND consumed_at IS NULL AND expires_at > $3
|
|
ORDER BY created_at DESC LIMIT 1`,
|
|
// decrementOTPAttempt drops attempts_remaining by 1 and consumes the
|
|
// row when it hits zero. Used after a wrong-code submission.
|
|
decrementOTPAttempt: `UPDATE ` + t.Tokens + `
|
|
SET attempts_remaining = GREATEST(COALESCE(attempts_remaining, 0) - 1, 0),
|
|
consumed_at = CASE WHEN COALESCE(attempts_remaining, 0) - 1 <= 0 THEN $1 ELSE consumed_at END
|
|
WHERE kind = $2 AND hash = $3 AND consumed_at IS NULL AND expires_at > $1
|
|
RETURNING attempts_remaining`,
|
|
// consumeOTPByID is the success path: mark the matched OTP consumed.
|
|
consumeOTPByID: `UPDATE ` + t.Tokens + `
|
|
SET consumed_at = $1
|
|
WHERE kind = $2 AND hash = $3 AND consumed_at IS NULL AND expires_at > $1
|
|
RETURNING hash, kind, user_id, chain_id, chain_started_at, consumed_at, attempts_remaining, created_at, expires_at`,
|
|
deleteByChain: `DELETE FROM ` + t.Tokens + ` WHERE chain_id = $1`,
|
|
deleteExpiredTokens: `DELETE FROM ` + t.Tokens + ` WHERE expires_at <= $1`,
|
|
|
|
// service keys
|
|
createServiceKey: `INSERT INTO ` + t.ServiceKeys + `
|
|
(id_hash, name, last_used_at, created_at, expires_at, revoked_at)
|
|
VALUES ($1, $2, $3, $4, $5, $6)`,
|
|
getServiceKey: `SELECT id_hash, name, last_used_at, created_at, expires_at, revoked_at
|
|
FROM ` + t.ServiceKeys + ` WHERE id_hash = $1`,
|
|
listServiceKeys: `SELECT id_hash, name, last_used_at, created_at, expires_at, revoked_at
|
|
FROM ` + t.ServiceKeys + ` ORDER BY created_at DESC`,
|
|
touchServiceKey: `UPDATE ` + t.ServiceKeys + ` SET last_used_at = $1 WHERE id_hash = $2`,
|
|
revokeServiceKey: `UPDATE ` + t.ServiceKeys + ` SET revoked_at = $1 WHERE id_hash = $2 AND revoked_at IS NULL`,
|
|
getServiceKeyAbilities: `SELECT a.slug FROM ` + t.Abilities + ` a
|
|
JOIN ` + t.ServiceKeyAbilities + ` ska ON ska.ability_id = a.id
|
|
WHERE ska.service_key_id_hash = $1 ORDER BY a.slug`,
|
|
insertServiceKeyAbil: `INSERT INTO ` + t.ServiceKeyAbilities + `
|
|
(service_key_id_hash, ability_id, granted_at) VALUES ($1, $2, $3)
|
|
ON CONFLICT DO NOTHING`,
|
|
|
|
// roles
|
|
createRole: `INSERT INTO ` + t.Roles + ` (id, slug, label, created_at) VALUES ($1, $2, $3, $4)`,
|
|
getRoleByID: `SELECT id, slug, label, created_at FROM ` + t.Roles + ` WHERE id = $1`,
|
|
getRoleBySlug: `SELECT id, slug, label, created_at FROM ` + t.Roles + ` WHERE slug = $1`,
|
|
listRoles: `SELECT id, slug, label, created_at FROM ` + t.Roles + ` ORDER BY slug`,
|
|
deleteRole: `DELETE FROM ` + t.Roles + ` WHERE id = $1`,
|
|
assignRoleToUser: `INSERT INTO ` + t.UserRoles + ` (user_id, role_id, granted_at)
|
|
VALUES ($1, $2, $3) ON CONFLICT DO NOTHING`,
|
|
removeRoleFromUser: `DELETE FROM ` + t.UserRoles + ` WHERE user_id = $1 AND role_id = $2`,
|
|
getUserRoles: `SELECT r.id, r.slug, r.label, r.created_at
|
|
FROM ` + t.Roles + ` r JOIN ` + t.UserRoles + ` ur ON ur.role_id = r.id
|
|
WHERE ur.user_id = $1 ORDER BY r.slug`,
|
|
hasAnyRolePrefix: `SELECT EXISTS (
|
|
SELECT 1 FROM ` + t.UserRoles + ` ur JOIN ` + t.Roles + ` r ON r.id = ur.role_id
|
|
WHERE ur.user_id = $1 AND r.slug IN (`,
|
|
|
|
// permissions
|
|
createPermission: `INSERT INTO ` + t.Permissions + ` (id, slug, label, created_at) VALUES ($1, $2, $3, $4)`,
|
|
getPermissionByID: `SELECT id, slug, label, created_at FROM ` + t.Permissions + ` WHERE id = $1`,
|
|
getPermissionBySlug: `SELECT id, slug, label, created_at FROM ` + t.Permissions + ` WHERE slug = $1`,
|
|
listPermissions: `SELECT id, slug, label, created_at FROM ` + t.Permissions + ` ORDER BY slug`,
|
|
deletePermission: `DELETE FROM ` + t.Permissions + ` WHERE id = $1`,
|
|
assignPermissionToRole: `INSERT INTO ` + t.RolePermissions + ` (role_id, permission_id)
|
|
VALUES ($1, $2) ON CONFLICT DO NOTHING`,
|
|
removePermissionFromRole: `DELETE FROM ` + t.RolePermissions + ` WHERE role_id = $1 AND permission_id = $2`,
|
|
getRolePermissions: `SELECT p.id, p.slug, p.label, p.created_at
|
|
FROM ` + t.Permissions + ` p JOIN ` + t.RolePermissions + ` rp ON rp.permission_id = p.id
|
|
WHERE rp.role_id = $1 ORDER BY p.slug`,
|
|
// UNION of role-derived and direct user permissions.
|
|
getUserPermissions: `SELECT DISTINCT p.id, p.slug, p.label, p.created_at FROM ` + t.Permissions + ` p
|
|
WHERE p.id IN (
|
|
SELECT rp.permission_id FROM ` + t.RolePermissions + ` rp
|
|
JOIN ` + t.UserRoles + ` ur ON ur.role_id = rp.role_id
|
|
WHERE ur.user_id = $1
|
|
UNION
|
|
SELECT up.permission_id FROM ` + t.UserPermissions + ` up
|
|
WHERE up.user_id = $1
|
|
) ORDER BY p.slug`,
|
|
|
|
// direct user permissions
|
|
grantPermissionToUser: `INSERT INTO ` + t.UserPermissions + `
|
|
(user_id, permission_id, granted_at) VALUES ($1, $2, $3)
|
|
ON CONFLICT DO NOTHING`,
|
|
revokePermissionFromUser: `DELETE FROM ` + t.UserPermissions + `
|
|
WHERE user_id = $1 AND permission_id = $2`,
|
|
|
|
// abilities
|
|
createAbility: `INSERT INTO ` + t.Abilities + ` (id, slug, label, created_at) VALUES ($1, $2, $3, $4)`,
|
|
getAbilityByID: `SELECT id, slug, label, created_at FROM ` + t.Abilities + ` WHERE id = $1`,
|
|
getAbilityBySlug: `SELECT id, slug, label, created_at FROM ` + t.Abilities + ` WHERE slug = $1`,
|
|
listAbilities: `SELECT id, slug, label, created_at FROM ` + t.Abilities + ` ORDER BY slug`,
|
|
deleteAbility: `DELETE FROM ` + t.Abilities + ` WHERE id = $1`,
|
|
|
|
// migrations
|
|
createMigrationsTable: `CREATE TABLE IF NOT EXISTS ` + t.SchemaMigrations + ` (
|
|
version TEXT PRIMARY KEY,
|
|
applied_at TIMESTAMPTZ NOT NULL
|
|
)`,
|
|
selectAppliedVersions: `SELECT version FROM ` + t.SchemaMigrations,
|
|
}
|
|
}
|
|
|
|
// hasAnyRoleSQL renders the dynamic IN-clause for HasAnyRole. Generated
|
|
// query is parameterized: $1 = user_id, $2..$N+1 = role slugs.
|
|
func (q queries) hasAnyRoleSQL(n int) string {
|
|
if n <= 0 {
|
|
return ""
|
|
}
|
|
var b strings.Builder
|
|
b.Grow(len(q.hasAnyRolePrefix) + 8*n + 4)
|
|
b.WriteString(q.hasAnyRolePrefix)
|
|
for i := 0; i < n; i++ {
|
|
if i > 0 {
|
|
b.WriteByte(',')
|
|
}
|
|
fmt.Fprintf(&b, "$%d", i+2)
|
|
}
|
|
b.WriteString("))")
|
|
return b.String()
|
|
}
|