DB Manager can't connect to PostgreSQL, but "Add PostGIS Layer" can.

My eventual goal is to save created layers to PostGIS. I assume I can do this using the DBManager Tool. I can alternatively use ogr2ogr or shp2pgsql, but I was hoping QGIS would make workflows between shapefiles and PostGIS a bit easier.

This behavior seems odd. Why can it connect one way and not the other?

enter image description here

Side Note: Why does DBManager display my password back to me?

I can connect easily with psql on command line

enter image description here

Additional Info:

  • This is a local database
  • When I originally connected using 'Add PostGIS Layer', I chose not to save the username and password. I am connecting using my OS user account.
  • I'm running OS X 10.9.2, PostGIS 2.1, PostgreSQL 9.3.3, and QGIS 2.2.0
  • PostgreSQL, PostGIS and QGIS were all installed using Homebrew. See Homebrew install info below.

Homebrew info:

brew info qgis-22
qgis-22: stable 2.2.0, HEAD
/usr/local/Cellar/qgis-22/2.2.0 (14074 files, 493M) *
  Built from source with: --with-grass, --with-api-docs, --with-postgis
From: https://github.com/dakcarto/homebrew-osgeo4mac/commits/master/Formula/qgis-22.rb
==> Dependencies
Build: cmake ✔, bison ✔
Required: qt ✔, pyqt ✔, qscintilla2 ✔, qwt ✔, qwtpolar ✔, gsl ✔, sqlite ✔, expat ✔, proj ✔, spatialindex ✔, fcgi ✔, gdal ✔, pyspatialite ✔
Recommended: postgresql ✔, gpsbabel ✔
Optional: postgis ✔, grass ✔, qt-mysql ✘, orfeo ✘, r ✘, saga-gis ✘

brew info postgis
postgis: stable 2.1.1, HEAD
/usr/local/Cellar/postgis/2.1.1 (44 files, 8.3M) *
  Built from source
From: https://github.com/Homebrew/homebrew/commits/master/Library/Formula/postgis.rb
==> Dependencies
Build: autoconf ✔, automake ✔, libtool ✔, gpp ✔
Required: postgresql ✔, proj ✔, geos ✔, json-c ✔, gdal ✔

brew info postgresql
postgresql: stable 9.3.3 (bottled)
Conflicts with: postgres-xc
/usr/local/Cellar/postgresql/9.3.2 (2968 files, 66M)
  Poured from bottle
/usr/local/Cellar/postgresql/9.3.3 (2967 files, 65M) *
  Poured from bottle
From: https://github.com/Homebrew/homebrew/commits/master/Library/Formula/postgresql.rb
==> Dependencies
Required: readline ✔
Recommended: ossp-uuid ✔
  • I have the same problem. Did you solve it? – Maurocrispin Mar 22 '18 at 17:08

=== Work on My Mac ===

I hope this can solve your problem to.

The problem because Qgis Connect with TCP / IP protocol, refer to this link : http://hub.qgis.org/issues/735

QGIS uses tcp/ip connections to PostgreSQL, regardless of whether both client and server are on the same host. You can configure PostgreSQL to accept such connections as trusted, however doing so may be a security risk depending on your environment.

The reason it works from psql for you is that your user is trusted for local connections using sockets. If you want to allow connections from QGIS without a password you can modify your pg_hba.conf file and add a line similar to this:

host mydatabase myuser trust

This will allow the user "myuser" trusted access to database "mydatabase" when connecting from QGIS on the localhost.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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