How to fix "Connection failed - Check settings and try again" when connecting to PostGIS?

Question

I have Postgres running via Postgres.app on rails already and am trying to connect QGis to the database.

PostGIS is working already. (questions already found online deal with this issue only)

I do not have a password or username set up for this database, as indicated in my database.yml file for rails:

development:
  adapter: postgis
  encoding: unicode
  postgis_extension: true
  schema_search_path: public,postgis
ffsdfsdfsdf  pool: 5
  database: gisdatabase
  host: localhost

So I've gone into QGis and am trying to set up a new PostGIS connection:

Service:
Host: localhost
Port: 5432
Database: gisdatabase
SSLMode: allow
username: 
password:

I've changed the port around and managed to get this error:

"could not connect to server: Connection refused..."

Changing the port back to 5432, I get a different error, thus indicating that the connection is not being refused:

"Connection failed - Check settings and try again."

I'm thinking that perhaps its the blank "Service" form or perhaps an error with the empty username and password fields that are causing the error, but I don't really know.

I've tried to find the correct Service name but have been unsuccessful

Answer 1

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.

Answer 2

In my experience, QGIS won't accept a connection without username and password. You will have to set one.