<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body 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=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')
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 class="moz-cite-prefix">On 19/11/2019 4:28 AM, Jason Carlson
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CAH=NrLF8A13cC0djEMM4vVp_mYt6kdDbz+aBw90SMfxMJr2UKw@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<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"
moz-do-not-send="true">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
style="font-size:12pt;font-family:"Calibri
Light",sans-serif,serif,EmojiFont"
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"
moz-do-not-send="true">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"
moz-do-not-send="true">Qgis-user@lists.osgeo.org</a><br>
List info: <a
href="https://lists.osgeo.org/mailman/listinfo/qgis-user"
rel="noreferrer" target="_blank" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/qgis-user</a><br>
Unsubscribe: <a
href="https://lists.osgeo.org/mailman/listinfo/qgis-user"
rel="noreferrer" target="_blank" moz-do-not-send="true">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"
moz-do-not-send="true">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>
</body>
</html>