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?

You can use a connection url. Check this answers in https://stackoverflow.com/questions/3582552/postgres-connection-url. — paulodiovani, Sep 19 '17 at 20:01
@paulodiovani I will just add that this works with no other options - just psql Thank you. — nsandersen, Aug 09 '21 at 08:14

score 921 · Answer 1 · edited Mar 12 '21 at 18:29

921

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

edited Mar 12 '21 at 18:29

rogerdpack

56,766
33
241
361

answered Jun 19 '11 at 21:46

a_horse_with_no_name

497,550
91
775
843

32

For example in one line you can do something like: PGPASSWORD=pass1234 psql -u MyUsername myUserName – andyortlieb Sep 12 '13 at 18:05
3

I think that this is the most convenient way for simply running an SQL script. – zr870 May 21 '14 at 17:42
4

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. – baldr Oct 15 '16 at 18:01
11

BONUS: Works for Docker: `docker run -e PGPASSWORD="$(pbpaste)" --rm postgres psql -h www.example.com dbname username -c 'SELECT * FROM table;'` – Bilal Akil Nov 23 '17 at 04:34
3

Be careful and *make sure to always add a preceding space* otherwise it'll show up in your bash history ~/.bash_history file... – rogerdpack Dec 20 '17 at 20:32
5

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 //to/secret.txt) ...`. – code_dredd Oct 29 '19 at 18:47
Also, https://www.postgresql.org/docs/current/libpq-pgpass.html – code_dredd Oct 29 '19 at 19:01
At least with bash this formulation fails. Check [this other answer](https://stackoverflow.com/a/28959903/2066215) instead. – Luís de Sousa Nov 09 '20 at 15:45
2

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. – shanem Jun 21 '21 at 13:51

score 205 · Accepted Answer · edited Dec 20 '17 at 17:28

205

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.

edited Dec 20 '17 at 17:28

rogerdpack

56,766
33
241
361

answered Jun 19 '11 at 21:15

Flimzy

68,325
15
126
165

50

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. – Alex N. Jun 19 '11 at 21:17
7

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. – Flimzy Jun 19 '11 at 21:19
2

That's what I feared :) Thanks for the info! – Alex N. Jun 19 '11 at 21:21
Another option might be to use expect. But I really hate expect :) – Flimzy Jun 19 '11 at 21:25
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. – Steve Byrne Mar 11 '17 at 01:20
4

Don't forget to remove group and other user permission to read, write, execute the .pgpass file! Run `chmod go-rwx .pgpass` – Vladislavs Dovgalecs Jan 29 '18 at 20:44

score 132 · Answer 3 · edited Mar 10 '15 at 10:08

132

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

edited Mar 10 '15 at 10:08

AdrieanKhisbe

3,719
7
35
45

answered Mar 10 '15 at 09:19

user4653174

1,345
1
8
2

31

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. – Garrett Jan 13 '16 at 21:36
3

`export PGPASSWORD=YourNewPassword` worked for me over other variations. – Mikeumus Apr 04 '16 at 04:38
16

`export PGPASSWORD` sounds like a really bad idea – hasen Aug 28 '17 at 10:38
4

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 :| – rogerdpack Dec 20 '17 at 20:34
There's no need to export `PGPASSWORD`. – Stephan Henningsen Sep 06 '21 at 19:33
on my case it worked, specifying also the `-w` flag. – Manuel Lazo Dec 25 '21 at 18:57

score 103 · Answer 4 · edited Mar 04 '20 at 20:35

103

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.

edited Mar 04 '20 at 20:35

Peter Mortensen

30,030
21
100
124

answered Apr 26 '18 at 01:04

Jacques Gaudin

14,274
6
46
69

can you use something like this with pg_restore? Thanks! – the_ccalderon May 03 '19 at 03:43
1

@ccalderon911217 Apparently you can: https://stackoverflow.com/a/28359470/1388292 – Jacques Gaudin May 03 '19 at 06:44
@JacquesGaudin yep tested myself! Thank you! – the_ccalderon May 03 '19 at 19:28

score 45 · Answer 5 · edited Mar 04 '18 at 15:02

45

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

edited Mar 04 '18 at 15:02

Brian Burns

17,878
8
77
67

answered Sep 19 '16 at 19:31

JAGJ jdfoxito

667
5
5

1

I tried that, and it did not work. Still being asked for a password. – antipattern Oct 19 '17 at 09:11
3

@antipattern You must pass option `-w` also. – mljrg Jul 27 '18 at 11:03
1

`PGPASSWORD=xxxx psql -U username -d database -w -c "select * from foo;"` works. – Steve Shipway Feb 20 '20 at 04:19
1

In Powershell, you can do this: `$env:PGPASSWORD=pass; psql -d database -U user` – Venryx Aug 11 '21 at 09:05
+1000 for the comment about no space before the && :) – sur.la.route Nov 03 '21 at 09:50

score 31 · Answer 6 · edited Mar 04 '20 at 20:36

31

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

edited Mar 04 '20 at 20:36

Peter Mortensen

30,030
21
100
124

answered Nov 09 '19 at 23:40

ubi

3,523
1
29
43

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 – Marco Carlo Moriggi Jan 25 '22 at 16:46

score 30 · Answer 7 · edited Sep 16 '12 at 15:14

30

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 `

edited Sep 16 '12 at 15:14

jonsca

9,627
26
54
61

answered Mar 28 '12 at 12:33

Srini

319
3
2

14

What's the point of echo "` chmod 0600 $HOME/.pgpass `"? How about just chmod 0600 $HOME/.pgpass ? – Vlad Patryshev Mar 27 '14 at 17:46

Raj Verma · Answer 8 · 2021-09-17T07:49:15.547

If its not too late to add most of the options in one answer:

There are a couple of options:

set it in the pgpass file. link

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
On windows you will have to use "set" :

set PGPASSWORD=pass and then login to the psql bash.
Pass it via URL & env variable:

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

score 9 · Answer 9 · answered Apr 17 '19 at 11:47

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'

score 3 · Answer 10 · answered May 08 '22 at 06:15

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.

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

10 Answers10

Linked

Related