Thread: Using psql, pg_dump, pg_restore in a script

Using psql, pg_dump, pg_restore in a script

From
John Scalia
Date:
Hi all,

My latest task requires me to script extracting the latest data from a partitioned table and put it into another
databaseon 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
otherDb. 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
passwordwhether "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
reasonthat 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.
--
Jay


Re: Using psql, pg_dump, pg_restore in a script

From
hubert depesz lubaczewski
Date:

On Mon, Jun 16, 2014 at 8:53 PM, John Scalia <jayknowsunix@gmail.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

Re: Using psql, pg_dump, pg_restore in a script

From
Tom Lane
Date:
hubert depesz lubaczewski <depesz@gmail.com> writes:
> Giving password as command line option would show it to everybody.

Exactly.

> 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).

Note that environment variables are also unsafe on some platforms, though
(I think) not all.  The .pgpass solution is actually quite the safest one.

            regards, tom lane