Horton

Thread

Commits

  1. Remove support for upgrading extensions from "unpackaged" state.

  2. Mark some contrib modules as "trusted".

  3. Invent "trusted" extensions, and remove the pg_pltemplate catalog.

  1. Marking some contrib modules as trusted extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-29T19:41:16Z

    Now that we're just about there on the patch to invent trusted
extensions [1], I'd like to start a discussion about whether to mark
anything besides the trusted PLs as trusted.  I think generally
we ought to mark contrib modules as trusted if it's sane to do so;
there's not much point in handing people plperl (even sandboxed)
but not, say, hstore.  I trawled through what's in contrib today
and broke things down like this:

Certainly NO, as these allow external or low-level access:

adminpack
dblink
file_fdw
postgres_fdw
pageinspect
pg_buffercache
pg_freespacemap
pg_visibility
pgstattuple

Probably NO, if only because you'd need additional privileges
to use these anyway:

amcheck
dict_xsyn
hstore_plperlu
hstore_plpython2u
hstore_plpython3u
hstore_plpythonu
jsonb_plperlu
jsonb_plpython2u
jsonb_plpython3u
jsonb_plpythonu
ltree_plpython2u
ltree_plpython3u
ltree_plpythonu
pg_prewarm
pg_stat_statements

Definitely candidates to mark trusted:

citext
cube
dict_int		(unlike dict_xsyn, this needs no external file)
earthdistance		(marginal usefulness though)
fuzzystrmatch
hstore
hstore_plperl
intagg			(marginal usefulness though)
intarray
isn
jsonb_plperl
lo
ltree
pg_trgm
pgcrypto
seg
tablefunc
tcn
tsm_system_rows
tsm_system_time
unaccent		(needs external file, but the default one is useful)
uuid-ossp

Not sure what I think about these:

bloom			(are these useful in production?)
btree_gin
btree_gist
pgrowlocks		(seems safe, but are there security issues?)
spi/autoinc		(I doubt that these four are production grade)
spi/insert_username
spi/moddatetime
spi/refint
sslinfo			(seems safe, but are there security issues?)
xml2			(nominally safe, but deprecated, and libxml2
			 has been a fertile source of security issues)

Any opinions about these, particularly the on-the-edge cases?

Also, how should we document this, if we do it?  Add a boilerplate
sentence to each module's description about whether it is trusted
or not?  Put a table up at the front of Appendxix F?  Both?

I'm happy to go make this happen, once we have consensus on what
should happen.

			regards, tom lane

[1] https://www.postgresql.org/message-id/flat/5889.1566415762%40sss.pgh.pa.us

  2. Re: Marking some contrib modules as trusted extensions

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2020-01-29T20:29:19Z

    On 2020-Jan-29, Tom Lane wrote:

> Not sure what I think about these:
> 
> bloom			(are these useful in production?)
> btree_gin
> btree_gist
> pgrowlocks		(seems safe, but are there security issues?)
> spi/autoinc		(I doubt that these four are production grade)
> spi/insert_username
> spi/moddatetime
> spi/refint
> sslinfo			(seems safe, but are there security issues?)
> xml2			(nominally safe, but deprecated, and libxml2
> 			 has been a fertile source of security issues)

Of these, btree_gist is definitely useful from a user perspective,
because it enables creation of certain exclusion constraints.

I've never heard of anyone using bloom indexes in production.  I'd
argue that if the feature is useful, then we should turn it into a
core-included index AM with regular WAL logging for improved
performance, and add a stripped-down version to src/test/modules to
cover the WAL-log testing needs.  Maybe exposing it more, as promoting
it as a trusted extension would do, would help find more use cases for
it.

> Also, how should we document this, if we do it?  Add a boilerplate
> sentence to each module's description about whether it is trusted
> or not?  Put a table up at the front of Appendxix F?  Both?

If it were possible to do both from a single source of truth, that would
be great.  Failing that, I'd just list it in each module's section.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  3. Re: Marking some contrib modules as trusted extensions

    Darafei Komяpa Praliaskouski <me@komzpa.net> — 2020-01-29T20:45:37Z

    Hello,


> btree_gin
> btree_gist


I would even ask btree_gin and btree_gist to be moved to core.

btree_gist is shipping opclasses for built in types to be used in gist
indexes. btree_* is confusing part in the name pretending there's some
magic happening linking btree and gist.

gist is the most popular way to get geometric indexes, and these often need
to be combined with some class identifier that's used in lookups together.
CREATE INDEX on geom_table using gist (zooom_level, geom); fails for no
reason without btree_gist - types are shipped in core,
gist itself is not an extension, but letting to use one core mechanism with
another in an obvious way is for some reason split out.


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

  4. Re: Marking some contrib modules as trusted extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-29T21:27:08Z

    =?UTF-8?Q?Darafei_=22Kom=D1=8Fpa=22_Praliaskouski?= <me@komzpa.net> writes:
>> btree_gin
>> btree_gist

> I would even ask btree_gin and btree_gist to be moved to core.

That's not in scope here.  Our past experience with trying to move
extensions into core is that it creates a pretty painful upgrade
experience for users, so that's not something I'm interested in doing
... especially for relatively marginal cases like these.

There's also a more generic question of why we should want to move
anything to core anymore.  The trusted-extension mechanism removes
one of the biggest remaining gripes about extensions, namely the
pain level for installing them.  (But please, let's not have that
debate on this thread.)

			regards, tom lane

  5. Re: Marking some contrib modules as trusted extensions

    Julien Rouhaud <rjuju123@gmail.com> — 2020-01-29T21:28:22Z

    On Wed, Jan 29, 2020 at 9:46 PM Darafei "Komяpa" Praliaskouski
<me@komzpa.net> wrote:
>
> Hello,
>
>>
>> btree_gin
>> btree_gist
>
>
> I would even ask btree_gin and btree_gist to be moved to core.

Without going that far, I also agree that I relied on those extension
quite often, so +1 for marking them as trusted.

>> Probably NO, if only because you'd need additional privileges
>> to use these anyway:
>> pg_stat_statements

But the additional privileges are global, so assuming the extension
has been properly setup, wouldn't it be sensible to ease the
per-database installation?  If not properly setup, there's no harm in
creating the extension anyway.

  6. Re: Marking some contrib modules as trusted extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-29T21:38:53Z

    Julien Rouhaud <rjuju123@gmail.com> writes:
>>> Probably NO, if only because you'd need additional privileges
>>> to use these anyway:
>>> pg_stat_statements

> But the additional privileges are global, so assuming the extension
> has been properly setup, wouldn't it be sensible to ease the
> per-database installation?  If not properly setup, there's no harm in
> creating the extension anyway.

Mmm, I'm not convinced --- the ability to see what statements are being
executed in other sessions (even other databases) is something that
paranoid installations might not be so happy about.  Our previous
discussions about what privilege level is needed to look at
pg_stat_statements info were all made against a background assumption
that you needed some extra privilege to set up the view in the first
place.  I think that would need another look or two before being
comfortable that we're not shifting the goal posts too far.

The bigger picture here is that I don't want to get push-back that
we've broken somebody's security posture by marking too many extensions
trusted.  So for anything where there's any question about security
implications, we should err in the conservative direction of leaving
it untrusted.

			regards, tom lane

  7. Re: Marking some contrib modules as trusted extensions

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2020-01-31T09:40:32Z

    On Wed, 29 Jan 2020 at 21:39, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> >>> pg_stat_statements
>
> Mmm, I'm not convinced --- the ability to see what statements are being
> executed in other sessions (even other databases) is something that
> paranoid installations might not be so happy about.  Our previous
> discussions about what privilege level is needed to look at
> pg_stat_statements info were all made against a background assumption
> that you needed some extra privilege to set up the view in the first
> place.  I think that would need another look or two before being
> comfortable that we're not shifting the goal posts too far.
>
> The bigger picture here is that I don't want to get push-back that
> we've broken somebody's security posture by marking too many extensions
> trusted.  So for anything where there's any question about security
> implications, we should err in the conservative direction of leaving
> it untrusted.
>

+1

I wonder if the same could be said about pgrowlocks.

Regards,
Dean

  8. Re: Marking some contrib modules as trusted extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-01-31T15:13:19Z

    Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> On Wed, 29 Jan 2020 at 21:39, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The bigger picture here is that I don't want to get push-back that
>> we've broken somebody's security posture by marking too many extensions
>> trusted.  So for anything where there's any question about security
>> implications, we should err in the conservative direction of leaving
>> it untrusted.

> I wonder if the same could be said about pgrowlocks.

Good point.  I had figured it was probably OK given that it's
analogous to the pg_locks view (which is unrestricted AFAIR),
and that it already has some restrictions on what you can see.
I'd have no hesitation about dropping it off this list though,
since it's probably not used that much and it could also be seen
as exposing internals.

			regards, tom lane

  9. Re: Marking some contrib modules as trusted extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-02-08T01:40:21Z

    After looking more closely at these modules, I'm kind of inclined
*not* to put the trusted marker on intagg.  That module is just
a backwards-compatibility wrapper around functionality that
exists in the core code nowadays.  So I think what we ought to be
doing with it is deprecating and eventually removing it, not
encouraging people to keep using it.

Given that and the other discussion in this thread, I think the
initial list of modules to trust is:

btree_gin
btree_gist
citext
cube
dict_int
earthdistance
fuzzystrmatch
hstore
hstore_plperl
intarray
isn
jsonb_plperl
lo
ltree
pg_trgm
pgcrypto
seg
tablefunc
tcn
tsm_system_rows
tsm_system_time
unaccent
uuid-ossp

So attached is a patch to do that.  The code changes are trivial; just
add "trusted = true" to each control file.  We don't need to bump the
module version numbers, since this doesn't change the contents of any
extension, just who can install it.  I do not think any regression
test changes are needed either.  (Note that commit 50fc694e4 already
added a test that trusted extensions behave as expected, see
src/pl/plperl/sql/plperl_setup.sql.)  So it seems like the only thing
that needs much discussion is the documentation changes.  I adjusted
contrib.sgml's discussion of how to install these modules in general,
and then labeled the individual modules if they are trusted.

			regards, tom lane

  10. Re: Marking some contrib modules as trusted extensions

    Stephen Frost <sfrost@snowman.net> — 2020-02-08T13:54:30Z

    Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Julien Rouhaud <rjuju123@gmail.com> writes:
> >>> Probably NO, if only because you'd need additional privileges
> >>> to use these anyway:
> >>> pg_stat_statements
> 
> > But the additional privileges are global, so assuming the extension
> > has been properly setup, wouldn't it be sensible to ease the
> > per-database installation?  If not properly setup, there's no harm in
> > creating the extension anyway.
> 
> Mmm, I'm not convinced --- the ability to see what statements are being
> executed in other sessions (even other databases) is something that
> paranoid installations might not be so happy about.

Of course, but that's why we have a default role which allows
installations to control access to that kind of information- and it's
already being checked in the pg_stat_statements case and in the
pg_stat_activity case:

/* Superusers or members of pg_read_all_stats members are allowed */
is_allowed_role = is_member_of_role(GetUserId(), DEFAULT_ROLE_READ_ALL_STATS);

> Our previous
> discussions about what privilege level is needed to look at
> pg_stat_statements info were all made against a background assumption
> that you needed some extra privilege to set up the view in the first
> place.  I think that would need another look or two before being
> comfortable that we're not shifting the goal posts too far.

While you could maybe argue that's true for pg_stat_statements, it's
certainly not true for pg_stat_activity, so I don't buy it for either.
This looks like revisionist history to justify paranoia.  I understand
the general concern, but if we were really depending on the mere
installation of the extension to provide security then we wouldn't have
bothered putting in checks like the one above, and, worse, I think our
users would be routinely complaining that our extensions don't follow
our security model and how they can't install them.

Lots of people want to use pg_stat_statements, even in environments
where not everyone on the database server, or even in the database you
want pg_stat_statements in, is trusted, and therefore we have to have
these additional checks inside the extension itself.

The same goes for just about everything else (I sure hope, at least) in
our extensions set- none of the core extensions should be allowing
access to things which break our security model, even if they're
installed, unless some additional privileges are granted out.  The act
of installing a core extension should not create a security risk for our
users- if it did, it'd be a security issue and CVE-worthy.

As such, I really don't agree with this entire line of thinking when it
comes to our core extensions.  I view the 'trusted extension' model as
really for things where the extension author doesn't care about, and
doesn't wish to care about, dealing with our security model and making
sure that it's followed.  We do care, and we do maintain, the security
model that we have throughout the core extensions.  

What I expect and hope will happen is that people will realize that, now
that they can have non-superusers installing these extensions and
therefore they don't have to give out superuser-level rights as much,
there will be asks for more default roles to allow granting out of
access to formerly superuser-only capabilities.  There's a bit of a
complication there since there might be privileges that only make sense
for a specific extension, but an extension can't really install a new
default role (and, even if it did, the role would have to be only
available to the superuser initially anyway), so we might have to try
and come up with some more generic and reusable default role for that
case.  Still, we can try to deal with that when it happens.

Consider that you may wish to have a system that, once installed, a
superuser will virtually never access again, but one where you want
users to be able to install and use extensions like postgis and
pg_stat_statements.  That can be done with these changes, and that's
fantastic progress- you just install PG, create a non-superuser role,
make them the DB owner, and then GRANT things like pg_read_all_stats to
their role with admin rights, and boom, they're good to go and you
didn't have to hack up the PG source code at all.

> The bigger picture here is that I don't want to get push-back that
> we've broken somebody's security posture by marking too many extensions
> trusted.  So for anything where there's any question about security
> implications, we should err in the conservative direction of leaving
> it untrusted.

This is just going to a) cause our users to complain about not being
able to install extensions that they've routinely installed in the past,
and b) make our users wonder what it is about these extensions that
we've decided can't be trusted to even just be installed and if they're
at risk today because they've installed them.

While it might not seem obvious, the discussion over on the thread about
DEFAULT PRIVILEGES and pg_init_privs is actually a lot more relevant
here- there's extensions we have that expect certain functions, once
installed, to be owned by a superuser (which will still be the case
here, thanks to how you've addressed that), but then to not have EXECUTE
rights GRANT'd to anyone (thanks to the REVERT FROM PUBLIC in the
installation), but that falls apart when someone's decided to set
up DEFAULT PRIVILEGES for the superuser.  While no one seems to want to
discuss that with me, unfortunately, it's becoming more and more clear
that we need to skip DEFAULT PRIVILEGES from being applied during
extension creation.

Thanks,

Stephen

  11. Re: Marking some contrib modules as trusted extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-02-08T17:34:53Z

    Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> Our previous
>> discussions about what privilege level is needed to look at
>> pg_stat_statements info were all made against a background assumption
>> that you needed some extra privilege to set up the view in the first
>> place.  I think that would need another look or two before being
>> comfortable that we're not shifting the goal posts too far.

> While you could maybe argue that's true for pg_stat_statements, it's
> certainly not true for pg_stat_activity, so I don't buy it for either.

The analogy of pg_stat_activity certainly suggests that there shouldn't be
a reason *in principle* why pg_stat_statements couldn't be made trusted.
There's a difference between that statement and saying that *in practice*
pg_stat_statements is ready to be trusted right now with no further
changes.  I haven't done the analysis needed to conclude that, and don't
care to do so as part of this patch proposal.

> The same goes for just about everything else (I sure hope, at least) in
> our extensions set- none of the core extensions should be allowing
> access to things which break our security model, even if they're
> installed, unless some additional privileges are granted out.

Maybe not, but the principle of defense-in-depth still says that admins
could reasonably want to not let dangerous tools get installed in the
first place.

> As such, I really don't agree with this entire line of thinking when it
> comes to our core extensions.  I view the 'trusted extension' model as
> really for things where the extension author doesn't care about, and
> doesn't wish to care about, dealing with our security model and making
> sure that it's followed.  We do care, and we do maintain, the security
> model that we have throughout the core extensions.  

I am confused as to what "entire line of thinking" you are objecting
to.  Are you now saying that we should forget the trusted-extension
model?  Or maybe that we can just mark *everything* we ship as trusted?
I'm not going to agree with either.

>> The bigger picture here is that I don't want to get push-back that
>> we've broken somebody's security posture by marking too many extensions
>> trusted.  So for anything where there's any question about security
>> implications, we should err in the conservative direction of leaving
>> it untrusted.

> This is just going to a) cause our users to complain about not being
> able to install extensions that they've routinely installed in the past,

That's utter nonsense.  Nothing here is taking away privileges that
existed before; if you could install $whatever as superuser before,
you still can.  OTOH, we *would* have a problem of that sort if we
marked $whatever as trusted and then later had to undo it.  So I
think there's plenty of reason to be conservative about the first
wave of what-to-mark-as-trusted.  Once we've got more experience
with this mechanism under our belts, we might decide we can be more
liberal about it.

> and b) make our users wonder what it is about these extensions that
> we've decided can't be trusted to even just be installed and if they're
> at risk today because they've installed them.

Yep, you're right, this patch does make value judgements of that
sort, and I'll stand behind them.  Giving people the impression that,
say, postgres_fdw isn't any more dangerous than cube isn't helpful.

> While it might not seem obvious, the discussion over on the thread about
> DEFAULT PRIVILEGES and pg_init_privs is actually a lot more relevant
> here- there's extensions we have that expect certain functions, once
> installed, to be owned by a superuser (which will still be the case
> here, thanks to how you've addressed that), but then to not have EXECUTE
> rights GRANT'd to anyone (thanks to the REVERT FROM PUBLIC in the
> installation), but that falls apart when someone's decided to set
> up DEFAULT PRIVILEGES for the superuser.  While no one seems to want to
> discuss that with me, unfortunately, it's becoming more and more clear
> that we need to skip DEFAULT PRIVILEGES from being applied during
> extension creation.

Or that we can't let people apply default privileges to superuser-created
objects at all.  But I agree that that's a different discussion.

			regards, tom lane

  12. Re: Marking some contrib modules as trusted extensions

    Andres Freund <andres@anarazel.de> — 2020-02-13T23:30:15Z

    Hi,

On 2020-01-29 14:41:16 -0500, Tom Lane wrote:
> pgcrypto

FWIW, given the code quality, I'm doubtful about putting itq into the trusted
section.


Have you audited how safe the create/upgrade scripts are against being
used to elevate privileges?

Especially with FROM UNPACKAGED it seems like it'd be fairly easy to get
an extension script to do dangerous things (as superuser). One could
just create pre-existing objects that have *not* been created by a
previous version, and some upgrade scripts would do pretty weird
stuff. There's several that do things like updating catalogs directly
etc.  It seems to me that FROM UNPACKAGED shouldn't support trusted.

Regards,

Andres Freund

  13. Re: Marking some contrib modules as trusted extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-02-13T23:57:10Z

    Andres Freund <andres@anarazel.de> writes:
> On 2020-01-29 14:41:16 -0500, Tom Lane wrote:
>> pgcrypto

> FWIW, given the code quality, I'm doubtful about putting itq into the trusted
> section.

I don't particularly have an opinion about that --- is it really that
awful?  If there is anything broken in it, wouldn't we consider that
a security problem anyhow?

> Especially with FROM UNPACKAGED it seems like it'd be fairly easy to get
> an extension script to do dangerous things (as superuser). One could
> just create pre-existing objects that have *not* been created by a
> previous version, and some upgrade scripts would do pretty weird
> stuff. There's several that do things like updating catalogs directly
> etc.  It seems to me that FROM UNPACKAGED shouldn't support trusted.

Hmm, seems like a reasonable idea, but I'm not quite sure how to mechanize
it given that "unpackaged" isn't magic in any way so far as extension.c
is concerned.  Maybe we could decide that the time for supporting easy
updates from pre-9.1 is past, and just remove all the unpackaged-to-XXX
scripts?  Maybe even remove the "FROM version" option altogether.

			regards, tom lane

  14. Re: Marking some contrib modules as trusted extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-02-14T00:09:18Z

    I wrote:
> Andres Freund <andres@anarazel.de> writes:
>> It seems to me that FROM UNPACKAGED shouldn't support trusted.

> Hmm, seems like a reasonable idea, but I'm not quite sure how to mechanize
> it given that "unpackaged" isn't magic in any way so far as extension.c
> is concerned.  Maybe we could decide that the time for supporting easy
> updates from pre-9.1 is past, and just remove all the unpackaged-to-XXX
> scripts?  Maybe even remove the "FROM version" option altogether.

[ thinks some more... ]  A less invasive idea would be to insist that
you be superuser to use the FROM option.  But I'm thinking that the
unpackaged-to-XXX scripts are pretty much dead letters anymore.  Has
anyone even tested them in years?  How much longer do we want to be
on the hook to fix them?

			regards, tom lane

  15. Re: Marking some contrib modules as trusted extensions

    Andres Freund <andres@anarazel.de> — 2020-02-14T01:26:38Z

    Hi,

On 2020-02-13 18:57:10 -0500, Tom Lane wrote:
> Maybe we could decide that the time for supporting easy updates from
> pre-9.1 is past, and just remove all the unpackaged-to-XXX scripts?
> Maybe even remove the "FROM version" option altogether.

Yea, that strikes me as a reasonable thing to do. These days that just
seems to be dangerous, without much advantage.

Greetings,

Andres Freund

  16. Re: Marking some contrib modules as trusted extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-02-14T17:39:19Z

    Andres Freund <andres@anarazel.de> writes:
> On 2020-02-13 18:57:10 -0500, Tom Lane wrote:
>> Maybe we could decide that the time for supporting easy updates from
>> pre-9.1 is past, and just remove all the unpackaged-to-XXX scripts?
>> Maybe even remove the "FROM version" option altogether.

> Yea, that strikes me as a reasonable thing to do. These days that just
> seems to be dangerous, without much advantage.

Here's a patch to remove the core-code support and documentation for
that.  I have not included the actual deletion of the contrib modules'
'unpackaged' scripts, as that seems both long and boring.

			regards, tom lane

  17. Re: Marking some contrib modules as trusted extensions

    Stephen Frost <sfrost@snowman.net> — 2020-02-18T15:13:50Z

    Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Andres Freund <andres@anarazel.de> writes:
> > On 2020-01-29 14:41:16 -0500, Tom Lane wrote:
> >> pgcrypto
> 
> > FWIW, given the code quality, I'm doubtful about putting itq into the trusted
> > section.
> 
> I don't particularly have an opinion about that --- is it really that
> awful?  If there is anything broken in it, wouldn't we consider that
> a security problem anyhow?

I would certainly hope so- and I would expect that to go for any of the
other extensions which are included in core.  If we aren't going to
maintain them and deal with security issues in them, then we should drop
them.

Which goes back to my earlier complaint that having extensions in core
which aren't or can't be marked as trusted is not a position we should
put our users in.  Either they're maintained and have been vetted
through our commit process, or they aren't and should be removed.

> > Especially with FROM UNPACKAGED it seems like it'd be fairly easy to get
> > an extension script to do dangerous things (as superuser). One could
> > just create pre-existing objects that have *not* been created by a
> > previous version, and some upgrade scripts would do pretty weird
> > stuff. There's several that do things like updating catalogs directly
> > etc.  It seems to me that FROM UNPACKAGED shouldn't support trusted.
> 
> Hmm, seems like a reasonable idea, but I'm not quite sure how to mechanize
> it given that "unpackaged" isn't magic in any way so far as extension.c
> is concerned.  Maybe we could decide that the time for supporting easy
> updates from pre-9.1 is past, and just remove all the unpackaged-to-XXX
> scripts?  Maybe even remove the "FROM version" option altogether.

I agree in general with dropping the unpackaged-to-XXX bits.

Thanks,

Stephen

  18. Re: Marking some contrib modules as trusted extensions

    Sandro Santilli <strk@kbt.io> — 2020-02-26T08:11:21Z

    On Thu, Feb 13, 2020 at 07:09:18PM -0500, Tom Lane wrote:
> I wrote:
> > Andres Freund <andres@anarazel.de> writes:
> >> It seems to me that FROM UNPACKAGED shouldn't support trusted.
> 
> > Hmm, seems like a reasonable idea, but I'm not quite sure how to mechanize
> > it given that "unpackaged" isn't magic in any way so far as extension.c
> > is concerned.  Maybe we could decide that the time for supporting easy
> > updates from pre-9.1 is past, and just remove all the unpackaged-to-XXX
> > scripts?  Maybe even remove the "FROM version" option altogether.
> 
> [ thinks some more... ]  A less invasive idea would be to insist that
> you be superuser to use the FROM option.  But I'm thinking that the
> unpackaged-to-XXX scripts are pretty much dead letters anymore.  Has
> anyone even tested them in years?  How much longer do we want to be
> on the hook to fix them?

PostGIS uses unpackaged-to-XXX pretty heavily, and has it under
automated testing (which broke since "FROM unpackaged" support was
removed, see 14514.1581638958@sss.pgh.pa.us)

We'd be ok with requiring SUPERUSER for doing that, since that's
what is currently required so nothing would change for us.

Instead, dropping UPGRADE..FROM completely puts us in trouble of
having to find another way to "package" postgis objects.

--strk;

  19. Re: Marking some contrib modules as trusted extensions

    Andres Freund <andres@anarazel.de> — 2020-02-26T08:17:37Z

    Hi,

On 2020-02-26 09:11:21 +0100, Sandro Santilli wrote:
> PostGIS uses unpackaged-to-XXX pretty heavily, and has it under
> automated testing (which broke since "FROM unpackaged" support was
> removed, see 14514.1581638958@sss.pgh.pa.us)
> 
> We'd be ok with requiring SUPERUSER for doing that, since that's
> what is currently required so nothing would change for us.
> 
> Instead, dropping UPGRADE..FROM completely puts us in trouble of
> having to find another way to "package" postgis objects.

Coul you explain what postgis is trying to achieve with FROM unpackaged?

Greetings,

Andres Freund

  20. Re: Marking some contrib modules as trusted extensions

    Sandro Santilli <strk@kbt.io> — 2020-02-26T08:46:32Z

    On Wed, Feb 26, 2020 at 12:17:37AM -0800, Andres Freund wrote:
> Hi,
> 
> On 2020-02-26 09:11:21 +0100, Sandro Santilli wrote:
> > PostGIS uses unpackaged-to-XXX pretty heavily, and has it under
> > automated testing (which broke since "FROM unpackaged" support was
> > removed, see 14514.1581638958@sss.pgh.pa.us)
> > 
> > We'd be ok with requiring SUPERUSER for doing that, since that's
> > what is currently required so nothing would change for us.
> > 
> > Instead, dropping UPGRADE..FROM completely puts us in trouble of
> > having to find another way to "package" postgis objects.
> 
> Coul you explain what postgis is trying to achieve with FROM unpackaged?

We're turning a non-extension based install into an extension-based
one. Common need for those who came to PostGIS way before EXTENSION
was even invented and for those who remained there for the bigger
flexibility (for example to avoid the raster component, which was
unavoidable with EXTENSION mechanism until PostGIS 3.0).

For the upgrades to 3.0.0 when coming from a previous version we're
using that `FROM unpackaged` SYNTAX for re-packaging the raster
component for those who still want it (raster objects are unpackaged
from 'postgis' extension on EXTENSION UPDATE because there was no other
way to move them from an extension to another).

I guess it would be ok for us to do the packaging directly from the
scripts that would run on `CREATE EXTENSION postgis`, but would that
mean we'd take the security risk you're trying to avoid by dropping
the `FROM unpackaged` syntax ?

--strk;