[Qgis-user] PostGIS- erroneous query : Unable to get list of tables from DB

Tue Nov 19 08:16:22 PST 2019

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.laurence at 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.laurence at 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 at 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. *