EQL install aborts entirely when the role is not `postgres`: one owner-scoped grant rolls back all 194 functions

[coderdan]

Reported by a Lovable agent implementing CipherStash in a Lovable-hosted project, where the database role is sandbox_exec — not postgres, and not a member of it.

What happens

npx stash eql install runs fine, right up until the grants. Supabase mode is auto-detected from the URL (packages/cli/src/commands/db/install.ts:119-126), so it cannot be avoided by omitting --supabase. The install then fails on:

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA eql_v3 GRANT SELECT ON TABLES TO anon, authenticated, service_role;

with permission denied to change default privileges.

Because the whole install is one transaction (packages/cli/src/installer/index.ts:181-188 — BEGIN, bundle, grants, COMMIT, with ROLLBACK on any failure), everything goes back: the schemas, the domains, all ~194 functions. The role could have created every one of them. What it cannot do is the owner-scoped form of the default-privileges statement, and that single statement takes the rest down with it.

Worth stating plainly, because it is not obvious: ALTER DEFAULT PRIVILEGES without FOR ROLE postgres succeeds as sandbox_exec. The feature is not blocked — only the owner-scoped form is. The blocker is role identity alone.

There are three of these statements, all in packages/cli/src/installer/grants.ts — two in supabasePermissionsSql() (lines 38-40) and one in supabaseInternalPermissionsSql() (line 63). The seven other statements in that block are plain GRANTs that any role owning the schema can execute.

Proposal

1. Let the privileged statements be deferred.

--skip-owner-grants (or --role <name>): install the engine as the connecting role, and skip only the ALTER DEFAULT PRIVILEGES FOR ROLE … statements. The immediate GRANTs still run, so the install is functional for the objects that exist — what is deferred is the default-privileges rule covering objects created later.

2. Emit the deferred statements as a labelled tail, both on stdout and in --print-sql output:

-- The statements below require a role that is a member of `postgres`.
-- Apply them via your platform's migration tool or the Supabase SQL editor.
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA eql_v3 …

3. Name the statement and the requirement in the error. Today the operator sees permission denied to change default privileges — the raw Postgres text, wrapped by Failed to install EQL: … (packages/cli/src/installer/index.ts:190). It does not say which statement failed, which role would satisfy it, or that the rest of the install was rolled back and is recoverable. All three are knowable at the point of failure.

4. Consider whether the grants belong in the same transaction at all. Rolling back 194 working functions because a permissions refinement failed is the wrong trade for a step that is separately re-runnable and idempotent.

Notes

• Keep packages/cli/src/installer/grants.ts the single source of truth. packages/stack/__tests__/supabase-v3-grants-pg.test.ts asserts against the exact shipped SQL; a split must preserve that.
• Whatever the split, SUPABASE_PERMISSIONS_SQL_V3 (immediate + deferred, in order) must stay byte-identical for the normal postgres-role path.
• Related: the sibling --print-sql issue depends on the deferred statements being separable to label the tail.