citusdata / citusdata/pg_cron

pg_cron databases unusable in clouds where postgresql user not provided

Open
#137 0 comments 8 reactions 0 assignees View on GitHub
Dominant language
C
Stars
3.9k
Forks
260
Avg merge
5d 5h
Merged PRs (30d)
1

Description

In some clouds the postgres user is not given, but another admin user is given instead. For example, on digital ocean, the doadmin user is given with admin privileges. But DO is not the only one that does that, it's fairly common practice.

However `CREATE EXTENSION pg_cron` creates its schema and tables with owner postgres.

pg_cron is unusable in that environments unless the default database is being used for everything.

This doesn't work:
```
CREATE SCHEMA cron AUTHORIZATION doadmin;
CREATE EXTENSION pg_cron WITH SCHEMA cron;
```

I'm also unable to use the https://github.com/citusdata/pg_cron/tree/beta-2.0#advanced-usage trick by changing the database field with an UPDATE/INSERT (permission denied because only postgres user have grants on the cron.job database).

Contributor guide

No contributing guide indexed for this repository

Research direction

Start by reproducing the documented CREATE SCHEMA cron AUTHORIZATION doadmin and CREATE EXTENSION pg_cron WITH SCHEMA cron commands in PostgreSQL, then compare them with the advanced-usage UPDATE/INSERT attempt. The work is done when a cloud-provided administrator can install and configure pg_cron without the postgres user, while preserving appropriate ownership and permissions.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.