4

I'm trying to setup a PostGIS db using PostgreSQL 9.1 on Ubuntu 12.04. I've set up the database, created a user account "jay" with no password, added spatial functionality following section 2.5 here, and set my pg_hba.conf for local to:

# "local" is for Unix domain socket connections only
local   all             all                                     trust

I can can connect to the database using the pgAdminIII, requiring no password to connect, but connection fails when I try to connect via PostGIS or with QuantumGIS. Here's a screencap of the connection window in QuantumGIS:
PostGIS connection

Looking into the problem a bit, I came across this post which led me to try connecting via the terminal using psql -U jay -h localhost gis. psql prompted me for a password, I hit enter because I had not set a password, and it returned psql: fe_sendauth: no password supplied. I tried again, this time supplying my Ubuntu user password, which returned

psql: FATAL:  password authentication failed for user "jay"
FATAL:  password authentication failed for user "jay"

I then tried setting a password for jay using ALTER USER, and entering this password in the prompt, and this failed as well. Clearly, I am unable to connect. However, I'm having trouble figuring out what troubleshooting steps to take next.

Any ideas?

3
  • Based on the image above, just dealing with the QGIS connection problem, it looks like you need to enter "gis" (I assume that's the database name) into the Database field and "localhost" into the Host field.
    – katahdin
    Mar 13, 2013 at 22:23
  • I did try those, which gave me slightly different error messages. I don't remember them at the moment, but I will try to duplicate them this evening and post screencaps.
    – Jay Guarneri
    Mar 14, 2013 at 14:09
  • I tried this again, and your are right about entering "gis" in the database field. I had that mixed up, I suppose. I was still having trouble with my connections, though. I re-created the user and db from scratch, paying careful attention to creation options, and added an additional line to pg_hba.conf: local gis jay trust to explicitly give me access to that specific database. I was then able to connect, when filling in the fields in the way you suggested. However, I changed too many things at once, so I can't yet isolate what fixed it. Will report back later. Mar 15, 2013 at 17:44

3 Answers 3

3

Just don't leave the Database field empty in the connection dialog.

When left empty, it will attempt to connect to a database named after your OS login name, which probably doesn't exist in your case.

The Host field should be left empty to use a Unix domain socket connection, as hinted by the snippet of your pg_hba.conf shown in the question.

When you put localhost in this field, it attempts a TCP connection which is not what's called local in the pg_hba.conf terminology, so that the entry local all all trust will not match with such a connection.

2
  • This sounds like it might be the problem. I was able to get a working connection this morning, but I changed a number of things at once (see my most recent comment on my question) and I haven't had time yet to isolate which change fixed the problem. I will try this out on a fresh user/db later. Mar 15, 2013 at 17:50
  • Thanks, that was the bit of understanding I was missing! Mar 16, 2013 at 1:41
2

First try connecting to the database as the postgres user

jay@machine$ sudo su postgres
postgres@machine$ psql

Then create a new user, and why not create a database if you haven't already

postgres=# CREATE USER jay WITH password 'donttell';
postgres=# CREATE DATABASE gis;
postgres=# \q

You should now be able to connect as yourself

jay@machine$ psql gis

EDIT:

Also, look in your postgresql.conf file for

listen_addresses = '*'      # what IP address(es) to listen on;

If listen_address is commented out, then postgresql won't respond to any requests, or it may be set to only listen to something other than localhost. The '*' tells it to listen for requests from any address.

EDIT 2:

Also, try using 0.0.0.0 instead of localhost for the Host setting.

5
  • also check pg_ident.conf in postgresql configure dir
    – simplexio
    Mar 14, 2013 at 8:35
  • I set up the database exactly like this, once with and once without a password, and I wasn't able to connect. However, I have not tried checking the pg_ident.conf. I'll see if I find anything there.
    – Jay Guarneri
    Mar 14, 2013 at 14:07
  • localhost all all trust : that should be right, and use server : localhost , port: 5432.
    – simplexio
    Mar 14, 2013 at 14:29
  • Is pg_ident.conf the same as pg_hba.conf, or does it serve a different role?
    – Jay Guarneri
    Mar 14, 2013 at 15:24
  • 1
    pg_ident.conf just maps a local user to a database user. For example, if you are logged in to your computer as guarneri, but the database user is 'jay', then you can use pg_ident.conf to set up that mapping. I also edited my answer to address the postgresql.conf file.
    – katahdin
    Mar 14, 2013 at 15:40
0

I had a similar problem, but my port number was off here is my original post

  • I think PgAdmin is connecting over UNIX Sockets. They get authenticated differently in pg_hba.conf
  • You didn't show the error with QGis. I think that's a different problem. It's trying to connect over a UNIX Socket, but on the wrong port.

Connect to qgis without a username and password.

It seems likely that your port is wrong. Keep in mind that Ubuntu/Debian compiles psql to connect on a different port than 5432 the upstream default. They do this so you can have different installed versions of the server running at the same time. The different clients are compiled to different default ports too. Unfortunately, Debian/Ubuntu's qgis isn't compiled with the matching default port of the latest server, and assumes a default port of 5432. Connect with psql and run SHOW port; or check the --help to find the default port your psql connects to, psql --help | grep PORT. You'll have to hard set this port into qgis because the qgis default of 5432 is likely not applicable.

Here, I is my connection screen to prove it works. Notice my port is hard set to 5433, not 5432. Connection in QGIS

Your Answer

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

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