I am writing a non-interactive script that needs to execute postgres commands. The script will only be run by root or a user with sudo permissions, and the postgres commands need to be executed as the postgres user in postgresql. The trouble is that postgresql always (as far as I can tell) prompts for a password interactively (i.e. pqsl -U postgres -W) or does not accept a password at all (i.e. psql -U postgres -w). I would like to pass a password on the command line, similar to how mysql allows mysql -u user -p password. How can I do this with postgres?
|
|
|||
|
There are two ways of doing the same thing . Approach 1 : Add a .pgpass file in home directory of user(user which runs the script) cat /home/user/.pgpass hostname:port:database:user:password chmod 600 /home/user/.pgpass After adding the above you don't need to add password in any script you using. It will connect directly. Approach 2 : Add the following in your shell script. But this will expose your password and become a problem if you are committing to any repository. export PGPASSWORD=password |
|||
|
|
|
Use the |
|||||
|
|
If you can run psql as the user postgres, the most useful solution is to modify pg_hba.conf so the the Unix user postgres, connecting via localhost/socket, is authenticated without any password pr external file. At the tip of the file, insert this: This, TBMI is the default. Perhaps someone thought they were being clever or more secure when they removed this line, idk. You could also use something like this for a monitroing service: for which you create a username&database named "zabbix". |
|||
|
|
-p passwordleaves your password visible in, for example,ps xaoutput. – derobert Apr 22 '14 at 17:59peeroridentauthentication (if the script is running on the same machine as postgres) or you can use a~/.pgpassfile. – derobert Apr 22 '14 at 18:05crontab? I have some scripts running directly aspostgresscheduled, and it works out of the box... – nwildner May 31 '16 at 14:02