More

OpenGeo postgres installation not working from command line but works from PGadminIII on fresh install

OpenGeo postgres installation not working from command line but works from PGadminIII on fresh install


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

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.


You did not change thelocalline, it'speermode.

If you want to usemd5auth instead, you must configuremd5auth, e.g.

local all all md5

instead of your current:

local all all peer

Watch the video: How to Install and Setup PostgreSQL on Windows 10