Thread: Re: Make contrib modules' installation scripts more secure.

I think this change neglected to add plpgsql to the extension
dependencies in the .control file:

12:53:51 #   Failed test 'psql -qc 'CREATE EXTENSION "cube"''
12:53:51 #   at t/TestLib.pm line 213.
12:53:51 not ok 68 - psql -qc 'CREATE EXTENSION "cube"'
12:53:51 #          got: '1'
12:53:51 #     expected: '0'
12:53:51 not ok 69 - extension cube installs without error
12:53:51 #   Failed test 'extension cube installs without error'
12:53:51 #   at t/TestLib.pm line 214.
12:53:51 #          got: 'ERROR:  language "plpgsql" does not exist
12:53:51 # HINT:  Use CREATE EXTENSION to load the language into the database.
12:53:51 # '

(The Debian regression tests remove plpgsql before testing all
extensions in turn.)

Christoph

Re: To PostgreSQL Hackers
> I think this change neglected to add plpgsql to the extension
> dependencies in the .control file:
> 
> 12:53:51 #   Failed test 'psql -qc 'CREATE EXTENSION "cube"''
> 12:53:51 #   at t/TestLib.pm line 213.
> 12:53:51 not ok 68 - psql -qc 'CREATE EXTENSION "cube"'
> 12:53:51 #          got: '1'
> 12:53:51 #     expected: '0'
> 12:53:51 not ok 69 - extension cube installs without error
> 12:53:51 #   Failed test 'extension cube installs without error'
> 12:53:51 #   at t/TestLib.pm line 214.
> 12:53:51 #          got: 'ERROR:  language "plpgsql" does not exist
> 12:53:51 # HINT:  Use CREATE EXTENSION to load the language into the database.
> 12:53:51 # '

Or maybe the argument is that the extension needs plpgsql only at
install time, and not to run, and you could really remove it after the
CREATE EXTENSION has been done. But that argument feels pretty icky.
And dump-restore would fail.

At least the error message is very explicit.

Christoph

Christoph Berg <myon@debian.org> writes:
> I think this change neglected to add plpgsql to the extension
> dependencies in the .control file:

Adding plpgsql to the extension's dependencies would be a cure worse
than the disease: it'd mean that you could not remove plpgsql from the
system after installing cube, either.  That is surely unhelpful from
the standpoint of someone who would like to have cube without plpgsql.

> (The Debian regression tests remove plpgsql before testing all
> extensions in turn.)

Meh.  I think that's testing a case that we don't guarantee to work.
There was already a plpgsql dependency in hstore--1.1--1.2.sql,
which I just cribbed from to make these fixes.

In the long term, perhaps it'd be worth inventing a concept of an
"install-time dependency", whereby an extension could name something
it needs to have to run its script, but not necessarily afterwards.
But if you're someone who's afraid to have plpgsql installed, the
idea that it can be sucked in on-demand, behind the scenes, might not
make you feel better either.

A band-aid sort of fix would be to roll up the base install scripts
for these modules to the latest version, so that a plain install from
scratch doesn't need to execute any of the catalog adjustments in
their update scripts.  That's not terribly attractive from a maintenance
or testing standpoint, though.

            regards, tom lane

Re: Tom Lane
> > (The Debian regression tests remove plpgsql before testing all
> > extensions in turn.)
> 
> Meh.  I think that's testing a case that we don't guarantee to work.
> There was already a plpgsql dependency in hstore--1.1--1.2.sql,
> which I just cribbed from to make these fixes.

The key difference is that hstore--1.1--1.2.sql was never required for
installing an extension from scratch, only for upgrades. The practical
relevance of this distinction is that the upgrade scripts are only run
once, while install-time scripts (including the upgrade scripts for
versions that do not have a direct creation script) are also required
for dump-restore cycles. As an admin, I'd very much hate databases
that couldn't be restored without extra fiddling.

The thing that maybe saves us here is that while hstore is trusted, so
any user can create it, plpgsql is trusted as well, but owned by
postgres, so even database owners can't drop it from beneath hstore.
Only superusers can "mess up" a database in that way. But still.

> A band-aid sort of fix would be to roll up the base install scripts
> for these modules to the latest version, so that a plain install from
> scratch doesn't need to execute any of the catalog adjustments in
> their update scripts.  That's not terribly attractive from a maintenance
> or testing standpoint, though.

That's a pretty small price compared to the dump-reload
inconsistencies.

I can see the extra maintenance effort, but how many extensions would
require rewriting as direct-install.sql scripts? I guess it's only a
few that need plpgsql for upgrades.

Christoph