pg_cron: Hard pg_cron 1.5+ dependency on pg_catalog breaks restores of dumps created when using older extension versions
Summary
We’ve noticed that restoring Postgres dumps created when using older versions of pg_cron (namely 1.4.2 in our case) against a database where pg_cron 1.5+ is installed break due to a newly introduced hard, and undocumented, requirement on creating the extension in the pg_catalog schema.
Description
pg_dump generates the extension-related section of its database dumps in the following format, explicitly stating the schema where the extension was enabled at dump-time:
--
-- Name: pg_cron; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS pg_cron WITH SCHEMA public;
This dump was created off a Postgres install running pg_cron 1.4.2, with the extension enabled in the public schema by running CREATE EXTENSION pg_cron;. As the user’s search_path was set to the public schema, the extension defaulted to it during creation.
When restoring the above dump against a database service running pg_cron 1.5+, the following error is encountered:
psql:db_cluster-01-06-2023@06-34-36.backup:203: ERROR: extension "pg_cron" must be installed in schema "pg_catalog"
This, in turn has the following effect:
- when allowing pg_restore to continue on error -
pg_cronis not created, and any previously created cronjob does not exist post-restore - when using pg_restore in conjunction with
--exit-on-error- restore fails
About this issue
- Original URL
- State: open
- Created a year ago
- Reactions: 12
- Comments: 17 (1 by maintainers)
Are there any updates to this issue?
@Exagone313 - thanks for the workaround! This’ll surely help out others which are impacted by the same issue.
Unfortunately when dealing with very large backups, or backups created using
pg_backupall, the approach is either computationally intensive, or non viable, which renders us unable to upgrade to pg_cron 1.5+ across the platform.@marcocitus - sorry to ping you like this, but is there any other workaround which could be implemented, if the hard requirement to create
pg_cronin thepg_catalogschema is unlikely to be changed?Saw the other open issue talking about allowing custom schemas and the reason for not doing this is security concerns.
https://github.com/citusdata/pg_cron/issues/225
If the pg_cron code is done properly (fully schema qualify all object reference calls), all security concerns around custom schemas can be avoided. I know this because I did it in pg_partman, even correcting a very serious CVE in the past about it.
Pretty sure any user code touching pg_catalog like this would be looked down upon by core.
Just got hit by this too 😞 1.4.2 worked fine, so I’m going to stick with it.