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

Wed Nov 20 13:05:38 PST 2019

Thanks Borys! Yes that final grant select on the table 
pointcloud_columns unlocks the access ...

Kind regards
Laurence

On 21/11/2019 2:17 AM, Borys Jurgiel wrote:
>> 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. *
>
>
>
>