The following bug has been logged online:

Bug reference: 1567
Logged by: Olivier Thauvin
Email address: nanardon (AT) nanardon (DOT) homelinux.org
PostgreSQL version: 8.0.1
Operating system: Linux (Mandrake cooker)
Description: can't hide password with pg_autovacuum
Details:

I found an security with pg_autovacuum
After looking the README and --help, it seems there is no way to start it
with a configuration file.

This is not a problem except when the database is password protected, so you
have to use -P option to get it started (no prompt excpet I missed
something).

The potential issue come from ps, the password is show in clear:

nanardon 28664 0.4 0.0 3644 1384 ? Ss 04:05 0:00 pg_autovacuum
-D -s rpm2sql -PXXXXXX

XXXXXX is my password in clear (hidden here of course).
As you can see, there is enought information here for someone having an
account on the host to connect to DB with admin privileges on the DB (not as
postgres user of course, but only the owner of the db can vacuum).

Solution:
- change the command line after start like some ftp client does
- having the possiblility to read password from a file
- taking password from envirronment variable (AUTOVACUUM_PASS=pass
pg_autovacuum...)

If I have any time, I will try to provide a patch, but my knowledge in C are
too poor to ensure quality

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

I believe that pg_autovacuum will work with a .pgpass file just like any
libpq based application.

Olivier Thauvin wrote:


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

The typical way to do this is to use .pgpass in the user's home
directory. Does that help?

---------------------------------------------------------------------------

Olivier Thauvin wrote:
--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

This is a multi-part message in MIME format.
--------------070502090904040106030104
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Matthew T. O'Connor wrote:
It should, yes. I've applied the attached patch to HEAD and
REL8_0_STABLE that makes the security warning about -P more broad, and
suggests using ~/.pgpass instead.

-Neil

--------------070502090904040106030104
Content-Type: text/x-patch;
name="pg_autovac_password_doc-1.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="pg_autovac_password_doc-1.patch"

Index: contrib/pg_autovacuum/README.pg_autovacuum
================================================== =================
RCS file: /cvsroot/pgsql/contrib/pg_autovacuum/README.pg_autovacuum,v
retrieving revision 1.12
diff -c -r1.12 README.pg_autovacuum
*** contrib/pg_autovacuum/README.pg_autovacuum 24 Jan 2005 00:17:33 -0000 1.12
--- contrib/pg_autovacuum/README.pg_autovacuum 30 Mar 2005 05:03:55 -0000
***************
*** 36,44 ****

pg_autovacuum requires that the statistics system be enabled and
reporting row level stats. The overhead of the stats system has been
! shown to have a significant cost under certain workloads. For
! instance, a tight loop of queries performing "select 1" was found to
! run nearly 30% slower when stats were enabled. However, in practice,
with more realistic workloads, the stats system overhead is usually
nominal.

--- 36,44 ----

pg_autovacuum requires that the statistics system be enabled and
reporting row level stats. The overhead of the stats system has been
! shown to be significant under certain workloads. For instance, a
! tight loop of queries performing "select 1" was found to run nearly
! 30% slower when row-level stats were enabled. However, in practice,
with more realistic workloads, the stats system overhead is usually
nominal.

***************
*** 127,134 ****
-U username: Username pg_autovacuum will use to connect with, if not
specified the current username is used.
-P password: Password pg_autovacuum will use to connect with. *WARNING*
! When installed as a Windows Service, this option will be stored in plain
! text in the registry.
-H host: host name or IP to connect to.
-p port: port used for connection.
-h help: list of command line options.
--- 127,137 ----
-U username: Username pg_autovacuum will use to connect with, if not
specified the current username is used.
-P password: Password pg_autovacuum will use to connect with. *WARNING*
! This option is insecure. When installed as a Windows Service, this
! option will be stored in plain text in the registry. When used with
! most Unix variants, other users will be able to see the argument to
! the "-P" option via ps(1). The ~/.pgpass file can be used to
! specify a password more securely.
-H host: host name or IP to connect to.
-p port: port used for connection.
-h help: list of command line options.

--------------070502090904040106030104
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--------------070502090904040106030104--

On Wed, Mar 30, 2005 at 03:06:44PM +1000, Neil Conway wrote:
I believe on Windows the file is called pgpass.conf instead?

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"La Primavera ha venido. Nadie sabe como ha sido" (A. Machado)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Alvaro Herrera wrote:
The SGML docs pretty consistently only mention "~/.pgpass" when
referencing this feature, so I didn't bother mentioning the other name
in the pg_autovacuum README. Perhaps someone should fix the SGML docs to
refer to both, or perhaps to speak in terms of a general "libpq password
file" feature or some such.

-Neil

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Neil Conway wrote:
Every place the docs mention just .pgpass they reference the section on
pgpass, which shows the Win32 naming. Seems OK to me.

--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--nextPart1872310.EQusavoHXO
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Le Tuesday 29 March 2005 00:40, vous avez =E9crit :
Yes it help, but:
=2D please notice the issue about ps into the README
=2D the .pgpass doesn't work on my configuration:

[postgres@virgo pgsql]$ pg_autovacuum
[2005-03-29 04:47:32 CEST] ERROR: Failed connection to database template1=
=20
with error: fe_sendauth: no password supplied
=2E
[2005-03-29 04:47:32 CEST] ERROR: Failed connection to database template1=
=20
with error: fe_sendauth: no password supplied
=2E
[2005-03-29 04:47:32 CEST] ERROR: Cannot connect to template1, exiting.

When permission are bad on .pgpass (other than 600), it complain, but faile=
d=20
to connect on my server. Notice I have seting up access to 'password' to al=
l=20
connection in my pg_hba.conf. psql... work fine and the password in .pgpass=
=20
is ok.

Maybe I will workaround by setting postgres user access as 'trust' for loca=
l=20
connection only, but I have to reread the doc before .

--nextPart1872310.EQusavoHXO
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQBCSMN8k29cDOWzfVARAo3rAJwLcglOtgzffX/aVEvwdNkxxLjMvwCg7qKz
bPYl1ru385z13pmYpLKTdzc=
=ao6/
-----END PGP SIGNATURE-----

--nextPart1872310.EQusavoHXO--

On Tue, Mar 29, 2005 at 03:54:48 +0100,
Olivier Thauvin <olivier.thauvin (AT) aerov (DOT) jussieu.fr> wrote:
Ident is better if you can use domain sockets and the equivalent of
getpeerid on your OS.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org