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

bash postgresql command-line
Alex N. picture Alex N. · Jun 19, 2011 · Viewed 324.4k times · Source

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?

Answer

a_horse_with_no_name picture a_horse_with_no_name · Jun 19, 2011

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.

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

Related questions

How to pass in password to pg_dump?

I'm trying to create a cronjob to back up my database every night before something catastrophic happens. It looks like this command should meet my needs: 0 3 * * * pg_dump dbname | gzip > ~/backup/db/$(date +%Y-%m-%d).psql.gz Except …

Read More
How to create User/Database in script for Docker Postgres

I have been trying to set up a container for a development postgres instance by creating a custom user & database. I am using the official postgres docker image. In the documentation it instructs you to insert a bash script …

Read More
psql: command not found Mac

I installed PostgreSQL via the graphical install on http://www.postgresql.org/download/macosx/ I see it in my applications and also have the psql terminal in my applications. I need psql to work in the regular terminal for another …

Read More