PostgreSQL 13 no longer supports FROM in CREATE EXTENSION

[robe]

Looks like our postgis 3.1 is broken again by PostgreSQL change upstream.

Debbie is showing this error on 3.1.0 (master) run

19:10:38 ERROR:  CREATE EXTENSION ... FROM is no longer supported
19:10:38 LINE 1: CREATE EXTENSION postgis VERSION '3.1.0dev' FROM unpackaged

This must be a test we don't run in the test run after postgresql is rebuilt since it didn't error there.

[robe]

Thread from IRC

02:17:49	robe2:	Komzpa good job except I don't see the release on the dev mailing list though I do see it on users list https://postgis.net/2020/02/20/postgis-3.0.1/
02:17:50	sigq:	Title: PostGIS PostGIS 3.0.1 (at postgis.net)
02:18:12	robe2:	Komzpa the exploding part yah that's not mentioned in the HOW TO :) it wasn't always that way
02:18:48	robe2:	that was my hack job to try to get something that only works for PHP 5 / PHP 7 to work on PHP 7.3
03:59:42	robe2:	RhodiumToad I assume this is an intentional change? What replaces it? https://trac.osgeo.org/postgis/ticket/4643
03:59:43	sigq:	Title: #4643 (PostgreSQL 13 no longer supports FROM in CREATE EXTENSION) – PostGIS (at trac.osgeo.org)
04:04:18	RhodiumToad:	FROM unpackaged is gone
04:04:51	RhodiumToad:	without that, the only way to upgrade an extenstion is from the version that's already installed, so no FROM option is needed
04:04:51	robe2:	RhodiumToad so how do people upgrade from unpackaged?
04:05:33	robe2:	oh so it's just CREATE EXTENSION postgis; and system checks if the things are installed?
04:06:07	RhodiumToad:	upgrading from unpackaged isn't supported any more (from pg 13+)
04:06:38	robe2:	:(
04:07:04	RhodiumToad:	it gets in the way of allowing "trusted" extensions which are installable by non-superusers
04:07:12	robe2:	In PostGIS we have a function that upgrades old version to new version so it's upgrade to the latest
04:07:46	RhodiumToad:	commit 70a77320
04:07:47	robe2:	and then the unpackaged step wraps these functions in an extension

[robe]

notes from that commit - ​https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=70a77320

Remove support for upgrading extensions from "unpackaged" state.

Andres Freund pointed out that allowing non-superusers to run
"CREATE EXTENSION ... FROM unpackaged" has security risks, since
the unpackaged-to-1.0 scripts don't try to verify that the existing
objects they're modifying are what they expect.  Just attaching such
objects to an extension doesn't seem too dangerous, but some of them
do more than that.

We could have resolved this, perhaps, by still requiring superuser
privilege to use the FROM option.  However, it's fair to ask just what
we're accomplishing by continuing to lug the unpackaged-to-1.0 scripts
forward.  None of them have received any real testing since 9.1 days,
so they may not even work anymore (even assuming that one could still
load the previous "loose" object definitions into a v13 database).
And an installation that's trying to go from pre-9.1 to v13 or later
in one jump is going to have worse compatibility problems than whether
there's a trivial way to convert their contrib modules into extension
style.

Hence, let's just drop both those scripts and the core-code support
for "CREATE EXTENSION ... FROM".

Discussion: https://postgr.es/m/20200213233015.r6rnubcvl4egdh5r@alap3.anarazel.de

[Sandro Santilli <strk@…>]

In 9e40e8e3/git:

Do not test FROM unpackaged upgrade paths with PostgreSQL ≥ 13

See #4643

[strk]

I've replied in that thread to report our problems with the discontinued support: ​https://www.postgresql.org/message-id/20200226081121.GA5242%40liz

[strk]

I've implemented the suggestion from Chapman Flack (found in the pgsql-hackers thread above) into ​https://git.osgeo.org/gitea/postgis/postgis/pulls/45

Basically, we install an empty postgis—unpackaged.sql script and use it to fool PostgreSQL into thinking we need unpackaged—targetversion for upgrading it…

The run_test.pl was changed to stop using the FROM unpackaged syntax and instead use the workaround syntax of creating the empty extension and then upgrading it.

[strk]

I've a better idea: simplify the user experience and require a single CREATE EXTENSION to either install the extension objects or package any pre-existing one. We don't need more complexity than that. We'll refrain from marking the extension as trusted until we make sure there are no open doors for attacks

[robe]

sounds fine to me. Why don't you just commit and lets see how it goes.

You think we can mark postgis extension as safe

but postgis_raster as not safe?

postgis_raster would technically not be safe if people allow out of db rasters I think.

[strk]

Why don't you just commit and lets see how it goes.

I don't have the code written, do you want to give it a try ?

[robe]

what then what's this - ​https://git.osgeo.org/gitea/postgis/postgis/commit/62644bcf05c329e1e1d4729ab0ebbc2675b2850b

Granted I didn't look at what you had there but I figured since it's in the pull request you reference that must be the code.

[strk]

That commit implements support for:

CREATE EXTENSION postgis VERSION 'unpackaged'; ALTER EXTENSION postgis UPDATE;

Basically, installs an empty "unpackaged" version….

[Sandro Santilli <strk@…>]

In 520af0e/git:

Work around PG13 drop of FROM unpackaged syntax

• Always install empty extension—unpackage.sql scripts
• Have run_test.pl use create/update trick for PG13
• Re-enable check_all_upgrades.sh testing of unpackaged
• Have postgis_extensions_upgrade use the unpackaged trick

Fixes #4643

[robe]

work around did not work so reopening this.

[strk]

How did it not work ? We provided a way to avoid the FROM part, if you're still calling FROM it's "your" fault, not a problem with the workaround…

[robe]

See #4773

[Sandro Santilli <strk@…>]

In e7c3c7f2/git:

Drop duplicated and mis-scoped variable, fixing PG13 unpackaged test

Fixes #4643 for real
Closes #4773

[Sandro Santilli <strk@…>]

In 5069436/git:

Work around PG13 drop of FROM unpackaged syntax

• Always install empty extension—unpackage.sql scripts
• Have run_test.pl use create/update trick for PG13
• Re-enable check_all_upgrades.sh testing of unpackaged
• Have postgis_extensions_upgrade use the unpackaged trick

References #4643 in stable-3.0 branch (3.0.7dev)

This code has been in branches 3.1 onward for a long time now,
but never made it into 3.0 until today when I saw 3.0 is advertised
as supporting PostgreSQL 13 here:

​https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS

This is a backport of master branch commits:

• 963aad2943924702e03b180a82bd1412c0d5880e
• 33db86ac377e060f59c353ab7b44703ebfbf90aa
• 43b1f58ed8869d4a6d0b2c58cbecc49d407e39e2
• 520af0e8d1bb6e9544d974713bb87e428ff4230b