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