2

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?

5
  • 1
    What language are you using? Shell? Commented Apr 22, 2014 at 17:56
  • You shouldn't do that. -p password leaves your password visible in, for example, ps xa output.
    – derobert
    Commented Apr 22, 2014 at 17:59
  • I'm using a bash script. And @derobert, so if I shouldn't do it this way, then how do I run postgres commands non-interactively at all? I'm going to need to authenticate to the postgres server somehow.
    – jayhendren
    Commented Apr 22, 2014 at 18:02
  • @jayhendren you can set up your pg_hba.conf file to use peer or ident authentication (if the script is running on the same machine as postgres) or you can use a ~/.pgpass file.
    – derobert
    Commented Apr 22, 2014 at 18:05
  • It will ask for passwords even if you execute your script as postgres using crontab? I have some scripts running directly as postgres scheduled, and it works out of the box...
    – user34720
    Commented May 31, 2016 at 14:02

3 Answers 3

4

Use the PGPASSWORD environment variable. For instance:

PGPASSWORD=<password> psql -U postgres -c "<postgresql query>"

Source

1
  • 1
    Not the mosy secure solution, as the process environment can be sniffed. See .pgpass solution or modify postgres_hba.conf
    – Otheus
    Commented May 31, 2016 at 14:05
2

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
1

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:

local all postgres  peer

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:

local zabbix zabbix peer

for which you create a username&database named "zabbix".

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .