[GIS] Why won’t OGR connect to the PostGIS database, but QGIS will

ogrpostgis

Trying to connect to my PostGIS database with some success…

One hiccup is that OGR will not connect to my server from a remote machine, though QGIS can connect to it just fine from that same machine.

Here is the connection string I'm using:

ogrinfo -ro PG:"host='machineName' port='5432' user='userName' dbname='postgisDB'"

This connection works in the following scenarios:

  • Connect using 'localhost' from PostGIS server machine
  • Connect using 'machineName' from PostGIS Server machine
  • (as stated) Connect using 'machineName' through QGIS from remote machine

I've tried adding a fully qualified domain name, as well as a password, to no avail.

Thanks!!!

-m

Best Answer

The error ERROR 1: ERROR: permission denied for relation geometry_columns shows that you connected to the database. So you shouldn't need to further investigate the connection issue.

However, the message shows that the username that you connected with does not have SELECT privileges for geometry_columns. If you are able to connect and view a table from the same computer through QGIS, then it must be through a different username, like postgres.

If you want to have multiple users connect to a remote PostGIS database, there are many ways to do this. If you are trusting of the user, grant superuser privileges to the role. Don't grant a superuser privilege to any role where there is a chance of any security risk. Otherwise, you will need to grant at least SELECT privileges for geometry_columns, geography_columns (etc. views), and the spatial_ref_sys table. See this page on how to grant permissions. I normally use pgAdmin III to do this.

Related Question