14

I have put a .pgpass file in /folder/.pgpass and it looks like this

*:*:*:postgres:password_for_postgres

I have a bash script where I backup the database from postgreSQL:

#!/bin/bash
export PGPASSFILE=/folder/.pgpass
echo $PGPASSFILE
pg_dump --username=postgres --format=c --file=/backup/db/db.sqlc database

However, the script still promts me for the password. PGPASSFILE has the value as it should, as far as I can see. Any hints on what the problem might be?

/K

araqnid
  • 843
  • 5
  • 10
Kerstin Viltersten
  • 141
  • 1
  • 1
  • 3

4 Answers4

8

Is the pgpass file set to mode 0600 (i.e. read/write only by the owner)? The client library will ignore it if it's group- or world-readable.

araqnid
  • 843
  • 5
  • 10
8

On my configuration (Ubuntu 10.04.3 and PostgreSQL 8.4), I could finally get it to work when the username I am logged in is the same as the one I'm trying to get a password from the .pgpass file for.

Logged in as deployer, I was trying to use the .pgpass file to access the database owned by an user name appname, which has no Unix user equivalent. I could not make the .pgpass work, until I started using deployer as the user to access my database...

Here is my /home/deployer/.pgpass file content:

*:*:*:deployer:password

Here a part of the /etc/postgresql/8.4/main/pg_hba.conf one:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD                                                

# "local" is for Unix domain socket connections only                                                        
local   all         all                               md5                                                   
# IPv4 local connections:                                                                                   
host    all         all         127.0.0.1/32          md5                                                   
host    all         all         192.168.0.1/32        md5                                                   
# IPv6 local connections:                                                                                   
host    all         all         ::1/128               md5

As you can see, all my connections requires password (md5).

With this configuration, assuming I have a database I created with this command:

deployer@ubuntu-server:~$ createdb -T template0 -O deployer -E UTF8 dbname

I'm able to perform the following operation without entering a password:

deployer@ubuntu-server:~$ dropdb dbname

As soon as I change the name of my .pgpass to .pgpass-no, it will require a password.

BTW, do not forget that your .pgpass file must be under 0600 permissions:

deployer@ubuntu-server:~$ ls -la .pgpass
-rw------- 1 deployer staff 24 2012-01-06 17:29 .pgpass
Romain Champourlier
  • 181
  • 1
  • 3
  • 2
    You can control the pg user to connect as using the `-U` flag to `dropdb` and `createdb` – Kevin Horn Jun 30 '12 at 21:17
  • As stated quite clearly in the Docs (https://www.postgresql.org/docs/12/libpq-pgpass.html) The .pgpass file must exist in the home directory of the user "using" it and must be readable only by that user. If these conditions are not net, the file will be ignored. – Phill W. Aug 10 '20 at 12:39
1
  1. Create .pgpass file with content

host:5432:somedb:someuser:somepass

  1. set the permissions using command

sudo chmod 600 .pgpass

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

sudo chown login_username:login_username .pgpass

  1. Set PGPASSFILE environment variable :

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

Now check by connecting to database :

psql -h host -U someuser somedb

I will not prompt for password and logged in to postgresql.

Shrinivas
  • 111
  • 4
0

Another thing to check is that you haven't used the -W flag with psql, because this will always force a manual password entry instead of checking your .pgpass

Rach Sharp
  • 101
  • 2