[Qgis-user] PostGIS- erroneous query : Unable to get list of tables from DB
Laurence Béchet
bechet.laurence at gmail.com
Tue Nov 19 15:11:37 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').
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). I have
reinstalled the 3.10 - no changes. I have reinstalled postgis - no changes.
2) I can access my tables if I use the DB manager within 3.10.
2) It happened after I created a new profile in 3.10. I never had used a
profile <> than the default one before.
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.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. *
>
>
>
>
More information about the Qgis-user
mailing list