Feature Overview (aka. Goal Summary)

This feature aims to validate, enable, and officially support the `pluginDivisionMode: schema` configuration within the Red Hat Developer Hub (RHDH) backend.

Currently, the default behavior for RHDH (inherited from upstream Backstage) often assumes the ability to dynamically create separate databases for each plugin (e.g., backstage_plugin_catalog, backstage_plugin_auth). This requires the RHDH service account to possess elevated privileges (often the CREATEDB role) and connection access to the default postgres database to manage these resources.

In many large, regulated enterprise environments, Database Administrators (DBAs) enforce strict security policies that forbid applications from creating databases or connecting to the administrative postgres database. By supporting pluginDivisionMode: schema, we allow RHDH to operate entirely within a single, pre-provisioned database, using PostgreSQL schemas to isolate plugin data. This ensures RHDH can be deployed in environments with strict "Least Privilege" security models.

Goals (aka. expected user outcomes)

• Compliance with Enterprise Security Policies: Allow Platform Engineers to deploy RHDH without requiring CREATEDB or SUPERUSER privileges on the PostgreSQL instance.

• Simplified Database Provisioning: Enable DBAs to provision a single logical database and a single user for RHDH, reducing operational overhead and complexity.

• Enhanced Cloud Compatibility: Improve compatibility with managed database services (e.g., AWS RDS, Azure Database for PostgreSQL) where administrative access is often restricted.

• Seamless Configuration: Ensure the switch between database-per-plugin and schema-per-plugin is handled via standard app-config.yaml configuration without code changes.

Requirements (aka. Acceptance Criteria):

• Configuration Support

• • As a Platform Engineer, I want to set backend.database.pluginDivisionMode to schema in my app-config.yaml, so that RHDH attempts to use schemas instead of separate databases.

• • As a Platform Engineer, I want to specify a custom knex configuration (including search path logic if necessary) that supports the schema mode.

• Plugin Isolation & Migration

• • As a Developer, I want core plugins (catalog, scaffolder, auth, permission, search, etc.) to automatically create their own schemas (e.g., catalog, scaffolder) within the single target database upon startup if they do not exist. The schema should prefix `rhdh_` to the schema name (e.g., rhdh_catalog instead of just catalog)

• • As a Developer, I need the database migration scripts (knex migrations) for all Red Hat-supported plugins to execute successfully within their respective schemas without errors.

• Connection Handling

• • As a Security Architect, I want to ensure that RHDH does not attempt to connect to the default postgres maintenance database during startup when pluginDivisionMode: schema is enabled.

• • As a DBA, I want the RHDH application to function correctly with a user that has CREATE SCHEMA permissions on the target database, but no CREATEDB permissions.

• Data Integrity

• • As a Platform Engineer, I want to verify that data written by one plugin is isolated within its specific schema and does not collide with tables from other plugins, ensuring system stability.

Out of Scope (Optional)

• Migration Tooling: Automatic migration of existing RHDH instances from "database-mode" to "schema-mode" (this ticket covers new deployments or manual migrations only).

• Non-PostgreSQL Support: Support for this mode on database engines other than PostgreSQL (e.g., SQLite is excluded).

• Third-Party Plugin Guarantees: While we ensure core/Red Hat plugins work, we cannot guarantee that all community/third-party plugins respect the schema isolation logic without testing.

Customer Considerations (Optional)

• Connection Pooling: Customers using PgBouncer (transaction pooling) may need specific configuration guidance, as schema switching can sometimes interact unexpectedly with aggressive pooling if search_path is not managed correctly by the application connection logic.

• Naming Collisions: In rare cases, if a customer has manually created schemas in the target DB that match plugin names, startup might fail. To avoid conflicts in shared databases, the customer needs to expose configuration for a custom schema prefix (e.g., rhdh_catalog instead of just catalog). This should be noted in release notes. 

Documentation Considerations

• Configuration Guide: Update the "Configuring the Database" section of the RHDH documentation to explicitly show the `pluginDivisionMode: schema` syntax.

• Permissions Matrix: Clearly list the minimum PostgreSQL permissions required for the database user in this mode (e.g., CONNECT, CREATE SCHEMA on the database).

• Architecture Diagram: Provide a visual comparison between "Database per Plugin" vs "Schema per Plugin" to help architects choose the right model.