How to use pgAgent Securely with PostgreSQL [guide]

pgAgent is a scheduling agent for PostgreSQL which is used to run scheduled batch/shell or SQL tasks. pgAgent doesn’t come bundled with PostgreSQL, it needs to be installed and set-up separately as it run as a stand alone daemon. pgAgent being a powerful tool has some security concerns to be aware of. This post describes, how to set up pgAgent in a way that security threats are minimized.

1. Limit privileges for the operating system user

pgAgent will run the batch or shell script as the operating system user under which it’s daemon is running. So, it is advised to create a separate user with limited privileges for running pgAgent daemon. In a Unix environment, the following command will do it:

sudo useradd pgagent

Limit the privileges of the user such that it is not granted access to anything other than the scripts and related objects.

2. Setting permissions for the Database user

It is always advised not to use a user with superuser privileges for pgAgent. Instead, create a normal user and grant access to the objects which are required by the Job. For instance, here we created a user pgagent and granted access to all objects in pgagent schema.

CREATE ROLE pgagent LOGIN  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

GRANT ALL on SCEHEMA pgagent to pgagent;

GRANT ALL ON ALL TABLES IN SCHEMA pgagent to pgagent;

GRANT ALL ON ALL SEQUENCES IN SCHEMA pgagent TO pgagent;

Now, lets assume we have a pgAgent Job which delete all logs, which are older than a certain amount of time, from the table logs , located in schema, test_schema of database, test_db. We have to again set permissions as follows:

GRANT CONNECT ON DATABASE test_db TO pgagent;

GRANT USAGE ON SCHEMA test_schema TO pgagent;

GRANT DELETE ON TABLE test_schema.logs TO pgagent;

In this way we have to grant access to all objects like, tables, functions and sequences which are required by the Job.

Finally, we can start the pgAgent daemon by running a command like the one below from terminal:

sudo su pgagent
pgagent hostaddr=localhsot dbname=postgres user=pgagent port=5432 -l 1

So, that’s it. Any questions and suggestions are most welcomed!