Issues with Postgres layers that have multiple geometry columns

[kohearn-cwf]

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
0aece28
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

• I'm running a supported QGIS version according to the roadmap.

New profile

• I tried with a new QGIS 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]

See also #52225.

[kohearn-cwf]

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]

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 #46078 (backported to 3.22.2 with PR #46101).

[Djedouas]

Hi, for the secondary issue, please see #57757 (comment)