3

Problem:

I just did a default install of opengeo suite (4.1) on Ubuntu 12.04 and followed instructions exactly. The problem is that I can connect just fine to my postgresql through pgadminIII (1.18.1), but I can't connect on command line. Has anyone encountered this and if so, how did you fix?

background:

As I said above, I am installing opengeo suite (4.1) on a 32bit ubuntu 12.04 installation. In fact I did it twice, once at home and once at work and I get the same error so it is repeatable:

Basically, i follow all the instructions on the opengeo website. (http://suite.opengeo.org/opengeo-docs/installation/ubuntu/install.html)

I am sure I am using the 12.04 repository to download software. I changed the postgres.conf and pg_hba.conf to listen on my machine and accept outside connections and use md5 authentication.

I created a password for the postgres user manually like it instructs. However, when it comes time to test the command line, it doesn't authenticate. I get the error:

~$ psql -U postgres -W 
Password for user postgres: 
psql: FATAL:  Peer authentication failed for user "postgres"

The problem is, that I can connect just fine through pgadminIII (1.18.1). I can run the postgis extension and it creates my postgis database. I can even edit files I created in QGIS. But I can't log in via command line so I can't run the shp2pgsql to load any preexisting data. This is a fresh installation on a machine and, as I mentioned, I tried it on two separate machines (both running ubuntu 12.04 x32).

my pga_hb.conf file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             192.168.0.0/16          md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5
2
  • Show the actual pg_hba.conf and explain the specific postgresql.conf changes made please. Did you reload/restart PostgreSQL after the change? Aug 10, 2014 at 11:45
  • Yes i do reload the service every time after making changes. postgres.conf i just changed the listen address to the ip of the machine, in this case- Listen_addresses = '192.168.1.101'. pga_hb.conf added above.
    – Nick
    Aug 11, 2014 at 14:42

2 Answers 2

2

You did not change the local line, it's peer mode.

If you want to use md5 auth instead, you must configure md5 auth, e.g.

local   all             all                                     md5

instead of your current:

local   all             all                                     peer
1

In psql try using -h localhost

psql -h localhost -U postgres -W 

I think psql by default tries to connect on local port (which is using unix sockets) instead of localhost which uses tcpip. I bet your pg_hba.conf is set to only use peer for local and md5 for localhost. So opengeo is using the localhost connection while psql is trying to use the local connection.

3
  • <linuxprompt>:~$ psql -h localhost -U postgres -W Password for user postgres: psql: could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?
    – Nick
    Aug 11, 2014 at 14:49
  • Since you do have the ip change -h to -h 192.168.1.101 . Normally in postgresql.conf I just set listen to * so if the IP of server changes I don't have to fiddle with the config. You can also do what Craig suggests and change to md5 for local. Then your local connection will work fine.
    – LR1234567
    Aug 11, 2014 at 19:10
  • LR1234567 that was it! i entered the ip instead of local host and i got in. Thank you so much!
    – Nick
    Aug 12, 2014 at 4:27

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.