Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have successfully set up pgAgent daemon (running on the same server as Postgres 9.3). I would like to restrict permissions on pgAgent. Created 'pgagent' login role, and granted it (via group role) all permissions on postgres.pgagent schema:

CREATE ROLE pgagent LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE scheduler
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT scheduler TO pgagent;
grant all on schema pgagent to scheduler;
grant all on all tables in schema pgagent to scheduler;
grant all on all functions in schema pgagent to scheduler;
grant connect on database postgres to scheduler;

However pgAgent refuses to execute any jobs, and just sits there idly. No error messages in logs. If I start pgagent as 'postgres' user it runs fine. Or, if I 'grant postgres to scheduler' it also runs fine.

What permissions am I missing here?

share|improve this question
add comment

1 Answer

Had this same issue!

Looking in the postgres log it appeared pgagent was failing to invoke one of the sequences in order to add entries into the job log. Once I did the below (in postgres 9.3) and restarted my pgagents, all worked well:

GRANT ALL ON ALL SEQUENCES IN SCHEMA pgagent TO pgagent_user;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA pgagent TO pgagent_user;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.