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

Borys Jurgiel lists at borysjurgiel.pl
Wed Nov 20 05:17:17 PST 2019 

> Thanks Boris and Jason for your messages. I tried your suggestion but it
> still doesn't work (new error message 'permission denied for relation
> pointcloud_columns').

So apparently you successfully fixed permissions to the topology tables and 
then you hit a similar problem related to the pointcloud_columns table/view, 
coming from another extension: pointcloud. Just try to grant select on it 
(alternatively, you can just remove the extension if you don't use it).

The first problem is already fixed in master, for the new one I just opened a 
ticket: https://github.com/qgis/QGIS/issues/32972

> However, I think that you miss the point somehow:
> 1) I can retrieve the tables if I use my qgis 3.4 version, but I cannot
> if I use my 3.10 version (manage layers toolbar or browser). 

Yes, because it's a regression in 3.10.

> I have
> reinstalled the 3.10 - no changes. I have reinstalled postgis - no changes.

Usually, reinstalling gives nothing. Please wait until next week for 3.10.1 - 
it contains the fix for the topology extension at least. For the pointcloud, I 
can't guarantee Sandro or someone else will be able to fix it before the 
release (it's only one day left), so just follow the ticket I mentioned above 
(you can consider sending a bottle of whisky to New Zealand to encourage 
Sandro to fix it ;-)

> 2) I can access my tables if I use the DB manager within 3.10.

Yes, because it uses another connection.

> 2) It happened after I created a new profile in 3.10. I never had used a
> profile <> than the default one before.

It shouldn't have anything to do (btw. multiple profiles are really useful, 
don't be discouraged!)

Regards,
Borys


> 
> Kind regards
> Laurence
> 
> On 20/11/2019 5:16 AM, Borys Jurgiel wrote:
> > 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.sri
> >>> d,
> >>> 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.attna
> >>> m
> >>> 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_descrip
> >>> ti
> >>> 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. *

More information about the Qgis-user mailing list