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

I am trying to automate database creation process with a shell script and one thing I've hit a road block with passing a password to psql. Here is a bit of code from the shell script:

psql -U $DB_USER -h localhost -c"$DB_RECREATE_SQL"

How do I pass a password to psql in a non-interactive way?



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:


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

Set the PGPASSWORD environment variable inside the script before calling psql

PGPASSWORD=pass1234 psql -U MyUsername myDatabaseName

For reference, see


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

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

  • 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")

I tend to prefer passing a url to psql:

psql "postgresql://$DB_USER:$DB_PWD@$DB_SERVER/$DB_NAME"

This gives me the freedom to name my environment variables as I wish and avoids creating unnecessary files.

This requires libpq. The documentation can be found here

On Windows:

  1. Assign value to PGPASSWORD: C:\>set PGPASSWORD=pass

  2. Run command: C:\>psql -d database -U user


Or in one line,

set PGPASSWORD=pass&& psql -d database -U user

Note the lack of space before the && !

This can be done by creating a .pgpass file in the home directory of the (Linux) User. .pgpass file format:


You can also use wild card * in place of details.

Say I wanted to run tmp.sql without prompting for a password.

With the following code you can in *.sh file

echo "*:*:postgres:postgrespwd" > $HOME/.pgpass
echo "` chmod 0600 $HOME/.pgpass `"

echo " ` psql -h -p 5432  -U postgres  postgres  -f tmp.sql `        

Added content of to my .bashrc:

cat /opt/PostgreSQL/10/

# The script sets environment variables helpful for PostgreSQL

export PATH=/opt/PostgreSQL/10/bin:$PATH
export PGDATA=/opt/PostgreSQL/10/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5433
export PGLOCALEDIR=/opt/PostgreSQL/10/share/locale
export MANPATH=$MANPATH:/opt/PostgreSQL/10/share/man

with addition of (as per user4653174 suggestion)

export PGPASSWORD='password'

Need Your Help

How do I step out of a loop with Ruby Pry?

ruby-on-rails ruby loops exit pry

I'm using Pry with my Rails application. I set binding.pry inside a loop in my model to try and debug a problem. For example:

Cannot change permissions of folders within vagrant home folder

centos virtualization virtualbox chmod vagrant

When I ssh in to my vagrant vm, I can change permissions of files and folders above and outside the vagrant user folder, and for files within the vagrant user folder. But cannot change permissions ...