Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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.

share|improve this answer

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
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.