[bash] 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?

This question is related to bash postgresql command-line

The answer is


On Windows:

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

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

Ready

Or in one line,

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

Note the lack of space before the && !


Added content of pg_env.sh to my .bashrc:

cat /opt/PostgreSQL/10/pg_env.sh

#!/bin/sh
# 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'

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

An alternative to using the PGPASSWORD environment variable is to use the 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=>

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.


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

<databaseip>:<port>:<databasename>:<dbusername>:<password>

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 "192.168.1.1:*:*:postgres:postgrespwd" > $HOME/.pgpass
echo "` chmod 0600 $HOME/.pgpass `"

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

If its not too late to add most of the options in one answer:

There are a couple of options:

  1. set it in the pgpass file. link
  1. set an environment variable and get it from there:

    export PGPASSWORD='password'

and then run your psql to login or even run the command from there:

 psql -h clustername -U username -d testdb
  1. On windows you will have to use "set" :

    set PGPASSWORD=pass and then login to the psql bash.


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


Examples related to bash

Comparing a variable with a string python not working when redirecting from bash script Zipping a file in bash fails How do I prevent Conda from activating the base environment by default? Get first line of a shell command's output Fixing a systemd service 203/EXEC failure (no such file or directory) /bin/sh: apt-get: not found VSCode Change Default Terminal Run bash command on jenkins pipeline How to check if the docker engine and a docker container are running? How to switch Python versions in Terminal?

Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside

Examples related to command-line

Git is not working after macOS Update (xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools) Flutter command not found Angular - ng: command not found how to run python files in windows command prompt? How to run .NET Core console app from the command line Copy Paste in Bash on Ubuntu on Windows How to find which version of TensorFlow is installed in my system? How to install JQ on Mac by command-line? Python not working in the command line of git bash Run function in script from command line (Node JS)