* fix(db): grant schema usage and re-run windmill role grants
Migration 20250205131523 grants the windmill_user and windmill_admin
roles access to the schema and its tables, but its first statement is
LOCK TABLE pg_catalog.pg_roles, which requires superuser. On managed
Postgres (RDS, Cloud SQL) the migration user is not one, the lock
raises, and the block's EXCEPTION WHEN OTHERS handler downgrades the
failure to a NOTICE, so every GRANT after it is skipped. Core tables end
up ungranted and queries on a user_db transaction (SET LOCAL ROLE
windmill_user/windmill_admin) fail with "permission denied for table"
or, when schema USAGE was never granted, "relation does not exist".
Add a migration that re-runs those grants without the lock and without a
catch-all handler, and add the missing GRANT USAGE ON SCHEMA public to
init-db-as-superuser.sql, which PostgreSQL 15+ no longer implies for
PUBLIC. The init script also now creates windmill_admin before the table
grants so role membership is in place when they run.
Fixes WIN-2208
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
* docs(db): correct schema-privilege rationale in grant migration
PostgreSQL 15 revoked CREATE, not USAGE, from PUBLIC on the public
schema, so USAGE is still granted by default there. The explicit grant
is what a hardened database that revoked it needs, not a PG15 default.
Also correct the description of what 20250205131523's failure actually
loses: 20221105003256 grants the tables outside any locked block, so the
gap is the ALTER DEFAULT PRIVILEGES covering later-created tables.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
* fix(db): grant only runner-owned objects to avoid aborting upgrade
GRANT ALL ON ALL TABLES IN SCHEMA raises a hard "permission denied for
table X" the moment the schema holds an object the migration runner does
not own (a superuser-installed extension such as PostGIS spatial_ref_sys,
or a co-located application table). With no catch-all handler that would
abort the whole upgrade -- a regression against 20250205131523, which
tolerated it only by swallowing every error.
Grant per-object over just the tables and sequences the runner owns,
which is exactly the set the GRANT can succeed on. Windmill's own tables
are all runner-owned, so coverage is unchanged; foreign objects are
skipped rather than aborted on. ALTER DEFAULT PRIVILEGES stays (it only
governs the runner's future objects, so it cannot conflict).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
* fix(db): guard each grant so the migration can never abort an upgrade
Wrap every grant in its own BEGIN/EXCEPTION/WARNING so no single failure
can abort the migration -- the opposite of 20250205131523's one
block-wide WHEN OTHERS, whose flaw was granularity: a single early
failure there silently skipped every remaining grant. Here each grant
that cannot be applied is isolated, re-raised as a named WARNING, and the
rest still run.
This closes the last abort paths: a grant on an object dropped by another
session between the catalog scan and the GRANT, USAGE on a schema the
runner cannot grant, or a missing role, now warn and continue instead of
failing the upgrade. On a clean owned schema the guards never fire (zero
warnings, verified). The owner filter stays so foreign objects are
skipped without even a warning; the guard is the backstop.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
* fix(db): also grant runner-owned views and materialized views
pg_tables returns only ordinary and partitioned tables, so the previous
loop left owned views (flow_workspace_runnables and any materialized
views) ungranted -- a coverage regression against the GRANT ... ON ALL
TABLES form, which grants views too. Those views are read through
user_db transactions, so windmill_user/windmill_admin need access.
Enumerate pg_class over the relkinds ALL TABLES covers (r, p, v, m, f),
keeping the ownership filter so foreign objects are still skipped.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
* fix(db): grant by effective authority, not owner-name equality
Filtering relations by pg_get_userbyid(relowner) = current_user skips
objects the runner can legally grant but does not own by name: after a
migration-credential rotation, tables and sequences stay owned by the
previous runner while the new runner grants through inherited membership
(or as a superuser). Owner-name equality would leave those ungranted and
their user_db access broken.
Filter by pg_has_role(current_user, owner, 'USAGE') instead -- objects
the runner owns directly, inherits ownership of, or reaches as superuser.
Genuinely foreign objects (owner the runner is not a member of) are still
skipped, so the per-object guards remain the backstop.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
* fix(db): scope grants by explicit membership, not superuser authority
pg_has_role treats a superuser as a member of every role, so a superuser
migration runner would grant windmill_user/windmill_admin access to every
co-located relation in the schema -- another application's tables, an
extension's tables -- not just Windmill's.
Compute the runner's role set from pg_auth_members (recursive explicit
membership) and grant only relations owned by it. This still covers the
credential-rotation case owner-name equality missed (the new runner is a
real member of the previous owner) without inheriting the superuser's
implicit authority over unrelated roles.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---------
Co-authored-by: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Ruben Fiszel
Anton Shutik
Faton Ramadani
Ruben Fiszel