Postgresql: Scripting psql execution with password

Solution 1:

You may wish to read a summary of the ways to authenticate to PostgreSQL.

To answer your question, there are several ways provide a password for password-based authentication:

1. Via the password prompt. Example:

   psql -h uta.biocommons.org -U foo
   Password for user foo: 
   

2. In a pgpass file. See libpq-pgpass. Format:

   <host>:<port>:<database>:<user>:<password>
   

3. With the PGPASSWORD environment variable. See libpq-envars. Example:

   export PGPASSWORD=yourpass
   psql ...
   
   # Or in one line for this invocation only:
   PGPASSWORD=yourpass psql ...
   

4. In the connection string The password and other options may be specified in the connection string/URI. See app-psql. Example:

   psql postgresql://username:password@dbmaster:5433/mydb?sslmode=require
   

Solution 2:

PGPASSWORD=[your password] psql -Umyuser < myscript.sql

Solution 3:

You can add this command line at the begining of your script:

set PGPASSWORD=[your password]

Solution 4:

This might be an old question, but there's an alternate method you can use that no one has mentioned. It's possible to specify the password directly in the connection URI. The documentation can be found here, alternatively here.

You can provide your username and password directly in the connection URI provided to psql:

# postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]
psql postgresql://username:password@localhost:5432/mydb