Version-agnostic database migration support

[pantierra]

We should think about handling more complex scenarios of database migrations with the following strong requirements:

• allow migration from any to any version
• ensure idempotency of database init scripts and migrations

This is a follow-up to #232. Which will require people to go through 0.7.x migration when migrating from pre-0.7.x versions to future ones.

One solution to handle this would be to save the database version in the database itself (e.g. schema_migrations table) and build this logic into our scripts. We could also have a logic that includes files per release.

Or we could rely on some existing tools for that, like Liquidbase, Flyway or migrate.

[zacdezgeo]

cc: @bitner

[pantierra]

We had some discussions around this, and it seems that in most cases, pypgstac migrate already handles things well. Introducing an additional migration tool would likely add unnecessary complexity without clear benefit.

The issue arose with this particular change which basically requires to run initdb.sql with db root permissions when updating from >=0.6.x to higher versions. Normally, initdb.sql is executed with lower privileges, so this creates an edge case.

Looking at it more closely, my earlier idea of a separate migration layer may have been overkill. I see two main paths forward:

• Break the upgrade path: Require users to first upgrade to 0.7.x before proceeding to newer versions.
• Always run initdb.sql with elevated privileges: This simplifies upgrades but comes with some security trade-offs.

Neither approach is perfect, and edge cases like this may still arise in the future — but for now, these seem like the most pragmatic options.

[bitner]

@pantierra pypgstac migrate is the only "official" way to run migrations for pgstac. Every release of pypgstac includes many tests to make sure that a base install of a release is the same as starting at the earliest version of pgstac and running incremental migrations to get to the released version.

As migrations that modify the schema can change the underlying data, there is no way that we can reliably support backwards migrations. The pypgstac migrate tool does allow migrating from any released version to any higher version. The current version of pgstac as well as a history of migrations is kept in the pgstac.migrations table.

Migrations necessarily need to be run at a higher permission level. Migrating is a security risk, period, and needs to be done by a role that has greater permissions that the permissions that are required to use pgstac.

I'm not sure what issue you are having updating from >=0.6.x, but if there is an issue there, that is a problem that we should fix in pgstac, not something that you should be trying to work around in eoapi-k8s.

I'm not sure what initdb.sql you are referring to here. I'm very happy to hop on a call to talk through any of this!

[pantierra]

We just discussed this with @bitner and @emmanuelmathot, and here's what we concluded:

• we don’t need to manually create all these roles and configurations; instead, pgstac migrate should handle that automatically.
• we also neither need nor want to run migrations using a user with ADMIN privileges.
• so, we'll create a special new role with: GRANT ALL PRIVILEGES ON databasename TO role; (without WITH ADMIN option). This role will be used to run pgstac migrate, and that should be sufficient.
• Any specific issues during migration should be handled within pgstac itself.

[pantierra]

Just checked, and in contrary what was talked in the meeting, it is currently not possible to run pypgstac migrate only with a user who has GRANT ALL PRIVILEGES ON databasename TO role . The steps lined out in the documentation seem to be absolutely needed (and to be run as root user).

I am proposing to follow the upstream instructions to rely on the root user for the initial database setup (for all kind of upgrades/setups), later we can then run the migrations with the less-priviledged user: #240