Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from gis.stackexchange.com Mar 15 '13 at 6:47

This question came from our site for cartographers, geographers and GIS professionals.

    
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 '13 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 '13 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. –  Jay Guarneri Mar 15 '13 at 17:44
add comment

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
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. –  Jay Guarneri Mar 15 '13 at 17:50
    
Thanks, that was the bit of understanding I was missing! –  Jay Guarneri Mar 16 '13 at 1:41
add comment

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.

share|improve this answer
    
also check pg_ident.conf in postgresql configure dir –  simplexio Mar 14 '13 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 '13 at 14:07
    
localhost all all trust : that should be right, and use server : localhost , port: 5432. –  simplexio Mar 14 '13 at 14:29
    
Is pg_ident.conf the same as pg_hba.conf, or does it serve a different role? –  Jay Guarneri Mar 14 '13 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 '13 at 15:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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