Integrate PostgreSQL and Bootstrap Initial Roles/Admin User (SZ-3)
rk@tigase.net opened 3 weeks ago

🐘 Integrate PostgreSQL and Bootstrap Initial Roles/Admin User

Goal:
Integrate PostgreSQL as the primary database for Sztab and initialize system roles and a default admin user at application startup.

Estimated Time: 4–5 hours


🧩 Task Breakdown

1. PostgreSQL Integration
  •  Add PostgreSQL JDBC driver to pom.xml
  •  Configure application.yml with:
    • spring.datasource.url
    • spring.datasource.username/password
    • spring.jpa.hibernate.ddl-auto
  •  Enable support for uuid primary keys if needed
2. Schema Generation & Validation
  •  Run schema auto-generation against PostgreSQL
  •  Confirm entity mappings behave as expected
  •  Verify enum handling (EnumType.STRING) and timestamps
3. Bootstrap Roles and Default Admin
  •  Create data.sql or use @PostConstruct or ApplicationRunner
  •  Insert system roles (ADMIN, DEVELOPER, etc.)
  •  Create default admin user assigned to ADMIN role
  •  Validate password hashing via PasswordEncoder
4. Secure Default Admin Password
  •  Make admin password configurable via application.yml or env variable
  •  Add fallback default (admin123) only in dev
  •  Mask password value in logs
5. Test Against Local PostgreSQL
  •  Run the app against local or Dockerized PostgreSQL
  •  Verify login flow and role-based access to REST endpoints

📦 Output Artifacts

  • PostgreSQL running as primary DB for Sztab
  • Admin user and roles initialized on startup
  • Secure config-based password management

Notes:
If we need Docker Compose for dev, I will include PostgreSQL container setup in docker-compose.yml.

  • rk@tigase.net changed state to 'In Progress' 3 weeks ago
    Previous Value Current Value
    Open
    In Progress
  • rk@tigase.net commented 3 weeks ago

    Work log: Using branch "feature/sz-3-postgresql-role-bootstrap"

    % git branch --show-current feature/sz-3-postgresql-role-bootstrap %

  • rk@tigase.net commented 3 weeks ago

    All changes for SZ-3 have been merged into wolnosc. See the attached feature-sz-3-postgresql-bootstrap.md for setup and verification instructions. (Originally intended as a PR but was merged directly.)

  • rk@tigase.net changed state to 'Closed' 3 weeks ago
    Previous Value Current Value
    In Progress
    Closed
  • rk@tigase.net commented 3 weeks ago

    Feature Walkthrough: PostgreSQL Integration and Initial Role/Admin Bootstrap

    This guide walks through how to verify that PostgreSQL integration is working correctly and that system roles (e.g. ADMIN) and a default admin user are bootstrapped properly at application startup.


    Prerequisites

    You’ll need the following installed on your system:

    • Java 17 or later
    • Maven
    • Docker
    • PostgreSQL not already running on port 5432 (or adjust port)

    1. Start PostgreSQL via Docker

    We’re using PostgreSQL 16 in a local container. Run the following command:

    docker run --rm \
      --name sztab-postgres \
      -e POSTGRES_USER=sztab_user \
      -e POSTGRES_PASSWORD=sztab_pass \
      -e POSTGRES_DB=sztab \
      -p 5432:5432 \
      postgres:16
    

    Leave this terminal open, or add -d if you prefer to daemonize it.


    2. Activate the postgresql Spring profile

    The application must use the application-postgresql.yml configuration file. You can activate the profile in one of two ways:

    Option A: Export the profile as an environment variable

    export SPRING_PROFILES_ACTIVE=postgresql
    

    Option B: Pass it directly when running with Maven

    mvn spring-boot:run -Dspring-boot.run.profiles=postgresql
    

    3. Build and Run the Application

    Use either spring-boot:run or a JAR run:

    mvn clean package
    java -jar target/sztab.jar
    

    Or:

    mvn spring-boot:run
    

    The application will connect to PostgreSQL and auto-create schema based on your entity mappings.


    4. Bootstrap Logic: Roles and Admin User

    On startup, the app initializes:

    • System roles: ADMIN, DEVELOPER, etc.
    • Default admin user:
      • Email: admin@sztab.local
      • Password: configured via environment or defaults to admin123 (dev only)

    To override the admin password:

    export SZTAB_ADMIN_PASSWORD=yourSecurePassword
    

    The log should indicate successful role/user creation.


    5. Verify Bootstrap via psql

    If you want to verify the bootstrapped data manually:

    psql -U sztab_user -d sztab -h localhost
    

    Then check:

    SELECT * FROM roles;
    SELECT * FROM users;
    SELECT * FROM users_roles;
    

    You should see entries for roles and the admin@sztab.local user.


    6. Admin Login

    If your login/authentication endpoint is already exposed, try logging in using the admin credentials:

    • Username: admin@sztab.local
    • Password: from SZTAB_ADMIN_PASSWORD or default (admin123)

    If authentication isn’t yet wired up, you can still verify the user creation via DB.


    7. Troubleshooting

    • App fails to start with No data.sql: ensure your application.yml or profile doesn’t enable spring.sql.init.mode.
    • Admin user not created: verify logs, environment variables, and DB contents.
    • Tables missing: make sure Hibernate DDL auto is enabled (ddl-auto=create) under the postgresql profile.

    Summary

    This feature confirms that:

    • PostgreSQL is integrated and active for the postgresql profile.
    • Entity schema is created automatically via JPA on app startup.
    • System roles are created in the roles table.
    • A default admin user is inserted and assigned the ADMIN role.
    • The admin password is configurable and defaults safely in development.
  • rk@tigase.net commented 3 weeks ago

    🕒 Actual Work Log: SZ‑3 – PostgreSQL Integration & Admin Bootstrap

    Total Time: 6h 30m

    Breakdown:

    • ✅ PostgreSQL integration and YAML config: 1h
    • ✅ Bootstrap logic (DataBootstrapper, roles, admin): 2h
    • ✅ Secure admin password handling (AdminProperties, fallback): 45m
    • ✅ H2 test profile + separation from production config: 45m
    • ✅ Validation and manual testing with Docker Postgres: 45m
    • ✅ Docs: Setup + usage guide (feature-sz-3-postgresql-bootstrap.md): 1h 15m
  • rk@tigase.net commented 2 weeks ago

    The feature branch feature/sz-3-postgresql-role-bootstrap was successfully merged into wolnosc, verified as merged via git branch --merged, and safely deleted both locally and remotely:

    % git branch -d feature/sz-3-postgresql-role-bootstrap
    % git push origin --delete feature/sz-3-postgresql-role-bootstrap
    

    No uncommitted changes were present. This completes cleanup for SZ-3.

issue 1 of 1
Type
New Feature
Priority
Normal
Assignee
Version
none
Sprints
n/a
Customer
n/a
Issue Votes (0)
Watchers (3)
Reference
SZ-3
Please wait...
Page is in error, reload to recover