Re: pgadmin's pgagent job scheduler - Mailing list pgsql-general

On Tue, 2007-10-16 at 10:35 +0100, Dave Page wrote:
> 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?

I'm not sure. I didn't try and the latest RPM was for 1.6.3 (sine 1.8 is
beta but works fine on windows)

I didnt' want to test it on my "server". I'll give it a whirl on my
centos virtualmachine later on.

>
> >>> 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'?

Typo.. sorry..

>  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.

I see.. and in the docs, I was told to import pgagent.sql into the
postgres DB so I did that. but actually. I want to deploy it to the
target DB, let's call it "MyDB". Should I have imported it to MyDB
instead?

>
> > 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.

never ask.. never know..

> 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!!

Woo... Okay.. I didn't know. (see, it pays to ask) If this is the case,
then I would say that the docs don't do it justice. (perhaps it's me,
but I had to muck around it for a few hours just to get it to work,
which it now does..)

Okay.. so, now it pgagent connects to the DB as user postgres (as I was
instructed by the docs) and runs as user postgres (which is a risk). How
do I connect as a less priv user? (I have a user, named "operator" which
does menial tasks like connect to the DB, pull data, crunch etc..)

I most certainly don't want it to run as user postgres. User operator
would be better.

I'll give it a try later on.. (but would appreciate a response
nonetheless)

>
>
>
> 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