Обсуждение: Suggestion for pgAgent
Dear hackers, I asked this question on pgadmin-general before, and there was no reply. Maybe this list is more appropriate. When you configure an SQL job step, you can configure a database on which the script should run, but you cannot specify a database user. I'd like to define schedules for various database users, and while it would be possible to work around the limitation by writing some wrapper functions in the database, the best thing from my perspective would be to enhance pgAgent (and pgAdmin III) to support a database user that pgAgent uses to connect to execute a certain job step. Is that an option? I think it would be a valuable addition, and it would alleviate one of the "security concerns" stated in the documentation. Yours, Laurenz Albe
On Mon, Nov 16, 2009 at 8:07 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > Dear hackers, > > I asked this question on pgadmin-general before, and > there was no reply. Maybe this list is more appropriate. > > When you configure an SQL job step, you can configure a database > on which the script should run, but you cannot specify a database user. > > I'd like to define schedules for various database users, and > while it would be possible to work around the limitation by > writing some wrapper functions in the database, the best thing > from my perspective would be to enhance pgAgent (and pgAdmin III) > to support a database user that pgAgent uses to connect to > execute a certain job step. > > Is that an option? I think it would be a valuable addition, > and it would alleviate one of the "security concerns" stated > in the documentation. Sure, it's definitely an option - provided it can be done in a secure way. Want to work on it? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page wrote: > > I asked this question on pgadmin-general before, and > > there was no reply. Maybe this list is more appropriate. > > > > When you configure an SQL job step, you can configure a database > > on which the script should run, but you cannot specify a database user. > > > > I'd like to define schedules for various database users, and > > while it would be possible to work around the limitation by > > writing some wrapper functions in the database, the best thing > > from my perspective would be to enhance pgAgent (and pgAdmin III) > > to support a database user that pgAgent uses to connect to > > execute a certain job step. > > > > Is that an option? I think it would be a valuable addition, > > and it would alleviate one of the "security concerns" stated > > in the documentation. > > Sure, it's definitely an option - provided it can be done in a secure > way. Want to work on it? I should be able to change pgAgent accordingly, but I am not an accomplished GUI programmer and have no experience with wxWidgets, so I don't want to promise that I can do the necessary modifications in pgAdmin. Can you think of any security concerns? Yours, Laurenz Albe
On Tue, Nov 17, 2009 at 7:50 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: >> Sure, it's definitely an option - provided it can be done in a secure >> way. Want to work on it? > > I should be able to change pgAgent accordingly, but I am not an > accomplished GUI programmer and have no experience with wxWidgets, > so I don't want to promise that I can do the necessary modifications > in pgAdmin. We can help with that. It should be fairly trivial from a GUI perspective. > Can you think of any security concerns? Privilege escalation. Currently, pgAgent relies on the security of the schema to prevent unauthorised users from creating jobs that run as the pgagent operating system user (typically 'postgres'). If you leave that as-is, and just allow a connection username to be specified, we shouldn't have a problem, but if the schema is opened up to allow users to schedule jobs by default, then there's potentially a big issue. In further thought though - why can't you just set the target database of the step to be 'remote' and then specify a connection string with a specified username? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page wrote: > In further thought though - why can't you just set the target database > of the step to be 'remote' and then specify a connection string with a > specified username? I am running version 1.8 here, and this feature is new in 1.10, that's why I didn't see it. You are right, that completely solves my problem and there is no need for an additional user field. Thanks! Yours, Laurenz Albe