It seems I can downgrade with ANY

[robe]

I thought we had protection for this, but maybe the protection is only in the

ALTER EXTENSION .. UPDATE;

machinery.

To test, I installed postgis 3.3.0dev.

CREATE EXTENSION postgis;

Then followed by installing postgis 3.2.2.

Then run:

SELECT postgis_extensions_upgrade();

Downgraded me to 3.2.2.

You can do the same by messing with the control files

[Regina Obe <lr@…>]

In 2f08c4f/git:

1) Only use ANY option if superuser installing
2) Don't allow downgrade
3) Schema qual changes
Closes #5194 for PostGIS 3.3.0
Closes #5202 for PostGIS 3.3.0

[strk]

The protection should actually be in the upgrade script itself (postgis_upgrade.sql). If you can downgrade by loading postgis_upgrade.sql then this bug is not fixed yet.

The bug still exists, and is in the older postgis version. Didn't check how old but I was able to install 3.3.0dev and downgrade to 3.2.3dev

[strk]

Ok I went looking the code and it does indeed NOT attempt to protect against downgrade, but ONLY against "upgrades" between different _major_ versions. Being 3.3 and 3.2 both in the same "major" version the upgrade script does not complain.

[Sandro Santilli <strk@…>]

In 497e7934/git:

Guard against downgrades

References #5202 in master branch (3.3.0dev)

[Sandro Santilli <strk@…>]

In 60244502/git:

Guard against downgrades

References #5202 in 3.2 branch (3.2.3dev)

[Sandro Santilli <strk@…>]

In c885bc7/git:

Consider full version when guarding against downgrades

References #5202 in master branch (3.3.0dev)

[Sandro Santilli <strk@…>]

In 1f3c83b4/git:

Consider full version when guarding against downgrades

References #5202 in 3.2 branch (3.2.3dev)

[Sandro Santilli <strk@…>]

In 7d50d1b/git:

Fix extraction of integer array version on upgrade

References #5202 in master branch (3.3.0dev)

[Sandro Santilli <strk@…>]

In a04d348c/git:

Fix extraction of integer array version on upgrade

References #5202 in 3.2 branch (3.2.3dev)

[Sandro Santilli <strk@…>]

In 07f6b5f/git:

Guard against downgrades

References #5202 in 3.1 branch (3.1.7dev)

[Sandro Santilli <strk@…>]

In 1c3ae91/git:

Guard against downgrades

References #5202 in 3.0 branch (3.0.7dev)

[Sandro Santilli <strk@…>]

In be4c7b6/git:

Guard against downgrades

Closes #5202 in 2.5 branch (2.5.8dev)

[strk]

Regina this is now fixed properly, but we still don't have any CI test to check that downgrades are not possible. I may add the test to utils/check_all_upgrades.sh or create another script utils/check_all_downgrades.sh . Maybe we should have another ticket for this

[strk]

For the record: the backport to 2.5 broke something, see #5203 (ci is green in later branches)

[Sandro Santilli <strk@…>]

In e5f7f23/git:

Fix syntax of major upgrade function

This was broken in previous commit
be4c7b69d697d0819aa68b8ca3af39a95936c1eb

References #5202
Closes #5203