colidevs / colidevs/create-coliapp

templates/express-ts: same CREATEROLE DB-role provisioning gap as ecommerce-admin-template — app_owner cannot create app_runtime

Open
#56 1 comment 0 reactions 0 assignees View on GitHub
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.