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.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
;
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
<[email protected] <mailto:[email protected]>> 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
[email protected] <mailto:[email protected]>
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
[email protected]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
