External Database Setup

If you are using your own PostgreSQL instance (e.g., AWS RDS, Azure Database, or a local installation) instead of the provided Docker Compose setup, follow these steps to ensure the database is correctly configured.

!WARNINGCommunity Supported Only External and managed database configurations are not officially tested or supported by the core application maintainers. Use this guide at your own risk.

1. Database and Users

The application uses two database roles:

  1. SPARKY_FITNESS_DB_USER: Used for migrations and schema management.
  2. SPARKY_FITNESS_APP_DB_USER: Used by the application components with restricted permissions.

Steps to Configure

Run the following as a database superuser:

-- 1. Create the Database
CREATE DATABASE sparkyfitness_db;

-- 2. Create the DB Owner user (referenced in .env as SPARKY_FITNESS_DB_USER)
CREATE USER sparky_admin WITH PASSWORD 'your_secure_password';

-- 3. Grant database ownership
ALTER DATABASE sparkyfitness_db OWNER TO sparky_admin;

-- 4. Grant Role Creation privilege
-- This allows the DB Owner to automatically create the App User during setup
ALTER USER sparky_admin CREATEROLE;

-- 5. Special Note for PostgreSQL 15+
-- Since PG 15, the default 'public' schema permissions have changed.
-- You may need to explicitly grant create permissions to the owner:
GRANT ALL ON SCHEMA public TO sparky_admin;

2. PostgreSQL Extensions

The application requires specific extensions. If your DB Owner user is not a superuser (common in managed environments like AWS RDS), you must pre-install these extensions as an administrator:

\c sparkyfitness_db

-- Standard extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Performance monitoring
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";

-- Grant with grant to functions that extensions add as they are still owned by postgres
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO "sparky_admin" WITH GRANT OPTION;

3. Row Level Security (RLS)

The sparky_admin user must be the Owner of the tables to enable and manage RLS policies. By setting the ALTER DATABASE ... OWNER TO sparky_admin, most systems will automatically make sparky_admin the owner of any tables it creates during migrations.

4. Environment Configuration

In your .env file, ensure you point to your external database:

SPARKY_FITNESS_DB_HOST=your-db-host
SPARKY_FITNESS_DB_NAME=sparkyfitness_db
SPARKY_FITNESS_DB_USER=sparky_admin
SPARKY_FITNESS_DB_PASSWORD=your_secure_password
SPARKY_FITNESS_DB_PORT=5432

!IMPORTANT The application will automatically create the restricted App User (defined in SPARKY_FITNESS_APP_DB_USER) and grant it necessary permissions during the first startup, provided the SPARKY_FITNESS_DB_USER has the CREATEROLE privilege.

5. Security Hardening

For users requiring maximum security, the CREATEROLE privilege is only strictly necessary during the initial installation or when new structural roles are added in application updates.

Revoking CREATEROLE

Once the application has successfully started for the first time and you see the log Successfully created role, you can revoke this privilege:

ALTER USER sparky_admin NOCREATEROLE;

!CAUTIONPotential Issues with NOCREATEROLE If you revoke this privilege, future application updates that require creating new specialized database roles will fail. If you encounter a "Permission Denied" error during a future upgrade, you may need to temporarily re-grant CREATEROLE or manually create the required roles as a superuser.

Table of Contents