Thread: pgadmin's pgagent job scheduler
Does anyone from this list here uses pgagent from pgadmin? it's a job schedular much like cron which is sort of integrated w/ pgadmin3. Only issue which I've found so far which I don't quite like is that to be able to use pgagent, I have to also pull in the entire pgadmin3 incl wxGTK and X. This isn't a good thing on a linux server. (even though X is never used) Anyway.. I've installed it from pgadmin-1.4.3 and it's running fine thus far w/ the jobs and all. (there are some quirks, but that's another story). 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)
Ow Mun Heng wrote: > Does anyone from this list here uses pgagent from pgadmin? > > it's a job schedular much like cron which is sort of integrated w/ > pgadmin3. Only issue which I've found so far which I don't quite like is > that to be able to use pgagent, I have to also pull in the entire > pgadmin3 incl wxGTK and X. This isn't a good thing on a linux server. > (even though X is never used) > > Anyway.. I've installed it from pgadmin-1.4.3 and it's running fine thus > far w/ the jobs and all. (there are some quirks, but that's another > story). Use 1.6.3's version or even 1.8RC1's - they should be much lighter on the library front. > 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. Regards, Dave.
On Tue, 2007-10-16 at 09:56 +0100, Dave Page wrote: > Ow Mun Heng wrote: > > Does anyone from this list here uses pgagent from pgadmin? > > > > it's a job schedular much like cron which is sort of integrated w/ > > pgadmin3. Only issue which I've found so far which I don't quite like is > > that to be able to use pgagent, I have to also pull in the entire > > pgadmin3 incl wxGTK and X. This isn't a good thing on a linux server. > > (even though X is never used) > > > > Anyway.. I've installed it from pgadmin-1.4.3 and it's running fine thus > > far w/ the jobs and all. (there are some quirks, but that's another > > story). > > Use 1.6.3's version or even 1.8RC1's - they should be much lighter on > the library front. I've only have 1.4.3 version for centos. (and for gentoo) so.. I have to use that version. > > > 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? 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.
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
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
Ow Mun Heng wrote: > 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? No, postgres is normal. You can specify with each job step which database it should run on. That way you can have a single job touching multiple databases. >>> 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.. :-) > 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. Just change the pgAgent connection string to use the operator role, and run the daemon under a similarly non-privileged user account. You'll also need to make sure you've granted appropriate permissions on the schema to allow the operator role to update rows etc (which happens when a job is run to set the next runtime). Regards, Dave
On Tue, 2007-10-16 at 11:10 +0100, Dave Page wrote: > Ow Mun Heng wrote: > > 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? > > No, postgres is normal. You can specify with each job step which > database it should run on. That way you can have a single job touching > multiple databases. I found out that I also needed to put in the whole schema.tablename notation. > > 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. > > Just change the pgAgent connection string to use the operator role, and > run the daemon under a similarly non-privileged user account. You'll > also need to make sure you've granted appropriate permissions on the > schema to allow the operator role to update rows etc (which happens when > a job is run to set the next runtime). Okay.. so use the example you sent/provided previously. Cool.. Thanks a bunch..