Thread: Using psql, pg_dump, pg_restore in a script
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
On Mon, Jun 16, 2014 at 8:53 PM, John Scalia <jayknowsunix@gmail.com> wrote:
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
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.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
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.
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