> > > How to use pgAgent Securely with PostgreSQL [guide]

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.Use pgagent securely

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!

The following two tabs change content below.

Vipin Raj

Vipin Raj is a software developer specialized in PostgreSQL Database and Data Modeling, the man behind technobytz and an IoT and Security enthusiast. Having 3+ years of experience in the IT industry, he is currently pursuing his masters in computer science and information security. He spend his free time writing blog posts with the intension of sharing his knowledge to the tech community.

2 thoughts on “How to use pgAgent Securely with PostgreSQL [guide]

Leave a Reply

Your email address will not be published. Required fields are marked *