[raster] upgrade drops privileges on the raster_columns view

[strk]

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.

[dustymugs]

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

[strk]

In which version was the view structure last changed ?

[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 postgis_proc_upgrade.pl 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.

[strk]

Upgrade from 2.0 fails for another reason already: #2673

[dustymugs]

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

[dustymugs]

Generally, addition of more columns.

[strk]

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

[robe]

strk,

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.

[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"…

[strk]

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

[robe]

Milestone renamed

[Algunenano]

The fact that this is assigned to "PostGIS Fund Me" leads me to believe that this isn't critical.