<div dir="auto"><div>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.<div dir="auto"><br></div><div dir="auto">Not at my computer now but try something along the lines of:</div><div dir="auto"><pre><code>GRANT SELECT ON topology TO username;</code></pre></div><br><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon., Nov. 18, 2019, 5:10 p.m. Laurence Béchet, <<a href="mailto:bechet.laurence@gmail.com">bechet.laurence@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF">
Thank you Jason for your answer.<br>
I've granted usage on schema topology to my user -> same problem.
I even granted PUBLIC. <br>
<br>
There is something 'wrong' with the passed request itself as it
fails in pgadmin as well (see it below).<br>
<br>
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.<br>
<br>
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=<a href="http://t.id" target="_blank" rel="noreferrer">t.id</a>),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')
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_description(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<br>
;<br>
<br>
I have postgresql 9.6, postGIS 2.3<br>
<br>
Kind regards<br>
Laurence<br>
<div>On 19/11/2019 4:28 AM, Jason Carlson
wrote:<br>
</div>
<blockquote type="cite">
<div dir="ltr">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.
<div><a href="https://github.com/qgis/QGIS/issues/32483" target="_blank" rel="noreferrer">https://github.com/qgis/QGIS/issues/32483</a> <br>
<div><br>
</div>
<div>I fixed by running the following SQL command (replace <b><i>USERNAME
as the username</i></b> you are using to connect to
postgres):</div>
<div><b>GRANT USAGE ON SCHEMA topology TO USERNAME;</b></div>
<div><br>
</div>
<div>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.</div>
<div>GRANT USAGE ON SCHEMA topology TO PUBLIC;</div>
<div><br>
Hope that helps. </div>
<div>
<div><br>
</div>
<div>
<div>
<div dir="ltr" data-smartmail="gmail_signature">
<div dir="ltr">
<div dir="ltr">
<p style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif;color:rgb(33,33,33)"><b><span lang="EN-US">Jason Carlson</span></b></p>
</div>
</div>
</div>
</div>
<br>
</div>
</div>
</div>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Fri, Nov 15, 2019 at 7:30
PM Laurence Béchet <<a href="mailto:bechet.laurence@gmail.com" target="_blank" rel="noreferrer">bechet.laurence@gmail.com</a>>
wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div bgcolor="#FFFFFF"> Good afternoon,<br>
<br>
Windows 8.1 pro 64 bits<br>
QGIS version 3.10.0-A Coruña QGIS code revision 6c816b4204<br>
<br>
I have a postgresql db with Postgis enabled to which I
connect using a pg service.<br>
It was working fine with the default profile.<br>
<br>
I created a new profile (settings/user profiles). My
projects were still displayed (tables located in the db) and
looked ok.<br>
<br>
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.<br>
I switched back to the former default profile: same
behaviour (no tables listed). <br>
<br>
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)<br>
<br>
In the logs I found the following messages (PostGIS
section):<br>
2019-11-16T12:27:02 WARNING NOTICE: row number 0 is
out of range 0..-1<br>
2019-11-16T12:28:20 WARNING Erroneous query: <long
query> [ERROR: permission denied for schema topology<br>
2019-11-16T12:28:20 WARNING NOTICE: WARNING: there is
no transaction in progress<br>
2019-11-16T12:28:20 WARNING Unable to get list of
spatially enabled tables from the database<br>
<br>
I don't have any problem with the default user and QGIS
version 3.4.13-Madeira<br>
(I have not tried to create another profile with 3.4.13
because I need a working version of qgis).<br>
<br>
The error message seems to point toward a lack of
permission, but that same user can run that query within
pgadmin without any problems.<br>
<br>
Any idea where to look? I looked on internet but to no avail
... <br>
<br>
Thank you in advance<br>
Laurence Bechet<br>
<b><span style="font-size:12pt;color:rgb(0,102,0)">ARK IN
THE PARK Volunteer Co-Ordinator</span></b><b><span style="font-size:14pt;color:rgb(0,102,0)"><br>
</span></b><b><span style="font-size:12pt;color:rgb(0,102,0)">Cascades
Ranger Station</span></b><b><span style="font-size:12pt;color:rgb(0,102,0)"><br>
Falls Road, Waitakere</span></b>, Auckland<b><span style="font-size:12pt;color:rgb(0,102,0)"><br>
</span></b> </div>
_______________________________________________<br>
Qgis-user mailing list<br>
<a href="mailto:Qgis-user@lists.osgeo.org" target="_blank" rel="noreferrer">Qgis-user@lists.osgeo.org</a><br>
List info: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-user" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/qgis-user</a><br>
Unsubscribe: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-user" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/qgis-user</a></blockquote>
</div>
<br>
<div><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(68,68,68);background-color:rgb(255,255,255)"><b><font size="2" face="Arial">Starland County</font></b></span></div>
<div><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(68,68,68);background-color:rgb(255,255,255)"><b><font size="2" face="Arial">Morrin, AB </font></b></span></div>
<div><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(68,68,68);background-color:rgb(255,255,255)"><b><font size="2" face="Arial">(403) 772-3793</font></b></span></div>
<div><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(68,68,68);background-color:rgb(255,255,255)"><b><font size="2" face="Arial"><i><a href="http://www.starlandcounty.com" target="_blank" rel="noreferrer">www.starlandcounty.com</a></i></font></b></span></div>
<div><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(68,68,68);background-color:rgb(255,255,255)"><i><font size="1" face="Arial"><br>
</font></i></span></div>
<div><font size="1" face="Arial"><i><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(68,68,68);background-color:rgb(255,255,255)"><span style="color:rgb(51,51,51);letter-spacing:-0.1px">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. </span></span><span style="color:rgb(68,68,68)">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. </span></i></font></div>
<br>
</blockquote>
<br>
</div>
</blockquote></div></div></div>
<br>
<div><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(68,68,68);background-color:rgb(255,255,255)"><b><font size="2" face="Arial">Starland County</font></b></span></div><div><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(68,68,68);background-color:rgb(255,255,255)"><b><font size="2" face="Arial">Morrin, AB </font></b></span></div><div><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(68,68,68);background-color:rgb(255,255,255)"><b><font size="2" face="Arial">(403) 772-3793</font></b></span></div><div><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(68,68,68);background-color:rgb(255,255,255)"><b><font size="2" face="Arial"><i><a href="http://www.starlandcounty.com" target="_blank">www.starlandcounty.com</a></i></font></b></span></div><div><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(68,68,68);background-color:rgb(255,255,255)"><i><font size="1" face="Arial"><br></font></i></span></div><div><font size="1" face="Arial"><i><span style="margin:0px;padding:0px;border:0px;vertical-align:baseline;color:rgb(68,68,68);background-color:rgb(255,255,255)"><span style="color:rgb(51,51,51);letter-spacing:-0.1px">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. </span></span><span style="color:rgb(68,68,68)">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. </span></i></font></div><br>