Thread: user privilages for executing pg_autovacuum?
For pg_dump minimum privilages is to have select right on tables.
For vacuumdb, one must be owner of tables or a superuser.
What are minimum rights for user that is trying to execute pg_autovacuum ?
How can I monitor vacuuming by pg_autovacuum ?
Thanks,
Zlatko
Zlatko Matić wrote: > For pg_dump minimum privilages is to have select right on tables. > For vacuumdb, one must be owner of tables or a superuser. > > What are minimum rights for user that is trying to execute pg_autovacuum ? Not sure exactly, you need to have permission to vacuum every table in the database including system tables. > > How can I monitor vacuuming by pg_autovacuum ? It logs all its activity if you set the debug option to an appropriate level. Try -d1 or -d2.
That's the reason why I ask. If a user that executes pg_autovacuum must be owner of tables or a superuser, that it is a security problem to pass password as plain text... How peple solve this problem ? Thanks, Zlatko ----- Original Message ----- From: "Matthew T. O'Connor" <matthew@zeut.net> To: "Zlatko Matić" <zlatko.matic1@sb.t-com.hr> Cc: <pgsql-general@postgresql.org> Sent: Tuesday, October 11, 2005 4:00 PM Subject: Re: [GENERAL] user privilages for executing pg_autovacuum? Zlatko Matić wrote: > For pg_dump minimum privilages is to have select right on tables. > For vacuumdb, one must be owner of tables or a superuser. > What are minimum rights for user that is trying to execute pg_autovacuum > ? Not sure exactly, you need to have permission to vacuum every table in the database including system tables. > How can I monitor vacuuming by pg_autovacuum ? It logs all its activity if you set the debug option to an appropriate level. Try -d1 or -d2. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
=?iso-8859-2?Q?Zlatko_Mati=E6?= <zlatko.matic1@sb.t-com.hr> writes:
> That's the reason why I ask. If a user that executes pg_autovacuum must be
> owner of tables or a superuser, that it is a security problem to pass
> password as plain text...
> How peple solve this problem ?
Put the password in a ~/.pgpass file belonging to the user that runs the
autovacuum task.
regards, tom lane
On Tue, Oct 11, 2005 at 02:39:24PM -0400, Tom Lane wrote: > =?iso-8859-2?Q?Zlatko_Mati=E6?= <zlatko.matic1@sb.t-com.hr> writes: > > That's the reason why I ask. If a user that executes pg_autovacuum must be > > owner of tables or a superuser, that it is a security problem to pass > > password as plain text... > > How peple solve this problem ? > > Put the password in a ~/.pgpass file belonging to the user that runs the > autovacuum task. Or you can run pg_autovacuum on the server itself and allow ident authentication for unix sockets (assuming you're on unix/linux). -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
No, I'm on Windows... ----- Original Message ----- From: "Jim C. Nasby" <jnasby@pervasive.com> To: "Tom Lane" <tgl@sss.pgh.pa.us> Cc: "Zlatko Mati?" <zlatko.matic1@sb.t-com.hr>; "Matthew T. O'Connor" <matthew@zeut.net>; <pgsql-general@postgresql.org> Sent: Wednesday, October 12, 2005 1:14 AM Subject: Re: [GENERAL] user privilages for executing pg_autovacuum? > On Tue, Oct 11, 2005 at 02:39:24PM -0400, Tom Lane wrote: >> =?iso-8859-2?Q?Zlatko_Mati=E6?= <zlatko.matic1@sb.t-com.hr> writes: >> > That's the reason why I ask. If a user that executes pg_autovacuum must >> > be >> > owner of tables or a superuser, that it is a security problem to pass >> > password as plain text... >> > How peple solve this problem ? >> >> Put the password in a ~/.pgpass file belonging to the user that runs the >> autovacuum task. > > Or you can run pg_autovacuum on the server itself and allow ident > authentication for unix sockets (assuming you're on unix/linux). > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq
If I put password in pgpass file it's still a plain text. How to hide it ? ----- Original Message ----- From: "Jim C. Nasby" <jnasby@pervasive.com> To: "Tom Lane" <tgl@sss.pgh.pa.us> Cc: "Zlatko Mati?" <zlatko.matic1@sb.t-com.hr>; "Matthew T. O'Connor" <matthew@zeut.net>; <pgsql-general@postgresql.org> Sent: Wednesday, October 12, 2005 1:14 AM Subject: Re: [GENERAL] user privilages for executing pg_autovacuum? > On Tue, Oct 11, 2005 at 02:39:24PM -0400, Tom Lane wrote: >> =?iso-8859-2?Q?Zlatko_Mati=E6?= <zlatko.matic1@sb.t-com.hr> writes: >> > That's the reason why I ask. If a user that executes pg_autovacuum must >> > be >> > owner of tables or a superuser, that it is a security problem to pass >> > password as plain text... >> > How peple solve this problem ? >> >> Put the password in a ~/.pgpass file belonging to the user that runs the >> autovacuum task. > > Or you can run pg_autovacuum on the server itself and allow ident > authentication for unix sockets (assuming you're on unix/linux). > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq
OK. but, is it required that the user is a superuser, owner of tables or just needs to have select rights on tables? Thanks, Zlatko ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Zlatko Matić" <zlatko.matic1@sb.t-com.hr> Cc: "Matthew T. O'Connor" <matthew@zeut.net>; <pgsql-general@postgresql.org> Sent: Tuesday, October 11, 2005 8:39 PM Subject: Re: [GENERAL] user privilages for executing pg_autovacuum? > =?iso-8859-2?Q?Zlatko_Mati=E6?= <zlatko.matic1@sb.t-com.hr> writes: >> That's the reason why I ask. If a user that executes pg_autovacuum must >> be >> owner of tables or a superuser, that it is a security problem to pass >> password as plain text... >> How peple solve this problem ? > > Put the password in a ~/.pgpass file belonging to the user that runs the > autovacuum task. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
I run pg_autovacuum as user postgres: owner of the database processes and an unprivileged user (nologin) ""Zlatko Mati�"" <zlatko.matic1@sb.t-com.hr> wrote in message news:000001c5ce4f$85121460$52bffea9@zlatkovyfkpgz6... For pg_dump minimum privilages is to have select right on tables. For vacuumdb, one must be owner of tables or a superuser. What are minimum rights for user that is trying to execute pg_autovacuum ? How can I monitor vacuuming by pg_autovacuum ? Thanks, Zlatko
AFAIK you can't, and there's not really much point anyway. Anyone with taccess to that file will be able to connect to the database. Have you looked at using ident authentication on localhost? On Wed, Oct 12, 2005 at 10:12:31AM +0200, Zlatko Matic wrote: > If I put password in pgpass file it's still a plain text. How to hide it ? > > ----- Original Message ----- > From: "Jim C. Nasby" <jnasby@pervasive.com> > To: "Tom Lane" <tgl@sss.pgh.pa.us> > Cc: "Zlatko Mati?" <zlatko.matic1@sb.t-com.hr>; "Matthew T. O'Connor" > <matthew@zeut.net>; <pgsql-general@postgresql.org> > Sent: Wednesday, October 12, 2005 1:14 AM > Subject: Re: [GENERAL] user privilages for executing pg_autovacuum? > > > >On Tue, Oct 11, 2005 at 02:39:24PM -0400, Tom Lane wrote: > >>=?iso-8859-2?Q?Zlatko_Mati=E6?= <zlatko.matic1@sb.t-com.hr> writes: > >>> That's the reason why I ask. If a user that executes pg_autovacuum must > >>> be > >>> owner of tables or a superuser, that it is a security problem to pass > >>> password as plain text... > >>> How peple solve this problem ? > >> > >>Put the password in a ~/.pgpass file belonging to the user that runs the > >>autovacuum task. > > > >Or you can run pg_autovacuum on the server itself and allow ident > >authentication for unix sockets (assuming you're on unix/linux). > >-- > >Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > >Pervasive Software http://pervasive.com work: 512-231-6117 > >vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > > >---------------------------(end of broadcast)--------------------------- > >TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
No, I didn't try ident authentication... It seems to me that security issues should be passd to client company's system administrator ? ----- Original Message ----- From: "Jim C. Nasby" <jnasby@pervasive.com> To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr> Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Matthew T. O'Connor" <matthew@zeut.net>; <pgsql-general@postgresql.org> Sent: Thursday, October 13, 2005 9:35 PM Subject: Re: [GENERAL] user privilages for executing pg_autovacuum? > AFAIK you can't, and there's not really much point anyway. Anyone with > taccess to that file will be able to connect to the database. > > Have you looked at using ident authentication on localhost? > > On Wed, Oct 12, 2005 at 10:12:31AM +0200, Zlatko Matic wrote: >> If I put password in pgpass file it's still a plain text. How to hide it >> ? >> >> ----- Original Message ----- >> From: "Jim C. Nasby" <jnasby@pervasive.com> >> To: "Tom Lane" <tgl@sss.pgh.pa.us> >> Cc: "Zlatko Mati?" <zlatko.matic1@sb.t-com.hr>; "Matthew T. O'Connor" >> <matthew@zeut.net>; <pgsql-general@postgresql.org> >> Sent: Wednesday, October 12, 2005 1:14 AM >> Subject: Re: [GENERAL] user privilages for executing pg_autovacuum? >> >> >> >On Tue, Oct 11, 2005 at 02:39:24PM -0400, Tom Lane wrote: >> >>=?iso-8859-2?Q?Zlatko_Mati=E6?= <zlatko.matic1@sb.t-com.hr> writes: >> >>> That's the reason why I ask. If a user that executes pg_autovacuum >> >>> must >> >>> be >> >>> owner of tables or a superuser, that it is a security problem to pass >> >>> password as plain text... >> >>> How peple solve this problem ? >> >> >> >>Put the password in a ~/.pgpass file belonging to the user that runs >> >>the >> >>autovacuum task. >> > >> >Or you can run pg_autovacuum on the server itself and allow ident >> >authentication for unix sockets (assuming you're on unix/linux). >> >-- >> >Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com >> >Pervasive Software http://pervasive.com work: 512-231-6117 >> >vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 >> > >> >---------------------------(end of broadcast)--------------------------- >> >TIP 3: Have you checked our extensive FAQ? >> > >> > http://www.postgresql.org/docs/faq >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: don't forget to increase your free space map settings >> > > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match