CODEWITHSUNDEEP
postgresqlbashcommand-linepsql
Alex N.
Alex N.

Reputation: 15935

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?

Upvotes: 745

Views: 826564

Answers (12)

Oleg Poltoratskii
Oleg Poltoratskii

Reputation: 806

Bash script for using in usual terminal:

echo $PASSWORD psql --host=$HOST --dbname=$DBNAME --username=$USER_NAME --command="$SQL_COMMAND"

If you have to connect to remote server by using openshift client you can use this:

oc rsh $POD echo $PASSWORD | oc rsh -c database $POD psql --host=$HOST --dbname=$DBNAME --username=$USER_NAME --command="$SQL_COMMAND"

Upvotes: 0

JDOaktown
JDOaktown

Reputation: 4467

psql postgresql://myawsumuser:[email protected]:5432/myawsumdb

Upvotes: 9

Promise Preston
Promise Preston

Reputation: 28800

Just to add more clarity.

You can assign the password to the PGPASSWORD variable.

So instead of the below which will require you to type the password:

psql --host=aurora-postgres.cluster-fgshdjdf.eu-west-1.rds.amazonaws.com --port=5432 --user=my_master_user --password --dbname=postgres

We will replace the --password flag with PGPASSWORD=QghyumjB3ZtCQkdf. So it will be:

PGPASSWORD=QghyumjB3ZtCQkdf psql --host=aurora-postgres.cluster-fgshdjdf.eu-west-1.rds.amazonaws.com --port=5432 --user=my_master_user --dbname=postgres

This way you will not be required to type the password.

Upvotes: 31

Raj Verma
Raj Verma

Reputation: 1172

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

  2. On windows you will have to use "set" :

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

  3. Pass it via URL & env variable:

    psql "postgresql://$USER_NAME:$PASSWORD@$HOST_NAME/$DB_NAME"

Upvotes: 29

user330315
user330315

Reputation:

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. Syntax is:

$ psql postgresql://[user[:password]@][host][:port][,...][/dbname][?param1=value1&...]

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

Upvotes: 1238

ubi
ubi

Reputation: 4399

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=>

Upvotes: 32

Jacques Gaudin
Jacques Gaudin

Reputation: 16958

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.

Upvotes: 132

Rob
Rob

Reputation: 481

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'

Upvotes: 11

JAGJ jdfoxito
JAGJ jdfoxito

Reputation: 777

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

Upvotes: 54

Jonathan Hall
Jonathan Hall

Reputation: 79536

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.

Upvotes: 248

user4653174
user4653174

Reputation: 1703

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

Upvotes: 167

Srini
Srini

Reputation: 329

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 `

Upvotes: 31

Related Questions