QGIS » QGIS Application

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