Database Bootstrap Troubleshooting

Guidance for Cloud SQL application database setup after infrastructure (Terraform) provisioning and before or after Helm deployment.

Bootstrap script: scripts/bighammer_db_setup.sql in the mirrored bighammer chart package.

Related: Infrastructure — Cloud SQL, Secret Manager Secrets, Scripts Reference — bighammer_db_setup.sql

Overview

Terraform provisions:

• Cloud SQL instance and application database (e.g. bighammer_db_<env>)
• Database users: postgres, bh_dev_user, bh_app_user
• GSM passwords: bh-dev-user-password, bh-app-user-password, db-postgres-password

Terraform does not create application schemas or grants. Run bighammer_db_setup.sql as the postgres user on the application database.

Recommended order of operations

Terraform (instance, database, users, GSM passwords)
    → bighammer_db_setup.sql as postgres (on application database)
    → Helm deploy / pod startup

Running the bootstrap script before app deployment avoids schema ownership and permission errors.

If Helm deploys before bootstrap, pods may create schemas as the wrong user. Recovery steps are in Recovery — wrong schema ownership below.

Prerequisites

Verify before running bootstrap or debugging pod errors:

Check	Expected
Application database	Exists; name matches POSTGRES_DB in Helm values
Users	bh_dev_user, bh_app_user exist (Terraform)
GSM passwords	bh-dev-user-password, bh-app-user-password match Cloud SQL users
Helm migration user	POSTGRES_USER: bh_dev_user
Helm runtime user	BH_APP_USER: bh_app_user
remoteSecrets.postgres.password	bh-dev-user-password
Bootstrap executed	Full bighammer_db_setup.sql on application database (not maintenance DB only)
Bootstrap identity	Script run as postgres (password: db-postgres-password)

Credential model (dual-role)

K8s env vars	DB user	GSM secret	Purpose
POSTGRES_USER / POSTGRES_PASSWORD	bh_dev_user	bh-dev-user-password	Alembic, migrations, DDL
BH_APP_USER / BH_APP_USER_PASSWORD	bh_app_user	bh-app-user-password	Runtime SQLAlchemy queries (DML)

Keycloak server DB (separate): keycloak_db_user / bh-kcdbpwd on keycloak_db_<env>.

Running bighammer_db_setup.sql

When the database and users already exist (typical Terraform flow):

1. Connect as postgres to the application database (or connect to postgres then \connect <app_db>).
2. Comment out CREATE DATABASE in the script.
3. Set \connect and GRANT CONNECT ON DATABASE to your application database name (e.g. bighammer_db_<env>).
4. Role creation (IF NOT EXISTS) is safe to run; skip if users already exist.
5. Run the full script: schemas, bh_dev_user grants, bh_app_user grants, default privileges.
6. Confirm CREATE EXTENSION IF NOT EXISTS vector SCHEMA catalog_db succeeds (requires postgres).

Common errors

Error / symptom	What to check	Fix
password authentication failed for user "bh_dev_user"	POSTGRES_PASSWORD ExternalSecret; GSM bh-dev-user-password vs Cloud SQL	Align secret with Cloud SQL user; refresh ExternalSecret; redeploy
password authentication failed for user "bh_app_user"	BH_APP_USER_PASSWORD; GSM bh-app-user-password	Align secret with Cloud SQL user
permission denied for schema <name> (Alembic / bh_dev_user)	Schema owner; POSTGRES_USER in values	Owner should be bh_dev_user. If bh_app_user owns schemas, see Recovery
permission denied for schema catalog_db (runtime / bh_app_user)	has_schema_privilege('bh_app_user', 'catalog_db', 'USAGE')	Re-run bh_app_user grants in bootstrap script as bh_dev_user
permission denied for schema celery_db at pod startup	Grants on celery_db	Re-run full bootstrap grants for all six schemas
Cross-schema query fails (e.g. service reads catalog_db.*)	App user USAGE on every schema the service queries	Grant USAGE on all required schemas, not only the service primary schema
must be able to SET ROLE "bh_dev_user" when changing ownership	Schemas owned by bh_app_user	Run Recovery SQL as postgres
GRANT fails when run as postgres	Cloud SQL postgres is not a full PostgreSQL superuser	Fix ownership first; run grant statements as bh_dev_user
Bootstrap or REASSIGN OWNED hangs	Active pod DB connections	Scale affected deployments to 0; retry
CREATE EXTENSION vector fails	Permissions / extension allow-list	Run as postgres on application database

Diagnostic queries

Run on the application database.

Session:

SELECT current_user, current_database();

Schema ownership (expect bh_dev_user for all application schemas):

SELECT n.nspname AS schema, r.rolname AS owner
FROM pg_namespace n
JOIN pg_roles r ON r.oid = n.nspowner
WHERE n.nspname IN (
  'catalog_db', 'celery_db', 'agent_db', 'audit_db',
  'keycloakapi_db', 'llm_observability_db'
)
ORDER BY 1;

App user USAGE (all should be t):

SELECT n.nspname,
       has_schema_privilege('bh_app_user', n.nspname, 'USAGE') AS app_has_usage
FROM pg_namespace n
WHERE n.nspname IN (
  'catalog_db', 'celery_db', 'agent_db', 'audit_db',
  'keycloakapi_db', 'llm_observability_db'
)
ORDER BY 1;

Recovery — wrong schema ownership

Occurs when apps deploy before bootstrap, or when POSTGRES_USER was bh_app_user so pods created schemas owned by the runtime user.

1. Scale down application deployments that connect to the database.
2. As postgres on the application database:

GRANT bh_app_user TO postgres;
GRANT bh_dev_user TO postgres;
REASSIGN OWNED BY bh_app_user TO bh_dev_user;

3. As bh_dev_user, re-run the grants sections of bighammer_db_setup.sql (bh_dev_user block, bh_app_user block, default privileges).
4. Re-run diagnostic queries.
5. Scale deployments back up.

Post-fix verification

• Diagnostic queries: all schemas owned by bh_dev_user; all app_has_usage = true.
• Pod logs: schema setup and alembic upgrade head complete without permission denied.
• Restart affected deployments after grant changes.

Platform-managed GSM secrets

Infrastructure provisioning creates prerequisite DB and SSL secrets. Do not recreate them manually — see Platform prerequisites.

Non-platform secrets (integrations, licenses, etc.): see bh_gsm_onetime_setup.sh in the mirrored chart scripts/ folder.

Related

Topic	Link
Scripts	Scripts Reference
Secrets	Secret Manager Secrets
Azure equivalent	Azure Database Bootstrap Troubleshooting
General troubleshooting	Troubleshooting