How do I specify a password to 'psql' non-interactively?

Solution 1:

Set the PGPASSWORD environment variable inside the script before calling psql

PGPASSWORD=pass1234 psql -U MyUsername myDatabaseName

For reference, see http://www.postgresql.org/docs/current/static/libpq-envars.html

Edit

Since Postgres 9.2 there is also the option to specify a connection string or URI that can contain the username and password. Syntax is:

$ psql postgresql://[user[:password]@][host][:port][,...][/dbname][?param1=value1&...]

Using that is a security risk because the password is visible in plain text when looking at the command line of a running process e.g. using ps (Linux), ProcessExplorer (Windows) or similar tools, by other users.

See also this question on Database Administrators

Solution 2:

From the official documentation:

It is also convenient to have a ~/.pgpass file to avoid regularly having to type in passwords. See Section 30.13 for more information.

...

This file should contain lines of the following format:

hostname:port:database:username:password

The password field from the first line that matches the current connection parameters will be used.

Solution 3:

• in one line:

  export PGPASSWORD='password'; psql -h 'server name' -U 'user name' -d 'base name' -c 'command'
  

  with command a sql command such as "select * from schema.table"

• or more readable:

  export PGPASSWORD='password'
  psql -h 'server name' -U 'user name' -d 'base name' \
       -c 'command' (eg. "select * from schema.table")