WeiCHEN
6d3efdf4d7
Add a login-visible user directory API and admin/self profile update paths so project duty selectors stop falling back to "用户 N", and so users can persist display fields with an admin-edit tip.
483 lines
13 KiB
Go
483 lines
13 KiB
Go
package auth
|
||
|
||
import (
|
||
"context"
|
||
"encoding/json"
|
||
"errors"
|
||
"fmt"
|
||
"strings"
|
||
"time"
|
||
|
||
"chat-agent-system/internal/config"
|
||
"github.com/jackc/pgx/v5"
|
||
"github.com/jackc/pgx/v5/pgxpool"
|
||
)
|
||
|
||
// PgStore PostgreSQL 实现的账户、凭证与会话存储。
|
||
type PgStore struct {
|
||
pool *pgxpool.Pool
|
||
cfg *config.Config
|
||
}
|
||
|
||
// NewPgStore 构造 PG AuthStore；pool 必填且 IAM 表已迁移。
|
||
func NewPgStore(pool *pgxpool.Pool, cfg *config.Config) (*PgStore, error) {
|
||
if pool == nil {
|
||
return nil, errors.New("pg auth store: pool required")
|
||
}
|
||
return &PgStore{pool: pool, cfg: cfg}, nil
|
||
}
|
||
|
||
// IAMTablesReady 检查 Phase 1 IAM 表是否可用。
|
||
func IAMTablesReady(ctx context.Context, pool *pgxpool.Pool) bool {
|
||
if pool == nil {
|
||
return false
|
||
}
|
||
var ok bool
|
||
err := pool.QueryRow(ctx, `
|
||
SELECT EXISTS (
|
||
SELECT 1 FROM information_schema.tables
|
||
WHERE table_schema = 'public' AND table_name = 'user_credentials'
|
||
)
|
||
`).Scan(&ok)
|
||
return err == nil && ok
|
||
}
|
||
|
||
func (s *PgStore) scanUser(row pgx.Row) (*UserAccount, error) {
|
||
var u UserAccount
|
||
var passwordHash string
|
||
var createdAt, updatedAt time.Time
|
||
var profileUpdatedBy *int64
|
||
var profileUpdatedAt *time.Time
|
||
err := row.Scan(
|
||
&u.ID,
|
||
&u.Username,
|
||
&u.Name,
|
||
&passwordHash,
|
||
&u.Status,
|
||
&u.MustChangePassword,
|
||
&u.RoleID,
|
||
&u.Position,
|
||
&u.Department,
|
||
&u.EmployeeID,
|
||
&u.Email,
|
||
&profileUpdatedBy,
|
||
&profileUpdatedAt,
|
||
&createdAt,
|
||
&updatedAt,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
u.PasswordHash = passwordHash
|
||
if profileUpdatedBy != nil {
|
||
u.ProfileUpdatedBy = *profileUpdatedBy
|
||
}
|
||
if profileUpdatedAt != nil {
|
||
u.ProfileUpdatedAt = profileUpdatedAt.Format("2006-01-02 15:04:05")
|
||
}
|
||
u.CreatedAt = createdAt.Format("2006-01-02 15:04:05")
|
||
u.UpdatedAt = updatedAt.Format("2006-01-02 15:04:05")
|
||
return &u, nil
|
||
}
|
||
|
||
const userSelectSQL = `
|
||
SELECT u.id,
|
||
COALESCE(NULLIF(u.username, ''), u.account_id) AS username,
|
||
u.name,
|
||
COALESCE(uc.password_hash, '') AS password_hash,
|
||
u.status,
|
||
u.must_change_password,
|
||
u.role_id,
|
||
u.position,
|
||
u.department,
|
||
u.employee_id,
|
||
u.email,
|
||
u.profile_updated_by,
|
||
u.profile_updated_at,
|
||
u.created_at,
|
||
u.updated_at
|
||
FROM users u
|
||
LEFT JOIN user_credentials uc ON uc.user_id = u.id
|
||
`
|
||
|
||
func (s *PgStore) GetUserByUsername(ctx context.Context, username string) (*UserAccount, error) {
|
||
username = strings.TrimSpace(username)
|
||
row := s.pool.QueryRow(ctx, userSelectSQL+`
|
||
WHERE lower(COALESCE(NULLIF(u.username, ''), u.account_id)) = lower($1)
|
||
LIMIT 1
|
||
`, username)
|
||
u, err := s.scanUser(row)
|
||
if err != nil {
|
||
if errors.Is(err, pgx.ErrNoRows) {
|
||
return nil, fmt.Errorf("user not found: %s", username)
|
||
}
|
||
return nil, err
|
||
}
|
||
return u, nil
|
||
}
|
||
|
||
func (s *PgStore) GetUserByID(ctx context.Context, id int64) (*UserAccount, error) {
|
||
row := s.pool.QueryRow(ctx, userSelectSQL+` WHERE u.id = $1`, id)
|
||
u, err := s.scanUser(row)
|
||
if err != nil {
|
||
if errors.Is(err, pgx.ErrNoRows) {
|
||
return nil, fmt.Errorf("user not found: %d", id)
|
||
}
|
||
return nil, err
|
||
}
|
||
return u, nil
|
||
}
|
||
|
||
func (s *PgStore) CreateUser(ctx context.Context, user *UserAccount) (*UserAccount, error) {
|
||
if user == nil || strings.TrimSpace(user.Username) == "" {
|
||
return nil, errors.New("username required")
|
||
}
|
||
tx, err := s.pool.Begin(ctx)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer tx.Rollback(ctx)
|
||
|
||
roleLegacy := "engineer"
|
||
switch user.RoleID {
|
||
case "role-director":
|
||
roleLegacy = "director"
|
||
case "role-tech-lead":
|
||
roleLegacy = "regulatory"
|
||
case "role-guest":
|
||
roleLegacy = "sales"
|
||
}
|
||
|
||
var id int64
|
||
err = tx.QueryRow(ctx, `
|
||
INSERT INTO users (
|
||
account_id, name, role, username, status, must_change_password,
|
||
role_id, position, department, employee_id, email
|
||
) VALUES ($1, $2, $3, $1, $4, $5, $6, $7, $8, $9, $10)
|
||
RETURNING id
|
||
`, user.Username, user.Name, roleLegacy, user.Status, user.MustChangePassword,
|
||
user.RoleID, user.Position, user.Department, user.EmployeeID, user.Email).Scan(&id)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("insert user: %w", err)
|
||
}
|
||
|
||
if user.PasswordHash != "" {
|
||
_, err = tx.Exec(ctx, `
|
||
INSERT INTO user_credentials (user_id, password_hash)
|
||
VALUES ($1, $2)
|
||
`, id, user.PasswordHash)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("insert credentials: %w", err)
|
||
}
|
||
}
|
||
|
||
if user.RoleID != "" {
|
||
if _, err = tx.Exec(ctx, `
|
||
INSERT INTO user_role_bindings (user_id, role_id, scope_type, scope_id, assigned_by)
|
||
VALUES ($1, $2, 'system', 0, $1)
|
||
ON CONFLICT DO NOTHING
|
||
`, id, user.RoleID); err != nil {
|
||
return nil, fmt.Errorf("insert role binding: %w", err)
|
||
}
|
||
}
|
||
|
||
if err := tx.Commit(ctx); err != nil {
|
||
return nil, err
|
||
}
|
||
return s.GetUserByID(ctx, id)
|
||
}
|
||
|
||
func (s *PgStore) UpdateUser(ctx context.Context, user *UserAccount, actorID int64) (*UserAccount, error) {
|
||
_, err := s.pool.Exec(ctx, `
|
||
UPDATE users SET
|
||
name = $2,
|
||
department = $3,
|
||
employee_id = $4,
|
||
email = $5,
|
||
profile_updated_by = CASE WHEN $6::bigint = $1 THEN NULL ELSE NULLIF($6, 0) END,
|
||
profile_updated_at = CASE WHEN $6::bigint = $1 OR $6 = 0 THEN NULL ELSE now() END,
|
||
updated_at = now()
|
||
WHERE id = $1
|
||
`, user.ID, user.Name, user.Department, user.EmployeeID, user.Email, actorID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
return s.GetUserByID(ctx, user.ID)
|
||
}
|
||
|
||
func (s *PgStore) ListActiveUserDirectory(ctx context.Context) ([]UserDirectoryEntry, error) {
|
||
rows, err := s.pool.Query(ctx, `
|
||
SELECT u.id,
|
||
COALESCE(NULLIF(u.username, ''), u.account_id) AS username,
|
||
u.name,
|
||
u.department,
|
||
u.position
|
||
FROM users u
|
||
WHERE u.status = 'active'
|
||
ORDER BY u.name ASC, u.id ASC
|
||
`)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
out := make([]UserDirectoryEntry, 0)
|
||
for rows.Next() {
|
||
var e UserDirectoryEntry
|
||
if err := rows.Scan(&e.ID, &e.Username, &e.Name, &e.Department, &e.Position); err != nil {
|
||
return nil, err
|
||
}
|
||
out = append(out, e)
|
||
}
|
||
return out, rows.Err()
|
||
}
|
||
|
||
func (s *PgStore) UpdateUserRole(ctx context.Context, id int64, roleID, position string) (*UserAccount, error) {
|
||
tx, err := s.pool.Begin(ctx)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer tx.Rollback(ctx)
|
||
|
||
_, err = tx.Exec(ctx, `
|
||
UPDATE users SET role_id = $2, position = $3, updated_at = now() WHERE id = $1
|
||
`, id, roleID, position)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
_, err = tx.Exec(ctx, `
|
||
DELETE FROM user_role_bindings
|
||
WHERE user_id = $1 AND scope_type = 'system' AND scope_id = 0 AND role_id <> $2
|
||
`, id, roleID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
_, err = tx.Exec(ctx, `
|
||
INSERT INTO user_role_bindings (user_id, role_id, scope_type, scope_id, assigned_by)
|
||
VALUES ($1, $2, 'system', 0, $1)
|
||
ON CONFLICT DO NOTHING
|
||
`, id, roleID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
if err := tx.Commit(ctx); err != nil {
|
||
return nil, err
|
||
}
|
||
return s.GetUserByID(ctx, id)
|
||
}
|
||
|
||
func (s *PgStore) UpdateUserStatus(ctx context.Context, id int64, status AccountStatus) (*UserAccount, error) {
|
||
_, err := s.pool.Exec(ctx, `
|
||
UPDATE users SET status = $2, updated_at = now() WHERE id = $1
|
||
`, id, status)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if status == StatusDisabled {
|
||
_ = s.RevokeAllForUser(ctx, id)
|
||
}
|
||
return s.GetUserByID(ctx, id)
|
||
}
|
||
|
||
func (s *PgStore) UpdateUserPassword(ctx context.Context, id int64, passwordHash string, mustChangePassword bool) (*UserAccount, error) {
|
||
tx, err := s.pool.Begin(ctx)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer tx.Rollback(ctx)
|
||
|
||
_, err = tx.Exec(ctx, `
|
||
INSERT INTO user_credentials (user_id, password_hash, password_changed_at, failed_count, locked_until)
|
||
VALUES ($1, $2, now(), 0, NULL)
|
||
ON CONFLICT (user_id) DO UPDATE SET
|
||
password_hash = EXCLUDED.password_hash,
|
||
password_changed_at = now(),
|
||
failed_count = 0,
|
||
locked_until = NULL
|
||
`, id, passwordHash)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
_, err = tx.Exec(ctx, `
|
||
UPDATE users SET must_change_password = $2, updated_at = now() WHERE id = $1
|
||
`, id, mustChangePassword)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
if err := tx.Commit(ctx); err != nil {
|
||
return nil, err
|
||
}
|
||
return s.GetUserByID(ctx, id)
|
||
}
|
||
|
||
func (s *PgStore) ListUsers(ctx context.Context) ([]*UserAccount, error) {
|
||
rows, err := s.pool.Query(ctx, userSelectSQL+` ORDER BY u.id ASC`)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
list := make([]*UserAccount, 0)
|
||
for rows.Next() {
|
||
u, err := s.scanUser(rows)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
u.PasswordHash = ""
|
||
list = append(list, u)
|
||
}
|
||
return list, rows.Err()
|
||
}
|
||
|
||
func (s *PgStore) GetConfig(ctx context.Context) (SystemAuthConfig, error) {
|
||
var raw []byte
|
||
err := s.pool.QueryRow(ctx, `
|
||
SELECT config_value FROM iam_system_config WHERE config_key = 'auth'
|
||
`).Scan(&raw)
|
||
if err != nil {
|
||
if errors.Is(err, pgx.ErrNoRows) {
|
||
return SystemAuthConfig{AllowRegistration: true, RequireApproval: false}, nil
|
||
}
|
||
return SystemAuthConfig{}, err
|
||
}
|
||
var cfg SystemAuthConfig
|
||
if err := json.Unmarshal(raw, &cfg); err != nil {
|
||
return SystemAuthConfig{}, err
|
||
}
|
||
return cfg, nil
|
||
}
|
||
|
||
func (s *PgStore) UpdateConfig(ctx context.Context, cfg SystemAuthConfig) error {
|
||
raw, err := json.Marshal(cfg)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
_, err = s.pool.Exec(ctx, `
|
||
INSERT INTO iam_system_config (config_key, config_value, updated_at)
|
||
VALUES ('auth', $1, now())
|
||
ON CONFLICT (config_key) DO UPDATE SET config_value = EXCLUDED.config_value, updated_at = now()
|
||
`, raw)
|
||
return err
|
||
}
|
||
|
||
func (s *PgStore) CreateSession(ctx context.Context, userID int64, tokenHash string, meta SessionMeta) error {
|
||
now := time.Now()
|
||
_, err := s.pool.Exec(ctx, `
|
||
INSERT INTO auth_sessions (
|
||
token_hash, user_id, created_at, last_seen_at, idle_expires_at, absolute_expires_at, ip_hash, user_agent
|
||
) VALUES ($1, $2, $3, $3, $4, $5, $6, $7)
|
||
`, tokenHash, userID, now, now.Add(defaultIdleSessionTTL), now.Add(defaultAbsoluteSessionTTL), meta.IPHash, meta.UserAgent)
|
||
return err
|
||
}
|
||
|
||
func (s *PgStore) ResolveUserID(ctx context.Context, tokenHash string) (int64, error) {
|
||
var userID int64
|
||
var idleExp, absExp time.Time
|
||
var revokedAt *time.Time
|
||
var status AccountStatus
|
||
err := s.pool.QueryRow(ctx, `
|
||
SELECT s.user_id, s.idle_expires_at, s.absolute_expires_at, s.revoked_at, u.status
|
||
FROM auth_sessions s
|
||
JOIN users u ON u.id = s.user_id
|
||
WHERE s.token_hash = $1
|
||
`, tokenHash).Scan(&userID, &idleExp, &absExp, &revokedAt, &status)
|
||
if err != nil {
|
||
if errors.Is(err, pgx.ErrNoRows) {
|
||
return 0, errSessionNotFound
|
||
}
|
||
return 0, err
|
||
}
|
||
if revokedAt != nil {
|
||
return 0, errSessionNotFound
|
||
}
|
||
now := time.Now()
|
||
if now.After(idleExp) || now.After(absExp) {
|
||
return 0, errSessionNotFound
|
||
}
|
||
if status == StatusDisabled || status == StatusPendingApproval {
|
||
return 0, errSessionNotFound
|
||
}
|
||
|
||
_, _ = s.pool.Exec(ctx, `
|
||
UPDATE auth_sessions SET last_seen_at = now(), idle_expires_at = $2 WHERE token_hash = $1
|
||
`, tokenHash, now.Add(defaultIdleSessionTTL))
|
||
return userID, nil
|
||
}
|
||
|
||
func (s *PgStore) TouchSession(ctx context.Context, tokenHash string) error {
|
||
_, err := s.pool.Exec(ctx, `
|
||
UPDATE auth_sessions SET last_seen_at = now(), idle_expires_at = $2
|
||
WHERE token_hash = $1 AND revoked_at IS NULL
|
||
`, tokenHash, time.Now().Add(defaultIdleSessionTTL))
|
||
return err
|
||
}
|
||
|
||
func (s *PgStore) RevokeByTokenHash(ctx context.Context, tokenHash string) error {
|
||
_, err := s.pool.Exec(ctx, `
|
||
UPDATE auth_sessions SET revoked_at = now() WHERE token_hash = $1 AND revoked_at IS NULL
|
||
`, tokenHash)
|
||
return err
|
||
}
|
||
|
||
func (s *PgStore) RevokeAllForUser(ctx context.Context, userID int64) error {
|
||
_, err := s.pool.Exec(ctx, `
|
||
UPDATE auth_sessions SET revoked_at = now() WHERE user_id = $1 AND revoked_at IS NULL
|
||
`, userID)
|
||
return err
|
||
}
|
||
|
||
func (s *PgStore) ListActiveSessions(ctx context.Context, userID int64, currentTokenHash string) ([]SessionRecord, error) {
|
||
var currentID string
|
||
if currentTokenHash != "" {
|
||
_ = s.pool.QueryRow(ctx, `
|
||
SELECT id::text FROM auth_sessions
|
||
WHERE token_hash = $1 AND user_id = $2 AND revoked_at IS NULL
|
||
`, currentTokenHash, userID).Scan(¤tID)
|
||
}
|
||
rows, err := s.pool.Query(ctx, `
|
||
SELECT id::text, created_at, last_seen_at, user_agent
|
||
FROM auth_sessions
|
||
WHERE user_id = $1
|
||
AND revoked_at IS NULL
|
||
AND idle_expires_at > now()
|
||
AND absolute_expires_at > now()
|
||
ORDER BY last_seen_at DESC
|
||
`, userID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
out := make([]SessionRecord, 0)
|
||
for rows.Next() {
|
||
var rec SessionRecord
|
||
var createdAt, lastSeenAt time.Time
|
||
if err := rows.Scan(&rec.ID, &createdAt, &lastSeenAt, &rec.UserAgent); err != nil {
|
||
return nil, err
|
||
}
|
||
rec.CreatedAt = createdAt.Format("2006-01-02 15:04:05")
|
||
rec.LastSeenAt = lastSeenAt.Format("2006-01-02 15:04:05")
|
||
rec.IsCurrent = currentID != "" && rec.ID == currentID
|
||
out = append(out, rec)
|
||
}
|
||
return out, rows.Err()
|
||
}
|
||
|
||
func (s *PgStore) RevokeSessionByID(ctx context.Context, userID int64, sessionID string) error {
|
||
tag, err := s.pool.Exec(ctx, `
|
||
UPDATE auth_sessions SET revoked_at = now()
|
||
WHERE id = $1::uuid AND user_id = $2 AND revoked_at IS NULL
|
||
`, sessionID, userID)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
if tag.RowsAffected() == 0 {
|
||
return errSessionNotFound
|
||
}
|
||
return nil
|
||
}
|