Обсуждение: BUG #1567: can't hide password with pg_autovacuum
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 :(
I believe that pg_autovacuum will work with a .pgpass file just like any libpq based application. 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 > > >
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
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
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.
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
Le Tuesday 29 March 2005 00:40, vous avez =E9crit : > 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= =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 :). > > -------------------------------------------------------------------------= -- > > 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 (n= ot > > 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=3Dpass > > 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
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.