Run PostgreSQL CLI (psql) in bash script without password prompt?

Question

Running the PostgreSQL CLI (psql) through a bash script as user postgres causes a password prompt to be sent, even though I can run it manually without a password. When I try to put -w in the script, this error occurs:

psql: fe_sendauth: no password supplied

Can I run psql in an automated way without a password?

Answer 1

You can use password file pgpass.conf or use PGPASSWORD variable.

For local development or if security isn't an issue, you could also configure PostgreSQL to run in trust authentication mode by modifying your pg_hba.conf file.

Answer 2

You can, as long as you are OK with not having authentication for that specific user connecting from the host where script is running. For this, you just add the following line to pghba.conf on your server (you will need to restart PostgreSQL daemon):

host YOUR_DB YOUR_USER YOUR_IP trust

Where:

1. YOUR_DB: database name
2. YOUR_USER: user you are using to run the script
3. YOUR_IP: IP where script runs

Other option could be using expect utility (assuming you are using linux or some unix variant or shell such as cygwin).