4

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$ 

2 Answers 2

6

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.

1
  • 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! Mar 31, 2012 at 17:59
1

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.

7
  • 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 :-/ Mar 30, 2012 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, 2012 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. Mar 31, 2012 at 12:05
  • 2
    @FrankHeikens: A change to listen_addresses requires a restart. A reload is not enough. Check the manual. Apr 2, 2012 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! Apr 3, 2012 at 12:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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