From 4b0271272e983fdb85d4b816cbd91f34d91a7884 Mon Sep 17 00:00:00 2001 From: haturatu Date: Wed, 22 Apr 2026 04:46:24 +0900 Subject: feat(backend): pocketbase auth --- database/database.go | 420 +++++++++++++++++++++++++-------------------------- 1 file changed, 203 insertions(+), 217 deletions(-) (limited to 'database/database.go') diff --git a/database/database.go b/database/database.go index 0e7a4c9..3b7e7c3 100644 --- a/database/database.go +++ b/database/database.go @@ -2,270 +2,256 @@ package database import ( "auth-proxy/logging" - "database/sql" + "bytes" + "context" + "crypto/sha1" + "encoding/hex" + "encoding/json" "fmt" + "io" + "net/http" "net/url" "os" "strings" - - _ "github.com/go-sql-driver/mysql" - _ "github.com/lib/pq" - _ "github.com/mattn/go-sqlite3" + "sync" + "time" ) -var ( - DB *sql.DB - dbType string -) +type Client struct { + baseURL string + collection string + identityField string + emailDomain string + superEmail string + superPassword string + httpClient *http.Client + + mu sync.Mutex + superToken string +} + +type apiListResponse[T any] struct { + Items []T `json:"items"` +} + +type userAuthResponse[T any] struct { + Token string `json:"token"` + Record T `json:"record"` +} + +var PB *Client -// InitDB determines the database type from environment variables and initializes the connection. func InitDB() { - databaseURL := os.Getenv("DATABASE_URL") - if databaseURL != "" { - initFromURL(databaseURL) - } else { - dbType = "sqlite3" - initSQLite() + baseURL := strings.TrimRight(os.Getenv("POCKETBASE_URL"), "/") + if baseURL == "" { + logging.AppLog.Error("POCKETBASE_URL environment variable not set") + os.Exit(1) } - createTables() -} -// initFromURL initializes the database based on the DATABASE_URL. -func initFromURL(databaseURL string) { - u, err := url.Parse(databaseURL) - if err != nil { - logging.AppLog.Error("Invalid DATABASE_URL", "error", err) + superEmail := os.Getenv("POCKETBASE_SUPERUSER_EMAIL") + superPassword := os.Getenv("POCKETBASE_SUPERUSER_PASSWORD") + if superEmail == "" || superPassword == "" { + logging.AppLog.Error("PocketBase superuser credentials are required", "missing_email", superEmail == "", "missing_password", superPassword == "") os.Exit(1) } - driver := u.Scheme - logging.AppLog.Info("Attempting to connect to database", "driver", driver) + collection := os.Getenv("POCKETBASE_COLLECTION") + if collection == "" { + collection = "proxy_users" + } - switch driver { - case "postgres", "postgresql": - dbType = "postgres" - initPostgreSQL(databaseURL) - case "mysql": - dbType = "mysql" + identityField := os.Getenv("POCKETBASE_IDENTITY_FIELD") + if identityField == "" { + identityField = "username" + } - // The mysql driver requires parseTime=true to scan DATE/DATETIME into time.Time - q := u.Query() - if q.Get("parseTime") == "" { - q.Set("parseTime", "true") - } - u.RawQuery = q.Encode() - - // The mysql driver DSN format is user:password@tcp(host:port)/dbname - var dsn string - if u.User != nil { - dsn = fmt.Sprintf("%s@tcp(%s)%s", u.User.String(), u.Host, u.Path) - } else { - dsn = fmt.Sprintf("tcp(%s)%s", u.Host, u.Path) - } - if u.RawQuery != "" { - dsn = fmt.Sprintf("%s?%s", dsn, u.RawQuery) - } - initMySQL(dsn) - default: - logging.AppLog.Error("Unsupported database driver in DATABASE_URL", "driver", driver) - os.Exit(1) + emailDomain := os.Getenv("POCKETBASE_EMAIL_DOMAIN") + if emailDomain == "" { + emailDomain = "pb-auth.local" + } + + PB = &Client{ + baseURL: baseURL, + collection: collection, + identityField: identityField, + emailDomain: emailDomain, + superEmail: superEmail, + superPassword: superPassword, + httpClient: &http.Client{ + Timeout: 15 * time.Second, + }, } + + logging.AppLog.Info("PocketBase client initialized", "url", baseURL, "collection", collection, "identity_field", identityField) } -// initSQLite initializes a SQLite database connection. -func initSQLite() { - databasePath := os.Getenv("DATABASE_PATH") - if databasePath == "" { - databasePath = "./auth.db" +func CloseDB() { + if PB != nil { + logging.AppLog.Info("PocketBase client closed") } +} - var err error - DB, err = sql.Open("sqlite3", databasePath) - if err != nil { - logging.AppLog.Error("Failed to open sqlite database", "error", err) - os.Exit(1) +func (c *Client) Collection() string { + return c.collection +} + +func (c *Client) IdentityField() string { + return c.identityField +} + +func (c *Client) SyntheticEmail(username string) string { + if strings.Contains(username, "@") { + return username } - if err = DB.Ping(); err != nil { - logging.AppLog.Error("Failed to connect to sqlite database", "error", err) - os.Exit(1) + + sum := sha1.Sum([]byte(username)) + return fmt.Sprintf("%s@%s", hex.EncodeToString(sum[:8]), c.emailDomain) +} + +func (c *Client) SuperuserAuth(ctx context.Context) (string, error) { + c.mu.Lock() + token := c.superToken + c.mu.Unlock() + + if token != "" { + return token, nil } - logging.AppLog.Info("Database connection established", "type", "sqlite", "path", databasePath) + + return c.refreshSuperuserToken(ctx) } -// initPostgreSQL initializes a PostgreSQL database connection. -func initPostgreSQL(dataSourceName string) { - var err error - DB, err = sql.Open("postgres", dataSourceName) +func (c *Client) refreshSuperuserToken(ctx context.Context) (string, error) { + var resp userAuthResponse[map[string]any] + err := c.doJSON(ctx, http.MethodPost, "/api/collections/_superusers/auth-with-password", "", map[string]string{ + "identity": c.superEmail, + "password": c.superPassword, + }, nil, &resp) if err != nil { - logging.AppLog.Error("Failed to open postgres database", "error", err) - os.Exit(1) - } - if err = DB.Ping(); err != nil { - logging.AppLog.Error("Failed to connect to postgres database", "error", err) - os.Exit(1) + return "", err } - logging.AppLog.Info("Database connection established", "type", "postgres") + + c.mu.Lock() + c.superToken = resp.Token + c.mu.Unlock() + + return resp.Token, nil } -// initMySQL initializes a MySQL database connection. -func initMySQL(dataSourceName string) { - var err error - DB, err = sql.Open("mysql", dataSourceName) +func (c *Client) DoWithSuperuser(ctx context.Context, method, path string, query url.Values, body any, out any) error { + token, err := c.SuperuserAuth(ctx) if err != nil { - logging.AppLog.Error("Failed to open mysql database", "error", err) - os.Exit(1) + return err } - if err = DB.Ping(); err != nil { - logging.AppLog.Error("Failed to connect to mysql database", "error", err) - os.Exit(1) + + err = c.doJSON(ctx, method, path, token, body, query, out) + if err == nil { + return nil } - logging.AppLog.Info("Database connection established", "type", "mysql") + + if !IsUnauthorized(err) { + return err + } + + token, refreshErr := c.refreshSuperuserToken(ctx) + if refreshErr != nil { + return refreshErr + } + + return c.doJSON(ctx, method, path, token, body, query, out) } -// createTables creates the necessary tables if they don't exist, using syntax appropriate for the current dbType. -func createTables() { - var createTableSQL string - switch dbType { - case "sqlite3": - createTableSQL = `CREATE TABLE IF NOT EXISTS users ( - "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - "username" TEXT NOT NULL UNIQUE, - "password_hash" TEXT NOT NULL, - "role" TEXT NOT NULL, - "is_active" BOOLEAN NOT NULL DEFAULT TRUE, - "failed_logins" INTEGER NOT NULL DEFAULT 0, - "last_login_at" DATETIME, - "created_at" DATETIME DEFAULT CURRENT_TIMESTAMP, - "updated_at" DATETIME DEFAULT CURRENT_TIMESTAMP - );` - case "postgres": - createTableSQL = `CREATE TABLE IF NOT EXISTS users ( - id SERIAL PRIMARY KEY, - username VARCHAR(255) NOT NULL UNIQUE, - password_hash VARCHAR(255) NOT NULL, - role VARCHAR(50) NOT NULL, - is_active BOOLEAN NOT NULL DEFAULT TRUE, - failed_logins INT NOT NULL DEFAULT 0, - last_login_at TIMESTAMP WITH TIME ZONE, - created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, - updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP - );` - case "mysql": - createTableSQL = `CREATE TABLE IF NOT EXISTS users ( - id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, - username VARCHAR(255) NOT NULL UNIQUE, - password_hash VARCHAR(255) NOT NULL, - role VARCHAR(50) NOT NULL, - is_active BOOLEAN NOT NULL DEFAULT TRUE, - failed_logins INT NOT NULL DEFAULT 0, - last_login_at DATETIME, - created_at DATETIME DEFAULT CURRENT_TIMESTAMP, - updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP - );` +func (c *Client) Do(ctx context.Context, method, path, bearerToken string, body any, out any) error { + return c.doJSON(ctx, method, path, bearerToken, body, nil, out) +} + +type APIError struct { + StatusCode int + Message string + Details map[string]any +} + +func (e *APIError) Error() string { + if e.Message != "" { + return e.Message } + return fmt.Sprintf("pocketbase request failed with status %d", e.StatusCode) +} - if _, err := DB.Exec(createTableSQL); err != nil { - logging.AppLog.Error("Failed to create users table", "error", err, "db_type", dbType) - os.Exit(1) +func IsUnauthorized(err error) bool { + apiErr, ok := err.(*APIError) + return ok && apiErr.StatusCode == http.StatusUnauthorized +} + +func (c *Client) doJSON(ctx context.Context, method, path, bearerToken string, body any, query url.Values, out any) error { + if c == nil { + return fmt.Errorf("pocketbase client is not initialized") + } + + var reader io.Reader + if body != nil { + payload, err := json.Marshal(body) + if err != nil { + return err + } + reader = bytes.NewReader(payload) } - logging.AppLog.Info("Users table verified successfully") - - // Now, create the auth_tokens table - var createTokenTableSQL string - switch dbType { - case "sqlite3": - createTokenTableSQL = `CREATE TABLE IF NOT EXISTS auth_tokens ( - "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - "user_id" INTEGER NOT NULL UNIQUE, - "token" TEXT NOT NULL UNIQUE, - "expires_at" DATETIME NOT NULL, - FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE - );` - case "postgres": - createTokenTableSQL = `CREATE TABLE IF NOT EXISTS auth_tokens ( - id SERIAL PRIMARY KEY, - user_id INTEGER NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE, - token VARCHAR(255) NOT NULL UNIQUE, - expires_at TIMESTAMP WITH TIME ZONE NOT NULL - );` - case "mysql": - createTokenTableSQL = `CREATE TABLE IF NOT EXISTS auth_tokens ( - id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, - user_id INT NOT NULL UNIQUE, - token VARCHAR(255) NOT NULL UNIQUE, - expires_at DATETIME NOT NULL, - FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE - );` + + reqURL := c.baseURL + path + if len(query) > 0 { + reqURL += "?" + query.Encode() } - if _, err := DB.Exec(createTokenTableSQL); err != nil { - logging.AppLog.Error("Failed to create auth_tokens table", "error", err, "db_type", dbType) - os.Exit(1) + req, err := http.NewRequestWithContext(ctx, method, reqURL, reader) + if err != nil { + return err } - logging.AppLog.Info("Auth_tokens table verified successfully") - - // Now, create the refresh_tokens table - var createRefreshTokenTableSQL string - switch dbType { - case "sqlite3": - createRefreshTokenTableSQL = `CREATE TABLE IF NOT EXISTS refresh_tokens ( - "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - "user_id" INTEGER NOT NULL UNIQUE, - "token" TEXT NOT NULL UNIQUE, - "expires_at" DATETIME NOT NULL, - "is_revoked" BOOLEAN NOT NULL DEFAULT FALSE, - FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE - );` - case "postgres": - createRefreshTokenTableSQL = `CREATE TABLE IF NOT EXISTS refresh_tokens ( - id SERIAL PRIMARY KEY, - user_id INTEGER NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE, - token VARCHAR(255) NOT NULL UNIQUE, - expires_at TIMESTAMP WITH TIME ZONE NOT NULL, - is_revoked BOOLEAN NOT NULL DEFAULT FALSE - );` - case "mysql": - createRefreshTokenTableSQL = `CREATE TABLE IF NOT EXISTS refresh_tokens ( - id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, - user_id INT NOT NULL UNIQUE, - token VARCHAR(255) NOT NULL UNIQUE, - expires_at DATETIME NOT NULL, - is_revoked BOOLEAN NOT NULL DEFAULT FALSE, - FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE - );` + + if body != nil { + req.Header.Set("Content-Type", "application/json") + } + if bearerToken != "" { + req.Header.Set("Authorization", bearerToken) } - if _, err := DB.Exec(createRefreshTokenTableSQL); err != nil { - logging.AppLog.Error("Failed to create refresh_tokens table", "error", err, "db_type", dbType) - os.Exit(1) + resp, err := c.httpClient.Do(req) + if err != nil { + return err } - logging.AppLog.Info("Refresh_tokens table verified successfully") + defer resp.Body.Close() //nolint:errcheck -} + if resp.StatusCode >= 400 { + return decodeAPIError(resp) + } -// Rebind converts a query with '_?_' placeholders to the database-specific format. -func Rebind(query string) string { - if dbType == "postgres" { - parts := strings.Split(query, "?") - var result strings.Builder - for i, part := range parts { - if i > 0 { - result.WriteString(fmt.Sprintf("$%d", i)) - } - result.WriteString(part) - } - return result.String() + if out == nil { + io.Copy(io.Discard, resp.Body) //nolint:errcheck + return nil } - // sqlite3 and mysql both use '?' - return query + + return json.NewDecoder(resp.Body).Decode(out) } -// CloseDB closes the database connection. -func CloseDB() { - if DB != nil { - if err := DB.Close(); err != nil { - logging.AppLog.Error("Failed to close database connection", "error", err) +func decodeAPIError(resp *http.Response) error { + payload := struct { + Message string `json:"message"` + Data map[string]any `json:"data"` + }{} + + if err := json.NewDecoder(resp.Body).Decode(&payload); err != nil { + return &APIError{ + StatusCode: resp.StatusCode, + Message: resp.Status, } - logging.AppLog.Info("Database connection closed") } + + return &APIError{ + StatusCode: resp.StatusCode, + Message: payload.Message, + Details: payload.Data, + } +} + +func QuoteFilterValue(value string) string { + replacer := strings.NewReplacer("\\", "\\\\", "\"", "\\\"") + return "\"" + replacer.Replace(value) + "\"" } -- cgit v1.2.3