Advertisement
Advertisement


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


Question

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?

2020/03/04
1
360
3/4/2020 8:33:31 PM

Accepted Answer

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:

hostname:port:database:username:password

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

2017/12/20
150
12/20/2017 5:28:48 PM


  • 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")
    
2015/03/10

I tend to prefer passing a URL to psql:

psql "postgresql://$DB_USER:[email protected]$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.

2020/03/04

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 && !

2018/03/04

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 `        
2012/09/16

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=>
2020/03/04

Source: https://stackoverflow.com/questions/6405127
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]