Re: pgadmin's pgagent job scheduler

Ow Mun Heng wrote:
> I've only have 1.4.3 version for centos. (and for gentoo)
> so.. I have to use that version.

We have RPMs for later versions on the pgAdmin site - do they not work?

>>> Thus far, the only serious issue I've seen with it is that for some
>>> reason, I can't or is not able to connect pgadmin running from another
>>> client, connecting to the server, to show the available/scheduled jobs.
>>> (I've already selected the File->options->pgagent jobs options)
>> Make sure you use the same database for the initial connection from
>> pgAdmin (the Maintenance DB on the server dialogue) on the remote
>> machine as pgagent connects to.
>
> I've changed the maintenance DB which it connects to to be postgres DB
> and not it shows up. Eh.. how come? Is this a feature?

I assume you mean 'now' not 'not'? It shows up because the pgAgent
schema is in that database and both pgAdmin and pgAgent only look for it
in the database to which they initially connect.

> Additionally, when I connect as NON-postgres user, I can't get to the
> pgagent schema. How do I grant access to it? pgagent schema doesnt' seem
> to show up as a table.

Hmm, that something that no-ones asked about before. We don't setup
things that way by default because there is a *BIG SECURITY RISK*: all
SQL job steps will run as the user that pgAgent connects to the server
as, and all shell/batch steps will run as the OS user that pgAgent runs
as. This means that any jobs created by non-privileged users will
generally be run by a different user.

You have been warned!!

You'd need to do something like this from the query tool in the
maintenance DB:

CREATE USAGE ON SCHEMA pgagent TO role;

GRANT EXECUTE ON FUNCTION pgagent.pga_next_schedule(int4, timestamptz,
timestamptz, _bool, _bool, _bool, _bool, _bool) TO role;

GRANT EXECUTE ON FUNCTION pgagent.pga_is_leap_year(int2) TO role;

GRANT EXECUTE ON FUNCTION pgagent.pga_schedule_trigger() TO role;

CRANT EXECUTE ON FUNCTION pgagent.pga_exception_trigger() TO role;

And then grant the desired update/delete/select/insert permissions on
the tables:

pgagent.pga_jobagent
pgagent.pga_jobclass
pgagent.pga_job
pgagent.pga_jobstep
pgagent.pga_schedule
pgagent.pga_exception
pgagent.pga_joblog
pgagent.pga_jobsteplog

Regards, Dave