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$ 
