Bug report #11417

Listing PostGIS layers from an EnterpriseDb fails

Added by Wouter Boasson over 9 years ago. Updated about 5 years ago.

Category:Data Provider/PostGIS
Affected QGIS version:2.4.0 Regression?:No
Operating System:All Easy fix?:No
Pull Request or Patch supplied:No Resolution:end of life
Crashes QGIS or corrupts data:No Copied to github as #:19694


Listing postgis layers from an EnterpriseDb postgres database fails. Connecting to the database is no problem, but when clicking 'Connect' the layer list remains empty. The query used to retrieve information on the available layers also lists non existing schema's, which makes the has_schema_privilege(n.nspname,'usage') function call fails on non-existing schema's, leading to errors like these:
2014-10-15 08:44:38 CEST ERROR schema "dbms_rls" does not exist

Cause: the EnterpriseDb catalog also contains virtual schema's for their Oracle compatibility layer, using an extended pg_namespace table. Affects all versions of QGis and EnterpriseDb Postgres.

My proposed solution is to take the ANSI Schema (information_schema) into account, as here the virtual schema's are filtered out. E.g. join or use an additional WHERE constraint. This approach should solve the problem, and is compatible with vanilla postgres databases:

SELECT l.f_table_name,l.f_table_schema,l.f_geometry_column,upper(l.type),l.srid,c.relkind
FROM geometry_columns l,pg_class c,pg_namespace n
WHERE c.relname=l.f_table_name
  AND l.f_table_schema=n.nspname
  AND n.oid=c.relnamespace
  AND has_schema_privilege(n.nspname,'usage')
  AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select')
  AND n.nspname IN (SELECT schema_name FROM information_schema.schemata)
ORDER BY n.nspname,c.relname,l.f_geometry_column


#1 Updated by Giovanni Manghi almost 7 years ago

  • Easy fix? set to No
  • Regression? set to No

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

  • Assignee deleted (Jürgen Fischer)

#3 Updated by Giovanni Manghi about 5 years ago

  • Resolution set to end of life
  • Status changed from Open to Closed

Also available in: Atom PDF