psql can be used as a remote scripting tool for PostgreSQL. While running a script remotely you may need to specify a user’s password. Normally, psql asks for a user’s password interactively.

TL;DR export PGPASSWORD=password

How psql works?

If you specify a remote server that is not same with server you run psql, psql asks for a password validation. It is an interactive prompt.

Echoing

Bash scripts do not have feature to reply interactive command line prompts. In that cases we usually use “echo” command to be executed:

echo your_pass psql --host=ip_addr --username=user_name --command="SQL_COMMAND" --dbname=db_name 

The statement above shows how you can reply to command prompt with echo command. In that case you need to display your password clearly.

Connection String

JDBC connection string can be used as parameter for psql:

psql postgresql://user_name:my_password@127.0.0.1:5432/database_name

Connection string option is also displays the password clearly. It is strongly discouraged.

PGPASSWORD Variable

There are several environmental variables for PostgreSQL. You can find whole list of environment variables from this page.

Defining variables are very helpful for scripting. If you manage multiple servers with multiple users and password that helps you to make your scripts portable.

export PGPASSWORD='my_passwrd'

After defining PGPASSWORD variable psql automatically recognizes it and uses as the system password.