postgresql: .pgpass not working

Question

I have created a .pgpass file in my home directory which looks like this

localhost:5432:somedb:someuser:somepass

I am using a shell script which creates a directory and puts a pg_dump of somedb there :

mkdir directory
pg_dump somedb > directory/somefile.dump

It still prompts for the password.

Where is the mistake here ?

Did you try specifying the host & user? `pg_dump -U someuser -h localhost somedb > directory/somefile.dump` — devnull, May 28 '13 at 07:44
Yes a really stupid mistake. Specified the user and it works :D Thanks — Deepankar Bajpeyi, May 28 '13 at 09:19
I was passing -W as the option (which implies "force password prompt"). Ensure that you don't have this (in addition to the accepted answer) — g_vk, Jul 20 '17 at 10:54

score 58 · Accepted Answer · edited Aug 03 '20 at 20:26

58

Did you try specifying the host, user, & db?

pg_dump -U someuser -h localhost somedb > directory/somefile.dump

edited Aug 03 '20 at 20:26

Tre232

3
2

answered May 28 '13 at 11:58

devnull

118,548
33
236
227

28

Isn't the .pgpass file supposed to eliminate the need to specify a host and a user? – Ian Hunter Dec 09 '14 at 20:06
30

@IanHunter No, the purpose of supplying the username and host in the pgpass file is to create matching criteria. "When this username and host are supplied, use this password". It can only perform this match if you give it a username and host to match with, – JHixson Mar 29 '16 at 17:52
1

@JHixson - I like your explanation, but this is the first time I've seen the .pgpass file described this way - can you provide a reference for that explanation? – John Prior Dec 06 '16 at 22:12
4

@JohnPrior sure, the documentation for the Pgpass file: https://www.postgresql.org/docs/9.4/static/libpq-pgpass.html `The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password.` Note how it only mentions being able to supply passwords. it cannot be used to supply anything *but* passwords. The things that you put in the file with the password are documented as "match criteria" for purposes of looking up a password. – JHixson Dec 06 '16 at 23:36
2

@JohnPrior a line from the documentation that better illustrates the functionality: `The password field from the first line that matches the current connection parameters will be used.` – JHixson Dec 06 '16 at 23:44
I'm insisting on the fact that -h IS important, especially if it's not localhost... – Fabien Haddadi Dec 03 '18 at 03:58
database must be mentioned in pgpass file too – andrej Jan 28 '19 at 16:51
1

Or you can use only the .pg_service.conf file, if you are using only one db user and schema. Define all the information (ip, port db name, user name and password) under given service name. Set file location in PGSERVICEFILE environment variable and service name in PGSERVICE variable. – Amith Chinthaka Jul 31 '19 at 05:27

Shrinivas · Answer 2 · 2020-11-25T11:32:03.540

43

Create .pgpass file with content

host:5432:somedb:someuser:somepass

set the permissions

sudo chmod 600 .pgpass

Set the file owner as the same user using which you logged in :

sudo chown login_username:login_username .pgpass

Set PGPASSFILE environment variable :

export PGPASSFILE='/home/user/.pgpass'

Now check by connecting to database :

psql -h host -U someuser somedb

It did not prompt for a password, and logged-in to postgresql.

edited Nov 25 '20 at 11:32

answered May 28 '18 at 09:36

Shrinivas

1,223
16
13

2

In my case "sudo chown login_username:login_username .pgpass" did the trick. – Adrian Oct 11 '19 at 15:26
Thank you for the details answer. I forgot to specify the `-U ` option and it kept asking for the password. – F. Santiago Jul 05 '22 at 11:06

score 26 · Answer 3 · answered Jan 26 '15 at 14:54

26

Although question has already been answered and accepted, it may also happen that permissions on .pgpass file are not properly set. It has to have the world and group access disallowed:

/bin/chmod 0600 ~/.pgpass

answered Jan 26 '15 at 14:54

Nacho Mezzadra

886
2
12
14

yonran · Answer 4 · 2020-05-27T01:59:26.693

psql (startup.c) calls PQconnectdbParams (fe-connect.c), and then passwordFromFile is called. Here’s a checklist to make sure the pgpass file will be used:

Make sure the flags --password/-W and password= in the connection string are unset. Otherwise, the pgpass file will not be used.
Make sure the environment variable PGPASSWORD is unset (echo $PGPASSWORD). Otherwise, the pgpass file will not be used.
Make sure the pgpass file is in the right location ($PGPASSFILE or default ~/.pgpass or %APPDATA%\postgresql\pgpass.conf)
Make sure the passfile is not readable, writable, or executable by group or other (e.g. chmod 600 ~/.pgpass); otherwise psql will print a warning.
Make sure the passfile is a file (not a symlink); otherwise psql will print a warning.
Make sure the passfile is readable by the psql user (e.g. cat ~/.pgpass); otherwise psql will ignore it without warning. Make sure that it is owned by the user, and that all its ancestor directories are executable by the user.
Make sure that the pgpass file has the correct format hostname:port:database:username:password (The Password File, passwordFromFile). Each field (other than password) can be *. The characters : and \ must be escaped \: and \\ (even in password). The password ends at : or the end of the line and can include any byte other than \r, \n, or \0. Any lines that aren’t formatted right or don’t match the host and user are ignored without warning as if they were comments.
Make sure the hostname, port, dbname, username of the line in the pgpass file match the server or are *. The server’s “pwhost” that is checked is the host name if non-empty, or the hostaddr ip address. Otherwise, the line will be ignored without warning.

Unfortunately, there is no logging within these files, so if this doesn’t help, then you may need to compile psql and libpq yourself and run it in a debugger.

Thank you so much. I was trying to use * all over the place and it just wasn't working. So if you are trying t ouse wildcards in the pgpass file, I recommend against it. PoS code. — Dexter, Apr 28 '20 at 19:41
Thank you so so much, I spent 2 days setting this file and getting password error, you saved me. — crbroflovski, Jun 05 '20 at 21:02
Thanks! For me, change from `localhost:5432:squared_development:squared:squared` to `localhost:5432:*:squared:squared` did the 'do not ask for password' trick. — Foton, Sep 16 '20 at 08:39

score 5 · Answer 5 · edited Feb 22 '18 at 06:57

5

Check the owner of the .pgpass file. Just lost half an hour to find out I had created my .pgpass file with sudo. The command for my user and location was chown postgres:postgres /var/lib/pgsql/.pgpass.

edited Feb 22 '18 at 06:57

Noumenon

5,099
4
53
73

answered Feb 07 '18 at 15:33

Jonathan DS

2,050
5
25
48

score 2 · Answer 6 · answered Oct 01 '21 at 19:32

2

In my case, it was I already set PGPASSWORD environment variable (to a wrong password), so psql was picking that instead of what's inside .pgpass

answered Oct 01 '21 at 19:32

viggy28

760
1
10
21

score 0 · Answer 7 · answered May 11 '22 at 13:45

0

If your password happens to include a colon, you have to escape it by preceding it with a \ in order for the password to be recognised.

answered May 11 '22 at 13:45

JGFMK

8,425
4
58
92

postgresql: .pgpass not working

7 Answers7

Linked