Bug report #5022
postgis tables that are not in the "public" schema are not available in browser
| 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).
Associated revisions
History
#1
Updated by Giovanni Manghi about 12 years ago
I'm on master under Ubuntu 64 bit and it works fine here.
#2
Updated by Etienne Tourigny about 12 years ago
- File 0a17ad5-ok.png added
- File d2775ba-notok.png added
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 about 12 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 about 12 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 about 12 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 about 12 years ago
- Status changed from Open to Closed
- % Done changed from 0 to 100
Fixed in changeset 4c0dd4496d788ea43ca7f765f7ace5fc449074b2.
#7
Updated by Etienne Tourigny about 12 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 about 12 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 about 12 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 about 12 years ago
- Resolution set to invalid
#11
Updated by Etienne Tourigny about 12 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 about 12 years ago
- Resolution changed from invalid to fixed