Re: Using psql, pg_dump, pg_restore in a script

On Mon, Jun 16, 2014 at 8:53 PM, John Scalia <jayknowsunix(at)gmail(dot)com> wrote:

> My latest task requires me to script extracting the latest data from a
> partitioned table and put it into another database on a different machine
> on an hourly basis. To do this, the script uses a psql command to determine
> which partition to grab, a pg_dump to get it, and pg_restore to put into
> the other Db. My issue is that I'd like to make this script very portable
> but it seems that whenever you pass a "-h <somehost> -U <someuser>" to
> these commands, they will ask for a password whether "someuser" actually
> has a password or not. I've currently set a password for the user the
> script uses and I'm using .pgpass to make this work, but was there some
> reason that an option was not given to enter a password as an argument to
> the command? Maybe something like --password <somepassword>? I know this
> could potentially be a security problem, but so is having a password in the
> .pgpass file in clear text.
>

On some operating system you can see arguments to program currently running
- for example, on linux, you see:

postgres 18804 0.0 0.2 663664 37676 ? S Jun14 0:08
\_ /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c
config_file=/etc/postgresql/9.3/main/postgresql.conf

Giving password as command line option would show it to everybody.

On the other hand - plaintext file should have privileges set so that
noone, except for owner of the account from which you're running
psql/pg_dump/pg_restore, can read it.

If you prefer, you can set PGPASSWORD environment variable - it will also
be used by all 3 of these programs (and most other).

depesz