BUG #1567: can't hide password with pg_autovacuum

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

Olivier Thauvin wrote:

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

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

Olivier Thauvin wrote:

The following bug has been logged online:

Bug reference: 1567
Logged by: Olivier Thauvin
Email address: nanardon@nanardon.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

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.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

Le Tuesday 29 March 2005 00:40, vous avez écrit :

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

Yes it help, but:
- please notice the issue about ps into the README
- 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
with error: fe_sendauth: no password supplied
.
[2005-03-29 04:47:32 CEST] ERROR: Failed connection to database template1
with error: fe_sendauth: no password supplied
.
[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 failed
to connect on my server. Notice I have seting up access to 'password' to all
connection in my pg_hba.conf. psql... work fine and the password in .pgpass
is ok.

Maybe I will workaround by setting postgres user access as 'trust' for local
connection only, but I have to reread the doc before :).

Matthew T. O'Connor wrote:

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

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

On Wed, Mar 30, 2005 at 03:06:44PM +1000, Neil Conway wrote:

Matthew T. O'Connor wrote:

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

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.

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)

Alvaro Herrera wrote:

I believe on Windows the file is called pgpass.conf instead?

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

Neil Conway wrote:

Alvaro Herrera wrote:

I believe on Windows the file is called pgpass.conf instead?

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.

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

On Tue, Mar 29, 2005 at 03:54:48 +0100,
Olivier Thauvin <olivier.thauvin@aerov.jussieu.fr> wrote:

Maybe I will workaround by setting postgres user access as 'trust' for local
connection only, but I have to reread the doc before :).

Ident is better if you can use domain sockets and the equivalent of
getpeerid on your OS.