QGIS browser error when using PostGIS topology extension

[cayetanobv]

Describe the bug

QGIS PostgreSQL connector raises an error and doesn't load any layer when you have Topology extension created and you are trying to access a database with a user without permission on topology schema (created automatically with admin user when you created this extension).

There is no problem when you don't have this extension created and you access to a database where you have other schemas without permission.

How to Reproduce

1. Go to Browser and open a PostGIS connection.
2. See error
   The user only has permissions on tests_2 schema but no layer is loaded because of error reported:
   

2019-10-29T12:22:37     WARNING    Erroneous query: SELECT l.f_table_name,l.f_table_schema,l.f_geometry_column,upper(l.type),l.srid,l.coord_dimension,c.relkind,obj_description(c.oid),array_agg(a.attname), count(CASE WHEN t.typname IN ('geometry','geography','topogeometry','raster') THEN 1 ELSE NULL END) , 1 FROM geometry_columns l,pg_class c,pg_namespace n,pg_attribute a,pg_type t WHERE c.relname=l.f_table_name AND l.f_table_schema=n.nspname AND NOT a.attisdropped AND a.attrelid=c.oid AND a.atttypid=t.oid AND a.attnum>0 AND n.oid=c.relnamespace AND has_schema_privilege(n.nspname,'usage') AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select') AND l.f_table_schema='topology' GROUP BY 1,2,3,4,5,6,7,c.oid,11 UNION SELECT l.f_table_name,l.f_table_schema,l.f_geography_column,upper(l.type),l.srid,2,c.relkind,obj_description(c.oid),array_agg(a.attname), count(CASE WHEN t.typname IN ('geometry','geography','topogeometry','raster') THEN 1 ELSE NULL END) , 1 FROM geography_columns l,pg_class c,pg_namespace n,pg_attribute a,pg_type t WHERE c.relname=l.f_table_name AND l.f_table_schema=n.nspname AND NOT a.attisdropped AND a.attrelid=c.oid AND a.atttypid=t.oid AND a.attnum>0 AND n.oid=c.relnamespace AND has_schema_privilege(n.nspname,'usage') AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select') AND l.f_table_schema='topology' GROUP BY 1,2,3,4,5,6,7,c.oid,11 UNION SELECT l.table_name,l.schema_name,l.feature_column,CASE WHEN l.feature_type = 1 THEN 'MULTIPOINT' WHEN l.feature_type = 2 THEN 'MULTILINESTRING' WHEN l.feature_type = 3 THEN 'MULTIPOLYGON' WHEN l.feature_type = 4 THEN 'GEOMETRYCOLLECTION' END AS type,(SELECT srid FROM topology.topology t WHERE l.topology_id=t.id),2,c.relkind,obj_description(c.oid),array_agg(a.attname), count(CASE WHEN t.typname IN ('geometry','geography','topogeometry','raster') THEN 1 ELSE NULL END) , 1 FROM topology.layer l,pg_class c,pg_namespace n,pg_attribute a,pg_type t WHERE c.relname=l.table_name AND l.schema_name=n.nspname AND NOT a.attisdropped AND a.attrelid=c.oid AND a.atttypid=t.oid AND a.attnum>0 AND n.oid=c.relnamespace AND has_schema_privilege(n.nspname,'usage') AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select') AND l.schema_name='topology' GROUP BY 1,2,3,4,5,6,7,c.oid,11 UNION SELECT l."r_table_name",l."r_table_schema",l."r_raster_column",'RASTER',l.srid,2,c.relkind,obj_description(c.oid),array_agg(a.attname), count(CASE WHEN t.typname IN ('geometry','geography','topogeometry','raster') THEN 1 ELSE NULL END) , 1 FROM raster_columns l,pg_class c,pg_namespace n,pg_attribute a,pg_type t WHERE c.relname=l."r_table_name" AND l."r_table_schema"=n.nspname AND NOT a.attisdropped AND a.attrelid=c.oid AND a.atttypid=t.oid AND a.attnum>0 AND n.oid=c.relnamespace AND has_schema_privilege(n.nspname,'usage') AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select') AND l."r_table_schema"='topology' GROUP BY 1,2,3,4,5,6,7,c.oid,11 ORDER BY 2,1,3 returned 7 [ERROR: permission denied for schema topology
             LÍNEA 1: ...ometry','raster') THEN 1 ELSE NULL END) , 1 FROM topology.l...
              ^
             ]
2019-10-29T12:22:37     WARNING    NOTICE: WARNING: there is no transaction in progress
2019-10-29T12:22:37     WARNING    Unable to get list of spatially enabled tables from the database

If I drop topology extension there is no ERROR and tests_2 layers are loaded:

QGIS and OS versions

QGIS version 3.10.0-A Coruña
Ubuntu 18.04

Additional context

This problem starts with QGIS 3.10 (QGIS 3.8 didn't raise this error).

[veragreen119]

I would like to add that we are having this issue as well but only with PG10.10, no issue with PG11.5 or PG12.0.

The issue can be resolved by granting permissions:
GRANT USAGE ON SCHEMA topology TO PUBLIC;

[starlandcounty]

The issue can be resolved by granting permissions:
GRANT USAGE ON SCHEMA topology TO PUBLIC;

Thanks veragreen119 the work-around worked for me, except instead of using PUBLIC, I used the user that I'm connecting as. I should mention I'm also using PostgreSQL 10.10 on the system I noticed the issue with.

[cayetanobv]

I would like to add that we are having this issue as well but only with PG10.10, no issue with PG11.5 or PG12.0.

This issue was created using PostgreSQL version 11.5 (and PostGIS 2.5).

The issue can be resolved by granting permissions:
GRANT USAGE ON SCHEMA topology TO PUBLIC;

This workaround only works if the user has permission over the schema Topology.

[borysiasty]

It is fixed in 9c4f77f, isn't it? It will be released in 3.10.1 (in a week).

[cayetanobv]

@borysiasty I think so. I'll try to test it when that version will be released

[gioman]

This is duplicate of #32726