425 lines
13 KiB
Go
425 lines
13 KiB
Go
package postgres
|
|
|
|
import (
|
|
"context"
|
|
"crypto/rand"
|
|
"crypto/sha256"
|
|
"database/sql"
|
|
"errors"
|
|
"time"
|
|
|
|
"gardomatic.kleiax.de/internal/auth"
|
|
"gardomatic.kleiax.de/internal/storage"
|
|
"github.com/lib/pq"
|
|
)
|
|
|
|
// UserModel stores user accounts in PostgreSQL.
|
|
// UserModel implements storage.UserModelInterface for PostgreSQL accounts.
|
|
type UserModel struct {
|
|
DB *sql.DB
|
|
}
|
|
|
|
// Insert creates a user account.
|
|
func (m UserModel) Insert(user storage.User) (storage.User, error) {
|
|
query := `
|
|
INSERT INTO users (name, email, password_hash, activated, application_role)
|
|
VALUES ($1, $2, $3, $4, CASE WHEN EXISTS (SELECT 1 FROM users WHERE deleted_at IS NULL) THEN $5 ELSE $6 END)
|
|
RETURNING id, created_at, color, application_role, version`
|
|
|
|
args := []any{user.Name, user.Email, user.Password.Get(), user.Activated, storage.ApplicationRoleUser, storage.ApplicationRoleAdmin}
|
|
|
|
ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
|
|
defer cancel()
|
|
|
|
tx, err := m.DB.BeginTx(ctx, nil)
|
|
if err != nil {
|
|
return storage.User{}, err
|
|
}
|
|
defer tx.Rollback()
|
|
// Serialize registrations until a first administrator exists. Without this,
|
|
// two simultaneous registrations could both observe an empty users table.
|
|
if _, err = tx.ExecContext(ctx, `SELECT pg_advisory_xact_lock(71432026)`); err != nil {
|
|
return storage.User{}, err
|
|
}
|
|
err = tx.QueryRowContext(ctx, query, args...).Scan(&user.ID, &user.CreatedAt, &user.Color, &user.Role, &user.Version)
|
|
if err != nil {
|
|
var pqErr *pq.Error
|
|
switch {
|
|
case errors.As(err, &pqErr) && pqErr.Code == "23505" && pqErr.Constraint == "users_email_key":
|
|
return storage.User{}, storage.ErrDuplicateEmail
|
|
default:
|
|
return storage.User{}, err
|
|
}
|
|
}
|
|
if err = tx.Commit(); err != nil {
|
|
return storage.User{}, err
|
|
}
|
|
if err = m.loadPermissions(&user); err != nil {
|
|
return storage.User{}, err
|
|
}
|
|
|
|
return user, nil
|
|
}
|
|
|
|
// GetByID returns a non-deleted account with resolved application permissions.
|
|
func (m UserModel) GetByID(id int) (storage.User, error) {
|
|
query := `
|
|
SELECT id, created_at, updated_at, name, email, password_hash, activated, color, application_role, version
|
|
FROM users
|
|
WHERE id = $1 AND deleted_at IS NULL`
|
|
|
|
var user storage.User
|
|
var pwHash []byte
|
|
|
|
ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
|
|
defer cancel()
|
|
|
|
err := m.DB.QueryRowContext(ctx, query, id).Scan(
|
|
&user.ID,
|
|
&user.CreatedAt,
|
|
&user.UpdatedAt,
|
|
&user.Name,
|
|
&user.Email,
|
|
&pwHash,
|
|
&user.Activated,
|
|
&user.Color,
|
|
&user.Role,
|
|
&user.Version,
|
|
)
|
|
if err != nil {
|
|
switch {
|
|
case errors.Is(err, sql.ErrNoRows):
|
|
return storage.User{}, storage.ErrRecordNotFound
|
|
default:
|
|
return storage.User{}, err
|
|
}
|
|
}
|
|
|
|
user.Password = *auth.NewPassword(pwHash)
|
|
if err = m.loadPermissions(&user); err != nil {
|
|
return storage.User{}, err
|
|
}
|
|
|
|
return user, nil
|
|
}
|
|
|
|
// GetByEmail returns a non-deleted account by case-insensitive email.
|
|
func (m UserModel) GetByEmail(email string) (storage.User, error) {
|
|
query := `
|
|
SELECT id, created_at, name, email, password_hash, activated, color, application_role, version
|
|
FROM users
|
|
WHERE email = $1 AND deleted_at IS NULL`
|
|
|
|
var user storage.User
|
|
var pwHash []byte
|
|
|
|
ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
|
|
defer cancel()
|
|
|
|
err := m.DB.QueryRowContext(ctx, query, email).Scan(
|
|
&user.ID,
|
|
&user.CreatedAt,
|
|
&user.Name,
|
|
&user.Email,
|
|
&pwHash,
|
|
&user.Activated,
|
|
&user.Color,
|
|
&user.Role,
|
|
&user.Version,
|
|
)
|
|
|
|
if err != nil {
|
|
switch {
|
|
case errors.Is(err, sql.ErrNoRows):
|
|
return storage.User{}, storage.ErrRecordNotFound
|
|
default:
|
|
return storage.User{}, err
|
|
}
|
|
}
|
|
|
|
user.Password = *auth.NewPassword(pwHash)
|
|
if err = m.loadPermissions(&user); err != nil {
|
|
return storage.User{}, err
|
|
}
|
|
|
|
return user, nil
|
|
}
|
|
|
|
// Update changes an account using optimistic locking.
|
|
func (m UserModel) Update(user storage.User) (storage.User, error) {
|
|
query := `
|
|
UPDATE users
|
|
SET name = $1, email = $2, password_hash = $3, activated = $4, color = $5,
|
|
updated_at = CURRENT_TIMESTAMP, version = version + 1
|
|
WHERE id = $6 AND version = $7 AND deleted_at IS NULL
|
|
RETURNING updated_at, version`
|
|
|
|
args := []any{
|
|
user.Name,
|
|
user.Email,
|
|
user.Password.Get(),
|
|
user.Activated,
|
|
user.Color,
|
|
user.ID,
|
|
user.Version,
|
|
}
|
|
|
|
ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
|
|
defer cancel()
|
|
|
|
err := m.DB.QueryRowContext(ctx, query, args...).Scan(&user.UpdatedAt, &user.Version)
|
|
if err != nil {
|
|
var pqErr *pq.Error
|
|
switch {
|
|
case errors.As(err, &pqErr) && pqErr.Code == "23505" && pqErr.Constraint == "users_email_key":
|
|
return storage.User{}, storage.ErrDuplicateEmail
|
|
case errors.Is(err, sql.ErrNoRows):
|
|
return storage.User{}, storage.ErrEditConflict
|
|
default:
|
|
return storage.User{}, err
|
|
}
|
|
}
|
|
|
|
return user, nil
|
|
}
|
|
|
|
// GetForToken resolves a non-expired hashed token to its user.
|
|
func (m UserModel) GetForToken(tokenScope, tokenPlaintext string) (storage.User, error) {
|
|
tokenHash := sha256.Sum256([]byte(tokenPlaintext))
|
|
|
|
query := `
|
|
SELECT users.id, users.created_at, users.name, users.email, users.password_hash, users.activated, users.color, users.application_role, users.version
|
|
FROM users
|
|
INNER JOIN tokens
|
|
ON users.id = tokens.user_id
|
|
WHERE tokens.hash = $1
|
|
AND tokens.scope = $2
|
|
AND tokens.expiry > $3
|
|
AND users.deleted_at IS NULL`
|
|
|
|
args := []any{tokenHash[:], tokenScope, time.Now()}
|
|
|
|
var user storage.User
|
|
var pwHash []byte
|
|
|
|
ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
|
|
defer cancel()
|
|
|
|
err := m.DB.QueryRowContext(ctx, query, args...).Scan(
|
|
&user.ID,
|
|
&user.CreatedAt,
|
|
&user.Name,
|
|
&user.Email,
|
|
&pwHash,
|
|
&user.Activated,
|
|
&user.Color,
|
|
&user.Role,
|
|
&user.Version,
|
|
)
|
|
if err != nil {
|
|
switch {
|
|
case errors.Is(err, sql.ErrNoRows):
|
|
return storage.User{}, storage.ErrRecordNotFound
|
|
default:
|
|
return storage.User{}, err
|
|
}
|
|
}
|
|
|
|
user.Password = *auth.NewPassword(pwHash)
|
|
if err = m.loadPermissions(&user); err != nil {
|
|
return storage.User{}, err
|
|
}
|
|
|
|
return user, nil
|
|
}
|
|
|
|
// GetAll lists non-deleted accounts with resolved application permissions.
|
|
func (m UserModel) GetAll() ([]storage.User, error) {
|
|
ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
|
|
defer cancel()
|
|
rows, err := m.DB.QueryContext(ctx, `SELECT id, created_at, updated_at, name, email, activated, color, application_role, version FROM users WHERE deleted_at IS NULL ORDER BY name, email, id`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
users := []storage.User{}
|
|
for rows.Next() {
|
|
var user storage.User
|
|
if err := rows.Scan(&user.ID, &user.CreatedAt, &user.UpdatedAt, &user.Name, &user.Email, &user.Activated, &user.Color, &user.Role, &user.Version); err != nil {
|
|
return nil, err
|
|
}
|
|
users = append(users, user)
|
|
}
|
|
if err = rows.Err(); err != nil {
|
|
rows.Close()
|
|
return nil, err
|
|
}
|
|
if err = rows.Close(); err != nil {
|
|
return nil, err
|
|
}
|
|
for i := range users {
|
|
if err = m.loadPermissions(&users[i]); err != nil {
|
|
return nil, err
|
|
}
|
|
}
|
|
return users, nil
|
|
}
|
|
|
|
// UpdateRole changes an account's application role.
|
|
func (m UserModel) UpdateRole(userID int, role storage.ApplicationRole) (storage.User, error) {
|
|
ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
|
|
defer cancel()
|
|
result, err := m.DB.ExecContext(ctx, `UPDATE users SET application_role=$1, updated_at=CURRENT_TIMESTAMP, version=version+1 WHERE id=$2 AND deleted_at IS NULL AND EXISTS (SELECT 1 FROM roles WHERE name=$1 AND scope='application')`, role, userID)
|
|
if err != nil {
|
|
return storage.User{}, err
|
|
}
|
|
count, err := result.RowsAffected()
|
|
if err != nil {
|
|
return storage.User{}, err
|
|
}
|
|
if count == 0 {
|
|
return storage.User{}, storage.ErrRecordNotFound
|
|
}
|
|
return m.GetByID(userID)
|
|
}
|
|
|
|
// Delete removes access and personal account data while retaining the user row
|
|
// as an anonymized author for historical garden content.
|
|
// Delete anonymizes an account while preserving authored garden content and
|
|
// removes authentication material and memberships transactionally.
|
|
func (m UserModel) Delete(userID int) error {
|
|
ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
|
|
defer cancel()
|
|
tx, err := m.DB.BeginTx(ctx, nil)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer tx.Rollback()
|
|
|
|
var email string
|
|
err = tx.QueryRowContext(ctx, `SELECT email FROM users WHERE id=$1 AND deleted_at IS NULL FOR UPDATE`, userID).Scan(&email)
|
|
if err != nil {
|
|
return recordError(err)
|
|
}
|
|
rows, err := tx.QueryContext(ctx, `SELECT garden_id FROM garden_members WHERE user_id=$1 ORDER BY garden_id`, userID)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
gardenIDs := []int{}
|
|
for rows.Next() {
|
|
var gardenID int
|
|
if err = rows.Scan(&gardenID); err != nil {
|
|
rows.Close()
|
|
return err
|
|
}
|
|
gardenIDs = append(gardenIDs, gardenID)
|
|
}
|
|
if err = rows.Close(); err != nil {
|
|
return err
|
|
}
|
|
if err = rows.Err(); err != nil {
|
|
return err
|
|
}
|
|
for _, gardenID := range gardenIDs {
|
|
if _, err = tx.ExecContext(ctx, `SELECT pg_advisory_xact_lock($1)`, gardenID); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
var ownsGarden bool
|
|
if err = tx.QueryRowContext(ctx, `SELECT EXISTS (SELECT 1 FROM garden_members WHERE user_id=$1 AND role='owner')`, userID).Scan(&ownsGarden); err != nil {
|
|
return err
|
|
}
|
|
if ownsGarden {
|
|
return storage.ErrConflict
|
|
}
|
|
if _, err = tx.ExecContext(ctx, `DELETE FROM garden_members WHERE user_id=$1`, userID); err != nil {
|
|
return err
|
|
}
|
|
if _, err = tx.ExecContext(ctx, `DELETE FROM garden_invites WHERE invited_by=$1 OR email=$2`, userID, email); err != nil {
|
|
return err
|
|
}
|
|
if _, err = tx.ExecContext(ctx, `DELETE FROM tokens WHERE user_id=$1`, userID); err != nil {
|
|
return err
|
|
}
|
|
if _, err = tx.ExecContext(ctx, `DELETE FROM user_email_changes WHERE user_id=$1`, userID); err != nil {
|
|
return err
|
|
}
|
|
result, err := tx.ExecContext(ctx, `
|
|
UPDATE users SET name='Gelöschter Nutzer', email='deleted-' || id::text || '@invalid',
|
|
activated=false, application_role=$2, deleted_at=now(), updated_at=now(), version=version+1
|
|
WHERE id=$1 AND deleted_at IS NULL`, userID, storage.ApplicationRoleUser)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if count, countErr := result.RowsAffected(); countErr != nil {
|
|
return countErr
|
|
} else if count == 0 {
|
|
return storage.ErrRecordNotFound
|
|
}
|
|
return tx.Commit()
|
|
}
|
|
|
|
func (m UserModel) loadPermissions(user *storage.User) error {
|
|
ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
|
|
defer cancel()
|
|
rows, err := m.DB.QueryContext(ctx, `SELECT permission FROM role_permissions WHERE role_name=$1 ORDER BY permission`, user.Role)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer rows.Close()
|
|
user.Permissions = []storage.ApplicationPermission{}
|
|
for rows.Next() {
|
|
var permission storage.ApplicationPermission
|
|
if err := rows.Scan(&permission); err != nil {
|
|
return err
|
|
}
|
|
user.Permissions = append(user.Permissions, permission)
|
|
}
|
|
return rows.Err()
|
|
}
|
|
|
|
// CreateEmailChange stores a pending address and returns its one-time plaintext token.
|
|
func (m UserModel) CreateEmailChange(userID int, email string, ttl time.Duration) (string, error) {
|
|
plaintext := rand.Text()
|
|
hash := sha256.Sum256([]byte(plaintext))
|
|
ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
|
|
defer cancel()
|
|
_, err := m.DB.ExecContext(ctx, `INSERT INTO user_email_changes (user_id,email,token_hash,expires_at) VALUES ($1,$2,$3,$4) ON CONFLICT (user_id) DO UPDATE SET email=EXCLUDED.email,token_hash=EXCLUDED.token_hash,expires_at=EXCLUDED.expires_at,created_at=now()`, userID, email, hash[:], time.Now().Add(ttl))
|
|
if err != nil {
|
|
var pqErr *pq.Error
|
|
if errors.As(err, &pqErr) && pqErr.Code == "23505" {
|
|
return "", storage.ErrDuplicateEmail
|
|
}
|
|
return "", err
|
|
}
|
|
return plaintext, nil
|
|
}
|
|
|
|
// ConfirmEmailChange consumes a token and applies its pending address atomically.
|
|
func (m UserModel) ConfirmEmailChange(tokenPlaintext string, expectedUserID int) (storage.User, error) {
|
|
hash := sha256.Sum256([]byte(tokenPlaintext))
|
|
ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
|
|
defer cancel()
|
|
tx, err := m.DB.BeginTx(ctx, nil)
|
|
if err != nil {
|
|
return storage.User{}, err
|
|
}
|
|
defer tx.Rollback()
|
|
var userID int
|
|
var email string
|
|
err = tx.QueryRowContext(ctx, `SELECT user_id,email FROM user_email_changes WHERE token_hash=$1 AND user_id=$2 AND expires_at>now() FOR UPDATE`, hash[:], expectedUserID).Scan(&userID, &email)
|
|
if err != nil {
|
|
return storage.User{}, recordError(err)
|
|
}
|
|
_, err = tx.ExecContext(ctx, `UPDATE users SET email=$1,updated_at=now(),version=version+1 WHERE id=$2 AND deleted_at IS NULL`, email, userID)
|
|
if err != nil {
|
|
return storage.User{}, err
|
|
}
|
|
if _, err = tx.ExecContext(ctx, `DELETE FROM user_email_changes WHERE user_id=$1`, userID); err != nil {
|
|
return storage.User{}, err
|
|
}
|
|
if err = tx.Commit(); err != nil {
|
|
return storage.User{}, err
|
|
}
|
|
return m.GetByID(userID)
|
|
}
|