Opened 4 years ago

Closed 3 years ago

Last modified 20 months ago

#4643 closed defect (fixed)

PostgreSQL 13 no longer supports FROM in CREATE EXTENSION

Reported by: robe Owned by: strk
Priority: blocker Milestone: PostGIS PostgreSQL
Component: build Version: master
Keywords: Cc:

Description

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.

Change History (18)

comment:1 by robe, 4 years ago

Component: postgisbuild/upgrade/install
Owner: changed from pramsey to strk

comment:2 by robe, 4 years ago

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

comment:3 by robe, 4 years ago

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

comment:4 by robe, 4 years ago

Milestone: PostGIS 3.1.0PostGIS PostgreSQL

comment:5 by Sandro Santilli <strk@…>, 4 years ago

In 9e40e8e3/git:

Do not test FROM unpackaged upgrade paths with PostgreSQL ≥ 13

See #4643

comment:6 by strk, 4 years ago

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

comment:7 by strk, 4 years ago

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.

comment:8 by strk, 4 years ago

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

comment:9 by robe, 4 years ago

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.

comment:10 by strk, 4 years ago

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 ?

comment:11 by robe, 4 years ago

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.

comment:12 by strk, 4 years ago

That commit implements support for:

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

Basically, installs an empty "unpackaged" version….

comment:13 by Sandro Santilli <strk@…>, 3 years ago

Resolution: fixed
Status: newclosed

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

comment:14 by robe, 3 years ago

Resolution: fixed
Status: closedreopened

work around did not work so reopening this.

comment:15 by strk, 3 years ago

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…

comment:17 by Sandro Santilli <strk@…>, 3 years ago

Resolution: fixed
Status: reopenedclosed

In e7c3c7f2/git:

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

Fixes #4643 for real
Closes #4773

comment:18 by Sandro Santilli <strk@…>, 20 months ago

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:

Note: See TracTickets for help on using tickets.