Bug report #5022

postgis tables that are not in the "public" schema are not available in browser

Added by Etienne Tourigny over 9 years ago. Updated over 9 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Browser
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:fixed
Crashes QGIS or corrupts data:No Copied to github as #:14798

Description

The PostGIS entry in browser (dock or standalone) does not show any layers, but instead the text "Failed to retrieve layers".

However, adding layers through the "Add PostGIS Layers" button works after clicking "connect", see attached screenshot.

Using current master (549d96c), this does not happen in 1.7.4 nor in a previous master version (0a17ad5).

qgis-browser-no-postgis.png (187 KB) Etienne Tourigny, 2012-02-15 04:45 AM

qgis-browser-0a17ad5-ok.png (161 KB) Etienne Tourigny, 2012-02-15 04:45 AM

0a17ad5-ok.png (185 KB) Etienne Tourigny, 2012-02-15 05:44 AM

d2775ba-notok.png (197 KB) Etienne Tourigny, 2012-02-15 05:44 AM

Associated revisions

History

#1 Updated by Giovanni Manghi over 9 years ago

I'm on master under Ubuntu 64 bit and it works fine here.

#2 Updated by Etienne Tourigny over 9 years ago

I have figured out the problem - my tables are in the "gis_schema", while the spatial tables are int the "public" schema. This works in 1.7.4 and 0a17ad5 master, but not in recent master. Adding 2 screenshots illustrating the problem.

I added a table to the "public" schema and now it is visible in the browser (but not the others). Both schemas are available in the "Add PostGIS Layers" dialog.

I have created the "gis_schema" because it is recommended, but not sure it it's really needed. Any way, this is unexpected behaviour.

Can you confirm this happens with you also? Thanks

#3 Updated by Giovanni Manghi over 9 years ago

  • Operating System deleted (Linux)
  • OS version deleted (Linux Mint 12)

Etienne Tourigny wrote:

I have figured out the problem - my tables are in the "gis_schema", while the spatial tables are int the "public" schema. This works in 1.7.4 and 0a17ad5 master, but not in recent master. Adding 2 screenshots illustrating the problem.

I added a table to the "public" schema and now it is visible in the browser (but not the others). Both schemas are available in the "Add PostGIS Layers" dialog.

I have created the "gis_schema" because it is recommended, but not sure it it's really needed. Any way, this is unexpected behaviour.

Can you confirm this happens with you also? Thanks

can you please change the title to reflect your new finding? thanks

#4 Updated by Etienne Tourigny over 9 years ago

I would like to but it seems I don't have the permissions... I can only add notes.

Perhaps it also should fall into another category, but I can't change that either.

The title should read: postgis tables that are not in the "public" schema are not available in browser

Thanks

#5 Updated by Giovanni Manghi over 9 years ago

  • Subject changed from postgis layers are not available in browser (master) - "Failed to retrieve layers" to postgis tables that are not in the "public" schema are not available in browser

#6 Updated by Jürgen Fischer over 9 years ago

  • Status changed from Open to Closed
  • % Done changed from 0 to 100

#7 Updated by Etienne Tourigny over 9 years ago

I cannot access my postgis tables (even in Add PostGIS layers) after applying this fix, and both schemas show "Failed to retrieve layers" in the browser.

Just to make sure I have selected "only look for tables in the public schema" (I have a table there).

Here is debugging output after I click "connect":

Debug: src/providers/postgres/qgspostgresconn.cpp: 1344: (connUri) theConnName = test
Debug: src/providers/postgres/qgspgsourceselect.cpp: 431: (on_btnConnect_clicked) Connection info: dbname='gisdb' host=localhost port=5432 user='gis' password='mypassword' sslmode=disable
Debug: src/providers/postgres/qgspostgresconn.cpp: 135: (connectDb) Using cached connection for dbname='gisdb' host=localhost port=5432 user='gis' password='mypassword' sslmode=disable
Debug: src/providers/postgres/qgspostgresconn.cpp: 305: (getTableInfo) Entering.
Debug: src/providers/postgres/qgspostgresconn.cpp: 347: (getTableInfo) getting table info: SELECT f_table_name,f_table_schema,f_geometry_column,upper(type),srid,pg_class.relkind FROM geometry_columns,pg_class,pg_namespace WHERE relname=f_table_name AND f_table_schema=nspname AND pg_namespace.oid=pg_class.relnamespace AND has_schema_privilege(pg_namespace.nspname,'usage') AND has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select') AND f_table_schema='public' ORDER BY f_table_schema,f_table_name,f_geometry_column
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : public.brazil-main.the_geom: MULTIPOLYGON 4618 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 347: (getTableInfo) getting table info: SELECT f_table_name,f_table_schema,f_geography_column,upper(type),srid,pg_class.relkind FROM geography_columns,pg_class,pg_namespace WHERE relname=f_table_name AND f_table_schema=nspname AND pg_namespace.oid=pg_class.relnamespace AND has_schema_privilege(pg_namespace.nspname,'usage') AND has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select') AND f_table_schema='public' ORDER BY f_table_schema,f_table_name,f_geography_column
Debug: src/providers/postgres/qgspostgresconn.cpp: 727: (PQexec) Not logged erroneous query: SELECT f_table_name,f_table_schema,f_geography_column,upper(type),srid,pg_class.relkind FROM geography_columns,pg_class,pg_namespace WHERE relname=f_table_name AND f_table_schema=nspname AND pg_namespace.oid=pg_class.relnamespace AND has_schema_privilege(pg_namespace.nspname,'usage') AND has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select') AND f_table_schema='public' ORDER BY f_table_schema,f_table_name,f_geography_column returned 7 [ERROR:  permission denied for relation geography_columns
]
WARNING:  there is no transaction in progress
Debug: src/core/qgsmessagelog.cpp: 41: (logMessage) 2012-02-18T16:32:41 PostGIS[0] Unable to get list of spatially enabled tables from the database

And here is the output without "only look for tables in the public schema":

Debug: src/providers/postgres/qgspostgresconn.cpp: 1344: (connUri) theConnName = test
Debug: src/providers/postgres/qgspgsourceselect.cpp: 431: (on_btnConnect_clicked) Connection info: dbname='gisdb' host=localhost port=5432 user='gis' password='mypassword' sslmode=disable
Debug: src/providers/postgres/qgspostgresconn.cpp: 135: (connectDb) Using cached connection for dbname='gisdb' host=localhost port=5432 user='gis' password='mypassword' sslmode=disable
Debug: src/providers/postgres/qgspostgresconn.cpp: 305: (getTableInfo) Entering.
Debug: src/providers/postgres/qgspostgresconn.cpp: 347: (getTableInfo) getting table info: SELECT f_table_name,f_table_schema,f_geometry_column,upper(type),srid,pg_class.relkind FROM geometry_columns,pg_class,pg_namespace WHERE relname=f_table_name AND f_table_schema=nspname AND pg_namespace.oid=pg_class.relnamespace AND has_schema_privilege(pg_namespace.nspname,'usage') AND has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select') ORDER BY f_table_schema,f_table_name,f_geometry_column
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : gis_schema.bioma_simple.the_geom: MULTIPOLYGON 4618 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : gis_schema.boundaries_polygon.the_geom: MULTIPOLYGON 4326 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : gis_schema.boundaries_polygon2.the_geom: MULTIPOLYGON 4326 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : gis_schema.brazil_cont.the_geom: MULTIPOLYGON 4618 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : gis_schema.grid-br-05.the_geom: MULTIPOLYGON 4326 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : gis_schema.modis_sinusoidal_grid_world-wgs84.the_geom: MULTIPOLYGON 4326 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 372: (getTableInfo) geometry_columns : public.brazil-main.the_geom: MULTIPOLYGON 4618 r
Debug: src/providers/postgres/qgspostgresconn.cpp: 347: (getTableInfo) getting table info: SELECT f_table_name,f_table_schema,f_geography_column,upper(type),srid,pg_class.relkind FROM geography_columns,pg_class,pg_namespace WHERE relname=f_table_name AND f_table_schema=nspname AND pg_namespace.oid=pg_class.relnamespace AND has_schema_privilege(pg_namespace.nspname,'usage') AND has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select') ORDER BY f_table_schema,f_table_name,f_geography_column
Debug: src/providers/postgres/qgspostgresconn.cpp: 727: (PQexec) Not logged erroneous query: SELECT f_table_name,f_table_schema,f_geography_column,upper(type),srid,pg_class.relkind FROM geography_columns,pg_class,pg_namespace WHERE relname=f_table_name AND f_table_schema=nspname AND pg_namespace.oid=pg_class.relnamespace AND has_schema_privilege(pg_namespace.nspname,'usage') AND has_table_privilege('"'||pg_namespace.nspname||'"."'||pg_class.relname||'"','select') ORDER BY f_table_schema,f_table_name,f_geography_column returned 7 [ERROR:  permission denied for relation geography_columns
]
WARNING:  there is no transaction in progress
Debug: src/core/qgsmessagelog.cpp: 41: (logMessage) 2012-02-18T16:36:35 PostGIS[0] Unable to get list of spatially enabled tables from the database

Thanks!

#8 Updated by Jürgen Fischer over 9 years ago

Etienne Tourigny wrote:

Here is debugging output after I click "connect":

[... ERROR: permission denied for relation geography_columns]

Why don't you have permission to query geography_columns? Should work with f432e830.

#9 Updated by Etienne Tourigny over 9 years ago

some weird permission issues with geography_columns view - resolved that and now it works (before and after f432e830).

Sorry for the trouble, but it worked previously so I had no idea about the permissions problem.

Thanks

#10 Updated by Paolo Cavallini over 9 years ago

  • Resolution set to invalid

#11 Updated by Etienne Tourigny over 9 years ago

Paolo - I am curious why you set this as invalid - there was a problem with tables not in the public schema, which is clearly not an "invalid" bug.

The problem with geography_columns was unrelated (I think).

#12 Updated by Jürgen Fischer over 9 years ago

  • Resolution changed from invalid to fixed

Also available in: Atom PDF