RFC 697: Provide a way for privileged SQL commands to be run outside of migrator
Created by: mike-r-mclaughlin
See RFC 697: Multiple version upgrades.
Feature request description
It would be helpful to provide an option that skips privileged SQL commands. When this flag is set, it will skip any SQL commands that require SUPERUSER permissions. Currently, this means any CREATE EXTENSION or COMMENT ON EXTENSION commands.
Ideally, this would be applicable for every upgrade/migration so the admin doesn't have to do anything special. This might lead to these migrations might be missed, though (if the admin missed the announcement in the changelog).
Is your feature request related to a problem? If so, please describe.
Security conscious customers like redacted customer are often not allowed to have SUPERUSER access. In this case, they need to run these commands outside of the normal install/upgrade path.
Describe alternatives you've considered.
From @efritz in this Slack thread:
- add whatever extensions via external process
- run migrations until it fails
- add a migration log for the failing privileged migration (this will trick the migrator into thinking it ran successfully on the next invocation)
- re-run migrations
To add a migration log, run
migrator add-log -db={database} -version={target migration version}. This can be a simple SQL query as well (it really just inserts a row into themigration_logstable).