Script 'mail_helper' called by obssrc Hello community, here is the log from the commit of package pg_cron for openSUSE:Factory checked in at 2023-01-02 15:02:01 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Comparing /work/SRC/openSUSE:Factory/pg_cron (Old) and /work/SRC/openSUSE:Factory/.pg_cron.new.1563 (New) ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Package is "pg_cron" Mon Jan 2 15:02:01 2023 rev:11 rq:1046024 version:1.4.2 Changes: -------- --- /work/SRC/openSUSE:Factory/pg_cron/pg_cron.changes 2022-04-06 21:52:30.794867515 +0200 +++ /work/SRC/openSUSE:Factory/.pg_cron.new.1563/pg_cron.changes 2023-01-02 15:02:04.861321496 +0100 @@ -1,0 +2,12 @@ +Sun Jan 1 18:22:36 UTC 2023 - Andrey Karepin <[email protected]> + +- drop patch pg_cron-32bit-compat.patch +- update to 1.4.2 + * Fixes a bug that could lead to privilege escalation if users can + trigger CREATE EXTENSION + * Add compatibility for PostgreSQL 15 beta + * Fixes a bug that could cause unschedule to crash + * Ensures that cron.max_running_jobs is not higher than possible + connection count + +------------------------------------------------------------------- Old: ---- pg_cron-1.4.1.tar.gz pg_cron-32bit-compat.patch New: ---- pg_cron-1.4.2.tar.gz ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Other differences: ------------------ ++++++ pg_cron.spec ++++++ --- /var/tmp/diff_new_pack.DRCVTM/_old 2023-01-02 15:02:05.529325250 +0100 +++ /var/tmp/diff_new_pack.DRCVTM/_new 2023-01-02 15:02:05.533325273 +0100 @@ -1,7 +1,7 @@ # # spec file # -# Copyright (c) 2022 SUSE LLC +# Copyright (c) 2023 SUSE LLC # # All modifications and additions to the file contributed by third parties # remain the property of their copyright owners, unless otherwise agreed @@ -21,14 +21,13 @@ %{pg_version_from_name} Name: %{pg_name}-%{ext_name} -Version: 1.4.1 +Version: 1.4.2 Release: 0 Summary: PostgreSQL module for simple job schedule License: PostgreSQL Group: Productivity/Databases/Servers URL: https://github.com/citusdata/pg_cron Source: https://github.com/citusdata/pg_cron/archive/refs/tags/v%{version}.tar.gz#/%{ext_name}-%{version}.tar.gz -Patch0: pg_cron-32bit-compat.patch BuildRequires: %{pg_name}-llvmjit-devel %pg_server_requires %if "%{pg_name}" == "" ++++++ pg_cron-1.4.1.tar.gz -> pg_cron-1.4.2.tar.gz ++++++ diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pg_cron-1.4.1/.gitignore new/pg_cron-1.4.2/.gitignore --- old/pg_cron-1.4.1/.gitignore 2021-09-25 18:55:16.000000000 +0200 +++ new/pg_cron-1.4.2/.gitignore 2022-07-15 00:14:59.000000000 +0200 @@ -38,3 +38,7 @@ /src/Makefile.custom pg_cron--?.?.sql + +log/ +results/ +src/*.bc diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pg_cron-1.4.1/CHANGELOG.md new/pg_cron-1.4.2/CHANGELOG.md --- old/pg_cron-1.4.1/CHANGELOG.md 2021-09-25 18:55:16.000000000 +0200 +++ new/pg_cron-1.4.2/CHANGELOG.md 2022-07-15 00:14:59.000000000 +0200 @@ -1,3 +1,10 @@ +### pg_cron v1.4.2 (July 15, 2022) ### + +* Fixes a bug that could lead to privilege escalation if users can trigger CREATE EXTENSION +* Add compatibility for PostgreSQL 15 beta +* Fixes a bug that could cause unschedule to crash +* Ensures that cron.max_running_jobs is not higher than possible connection count + ### pg_cron v1.4.1 (September 25, 2021) ### * Fixes PostgreSQL 11- support diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pg_cron-1.4.1/Makefile new/pg_cron-1.4.2/Makefile --- old/pg_cron-1.4.1/Makefile 2021-09-25 18:55:16.000000000 +0200 +++ new/pg_cron-1.4.2/Makefile 2022-07-15 00:14:59.000000000 +0200 @@ -1,10 +1,11 @@ # src/test/modules/pg_cron/Makefile EXTENSION = pg_cron -EXTVERSION = 1.3 DATA_built = $(EXTENSION)--1.0.sql DATA = $(wildcard $(EXTENSION)--*--*.sql) + +REGRESS_OPTS =--temp-config=./pg_cron.conf --temp-instance=./tmp_check REGRESS = pg_cron-test # compilation configuration diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pg_cron-1.4.1/README.md new/pg_cron-1.4.2/README.md --- old/pg_cron-1.4.1/README.md 2021-09-25 18:55:16.000000000 +0200 +++ new/pg_cron-1.4.2/README.md 2022-07-15 00:14:59.000000000 +0200 @@ -4,7 +4,7 @@ ## What is pg_cron? -pg_cron is a simple cron-based job scheduler for PostgreSQL (9.5 or higher) that runs inside the database as an extension. It uses the same syntax as regular cron, but it allows you to schedule PostgreSQL commands directly from the database: +pg_cron is a simple cron-based job scheduler for PostgreSQL (10 or higher) that runs inside the database as an extension. It uses the same syntax as regular cron, but it allows you to schedule PostgreSQL commands directly from the database: ```sql -- Delete old data on Saturday at 3:30am (GMT) @@ -88,11 +88,18 @@ To start the pg_cron background worker when PostgreSQL starts, you need to add pg_cron to `shared_preload_libraries` in postgresql.conf. Note that pg_cron does not run any jobs as a long a server is in [hot standby](https://www.postgresql.org/docs/current/static/hot-standby.html) mode, but it automatically starts when the server is promoted. -By default, the pg_cron background worker expects its metadata tables to be created in the "postgres" database. However, you can configure this by setting the `cron.database_name` configuration parameter in postgresql.conf. - ``` -# add to postgresql.conf: +# add to postgresql.conf + +# required to load pg_cron background worker on start-up shared_preload_libraries = 'pg_cron' +``` + +By default, the pg_cron background worker expects its metadata tables to be created in the "postgres" database. However, you can configure this by setting the `cron.database_name` configuration parameter in postgresql.conf. +``` +# add to postgresql.conf + +# optionally, specify the database in which the pg_cron background worker should run (defaults to postgres) cron.database_name = 'postgres' ``` @@ -106,7 +113,23 @@ GRANT USAGE ON SCHEMA cron TO marco; ``` -**Important**: Internally, pg_cron uses libpq to open a new connection to the local database. It may be necessary to enable `trust` authentication for connections coming from localhost in [pg_hba.conf](https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html) for the user running the cron job. Alternatively, you can add the password to a [.pgpass file](https://www.postgresql.org/docs/current/static/libpq-pgpass.html), which libpq will use when opening a connection. +**Important**: By default, pg_cron uses libpq to open a new connection to the local database, which needs to be allowed by [pg_hba.conf](https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html). +It may be necessary to enable `trust` authentication for connections coming from localhost in for the user running the cron job, or you can add the password to a [.pgpass file](https://www.postgresql.org/docs/current/static/libpq-pgpass.html), which libpq will use when opening a connection. + +Alternatively, pg_cron can be configured to use background workers. In that case, the number of concurrent jobs is limited by the `max_worker_processes` setting, so you may need to raise that. + +``` +# Schedule jobs via background workers instead of localhost connections +cron.use_background_workers = on +# Increase the number of available background workers from the default of 8 +max_worker_processes = 20 +``` + +You can also use a unix domain socket directory as the hostname and enable `trust` authentication for local connections in [pg_hba.conf](https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html), which is normally safe: +``` +# Connect via a unix domain socket +cron.host = '/tmp' +``` For security, jobs are executed in the database in which the `cron.schedule` function is called with the same permissions as the current user. In addition, users are only able to see their own jobs in the `cron.job` table. @@ -114,11 +137,12 @@ Articles showing possible ways of using pg_cron: -* [Auto-partitioning using pg_partman](https://www.citusdata.com/blog/2018/01/24/citus-and-pg-partman-creating-a-scalable-time-series-database-on-PostgreSQL/) +* [Auto-partitioning using pg_partman](https://www.citusdata.com/blog/2018/01/24/citus-and-pg-partman-creating-a-scalable-time-series-database-on-postgresql/) * [Computing rollups in an analytical dashboard](https://www.citusdata.com/blog/2017/12/27/real-time-analytics-dashboards-with-citus/) * [Deleting old data, vacuum](https://www.citusdata.com/blog/2016/09/09/pgcron-run-periodic-jobs-in-postgres/) * [Feeding cats](http://bonesmoses.org/2016/09/09/pg-phriday-irrelevant-inclinations/) * [Routinely invoking a function](https://fluca1978.github.io/2019/05/21/pgcron.html) +* [Postgres as a cron server](https://supabase.io/blog/2021/03/05/postgres-as-a-cron-server) ## Managed services @@ -130,10 +154,9 @@ | [Alibaba Cloud](https://www.alibabacloud.com/help/doc-detail/150355.htm) | :heavy_check_mark: | | [Amazon RDS](https://aws.amazon.com/rds/postgresql/) | :heavy_check_mark: | | | [Azure](https://azure.microsoft.com/en-us/services/postgresql/) | :heavy_check_mark: | -| [Citus Cloud](https://www.citusdata.com/product/cloud) | :heavy_check_mark: | | [Crunchy Bridge](https://www.crunchydata.com/products/crunchy-bridge/?ref=producthunt) | :heavy_check_mark: | | [DigitalOcean](https://www.digitalocean.com/products/managed-databases/) | :heavy_check_mark: | -| [Google Cloud](https://cloud.google.com/sql/docs/postgres/) | :x: | +| [Google Cloud](https://cloud.google.com/sql/postgresql/) | :heavy_check_mark: | | [Heroku](https://elements.heroku.com/addons/heroku-postgresql) | :x: | | | [ScaleGrid](https://scalegrid.io/postgresql.html) | :heavy_check_mark: | | [Scaleway](https://www.scaleway.com/en/database/) | :heavy_check_mark: | diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pg_cron-1.4.1/expected/pg_cron-test.out new/pg_cron-1.4.2/expected/pg_cron-test.out --- old/pg_cron-1.4.1/expected/pg_cron-test.out 2021-09-25 18:55:16.000000000 +0200 +++ new/pg_cron-1.4.2/expected/pg_cron-test.out 2022-07-15 00:14:59.000000000 +0200 @@ -12,7 +12,6 @@ 1.4 (1 row) -SET cron.enable_superuser_jobs TO on; -- Vacuum every day at 10:00am (GMT) SELECT cron.schedule('0 10 * * *', 'VACUUM'); schedule @@ -190,26 +189,22 @@ pgcron_cront (1 row) --- Try to schedule a job as superuser when it is not allowed -SET cron.enable_superuser_jobs TO off; -SELECT cron.schedule(job_name:='disallowed-superuser', schedule:='* * * * *', command:='drop database pg_crondbno'); -ERROR: cannot schedule jobs as superuser -DETAIL: Scheduling jobs as superuser is disallowed when cron.enable_superuser_jobs is set to off. -SELECT cron.alter_job(7, username := current_user); -ERROR: cannot schedule jobs as superuser -DETAIL: Scheduling jobs as superuser is disallowed when cron.enable_superuser_jobs is set to off. --- Scheduling as other users is allowed as superuser -SELECT cron.schedule_in_database(job_name:='more vacuum', schedule:='0 12 * * *', command:='VACUUM', database:=current_database(), username:='pgcron_cront'); - schedule_in_database ----------------------- - 8 -(1 row) - -SELECT cron.alter_job(7, username := 'pgcron_cront'); - alter_job ------------ - -(1 row) +-- Override function +DROP EXTENSION IF EXISTS pg_cron cascade; +CREATE TABLE test (data text); +DROP TYPE IF EXISTS current_setting cascade; +NOTICE: type "current_setting" does not exist, skipping +CREATE TYPE current_setting AS ENUM ('cron.database_name'); +CREATE OR REPLACE FUNCTION public.func1(text, current_setting) RETURNS text + LANGUAGE sql volatile AS 'INSERT INTO test(data) VALUES (current_user); SELECT current_database();'; +CREATE OR REPLACE FUNCTION public.func1(current_setting) RETURNS text + LANGUAGE sql volatile AS 'INSERT INTO test(data) VALUES (current_user); SELECT current_database();'; +CREATE CAST (current_setting AS text) WITH FUNCTION public.func1(current_setting) AS IMPLICIT; +CREATE EXTENSION pg_cron VERSION '1.4'; +select * from public.test; + data +------ +(0 rows) -- cleaning DROP EXTENSION pg_cron; diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pg_cron-1.4.1/pg_cron--1.4--1.4-1.sql new/pg_cron-1.4.2/pg_cron--1.4--1.4-1.sql --- old/pg_cron-1.4.1/pg_cron--1.4--1.4-1.sql 1970-01-01 01:00:00.000000000 +0100 +++ new/pg_cron-1.4.2/pg_cron--1.4--1.4-1.sql 2022-07-15 00:14:59.000000000 +0200 @@ -0,0 +1,10 @@ +/* pg_cron--1.4--1.4-1.sql */ + +/* + * pg_dump will read from these sequences. Grant everyone permission + * to read from the sequence. That way, a user with usage on the cron + * schema can also do pg_dump. This does not grant write/nextval + * permission. + */ +GRANT SELECT ON SEQUENCE cron.jobid_seq TO public; +GRANT SELECT ON SEQUENCE cron.runid_seq TO public; diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pg_cron-1.4.1/pg_cron.conf new/pg_cron-1.4.2/pg_cron.conf --- old/pg_cron-1.4.1/pg_cron.conf 1970-01-01 01:00:00.000000000 +0100 +++ new/pg_cron-1.4.2/pg_cron.conf 2022-07-15 00:14:59.000000000 +0200 @@ -0,0 +1 @@ +shared_preload_libraries = 'pg_cron' diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pg_cron-1.4.1/pg_cron.control new/pg_cron-1.4.2/pg_cron.control --- old/pg_cron-1.4.1/pg_cron.control 2021-09-25 18:55:16.000000000 +0200 +++ new/pg_cron-1.4.2/pg_cron.control 2022-07-15 00:14:59.000000000 +0200 @@ -1,4 +1,4 @@ comment = 'Job scheduler for PostgreSQL' -default_version = '1.4' +default_version = '1.4-1' module_pathname = '$libdir/pg_cron' relocatable = false diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pg_cron-1.4.1/pg_cron.sql new/pg_cron-1.4.2/pg_cron.sql --- old/pg_cron-1.4.1/pg_cron.sql 2021-09-25 18:55:16.000000000 +0200 +++ new/pg_cron-1.4.2/pg_cron.sql 2022-07-15 00:14:59.000000000 +0200 @@ -1,13 +1,13 @@ DO $$ BEGIN - IF current_database() <> current_setting('cron.database_name') AND current_database() <> 'contrib_regression' THEN + IF pg_catalog.current_database() OPERATOR(pg_catalog.<>) pg_catalog.current_setting('cron.database_name') AND pg_catalog.current_database() OPERATOR(pg_catalog.<>) 'contrib_regression' THEN RAISE EXCEPTION 'can only create extension in database %', - current_setting('cron.database_name') - USING DETAIL = 'Jobs must be scheduled from the database configured in '|| - 'cron.database_name, since the pg_cron background worker '|| + pg_catalog.current_setting('cron.database_name') + USING DETAIL = 'Jobs must be scheduled from the database configured in 'OPERATOR(pg_catalog.||) + 'cron.database_name, since the pg_cron background worker 'OPERATOR(pg_catalog.||) 'reads job descriptions from this database.', - HINT = format('Add cron.database_name = ''%s'' in postgresql.conf '|| - 'to use the current database.', current_database()); + HINT = pg_catalog.format('Add cron.database_name = ''%s'' in postgresql.conf 'OPERATOR(pg_catalog.||) + 'to use the current database.', pg_catalog.current_database()); END IF; END; $$; @@ -16,17 +16,17 @@ CREATE SEQUENCE cron.jobid_seq; CREATE TABLE cron.job ( - jobid bigint primary key default nextval('cron.jobid_seq'), + jobid bigint primary key default pg_catalog.nextval('cron.jobid_seq'), schedule text not null, command text not null, nodename text not null default 'localhost', - nodeport int not null default inet_server_port(), - database text not null default current_database(), + nodeport int not null default pg_catalog.inet_server_port(), + database text not null default pg_catalog.current_database(), username text not null default current_user ); GRANT SELECT ON cron.job TO public; ALTER TABLE cron.job ENABLE ROW LEVEL SECURITY; -CREATE POLICY cron_job_policy ON cron.job USING (username = current_user); +CREATE POLICY cron_job_policy ON cron.job USING (username OPERATOR(pg_catalog.=) current_user); CREATE FUNCTION cron.schedule(schedule text, command text) RETURNS bigint diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pg_cron-1.4.1/sql/pg_cron-test.sql new/pg_cron-1.4.2/sql/pg_cron-test.sql --- old/pg_cron-1.4.1/sql/pg_cron-test.sql 2021-09-25 18:55:16.000000000 +0200 +++ new/pg_cron-1.4.2/sql/pg_cron-test.sql 2022-07-15 00:14:59.000000000 +0200 @@ -3,8 +3,6 @@ ALTER EXTENSION pg_cron UPDATE TO '1.4'; SELECT extversion FROM pg_extension WHERE extname='pg_cron'; -SET cron.enable_superuser_jobs TO on; - -- Vacuum every day at 10:00am (GMT) SELECT cron.schedule('0 10 * * *', 'VACUUM'); @@ -105,15 +103,22 @@ SELECT cron.schedule_in_database(job_name:='his vacuum', schedule:='0 11 * * *', command:='VACUUM',database:=current_database(), username:='pgcron_cront'); SELECT username FROM cron.job where jobid=7; --- Try to schedule a job as superuser when it is not allowed -SET cron.enable_superuser_jobs TO off; +-- Override function +DROP EXTENSION IF EXISTS pg_cron cascade; +CREATE TABLE test (data text); +DROP TYPE IF EXISTS current_setting cascade; +CREATE TYPE current_setting AS ENUM ('cron.database_name'); + +CREATE OR REPLACE FUNCTION public.func1(text, current_setting) RETURNS text + LANGUAGE sql volatile AS 'INSERT INTO test(data) VALUES (current_user); SELECT current_database();'; + +CREATE OR REPLACE FUNCTION public.func1(current_setting) RETURNS text + LANGUAGE sql volatile AS 'INSERT INTO test(data) VALUES (current_user); SELECT current_database();'; -SELECT cron.schedule(job_name:='disallowed-superuser', schedule:='* * * * *', command:='drop database pg_crondbno'); -SELECT cron.alter_job(7, username := current_user); +CREATE CAST (current_setting AS text) WITH FUNCTION public.func1(current_setting) AS IMPLICIT; --- Scheduling as other users is allowed as superuser -SELECT cron.schedule_in_database(job_name:='more vacuum', schedule:='0 12 * * *', command:='VACUUM', database:=current_database(), username:='pgcron_cront'); -SELECT cron.alter_job(7, username := 'pgcron_cront'); +CREATE EXTENSION pg_cron VERSION '1.4'; +select * from public.test; -- cleaning DROP EXTENSION pg_cron; diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pg_cron-1.4.1/src/job_metadata.c new/pg_cron-1.4.2/src/job_metadata.c --- old/pg_cron-1.4.1/src/job_metadata.c 2021-09-25 18:55:16.000000000 +0200 +++ new/pg_cron-1.4.2/src/job_metadata.c 2022-07-15 00:14:59.000000000 +0200 @@ -1359,7 +1359,7 @@ pfree(querybuf.data); if (SPI_processed <= 0) - elog(ERROR, "Job %ld does not exist or you don't own it", jobId); + elog(ERROR, "Job " INT64_FORMAT " does not exist or you don't own it", jobId); SPI_finish(); SetUserIdAndSecContext(savedUserId, savedSecurityContext); diff -urN '--exclude=CVS' '--exclude=.cvsignore' '--exclude=.svn' '--exclude=.svnignore' old/pg_cron-1.4.1/src/pg_cron.c new/pg_cron-1.4.2/src/pg_cron.c --- old/pg_cron-1.4.1/src/pg_cron.c 2021-09-25 18:55:16.000000000 +0200 +++ new/pg_cron-1.4.2/src/pg_cron.c 2022-07-15 00:14:59.000000000 +0200 @@ -238,7 +238,7 @@ NULL, &EnableSuperuserJobs, true, - PGC_USERSET, + PGC_POSTMASTER, GUC_SUPERUSER_ONLY, NULL, NULL, NULL); @@ -268,7 +268,7 @@ gettext_noop("Maximum number of jobs that can run concurrently."), NULL, &MaxRunningTasks, - 32, + (MaxConnections < 32) ? MaxConnections : 32, 0, MaxConnections, PGC_POSTMASTER, @@ -280,7 +280,7 @@ gettext_noop("Maximum number of jobs that can run concurrently."), NULL, &MaxRunningTasks, - 5, + (max_worker_processes - 1 < 5) ? max_worker_processes - 1 : 5, 0, max_worker_processes - 1, PGC_POSTMASTER, @@ -998,6 +998,8 @@ ResetLatch(MyLatch); + CHECK_FOR_INTERRUPTS(); + if (rc & WL_POSTMASTER_DEATH) { /* postmaster died and we should bail out immediately */ @@ -1745,8 +1747,18 @@ default: { int currentPendingRunCount = task->pendingRunCount; + CronJob *job = GetCronJob(jobId); - InitializeCronTask(task, jobId); + /* + * It may happen that job was unscheduled during task execution. + * In this case we keep task as-is. Otherwise, we should + * re-initialize task, i.e. reset fields to initial values including + * status. + */ + if (job != NULL && job->active) + InitializeCronTask(task, jobId); + else + task->state = CRON_TASK_WAITING; /* * We keep the number of runs that should have started while @@ -2028,7 +2040,6 @@ /* Post-execution cleanup. */ disable_timeout(STATEMENT_TIMEOUT, false); CommitTransactionCommand(); - ProcessCompletedNotifies(); pgstat_report_activity(STATE_IDLE, command); pgstat_report_stat(true); @@ -2144,8 +2155,10 @@ * perform internal transaction control. */ oldcontext = MemoryContextSwitchTo(parsecontext); - #if PG_VERSION_NUM >= 100000 - querytree_list = pg_analyze_and_rewrite(parsetree, sql, NULL, 0,NULL); + #if PG_VERSION_NUM >= 150000 + querytree_list = pg_analyze_and_rewrite_fixedparams(parsetree, sql, NULL, 0, NULL); + #elif PG_VERSION_NUM >= 100000 + querytree_list = pg_analyze_and_rewrite(parsetree, sql, NULL, 0, NULL); #else querytree_list = pg_analyze_and_rewrite(parsetree, sql, NULL, 0); #endif
