Opened 3 years ago

Last modified 5 weeks ago

#2823 new defect

[raster] upgrade drops privileges on the raster_columns view

Reported by: strk Owned by: dustymugs
Priority: critical Milestone: PostGIS Fund Me
Component: raster Version: trunk
Keywords: Cc:


Whenever you ALTER EXTENSION postgis UPDATE ("next" is enough) the "raster_columns" view looses all the permissions settings (grants).

The "raster_overviews" view is fine instead. This is from 2.2.0dev to 2.2.0devnext and back, hadn't tried with older versions.

Change History (13)

comment:1 Changed 3 years ago by strk

Component: postgisraster
Owner: changed from pramsey to dustymugs
Summary: Raster upgrade drops privileges on the raster_columns view[raster] upgrade drops privileges on the raster_columns view

comment:2 Changed 3 years ago by dustymugs

I see what's going on. In the upgrade, the raster_columns view is dropped as the view has new columns.

comment:3 Changed 3 years ago by strk

In which version was the view structure last changed ?

comment:4 Changed 3 years ago by strk

Extension upgrade from 2.1.0 to 2.2.0dev works fine without the DROP VIEW. I'm about to test 2.0.x to 2.2.x upgrade. I guess this would make a new label to recognize by to help it decide when to DROP views before the CREATE OR REPLACE call. It is not necessarely "Last Updated" in that you can update it in compatible ways, so we have to find an easy to remember and document label, if any.

comment:5 Changed 3 years ago by strk

Upgrade from 2.0 fails for another reason already: #2673

comment:6 Changed 3 years ago by dustymugs

The view structure has changed in every version. 2.1 is different than 2.2.

comment:7 Changed 3 years ago by dustymugs

Generally, addition of more columns.

comment:8 Changed 3 years ago by strk

The code forcing a drop is in raster/rt_pg/ Instead, there could be a smarter code path, like the one used to rename old table version of those objects in Such smarter code path would take note of permissions on the views, and recreate those permissions.

comment:9 Changed 3 years ago by robe

Milestone: PostGIS 2.2.0


The more global issue is the GRANT statements in the full scripts are being stripped presumably by our upgrade script generator so we should have this issue anytime we drop a view. Can we just always include the GRANTS? I don't think there is a case where applying grants more than once is an issue so we can simply just always include it. That will fix this issue and the future cases where we need to drop views.

I tried running this with my 2.1.3 to 2.1.3next and don't see the issue there. I also don't see a DROP VIEW raster_columns in the 2.1.3 upgrade scripts, so this was added in 2.2 to add a new column. If we add the column at the end, we don't even need to drop. Only need to drop if we are changing column defs or adding a column in between others which we should probably avoid.

I thought our upgrade scripts now handle view changes no? So we shouldn't even need to put a drop in the rtpostgis_upgrade_cleanup even if we really needed to drop a view.

comment:10 Changed 3 years ago by strk

I guess the issue on upgrade is that someone may not want the upgrade to change permissions that were manually changed to something else. Hard to detect as a condition as it would imply knowing the system-defined permissions as of the version it's been upgraded from.

Not dropping is surely a good idea, generally. But there will still be cases in which you'll need to. In those cases, the upgrade script should probably note the permissions before the drop and re-set them after the creation. Beside augmented complexity, this behavior would make it impossible for an upgrade to change default permissions. The only way to not have that problem would be for permissions to not be part of the install scripts at all, but maybe have a function to "set default permissions"...

comment:11 Changed 3 years ago by strk

The "add new column at the end" is a great idea, if it works, btw.

comment:12 Changed 2 years ago by dustymugs

Milestone: PostGIS 2.2.0PostGIS Future

comment:13 Changed 5 weeks ago by robe

Milestone: PostGIS FuturePostGIS Fund Me

Milestone renamed

Note: See TracTickets for help on using tickets.