2026-08-25 · run 2026-08-25-s4-dml-only-db-user

S4: it is not DDL *rights* that the migration needs, it is *ownership*

Summary

S4 asked whether a DML-only database account fails cleanly. It does. But the run answered a more useful question than the one asked: granting that account full DDL rights does not fix it. The first changeset in this upgrade issues DROP INDEX, and in Postgres DROP INDEX requires ownership of the index — a thing no GRANT confers.

The correct pre-flight check is therefore not "does the Keycloak database user have DDL permissions", which is what everyone asks. It is "does the Keycloak database user own the Keycloak objects".

Environment

Procedure

./bin/restore.sh baseline-26.0.0-typical-1k

# a locked-down enterprise account: DML on everything, DDL on nothing
create role kc_dml login password 'kc_dml';
grant connect on database keycloak to kc_dml;
grant usage on schema public to kc_dml;
grant select,insert,update,delete on all tables in schema public to kc_dml;
grant usage,select on all sequences in schema public to kc_dml;
revoke create on schema public from kc_dml, public;

# .env
COMPOSE_FILE=docker-compose.yml:docker-compose.dbidentity.yml
KC_DB_USERNAME=kc_dml
KC_DB_PASSWORD=kc_dml

./bin/upgrade.sh 26.7.1

Then two further runs, each changing exactly one thing: grant create on schema public to kc_dml (run B), and alter table … owner to kc_dml for every table and sequence (run C).

Results

The failure, identical in runs A and B

ERROR [liquibase.changelog.ChangeSet] ChangeSet
  META-INF/jpa-changelog-20.0.0.xml::20.0.0-12964-supported-dbs-edb-migration::keycloak
  encountered an exception.: liquibase.exception.DatabaseException:
  ERROR: must be owner of index idx_group_att_by_name_value
  [Failed SQL: (0) DROP INDEX public.IDX_GROUP_ATT_BY_NAME_VALUE]
ERROR: Failed to update database
ERROR: Failed to start server in (production) mode

Run B is the load-bearing one. kc_dml demonstrably had DDL rights — the probe create table ddl_probe(x int); drop table ddl_probe; succeeded as that role immediately before the run — and the migration failed at exactly the same changeset with exactly the same message. must be owner of is not a privilege error and cannot be granted away. Postgres reserves DROP INDEX, and ALTER TABLE, to the object's owner (or a superuser, or a member of the owning role).

Why it fails on the first changeset

The 26.0.0 baseline carries 144 changelog rows. Reading them back after the successful run C:

Order 145 is the first row past the 144-row baseline. The very first thing a 26.0.0 → 26.7.1 migration does is drop and rebuild IDX_GROUP_ATT_BY_NAME_VALUE, via a changeset that lives in the 20.0.0 changelog file and exists to fix up EDB deployments. So the ownership requirement bites immediately — which is the good news, because nothing else has been applied when it does.

Outcome

• Failed cleanly, before touching data — databasechangelog still 144, all 1,002 users present, both databasechangeloglock rows locked=f
• Container exited 1 and stayed exited (one attempt, no retry loop)
• 503 from BootstrapFilter while the failed bootstrap was in flight
• Recoverable by fixing ownership alone — no restore needed

Verification

Publishable extract

Everyone asks whether the Keycloak database user has DDL permissions. That is the wrong question.

We gave Keycloak a locked-down Postgres account — select, insert, update, delete on every table, no create — and upgraded a 1,002-user realm from 26.0.0 to 26.7.1 on Postgres 16.15. It failed, cleanly, having applied nothing:

ERROR: must be owner of index idx_group_att_by_name_value
[Failed SQL: (0) DROP INDEX public.IDX_GROUP_ATT_BY_NAME_VALUE]

Then we granted that same account CREATE on the schema and proved it could create and drop a table. The upgrade failed again, at the same changeset, with the same message. Only making the account the owner of the tables let the migration run — after which it completed in 17 seconds.

The reason is plain Postgres, not Keycloak: DROP INDEX and ALTER TABLE require ownership, and ownership is not a privilege you can GRANT. Any shop where a DBA creates the schema and the application connects as a different role — which is most shops with a change-control process — has a Keycloak upgrade that will stop dead on its first changeset.

The check to run before your maintenance window is one query:

select tablename, tableowner from pg_tables
where schemaname = 'public' and tableowner <> '<your keycloak role>';

If that returns rows, your upgrade has not been tested. Ours hadn't either.