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 1083 · Answer 1 · edited Mar 12 '21 at 18:29

1083

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

62,887
36
269
388

answered Jun 19 '11 at 21:46

36

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
6

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
15

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
4

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
12

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
And obviously, check your .bashrc file for preceding space works! `cat ~/.bashrc | grep HISTCONTROL` It should be "=ignorespace" or ="ignoreboth" to ignore commands with preceding spaces or duplicated history – Uriel Alves Jun 06 '23 at 16:18
but it doesn't work if user `MyUsername` already has a password. is there a way of setting PGPASSWORD in that case? – mangusta Jun 28 '23 at 20:03

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

227

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

62,887
36
269
388

answered Jun 19 '11 at 21:15

Jonathan Hall

75,165
16
143
189

51

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. – Jonathan Hall 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 :) – Jonathan Hall 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
6

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 155 · Answer 3 · edited Mar 10 '15 at 10:08

155

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,899
8
37
45

answered Mar 10 '15 at 09:19

user4653174

1,575
1
9
2

38

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
18

`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
ey Lads, i guess that the magic things is this => https://help.ubuntu.com/community/EnvironmentVariables#Bash.27s_quick_assignment_and_inheritance_trick. It seems it hasn't an oficial name... yet.... – Victor Jul 06 '22 at 23:09

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

116

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,738
21
105
131

answered Apr 26 '18 at 01:04

Jacques Gaudin

15,779
10
54
75

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
This way the password will be visible to all users on your machine while the psql process is running. The complete command line (including the resolved content of the variable $DB_PWD) shows in "ps -ef" or "ps aux" commands. Therefore NOT RECOMMENDED AT ALL. – Holger Jakobs Jan 30 '23 at 17:02

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

51

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

20,575
8
83
77

answered Sep 19 '16 at 19:31

JAGJ jdfoxito

747
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
2

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,738
21
105
131

answered Nov 09 '19 at 23:40

ubi

4,041
3
33
50

1

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 31 · Answer 7 · edited Sep 16 '12 at 15:14

31

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

10,218
26
54
62

answered Mar 28 '12 at 12:33

Srini

329
3
2

15

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

26

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"

edited Sep 17 '21 at 07:49

answered Sep 02 '20 at 06:25

Raj Verma

1,050
1
7
19

Version 4 makes the content of the variable $PASSWORD visible to all sessions on the machine. Therefore STRONGLY DISCOURAGED. – Holger Jakobs Jan 30 '23 at 17:04

score 17 · Answer 9 · 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.

score 10 · Answer 10 · 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'

JDOaktown · Answer 11 · 2022-09-08T18:37:58.853

5

psql postgresql://myawsumuser:myPassword@127.0.0.1:5432/myawsumdb

edited Sep 08 '22 at 18:37

answered Sep 08 '22 at 18:12

JDOaktown

4,262
7
37
52

1

This makes myPassword visible to all sessions on the machine. Therefore STRONGLY DISCOURAGED. – Holger Jakobs Jan 30 '23 at 17:05

score 0 · Answer 12 · answered May 19 '23 at 17:54

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"

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

12 Answers12

Linked

Related