search

qgis / QGIS

QGIS is a free, open source, cross platform (lin/win/mac) geographical information system (GIS)
https://qgis.org
GNU General Public License v2.0
10.43k stars 2.98k forks source link

Issues with Postgres layers that have multiple geometry columns #53892

Open kohearn-cwf opened 1 year ago

kohearn-cwf commented 1 year ago

What is the bug or the crash?

I've noticed some very odd behaviour working with Postgres layers with multiple geometry columns in QGIS.

Main issue:

This issue is present in 3.26.0 and the latest LTR - 3.28.8-Firenze. It is not present in QGIS 3.22.1-Białowieża, but may be present in other versions after that (I have not checked all versions to see where the issues begin).

When editing a Postgres table in QGIS that has multiple geometry columns, if a user does not have UPDATE permissions on all geometry columns, the user will not be able to save edits to other columns that they have been granted UPDATE permissions on. No edits are being made to the geometry columns in these cases.

There are no issues for the same user if they execute a query to make that update using the PostgreSQL execute SQL tool in QGIS or through a GUI like pgAdmin or DBeaver.

The only solution I have found that does not require granting UPDATE permissions is to set the additional geometry columns in QGIS to not be editable - either through the Editable checkbox in the Attributes Form properties, or making the column hidden in the Attribute form.

Secondary issue:

This issue seems to be present in 3.22.1-Białowieża and beyond - it might also exist in older versions.

Geoprocessing tools like Buffer or Reproject Layer also fail on Postgres tables that have multiple geometry columns. The output file type does not seem to matter.

Steps to reproduce the issue

Sample data is attached: sample_data.zip

  1. Create a PostgreSQL table with a primary key, a geometry column, and one or more additional columns.
  2. Populate this table with some data.
  3. Add a new geometry column that reprojects the original geometry to a new SRID.
  4. Grant USAGE permission on the schema and SELECT permission on the table to the user. Grant UPDATE permissions on one of the additional columns that are not the primary key or the geometry columns.
  5. In QGIS, connect to the database with the user's credentials.
  6. Load the table in QGIS, using any of the geometry columns.
  7. Start editing and change the value in one of the additional columns where UPDATE permissions were granted.
  8. Try to save your edits
  9. Get this error message:

Could not commit changes to layer Errors: ERROR: 1 attribute value change not applied. Provider errors: PostGIS error while changing attributes: ERROR: permission denied for table

Starting from Step 6, if you then try running geoprocessing tools like Buffer or Reproject Layer, this error will pop up:

Feature could not be written to Buffered_d5d62f46_4143_4263_9181_54bab99468a1: Could not store attribute "geometry_m": > Could not convert value "" to target type Could not write feature into OUTPUT Execution failed after 0.29 seconds

Versions

QGIS version 3.26.0-Buenos Aires QGIS code revision 0aece2818e Qt version 5.15.3 Python version 3.9.5 GDAL/OGR version 3.5.0 PROJ version 9.0.1 EPSG Registry database version v10.064 (2022-05-19) GEOS version 3.10.3-CAPI-1.16.1 SQLite version 3.38.1 PDAL version 2.3.0 PostgreSQL client version unknown SpatiaLite version 5.0.1 QWT version 6.1.6 QScintilla2 version 2.13.1 OS version Windows 10 Version 2009

Active Python plugins db_manager 0.1.20 processing 2.12.99

Supported QGIS version

New profile

Additional context

Storing this data in two separate Postgres tables is not an ideal solution, as we have users who need to switch between spatial reference systems while viewing and editing attributes in this data.

The geometry columns are the same type (Point), just different SRIDs.

agiudiceandrea commented 1 year ago

See also https://github.com/qgis/QGIS/issues/52225.

kohearn-cwf commented 1 year ago

Update: the editing issue identified appears to begin in 3.22.2. One of my colleagues has this version of QGIS and was unable to save edits to this layer in 3.22.2, but was able to save edits in 3.22.1.

agiudiceandrea commented 1 year ago

Hi @kohearn-cwf, the only change I've found in the Postgres provider code between 3.22.1 and 3.22.2 was made with PR https://github.com/qgis/QGIS/pull/46078 (backported to 3.22.2 with PR https://github.com/qgis/QGIS/pull/46101).

Djedouas commented 3 months ago

Hi, for the secondary issue, please see https://github.com/qgis/QGIS/issues/57757#issuecomment-2180063971