Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a situation that I'm scratching my head over, but I'm new to postgresql, so I may be overlooking something obvious.

I set up a testbed server and set access in pg_hba.conf:

local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 md5

Then restarted the postgresql server and after that, I could, as my regular user, use

psql -h localhost -U postgres -l

And this gave me the table listing.

On the second system pg_hba.conf matches that other file. I restarted the database server process and yet psql -h localhost -U postgres -l as my user yields this error:

2012-03-29 23:31:17 UTC LOG:  could not receive data from client: Connection reset by peer
Password for user postgres: 
psql: fe_sendauth: no password supplied
2012-03-29 23:31:29 UTC LOG:  could not receive data from client: Connection reset by peer

I do not have a password configured for that user. If I

sudo su - postgres

Then try

psql -h localhost -U postgres -l

...as postgres won't work, still prompting for a password, but if I use:

psql -l

it gives the list of databases.

This server, the second one, has a "production" database on it that another developer set up. But he doesn't know why it wouldn't let me list it as my username with the -h host -U postgres arguments on that server, while the fresh-install on the first server will list them (although the first one has nothing but the default installation on it.)

The platform is Ubuntu 11.10, postgres is 9.1. Any idea what I'm overlooking?

EDIT Here's what's happening when I try getting a database list:

user@testbed:/etc/postgresql/9.1/main$ grep listen_addresses *
grep: pg_hba.conf: Permission denied
grep: pg_ident.conf: Permission denied
postgresql.conf:#listen_addresses = 'localhost'     # what IP address(es) to listen on;
user@testbed:/etc/postgresql/9.1/main$ psql -h localhost -U postgres -l
                              List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

user@testbed:/etc/postgresql/9.1/main$ 

On the other system:

user@production:/etc/postgresql/9.1/main$ grep listen_addresses *
grep: pg_hba.conf: Permission denied
grep: pg_hba.conf~: Permission denied
grep: pg_ident.conf: Permission denied
postgresql.conf:#listen_addresses = 'localhost'     # what IP address(es) to listen on;
user@production:/etc/postgresql/9.1/main$ psql -h localhost -U postgres -l
Password for user postgres: 
psql: fe_sendauth: no password supplied
user@production:/etc/postgresql/9.1/main$ 
share|improve this question

2 Answers 2

up vote 6 down vote accepted

According to the pg_hba.conf snippet, a password is required if you connect from ::1, which is the IPv6 address for localhost.

It may be that the box on which you have the problem has the resolver configured so that the name "localhost" refers to both 127.0.0.1 (IPv4) and ::1 (IPv6), so that the command psql -h localhost... may connect to one or the other address.

I'd suggest to check the /etc/hosts file for different localhost entries, or bypass the resolver by connecting to 127.0.0.1, or update the pg_hba.conf with "trust" for IPv6 local connections.

share|improve this answer
    
Attempting to connect as 127.0.0.1 for the host does give the list of databases! So most likely it is an IPv6/v4 issue on that machine. Thanks! –  Bart Silverstrim Mar 31 '12 at 17:59

Connections via localhost need TCP/IP connections enabled.

Try setting

listen_addresses = *

in your postgresql.conf, and restart the server.
Be aware that the whole world can connect to your database then!

As @Bart and @dbenhur commented, the default setting listen_addresses = 'localhost' already covers connections to localhost. This is also true if the setting is commented out.

share|improve this answer
1  
Just checked the testbed and the non-working system's postgresql.conf. Both have: "#listen_addresses = 'localhost'" line, so it's commented out and I'm thinking the default is to allow the localhost to connect to itself :-/ –  Bart Silverstrim Mar 30 '12 at 13:52
1  
@BartSilverstrim The Fine Manual agrees, The default value is localhost, which allows only local TCP/IP "loopback" connections to be made.. –  dbenhur Mar 30 '12 at 17:57
    
@dbenur - I'm not saying it doesn't, I just can't explain the behavior because this configuration appears to match. I added an edit to the question to demonstrate. –  Bart Silverstrim Mar 31 '12 at 12:05
2  
@FrankHeikens: A change to listen_addresses requires a restart. A reload is not enough. Check the manual. –  Erwin Brandstetter Apr 2 '12 at 18:59
1  
I've run across many Linux-like applications that will have the setting commented out but set as the default compiled in as a reference; good to have noted here as a comment for future reference for others! –  Bart Silverstrim Apr 3 '12 at 12:25

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.