[stork-users] Stork 2.6.0 server fails schema migration when the DB user is not the table owner

[fbcadmin]

(I had grok-build fix the issue and write up the following)


Stork server 2.6.0.261009212753 on Debian 13 refused to start after
upgrade. The schema migration is valid. It failed because the role in
STORK_DATABASE_USER_NAME does not own the existing tables. PostgreSQL
requires the table owner for ALTER TABLE. A second, smaller problem is
that the log reports the failed migration as "new: 0".

Summary
-------
Installed package: isc-stork-server 2.6.0.261009212753
Previous schema version: 71, applied 2026-03-01
Database: PostgreSQL 17, database name "stork"
Connection user in /etc/stork/server.env: stork
Owner of the existing application tables, sequences, and functions: 
stork-server
That role is not a superuser. "stork" is not a member of it.
"stork" already had SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES,
and TRIGGER on the tables, which is why the previous server version ran.

Startup error
-------------
systemd: isc-stork-server.service, exit status 1

time="2026-10-30 08:54:22" level="fatal" msg="Unexpected error"
file="main.go:31"
error="Cannot start the Stork Server: problem migrating database, old: 
72, new: 0: ERROR #42501 must be owner of table kea_daemon"

Stack:
  isc.org/stork/server/database.Migrate
    backend/server/database/migrations.go:93
  isc.org/stork/server/database.MigrateToLatest
    backend/server/database/migrations.go:102
  isc.org/stork/server/database.NewApplicationDatabaseConn
    backend/server/database/connection.go:127
  isc.org/stork/server.(*StorkServer).Bootstrap
    backend/server/server.go:124

PostgreSQL log, same second, user stork database stork:

  ERROR: must be owner of table kea_daemon
  STATEMENT:
    ALTER TABLE public.kea_daemon
      ADD COLUMN server_tag TEXT;
    UPDATE public.kea_daemon
      SET server_tag = COALESCE(
        config->'Dhcp4'->>'server-tag',
        config->'Dhcp6'->>'server-tag'
      )
      WHERE config IS NOT NULL;

That statement is migration 73, 
backend/server/database/migrations/73_daemon_server_tag.go.

What the schema version table showed
------------------------------------
gopg_migrations reached version 72 at 2026-10-30 08:45:14, during the
first failed start. Version 72 is 72_leases.go. It creates new tables, so
the connecting user can run it without owning the old tables. Those new
tables (lease, lease_update) were owned by stork. The version row was
committed. Migration 73 then ran in its own transaction, failed the
ALTER, and rolled back. server_tag was not added. The schema stayed at 72.

"new: 0" does not mean the migrator tried to reset the database to
version 0. migrations.go wraps a failed migrations.Run result as:

  problem migrating database, old: %d, new: %d

go-pg returns 0 as the new version when the run fails. The old version in
the message, 72, was the real schema version.

Why this showed up on 2.6.0
----------------------------
Versions 66 through 71 were applied on 2026-03-01 and do ALTER existing
tables (service, subnet, shared_network, access_point, daemon). Those
succeeded, so the role that connected that morning was able to alter
tables it owned. /etc/stork/server.env was last modified the same day.
The active setting is:

  STORK_DATABASE_USER_NAME=stork

The file still contains the commented previous value:

  #STORK_DATABASE_USER_NAME=stork-server

No later migration ran until 2.6.0. Normal operation only needs the grants
"stork" already had. Migration 73 is the first statement since that user
change that must own kea_daemon. There was no package script in
isc-stork-server 2.6.0 that moved ownership; the postinst only restarts
the service if it was already active.

Before the repair, pg_tables in schema public was:

  stork-server   41 tables
  stork           2 tables   (lease and lease_update, created by 
migration 72)

Repair
------
Run as the database superuser, in the stork database only. "stork-server"
owned nothing in the other databases on this cluster.

  REASSIGN OWNED BY "stork-server" TO stork;

After that, every table in public was owned by stork. Restarting
isc-stork-server applied migrations 73 through 79 and stayed up.
gopg_migrations max version is 79. The HTTP UI on port 8080 returns 200.

Suggested changes
-----------------
1. In the 2.6.0 release notes, say that an upgrade runs ALTER TABLE on
   existing tables, so the role in STORK_DATABASE_USER_NAME must own those
   tables, not merely have DML grants. The remedy is:

     REASSIGN OWNED BY <current owner> TO <STORK_DATABASE_USER_NAME>;

2. When migration fails with SQLSTATE 42501, name the connecting role and
   the table owner in the fatal log, and do not report the target version
   as 0.

3. Optionally, have the server or stork-tool check ownership before
   migrating and refuse with that explanation.

Environment, for the record
---------------------------
OS: Debian 13 (trixie), amd64
PostgreSQL: 17, local socket and 127.0.0.1:5432, scram-sha-256 for host auth
isc-stork-server: 2.6.0.261009212753
Database name: stork
Connecting role: stork (LOGIN, not superuser)
Previous owner: stork-server (LOGIN, not superuser)
Schema before the failed migration: 71
Schema after migration 72 committed and 73 rolled back: 72
Schema after REASSIGN OWNED and a successful start: 79



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.isc.org/pipermail/stork-users/attachments/20261030/af0775e0/attachment.htm>