How do you use pgAgent with securely

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(installation)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.

Security concerns:

Database password - DO NOT be tempted to include a password in the pgAgent connection string - on Unix systems it may be visible to all users in ‘ps’ output, and on Windows systems it will be stored in the registry in plain text. Instead, use a libpq ~/.pgpass file to store the passwords for every database that pgAgent must access. Details of this technique may be found in the PostgreSQL documentation on .pgpass file.

System/database access - all jobs run by pgAgent will run with the security privileges of the pgAgent user. SQL steps will run as the user that pgAgent connects to the database as, and batch/shell scripts will run as the operating system user that the pgAgent service or daemon is running under. Because of this, it is essential to maintain control over the users that are able to create and modify jobs. By default, only the user that created the pgAgent database objects will be able to do this - this will normally be the PostgreSQL superuser.

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

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