Thread: Re: [postgis-devel] About EXTENSION from UNPACKAGED on PostgreSQL 13

Hi,

PostGIS 2.5 had raster and vector blended together in single extension.
In PostGIS 3, they were split out into postgis and postgis_raster extensions.
To upgrade, there is now postgis_extensions_upgrade() function, that
unpackages the raster part out of postgis extensions, upgrades it, and
packages raster functions back into postgis_raster by utilizing FROM
UNPACKAGED.
Removal of FROM UNPACKAGED breaks PostGIS 2.5 -> 3.0 upgrade path, and
we haven't yet found a proper replacement since such removal wasn't
something we were expecting.

On Tue, Feb 25, 2020 at 11:37 PM Stephen Frost <sfrost@snowman.net> wrote:
>
> Greetings,
>
> * Darafei "Komяpa" Praliaskouski (me@komzpa.net) wrote:
> > can it be raised on pgsql-hackers as a thing impacting PostGIS upgrade path?
>
> Why is it impacting the PostGIS upgrade path?  The FROM UNPACKAGED was
> never intended to be used as an upgrade path..
>
> Thanks,
>
> Stephen
> _______________________________________________
> postgis-devel mailing list
> postgis-devel@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel



--
Darafei Praliaskouski
Support me: http://patreon.com/komzpa

=?UTF-8?Q?Darafei_=22Kom=D1=8Fpa=22_Praliaskouski?= <me@komzpa.net> writes:
> Removal of FROM UNPACKAGED breaks PostGIS 2.5 -> 3.0 upgrade path, and
> we haven't yet found a proper replacement since such removal wasn't
> something we were expecting.

I'd agree with Stephen's comment:

> On Tue, Feb 25, 2020 at 11:37 PM Stephen Frost <sfrost@snowman.net> wrote:
>> Why is it impacting the PostGIS upgrade path?  The FROM UNPACKAGED was
>> never intended to be used as an upgrade path..

This seems like a serious abuse of the FROM option, not to mention being
fundamentally unsafe --- the whole problem with FROM is that you can't
be entirely sure what the starting state is.  So unless you can make a
pretty strong case as to why you need to do it like that, and that there's
no other way to handle it in the many months before v13 ships, I'm not
going to have a lot of sympathy.

            regards, tom lane

Greetings,

* Darafei "Komяpa" Praliaskouski (me@komzpa.net) wrote:
> PostGIS 2.5 had raster and vector blended together in single extension.
> In PostGIS 3, they were split out into postgis and postgis_raster extensions.

For my 2c, at least, I still don't really get why that split was done.

> To upgrade, there is now postgis_extensions_upgrade() function, that
> unpackages the raster part out of postgis extensions, upgrades it, and
> packages raster functions back into postgis_raster by utilizing FROM
> UNPACKAGED.
> Removal of FROM UNPACKAGED breaks PostGIS 2.5 -> 3.0 upgrade path, and
> we haven't yet found a proper replacement since such removal wasn't
> something we were expecting.

I agree that there probably isn't a very good path to allow an extension
to be split up like that without having to drop some things.  An
alternative would have been to *not* split up postgis, but rather to
have a postgis_raster and a postgis_vector.  Adding in support for other
ways to migrate a function from one extension to another would make
sense too.

Thanks,

Stephen

On Wed, Feb 26, 2020 at 08:55:03AM -0500, Stephen Frost wrote:
> Greetings,
> 
> * Darafei "Komяpa" Praliaskouski (me@komzpa.net) wrote:
> > PostGIS 2.5 had raster and vector blended together in single extension.
> > In PostGIS 3, they were split out into postgis and postgis_raster extensions.
> 
> For my 2c, at least, I still don't really get why that split was done.

It's pretty easy to understand: to let user decide what he needs and
what not.

> > Removal of FROM UNPACKAGED breaks PostGIS 2.5 -> 3.0 upgrade path, and
> > we haven't yet found a proper replacement since such removal wasn't
> > something we were expecting.
> 
> I agree that there probably isn't a very good path to allow an extension
> to be split up like that without having to drop some things.  An
> alternative would have been to *not* split up postgis, but rather to
> have a postgis_raster and a postgis_vector.  Adding in support for other
> ways to migrate a function from one extension to another would make
> sense too.

I think support for migrating an object between extensions DOES exist,
it's just that you cannot use it from extension upgrade scripts.

Anyway pgsql-hackers is not the right place for discussion.
On pgsql-hackers we only want to find a future-proof way to "package
existing objects into an extension".  If the syntax
`CREATE EXTENSION <extname> FROM UNPACKAGED` 
has gone, would it be ok for just:
`CREATE EXTENSION <extname>`
to intercept unpackaged objects and package them ?

--strk;

> On 26 Feb 2020, at 15:13, Sandro Santilli <strk@kbt.io> wrote:

> On pgsql-hackers we only want to find a future-proof way to "package
> existing objects into an extension".

What is the longterm goal of PostGIS, to use this as a stepping stone to reach
a point where no unpackaged extensions exist; or find a way to continue with
the current setup except with syntax that isn't going away?

> If the syntax
> `CREATE EXTENSION <extname> FROM UNPACKAGED`
> has gone, would it be ok for just:
> `CREATE EXTENSION <extname>`
> to intercept unpackaged objects and package them ?

Overloading the same syntax for creating packaged as well as unpackaged
extensions sounds like the wrong path to go down.

cheers ./daniel

On Wed, Feb 26, 2020 at 03:35:46PM +0100, Daniel Gustafsson wrote:
> > On 26 Feb 2020, at 15:13, Sandro Santilli <strk@kbt.io> wrote:
> 
> > On pgsql-hackers we only want to find a future-proof way to "package
> > existing objects into an extension".
> 
> What is the longterm goal of PostGIS, to use this as a stepping stone to reach
> a point where no unpackaged extensions exist; or find a way to continue with
> the current setup except with syntax that isn't going away?

No unpackaged extension seems like a good goal in the long term.

> Overloading the same syntax for creating packaged as well as unpackaged
> extensions sounds like the wrong path to go down.

So what other options would we have to let people upgrade a running
postgis or postgis_raster outside of the EXTENSION mechanism ?

--strk;

Greetings,

* Sandro Santilli (strk@kbt.io) wrote:
> On pgsql-hackers we only want to find a future-proof way to "package
> existing objects into an extension".  If the syntax
> `CREATE EXTENSION <extname> FROM UNPACKAGED`
> has gone, would it be ok for just:
> `CREATE EXTENSION <extname>`
> to intercept unpackaged objects and package them ?

No.  The reason it was removed is because it's not going to be safe to
do when we have trusted extensions.  Perhaps it would be possible to
figure out a way to make it safe, but the reason FROM UNPACKAGED was
created and existed doesn't apply any more.  That PostGIS has been using
it for something else entirely is unfortunate, but the way to address
what PostGIS needs is to talk about that, not talk about how this ugly
hack used to work and doesn't any more.

Thanks,

Stephen

On Wed, Feb 26, 2020 at 10:37:41AM -0500, Stephen Frost wrote:
> Greetings,
> 
> * Sandro Santilli (strk@kbt.io) wrote:
> > On pgsql-hackers we only want to find a future-proof way to "package
> > existing objects into an extension".  If the syntax
> > `CREATE EXTENSION <extname> FROM UNPACKAGED` 
> > has gone, would it be ok for just:
> > `CREATE EXTENSION <extname>`
> > to intercept unpackaged objects and package them ?
> 
> No.  The reason it was removed is because it's not going to be safe to
> do when we have trusted extensions.

This part is not clear to me. You're _assuming_ that the unpackaged--xxx
will not make checks, so you _drop_ support for it ? Can't the normal
extension script also be unsafe for some reason ? Or can't the
unpackaged-xxx script be made safe by the publishers ? Or, as a last
resort.. can't you just mark postgis as UNSAFE and still require
superuser, which would give us the same experience as before ?


> Perhaps it would be possible to
> figure out a way to make it safe, but the reason FROM UNPACKAGED was
> created and existed doesn't apply any more.

Wasn't the reason of existance the ability for people to switch from
non-extension to extension based installs ?

>  That PostGIS has been using
> it for something else entirely is unfortunate, but the way to address
> what PostGIS needs is to talk about that, not talk about how this ugly
> hack used to work and doesn't any more.

Seriously, what was FROM UNPACKAGED meant to be used for ?

--strk;

OK, well, what PostGIS needs is the ability for 'ALTER EXTENSION …. UPDATE foo’ to end up with two extensions in the
end,‘foo’ and ‘foo_new’. That’s what’s happening in the 2.x -> 3 upgrade process, as ‘postgis’ becomes ‘postgis’ and
‘postgis_raster’. 

Presumably 15 years out from the 1.x -> 2.x we can stop worrying about bundling unpackaged postgis into an extension,
andjust recommend a hard upgrade dump/restore to the hardy souls still running 1.x. 

P.

> On Feb 26, 2020, at 7:37 AM, Stephen Frost <sfrost@snowman.net> wrote:
>
> Greetings,
>
> * Sandro Santilli (strk@kbt.io) wrote:
>> On pgsql-hackers we only want to find a future-proof way to "package
>> existing objects into an extension".  If the syntax
>> `CREATE EXTENSION <extname> FROM UNPACKAGED`
>> has gone, would it be ok for just:
>> `CREATE EXTENSION <extname>`
>> to intercept unpackaged objects and package them ?
>
> No.  The reason it was removed is because it's not going to be safe to
> do when we have trusted extensions.  Perhaps it would be possible to
> figure out a way to make it safe, but the reason FROM UNPACKAGED was
> created and existed doesn't apply any more.  That PostGIS has been using
> it for something else entirely is unfortunate, but the way to address
> what PostGIS needs is to talk about that, not talk about how this ugly
> hack used to work and doesn't any more.
>
> Thanks,
>
> Stephen
> _______________________________________________
> postgis-devel mailing list
> postgis-devel@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel

On 2/26/20 10:52 AM, Sandro Santilli wrote:

> This part is not clear to me. You're _assuming_ that the unpackaged--xxx
> will not make checks, so you _drop_ support for it ? Can't the normal
> extension script also be unsafe for some reason ? Or can't the
> unpackaged-xxx script be made safe by the publishers ? Or, as a last
> resort.. can't you just mark postgis as UNSAFE and still require
> superuser, which would give us the same experience as before ?

I am wondering: does anything in the PG 13 change preclude writing
a postgis_raster--unpackaged.sql script that could be applied with
CREATE EXTENSION postgis_raster VERSION unpackaged;
and would do perhaps nothing at all, or merely confirm that the
right unpackaged things are present and are the right things...

... from which an ALTER EXTENSION postgis_raster UPDATE TO 3.0;
would naturally run the existing postgis_raster--unpackaged--3.0.sql
and execute all of its existing ALTER EXTENSION ... ADD operations?

Has the disadvantage of being goofy, but possibly the advantage of
being implementable in the current state of affairs.

Regards,
-Chap

> Presumably 15 years out from the 1.x -> 2.x we can stop worrying about
> bundling unpackaged postgis into an extension, and just recommend a hard
> upgrade dump/restore to the hardy souls still running 1.x.
>
> P.
>

We don't need to worry about 1.x cause 1.x can only do a hard upgrade to 2 or 3.  We never supported soft upgrade from
1.x
Easy solution there is just to install postgis extension and do pg_restore/postgis_restore of your data.

So it's really just the 2.1 -> 3 that are of concern.
I think now is a fine time to encourage everyone to upgrade to 3 if they can so they don't need to suffer any crazy
solutionswe come up with  :) 

Turn this into a convenient emergency.

Thanks,
Regina

On Wed, Feb 26, 2020 at 11:18:43AM -0500, Chapman Flack wrote:
> On 2/26/20 10:52 AM, Sandro Santilli wrote:
> 
> > This part is not clear to me. You're _assuming_ that the unpackaged--xxx
> > will not make checks, so you _drop_ support for it ? Can't the normal
> > extension script also be unsafe for some reason ? Or can't the
> > unpackaged-xxx script be made safe by the publishers ? Or, as a last
> > resort.. can't you just mark postgis as UNSAFE and still require
> > superuser, which would give us the same experience as before ?
> 
> I am wondering: does anything in the PG 13 change preclude writing
> a postgis_raster--unpackaged.sql script that could be applied with
> CREATE EXTENSION postgis_raster VERSION unpackaged;
> and would do perhaps nothing at all, or merely confirm that the
> right unpackaged things are present and are the right things...
> 
> ... from which an ALTER EXTENSION postgis_raster UPDATE TO 3.0;
> would naturally run the existing postgis_raster--unpackaged--3.0.sql
> and execute all of its existing ALTER EXTENSION ... ADD operations?
> 
> Has the disadvantage of being goofy, but possibly the advantage of
> being implementable in the current state of affairs.

Thanks for this hint, yes, seems to be technically feasible, as well
as doing packaging in the extension creation scripts. Only... this
would basically work around the intentionally removed syntax, which
Steven Frost was against (still unclear to me why)...

--strk;

On Thu, Feb 27, 2020 at 09:32:24AM +0100, Sandro Santilli wrote:
> On Wed, Feb 26, 2020 at 11:18:43AM -0500, Chapman Flack wrote:
> > On 2/26/20 10:52 AM, Sandro Santilli wrote:
> > 
> > > This part is not clear to me. You're _assuming_ that the unpackaged--xxx
> > > will not make checks, so you _drop_ support for it ? Can't the normal
> > > extension script also be unsafe for some reason ? Or can't the
> > > unpackaged-xxx script be made safe by the publishers ? Or, as a last
> > > resort.. can't you just mark postgis as UNSAFE and still require
> > > superuser, which would give us the same experience as before ?
> > 
> > I am wondering: does anything in the PG 13 change preclude writing
> > a postgis_raster--unpackaged.sql script that could be applied with
> > CREATE EXTENSION postgis_raster VERSION unpackaged;
> > and would do perhaps nothing at all, or merely confirm that the
> > right unpackaged things are present and are the right things...
> > 
> > ... from which an ALTER EXTENSION postgis_raster UPDATE TO 3.0;
> > would naturally run the existing postgis_raster--unpackaged--3.0.sql
> > and execute all of its existing ALTER EXTENSION ... ADD operations?
> > 
> > Has the disadvantage of being goofy, but possibly the advantage of
> > being implementable in the current state of affairs.
> 
> Thanks for this hint, yes, seems to be technically feasible, as well
> as doing packaging in the extension creation scripts. Only... this
> would basically work around the intentionally removed syntax, which
> Steven Frost was against (still unclear to me why)...

NOTE: my suggestion was to directly have CREATE EXTENSION do the
packaging, which would give the same level of security as the
workaround suggested here, but with less hops.

--strk;

Hi,

On 2020-02-26 16:52:13 +0100, Sandro Santilli wrote:
> This part is not clear to me. You're _assuming_ that the unpackaged--xxx
> will not make checks, so you _drop_ support for it ? Can't the normal
> extension script also be unsafe for some reason ?

Yes. But it's at least plausible to make it safe. But in the case of an
indeterminate start state there's basically no way to make it safe. If
an attacker has entire control over the start state, you really can't
write a non-trivial upgrade script that safely manipulate that state.


> Or can't the unpackaged-xxx script be made safe by the publishers ?

Pretty much.


> Or, as a last resort.. can't you just mark postgis as UNSAFE and still
> require superuser, which would give us the same experience as before ?

Yes, we could potentially do that. But it's also a huge trap. And users
want to have the option of trusted extensions.


> > Perhaps it would be possible to
> > figure out a way to make it safe, but the reason FROM UNPACKAGED was
> > created and existed doesn't apply any more.
> 
> Wasn't the reason of existance the ability for people to switch from
> non-extension to extension based installs ?

Yea. But that was many years ago. It is/was a transition
functionality. And you're not using it as a way to transition, you're
using it to support a somewhat odd separate usecase that nobody ever
tried to make supported in postgres.


> >  That PostGIS has been using
> > it for something else entirely is unfortunate, but the way to address
> > what PostGIS needs is to talk about that, not talk about how this ugly
> > hack used to work and doesn't any more.
> 
> Seriously, what was FROM UNPACKAGED meant to be used for ?

?

Greetings,

Andres Freund