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

postgresql bash command-line psql

443,606

Solution 1

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.

Solution 2

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

Solution 3

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

Solution 4

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.

Solution 5

On Windows:

Assign value to PGPASSWORD: C:\>set PGPASSWORD=pass
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 && !

View more solutions

443,606

Author by

Maya Tzadik

Updated on December 06, 2021

Comments

Maya Tzadik over 2 years
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?
Maya Tzadik almost 13 years

Thanks, I am aware of pgpass, but this doesn't solve the issue - I need a self-contained bash script to operate over the database, hence my question about passing info to psql via command line.
Flimzy almost 13 years

I think your only option is to set up a .pgpass file that your bash script has access to. Or don't use passwords at all--you could set up another form of authentication, such as ident, or using SSL certificates.
Maya Tzadik almost 13 years

That's what I feared :) Thanks for the info!
Flimzy almost 13 years

Another option might be to use expect. But I really hate expect :)
andyortlieb over 10 years

For example in one line you can do something like: PGPASSWORD=pass1234 psql -u MyUsername myUserName
Vlad Patryshev about 10 years

What's the point of echo "` chmod 0600 $HOME/.pgpass `"? How about just chmod 0600 $HOME/.pgpass ?
zr870 almost 10 years

I think that this is the most convenient way for simply running an SQL script.
Garrett over 8 years

The one line can be slightly simplified to: PGPASSWORD='password' psql .... which also has the benefit of the variable not being accessible after the command is done.
Mikeumus about 8 years

export PGPASSWORD=YourNewPassword worked for me over other variations.
baldr over 7 years

I can only add - add a space in the command line before the first character and the command won't be stored in bash history. Works for ubuntu/bash.
Steve Byrne about 7 years

See the comment below about PGPASSWORD. Arguably not as secure since it can be sniffed by poking around in /proc I think, but it does work quite well.
hasen over 6 years

export PGPASSWORD sounds like a really bad idea
antipattern over 6 years

I tried that, and it did not work. Still being asked for a password.
Bilal Akil over 6 years

BONUS: Works for Docker: docker run -e PGPASSWORD="$(pbpaste)" --rm postgres psql -h www.example.com dbname username -c 'SELECT * FROM table;'
rogerdpack over 6 years

Be careful and make sure to always add a preceding space otherwise it'll show up in your bash history ~/.bash_history file...
rogerdpack over 6 years

This will save the password in your bash history ~/.bash_history file (unless you carefully always add a preceding space), and also export the password to your current environment FWIW :|
Vladislavs Dovgalecs over 6 years

Don't forget to remove group and other user permission to read, write, execute the .pgpass file! Run chmod go-rwx .pgpass
mljrg almost 6 years

@antipattern You must pass option -w also.
the_ccalderon about 5 years

can you use something like this with pg_restore? Thanks!
Jacques Gaudin about 5 years

@ccalderon911217 Apparently you can: stackoverflow.com/a/28359470/1388292
the_ccalderon about 5 years

@JacquesGaudin yep tested myself! Thank you!
code_dredd over 4 years

For those about to use this, be aware that including a password as part of a shell command will 1) display it in the process list visible by all users of the system (e.g. ps -ef), and 2) will add it to your shell's history file (e.g. .bash_history). My recommendation is to store the password in a safe file (e.g. use OS-level permissions to restrict access) and then PGPASSWORD=$(cat /<path>/to/secret.txt) ....
code_dredd over 4 years

Also, postgresql.org/docs/current/libpq-pgpass.html
Steve Shipway about 4 years

PGPASSWORD=xxxx psql -U username -d database -w -c "select * from foo;" works.
Luís de Sousa over 3 years

At least with bash this formulation fails. Check this other answer instead.
shanem almost 3 years

Welcome fellow time travellers & thank you for your service here on SO. A quick warning from this mid-2021 visitor to honour the 10-year anniversary of this question: On Fedora 32+ the old magic of putting a space before a command to hide it from history no longer works. Even if "it works on my machine" today, you may be one OS update or malicious config change away from your secret being dumped into your bash history (and it was always leaked in ps). I will stick to the accepted answer or @code_dredd's useful alternative in a comment above, those feel like safer practices.
Venryx almost 3 years

In Powershell, you can do this: $env:PGPASSWORD=pass; psql -d database -U user
Stephan Henningsen over 2 years

There's no need to export PGPASSWORD.
sur.la.route over 2 years

+1000 for the comment about no space before the && :)
Manuel Lazo over 2 years

on my case it worked, specifying also the -w flag.
Marco Carlo Moriggi over 2 years

I often use this approach as it seems more readable, but for the sake of security, having the password in the command is not a brilliant idea, as it can be read with a simple ps a command by any (non-root) user
Victor almost 2 years

ey Lads, i guess that the magic things is this => help.ubuntu.com/community/…. It seems it hasn't an oficial name... yet....