Listing PostGIS layers from an EnterpriseDb fails

[qgib]

Author Name: Wouter Boasson (Wouter Boasson)
Original Redmine Issue: 11417
Affected QGIS version: 2.4.0
Redmine category:data_provider/postgis

---

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:

<code class="sql">
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
</code>

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• easy_fix was configured as 0
• regression was configured as 0

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

• assigned_to_id removed Jürgen Fischer

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

End of life notice: QGIS 2.18 LTR

Source:
http://blog.qgis.org/2019/03/09/end-of-life-notice-qgis-2-18-ltr/

---

• resolution was changed from to end of life
• status_id was changed from Open to Closed