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$
