Postgresql: Scripting psql execution with password

Question

How can I call psql so that it doesn't prompt for a password?

This is what I have:

psql -Umyuser < myscript.sql

However, I couldn't find the argument that passes the password, and so psql always prompts for it.

I ended up going for the PGPASSWORD environment variable. This fitted my usecase perfectly. Simple and self-contained in the script. — Axel Fontaine, Jan 01 '12 at 05:03
Just found this http://postgresguide.com/utilities/psql.html — D_C, Sep 05 '18 at 16:42
Possible duplicate of [How do I specify a password to psql non-interactively?](https://stackoverflow.com/questions/6405127/how-do-i-specify-a-password-to-psql-non-interactively) — Helder Pereira, May 11 '19 at 21:00

Reece · Accepted Answer · 2021-07-02T23:45:58.767

505

You may wish to read a summary of the ways to authenticate to PostgreSQL.

To answer your question, there are several ways provide a password for password-based authentication:

Via the password prompt. Example:

psql -h uta.biocommons.org -U foo
Password for user foo:

In a pgpass file. See libpq-pgpass. Format:

<host>:<port>:<database>:<user>:<password>

With the PGPASSWORD environment variable. See libpq-envars. Example:

export PGPASSWORD=yourpass
psql ...

# Or in one line for this invocation only:
PGPASSWORD=yourpass psql ...

In the connection string The password and other options may be specified in the connection string/URI. See app-psql. Example:
```
psql postgresql://username:password@dbmaster:5433/mydb?sslmode=require
```

edited Jul 02 '21 at 23:45

answered Jun 29 '11 at 16:39

Reece

7,616
4
30
46

18

I think PGPASSWORD is deprecated but still works, btw. Just FYI – Scott Marlowe Jun 29 '11 at 17:16
49

Yep, it's deprecated (and so noted in one of the links). Since it's come up, it's probably also worth noting that the deprecation is hotly contested because it's extremely useful for many people yet can be used in some circumstances without serious security concerns. It seems to me that it's no worse than storing .pgpass on an NFS filesystem, for example. I use PGPASSWORD routinely. – Reece Jun 29 '11 at 18:35
8

the idea that command line information is "available to all users" is based on antiquated assumptions about multi-user systems and does not apply in most modern environments where systems just run a single application and it's all automated – Alex R May 17 '19 at 16:16
"There is no option to provide the password as a command line argument" is false, as you'll find in another answer (using a URI). – Gerard H. Pille Dec 27 '20 at 12:22
4

Fast Forward 2021: For anyone using Postgresql version 13 - `PGPASSWORD=yourpass psql ...` doesn't seem to work with version 13 anymore (on Debian 10 in my case), therefore I successfully did with the connection string as explained in the [official docs](https://www.postgresql.org/docs/13/) – iLuvLogix Jun 16 '21 at 13:08
2

Works for me. `psql (PostgreSQL) 13.2` on a Mac. – jbrown Jul 09 '21 at 07:03
1

The `PGPASSWORD` environment variable is documented for Postgres 13 and 14 - see [doc page for 13](https://www.postgresql.org/docs/13/libpq-envars.html) – RichVel Nov 09 '21 at 17:29
With the connection string it looks like it is impossible to output the results of the sql command. – user2134488 Mar 13 '23 at 03:10

score 196 · Answer 2 · answered Dec 04 '13 at 07:48

196

PGPASSWORD=[your password] psql -Umyuser < myscript.sql

answered Dec 04 '13 at 07:48

Greg

4,509
2
29
22

1

this works in terraform as well. you, my friend, are a lifesaver – wildthing81 Feb 08 '19 at 04:14

score 82 · Answer 3 · edited May 31 '22 at 16:38

82

You can add this command line at the beginning of your script:

export PGPASSWORD="[your password]"

edited May 31 '22 at 16:38

Eduardo Cuomo

17,828
6
117
94

answered Sep 19 '11 at 16:19

jbaylina

4,408
1
30
39

29

in my case set command did not work but `export PGPASSWORD=[password]` did work – Can Kavaklıoğlu Jun 03 '13 at 09:09
1

it does not work in shell script. I am using it `#!/bin/sh set PGPASSWORD = postgres psql -h 192.168.3.200 -U postgres incx_um << EOF DELETE FROM usrmgt.user_one_time_codes WHERE time < NOW() - INTERVAL '30 minute' EOF` – Govind Gupta Aug 14 '18 at 09:37
3

Try not using spaces, eg. `PGPASSWORD=password`. – Ariejan Nov 29 '18 at 13:01

score 59 · Answer 4 · edited Mar 13 '19 at 03:45

If you intend on having multiple hosts/database connections, the ~/.pgpass file is the way to go.

Steps:

Create the file using vim ~/.pgpass or similar. Input your information in the following format: hostname:port:database:username:password Do not add string quotes around your field values. You can also use * as a wildcard for your port/database fields.
You must chmod 0600 ~/.pgpass in order for it to not be silently ignored by psql.
Create an alias in your bash profile that runs your psql command for you. For example:alias postygresy='psql --host hostname database_name -U username' The values should match those that you inputted to the ~/.pgpass file.
Source your bash profile with . ~/.bashrc or similar.
Type your alias from the command line.

Note that if you have an export PGPASSWORD='' variable set, it will take precedence over the file.

You must do a `chmod 600` on the file, otherwise `psql` will silently ignore it (according to the docs). — RichVel, Feb 19 '19 at 10:54
This may not be such a good solution on Windows servers, where many things run with users without a user directory. — Jim Lutz, Aug 06 '20 at 11:27

ajxs · Answer 5 · 2019-02-19T00:20:04.050

58

This might be an old question, but there's an alternate method you can use that no one has mentioned. It's possible to specify the password directly in the connection URI. The documentation can be found here, alternatively here.

You can provide your username and password directly in the connection URI provided to psql:

# postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]
psql postgresql://username:password@localhost:5432/mydb

edited Feb 19 '19 at 00:20

answered Nov 02 '18 at 13:25

ajxs

3,347
2
18
33

2

Postrgres 9.3 ignores environment variable `PGPASSWORD` – david.perez Mar 27 '19 at 15:59
It may need to quote the argument for it to work: `psql.exe "postgresql://username:password@address:5432/database"` – Ivan Chau Mar 19 '21 at 02:05

score 22 · Answer 6 · answered Nov 09 '19 at 23:40

22

An alternative to using PGPASSWORD environment variable is to use 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=>

answered Nov 09 '19 at 23:40

ubi

4,041
3
33
50

2

This is good for sanity testing other methods aren't working. Besides, keeping the password out of your shell history can be as simple as [putting a space before a command](https://digi.ninja/blog/hiding_bash_history.php). – vhs Jan 12 '21 at 04:10

Jamie Hutber · Answer 7 · 2015-03-05T10:12:31.157

13

If you're having problems on windows like me (I'm using Windows 7 64-bit) and set PGPASSWORD=[Password] did not work.

Then, as Kavaklioglu said in one of the comments,

export PGPASSWORD=[password]

You will need to save this at the top of the file, or before any usage so its set before being called.

Certainly does work on windows :)

edited Mar 05 '15 at 10:12

answered Mar 27 '14 at 16:21

Jamie Hutber

26,790
46
179
291

1

`export PGPASSWORD=[password]` does not work for me using the command line (cmd.exe) at all. Are you sure you weren't using cygwin or something similar? – Devin Snyder Nov 18 '14 at 23:57
Only works with cl, you added it to the file right? Now just typed it into command? – Jamie Hutber Nov 19 '14 at 09:06
it's ok to use it in linux/mac environment, for windows, i think you should find a way to export this environment variable. – Pengfei.X Mar 05 '15 at 02:42
So add a global password... Thats a interesting idea as well – Jamie Hutber Mar 05 '15 at 10:12

score 13 · Answer 8 · answered Jun 29 '11 at 15:24

13

You have to create a password file: see http://www.postgresql.org/docs/9.0/interactive/libpq-pgpass.html for more info.

answered Jun 29 '11 at 15:24

Femi

64,273
8
118
148

mightybyte · Answer 9 · 2014-12-05T20:17:00.350

8

Given the security concerns about using the PGPASSWORD environment variable, I think the best overall solution is as follows:

Write your own temporary pgpass file with the password you want to use.
Use the PGPASSFILE environment variable to tell psql to use that file.
Remove the temporary pgpass file

There are a couple points of note here. Step 1 is there to avoid mucking with the user's ~/.pgpass file that might exist. You also must make sure that the file has permissions 0600 or less.

Some have suggested leveraging bash to shortcut this as follows:

PGPASSFILE=<(echo myserver:5432:mydb:jdoe:password) psql -h myserver -U jdoe -p 5432 mydb

This uses the <() syntax to avoid needing to write the data to an actual file. But it doesn't work because psql checks what file is being used and will throw an error like this:

WARNING: password file "/dev/fd/63" is not a plain file

edited Dec 05 '14 at 20:17

answered Dec 05 '14 at 17:36

mightybyte

7,282
3
23
39

A working example of this approach appears in https://stackoverflow.com/a/40614592/3696363 - another answer to this question. – Eliyahu Skoczylas Sep 20 '17 at 04:43
1

Although this user didn't really ask for the same thing I'm looking for, I would say that the approach doesn't match. When using the PGPASSFILE=<(whatever) syntax, you can do things like decrypt a file and only have it present in the file descriptor that is created. By writing a temp file, you're not fundamentally solving the problem of having a file on disk with credentials. It's not fun dealing with arbitrary industry rules like that, but it's a thing many people deal with. – Desidero Oct 18 '17 at 19:21

score 8 · Answer 10 · edited Jan 23 '18 at 06:09

8

It can be done simply using PGPASSWORD. I am using psql 9.5.10. In your case the solution would be

PGPASSWORD=password psql -U myuser < myscript.sql

edited Jan 23 '18 at 06:09

Farvardin

5,336
5
33
54

answered Jan 19 '18 at 06:53

pyAddict

1,576
13
15

Eliyahu Skoczylas · Answer 11 · 2019-04-11T10:35:11.200

5

Building on mightybyte's answer for those who aren't comfortable with *nix shell scripting, here's a working script:

#!/bin/sh
PGPASSFILE=/tmp/pgpasswd$$
touch $PGPASSFILE
chmod 600 $PGPASSFILE
echo "myserver:5432:mydb:jdoe:password" > $PGPASSFILE
export PGPASSFILE
psql mydb
rm $PGPASSFILE

The double dollar sign ($$) in /tmp/pgpasswd$$ at line 2 appends the process ID number to the file name, so that this script can be run more than once, even simultaneously, without side effects.

Note the use of the chmod command at line 4 - just like the "not a plain file" error that mightybyte described, there's also a "permissions" error if this is not done.

At line 7, you won't have to use the -hmyserver, the -pmyport, or -Ujdoe flag if you use the defaults (localhost : 5432) and only have one database user. For multiple users, (but the default connection) change that line to

psql mydb jdoe

Don't forget to make the script executable with

chmod +x runpsql (or whatever you called the script file)

UPDATE:

I took RichVel's advice and made the file unreadable before putting the password into it. That closes a slight security hole. Thanks!

edited Apr 11 '19 at 10:35

answered Nov 15 '16 at 16:11

Eliyahu Skoczylas

349
3
8

4

You can use [`mktemp`](https://en.wikipedia.org/wiki/Mktemp) to create a temporary file instead of coming up with your own naming scheme. It creates a new temp file (named something like `/tmp/tmp.ITXUNYgiNh` in Linux and `/var/folders/xx/7gws2yy91vn9_t2lb8jcr2gr0000gn/T/tmp.QmbVOQk4` on MacOS X) and prints its name to stdout. – Ivan Kolmychek Nov 20 '17 at 11:23
3

**Security issue** It's best to do the `chmod 600` after creating the file, but before writing the password to it. As written, a malicious script on the server could continually try to read files of this format, and would sometimes succeed in getting the password. Also, if this script is interrupted for some reason, the file would be left on disk - writing a shell `trap` handler would address this. Given that it's not trivial to write a secure script like this, I recommend using `export PGPASSWORD` instead. – RichVel Feb 18 '19 at 16:31
1

Thanks, @RichVel for pointing out that small security hole. _Touch_ creating and making the file private before putting the password in it is a definite improvement. This kind of solution is needed because `PGPASSWORD` has been deprecated in 9.3. – Eliyahu Skoczylas Apr 11 '19 at 10:38
1

Some of the docs say it's deprecated but as mentioned in a comment in [this Q&A](https://stackoverflow.com/questions/6523019/postgresql-scripting-psql-execution-with-password), the deprecation is contested and it still works as of Postgres 10.6 – RichVel Apr 11 '19 at 13:03
create the file in your home directory, mkdir ~/.creds; touch ~/.creds/pgpass$$ && chmod 0600 ~/.creds/pgpass$$ – ChuckCottrill Mar 17 '22 at 02:30

score 2 · Answer 12 · answered Aug 13 '19 at 09:19

2

8 years later...

On my mac, I had to put a line into the file ~/.pgpass like:

<IP>:<PORT>:<dbname>:<user>:<password>

Also see:
https://www.postgresql.org/docs/current/libpq-pgpass.html
https://wiki.postgresql.org/wiki/Pgpass

answered Aug 13 '19 at 09:19

Dirk Schumacher

1,479
19
37

Don't use -W for psql if you use .pgpass. This parameter cancel .pgpass usage. – avvensis Mar 23 '20 at 04:19
Also ensure permissions `u=rw` for that file. – Dmytro Bogatov May 24 '22 at 19:37

score 2 · Answer 13 · edited May 23 '17 at 12:26

2

You may find this useful: Windows PSQL command line: is there a way to allow for passwordless login?

edited May 23 '17 at 12:26

Community

1
1

answered Jun 29 '11 at 15:33

A Question Asker

3,339
7
31
39

score 1 · Answer 14 · answered Nov 13 '20 at 21:18

1

This also works for other postgresql clis for example you can run pgbench in non-interactive mode.

export PGPASSWORD=yourpassword
/usr/pgsql-9.5/bin/pgbench -h $REMOTE_PG_HOST -p 5432 -U postgres -c 12 -j 4 -t 10000 example > pgbench.out 2>&1 &

answered Nov 13 '20 at 21:18

shane

131
3

score 1 · Answer 15 · answered Dec 01 '22 at 08:26

1

On Windows worked the combination of url string and using psql options:

> psql -d postgresql://username:password@localhost:5432/mydb -f myscript.sql

passing the the command without -d was not working, just connected to the database, but no execution of the script

answered Dec 01 '22 at 08:26

lorson

11
2

On Ubuntu worked too! – Vitaly Sazanovich Mar 08 '23 at 16:29

score -1 · Answer 16 · answered Jul 20 '17 at 09:12

-1

I find, that psql show password prompt even you define PGPASSWORD variable, but you can specify -w option for psql to omit password prompt.

answered Jul 20 '17 at 09:12

Mark Lokshin

1

score -6 · Answer 17 · answered Dec 22 '16 at 17:43

-6

Use -w in the command: psql -h localhost -p 5432 -U user -w

answered Dec 22 '16 at 17:43

vjOnstack

1
3

Postgresql: Scripting psql execution with password

17 Answers17

Linked

Related