colidevs / colidevs/create-coliapp
templates/express-ts: same CREATEROLE DB-role provisioning gap as ecommerce-admin-template — app_owner cannot create app_runtime
- Dominant language
- TypeScript
- Stars
- 2
- Forks
- 0
- Avg merge
- 4h 42m
- Merged PRs (30d)
- 43
Description
## Root cause
`templates/express-ts/drizzle/0001_rls_roles.sql` has the same structural bug independently confirmed and fixed in `templates/nextjs-shadcn-ecommerce-admin` (`ecommerce-admin-template` PR10):
```sql
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_runtime') THEN
CREATE ROLE app_runtime NOBYPASSRLS LOGIN PASSWORD 'change_me_in_infisical';
END IF;
END
$$;
```
This migration is applied through `DATABASE_OWNER_URL` — `app_migrator` connecting with an auto `SET ROLE app_owner` (per `.claude/rules/backend-template-stack.md`'s 3-role split). Postgres role ATTRIBUTES (like CREATEROLE), unlike privileges, are never inherited via membership. `app_owner` has no CREATEROLE attribute (correctly, per least privilege — granting it as a standing attribute would itself be a privilege-escalation surface), so this `CREATE ROLE` statement fails with:
```
ERROR: permission denied to create role
DETAIL: Only roles with the CREATEROLE attribute may create roles.
```
on any fresh Postgres instance that actually enforces least privilege — reproduced live and independently confirmed against a throwaway Postgres 17 container during `ecommerce-admin-template` PR10's own verification.
A second, related gap: even once the roles exist, `app_owner` lacks `CREATE` on the target database and on the `public` schema (Postgres 15+ no longer grants this to PUBLIC by default), which separately breaks `drizzle-kit migrate`'s own internal `CREATE SCHEMA IF NOT EXISTS "drizzle"` bookkeeping step.
## Fix pattern to mirror
See `ecommerce-admin-template` PR10 (`colidevs/create-coliapp`, branch `feat/ecommerce-admin-template-10-pilot-fixes`):
- Added `drizzle/bootstrap-roles.sql` — a one-time, idempotent script run by the Postgres superuser BEFORE any migration, provisioning `app_owner`/`app_migrator`/`app_runtime` plus the `GRANT CREATE ON DATABASE ...` / `GRANT CREATE ON SCHEMA public ...` grants `app_owner` needs.
- `0001_rls_roles.sql` no longer `CREATE ROLE`s anything — it only `ALTER`/`GRANT`s against already-provisioned roles.
- `drizzle/MIGRATIONS.md` documents the mandatory bootstrap step.
`templates/express-ts` is a pre-existing, already-shipped template — this is a separate, independent bug from the `ecommerce-admin-template` change and is intentionally not fixed as part of that PR. Filing so it can be picked up on its own.
## Verification once fixed
Tear down and recreate a fresh Postgres container, run the full migration sequence from zero (bootstrap step + `drizzle-kit migrate`), confirm it succeeds with no manual intervention beyond the documented bootstrap step.
Contributor guide
No contributing guide indexed for this repository
Research direction
Start with templates/express-ts/drizzle/0001_rls_roles.sql and compare it with the ecommerce-admin-template PR10 fix pattern. Add the documented bootstrap step, update drizzle/MIGRATIONS.md, and ensure the migration only operates on provisioned roles and grants. Verify by recreating a fresh Postgres container and running the full migration sequence from zero.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- express, postgresql, typescript
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 64/100