Thread: about "pg_dump " without pompt password

Hello all,

Is it possible that we setup the password in the pg_dump command line
instead of let users input it through prompt command. E.g.,

 pg_dump test -c -d --host=localhost -U testUser1 --file='a.dmp'
--no-privileges

Thanks a lot!
Ly

Ying Lu <ying_lu@cs.concordia.ca> writes:
> Is it possible that we setup the password in the pg_dump command line

You might as well put it on a billboard --- anything in the command line
can be seen by anyone who runs "ps".

If you don't want to supply it manually, put it in ~/.pgpass.

            regards, tom lane

David Garamond <lists@zara.6.isreserved.com> writes:
> Tom Lane wrote:
>>> Is it possible that we setup the password in the pg_dump command line
>>
>> You might as well put it on a billboard --- anything in the command line
>> can be seen by anyone who runs "ps".
>>
>> If you don't want to supply it manually, put it in ~/.pgpass.

> At least in Linux, mysql replaces the password in the command line
> argument with "xxxxxxxx" so you can't see them via "ps" nor via peeking
> into /proc/<PID>/cmdline.

> There is a short period where the password is visible though.

> Are there any other risks? Or is the reason for not doing this is
> because not all OS'es supports replacing the command line information?

You just enumerated two fatal strikes against it; do you need more?
If so, consider the question of where the password on the command line
is going to come from.  Allowing that would encourage people to put
passwords into possibly-insecurely-stored scripts.  Or, depending on how
complicated the shell script is, there might be ancestor shell processes
that also have the password visible in their arguments ... and they
are certainly not going to know to xxx it out.

The ~/.pgpass technique is secure on every Unix, and we can *check* that
it's secure, by refusing to use .pgpass if it's got group or world
access allowed.

            regards, tom lane

Tom Lane wrote:
>>Is it possible that we setup the password in the pg_dump command line
>
> You might as well put it on a billboard --- anything in the command line
> can be seen by anyone who runs "ps".
>
> If you don't want to supply it manually, put it in ~/.pgpass.

At least in Linux, mysql replaces the password in the command line
argument with "xxxxxxxx" so you can't see them via "ps" nor via peeking
into /proc/<PID>/cmdline.

There is a short period where the password is visible though.

Are there any other risks? Or is the reason for not doing this is
because not all OS'es supports replacing the command line information?

--
dave

Tom Lane wrote:
>>At least in Linux, mysql replaces the password in the command line
>>argument with "xxxxxxxx" so you can't see them via "ps" nor via peeking
>>into /proc/<PID>/cmdline.
>
>>There is a short period where the password is visible though.
>
>>Are there any other risks? Or is the reason for not doing this is
>>because not all OS'es supports replacing the command line information?
>
> You just enumerated two fatal strikes against it; do you need more?
> If so, consider the question of where the password on the command line
> is going to come from.  Allowing that would encourage people to put
> passwords into possibly-insecurely-stored scripts.  Or, depending on how
> complicated the shell script is, there might be ancestor shell processes
> that also have the password visible in their arguments ... and they
> are certainly not going to know to xxx it out.

Yeah, I have some Perl/Ruby scripts that does "wget --proxy-user ...
--proxy-passwd ..." that reports the output through crontab and I have
to do the XXX-ing manually to prevent everyone that receives the cron
output to read the username/password. Should've stored the password in
~/.wgetrc too, I guess.

> The ~/.pgpass technique is secure on every Unix, and we can *check* that
> it's secure, by refusing to use .pgpass if it's got group or world
> access allowed.

I love the Postgres community. It's all about doing things _properly_. :-)

--
dave