How can I call psql so that it doesn't prompt for a password?
This is what I have:
psql -Umyuser < myscript.sql
However, I couldn't find the argument that passes the password, and so psql always prompts for it.
16 Answers
This might be an old question, but there's an alternate method you can use that no one has mentioned. It's possible to specify the password directly in the connection URI. The documentation can be found here, alternatively here.
You can provide your username and password directly in the connection URI provided to psql:
# postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]
psql postgresql://username:password@localhost:5432/mydb
An alternative to using PGPASSWORD environment variable is to use conninfo string according to the documentation
An alternative way to specify connection parameters is in a conninfo string or a URI, which is used instead of a database name. This mechanism give you very wide control over the connection.
$ psql "host=<server> port=5432 dbname=<db> user=<user> password=<password>"
postgres=>
It can be done simply using PGPASSWORD. I am using psql 9.5.10. In your case the solution would be
PGPASSWORD=password psql -U myuser < myscript.sql
8 years later...
On my mac, I had to put a line into the file
~/.pgpass like:
<IP>:<PORT>:<dbname>:<user>:<password>
Also see:
https://www.postgresql.org/docs/current/libpq-pgpass.html
https://wiki.postgresql.org/wiki/Pgpass
This also works for other postgresql clis for example you can run pgbench in non-interactive mode.
export PGPASSWORD=yourpassword
/usr/pgsql-9.5/bin/pgbench -h $REMOTE_PG_HOST -p 5432 -U postgres -c 12 -j 4 -t 10000 example > pgbench.out 2>&1 &
Related