It's fixed now in master, so the upcoming 3.10.1 (to be released in a few days) should be free from that bug.
For now, as GRANT USAGE ON SCHEMA is not enough, try also grant permissions to two tables used in that failing query: GRANT SELECT ON TABLE topology.topology GRANT SELECT ON TABLE topology.layer Regards, Borys Dnia wtorek, 19 listopada 2019 15:18:17 CET Jason Carlson pisze: > The error message would still suggest it is an access problem to the > topology table. Perhaps you need to take it a step further with your > postgres version and grant select to the user. > > Not at my computer now but try something along the lines of: > > GRANT SELECT ON topology TO username; > > > > On Mon., Nov. 18, 2019, 5:10 p.m. Laurence Béchet, < > > bechet.laure...@gmail.com> wrote: > > Thank you Jason for your answer. > > I've granted usage on schema topology to my user -> same problem. I even > > granted PUBLIC. > > > > There is something 'wrong' with the passed request itself as it fails in > > pgadmin as well (see it below). > > > > I have a very basic test.qgz file. I launch it with 3.4.13 (I've updated > > all my qgis versions), open the browser, connect to my db and see the > > tables. Same file, launched with 3.10.0, open browser, connect to my db > > (same profile so I guess same qgis-auth.db file I suppose) and get the > > 'failed to get layers' as described in the bug. I can see the sql request > > which failed and that one doesn't work either in pg_admin. Unfortunately > > the sql request passed with 3.4.13 is not displayed. > > > > 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') 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') 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.attnam > > e), 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') GROUP > > BY 1,2,3,4,5,6,7,c.oid,11 UNION SELECT > > l."table",l."schema",l."column",'POLYGON',l.srid,2,c.relkind,obj_descripti > > on(c.oid),array_agg(a.attname), count(CASE WHEN t.typname IN > > ('geometry','geography','topogeometry','raster') THEN 1 ELSE NULL END) , 1 > > FROM pointcloud_columns l,pg_class c,pg_namespace n,pg_attribute a,pg_type > > t WHERE c.relname=l."table" AND l."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') 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') GROUP > > BY 1,2,3,4,5,6,7,c.oid,11 ORDER BY 2,1,3 > > ; > > > > I have postgresql 9.6, postGIS 2.3 > > > > Kind regards > > Laurence > > On 19/11/2019 4:28 AM, Jason Carlson wrote: > > > > I think your issue sounds like what I experienced and from what I dug up > > it can be as simple as what version of Postgres you were using (I was > > using > > Postgres version 10.10) not allowing any access to the "topology" schema. > > https://github.com/qgis/QGIS/issues/32483 > > > > I fixed by running the following SQL command (replace *USERNAME as the > > username* you are using to connect to postgres): > > *GRANT USAGE ON SCHEMA topology TO USERNAME;* > > > > it was also suggested you could instead give access to public but I'm not > > sure if that is as secure but maybe other issues are created if public > > doesn't have access, this is not my area of expertise. > > GRANT USAGE ON SCHEMA topology TO PUBLIC; > > > > Hope that helps. > > > > *Jason Carlson* > > > > > > On Fri, Nov 15, 2019 at 7:30 PM Laurence Béchet > > <bechet.laure...@gmail.com> > > > > wrote: > >> Good afternoon, > >> > >> Windows 8.1 pro 64 bits > >> QGIS version 3.10.0-A Coruña QGIS code revision 6c816b4204 > >> > >> I have a postgresql db with Postgis enabled to which I connect using a pg > >> service. > >> It was working fine with the default profile. > >> > >> I created a new profile (settings/user profiles). My projects were still > >> displayed (tables located in the db) and looked ok. > >> > >> But when I wanted to add a new table from datasource manager/postgresql I > >> was prompted the first time to create a connection. I created the same > >> one > >> as I had for the default profile. I tested the connection and it passed. > >> I > >> clicked to 'connect' and nothing happened: no table list displayed. I > >> checked with DB/DB manager and I can see my tables. > >> I switched back to the former default profile: same behaviour (no tables > >> listed). > >> > >> There are different qgis-auth.db files (different sizes) in each profile > >> folder of C:\Users\<myuser>\AppData\Roaming\QGIS\QGIS3\profiles (don't > >> know > >> how to read them though) > >> > >> In the logs I found the following messages (PostGIS section): > >> 2019-11-16T12:27:02 WARNING NOTICE: row number 0 is out of range > >> 0..-1 > >> 2019-11-16T12:28:20 WARNING Erroneous query: <long query> [ERROR: > >> permission denied for schema topology > >> 2019-11-16T12:28:20 WARNING NOTICE: WARNING: there is no > >> transaction in progress > >> 2019-11-16T12:28:20 WARNING Unable to get list of spatially > >> enabled tables from the database > >> > >> I don't have any problem with the default user and QGIS version > >> 3.4.13-Madeira > >> (I have not tried to create another profile with 3.4.13 because I need a > >> working version of qgis). > >> > >> The error message seems to point toward a lack of permission, but that > >> same user can run that query within pgadmin without any problems. > >> > >> Any idea where to look? I looked on internet but to no avail ... > >> > >> Thank you in advance > >> Laurence Bechet > >> *ARK IN THE PARK Volunteer Co-Ordinator* > >> *Cascades Ranger Station* > >> * Falls Road, Waitakere*, Auckland > >> _______________________________________________ > >> Qgis-user mailing list > >> Qgis-user@lists.osgeo.org > >> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > >> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user > > > > *Starland County* > > *Morrin, AB * > > *(403) 772-3793* > > *www.starlandcounty.com <http://www.starlandcounty.com>* > > > > *Our organization accepts no liability for the content of this email, or > > for the consequences of any actions taken on the basis of the information > > provided, unless that information is subsequently confirmed in writing. > > The > > content of this message is confidential. If you have received it by > > mistake, please inform us by an email reply and then delete the message. > > It > > is forbidden to copy, forward, or in any way reveal the contents of this > > message to anyone. * _______________________________________________ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user