Re: Extensions User Design

Peter Eisentraut <peter_e@gmx.net> writes:
> On Tuesday 23 June 2009 20:44:39 Dimitri Fontaine wrote:
> Instead of installing an "extension", that is, say, a collection of types and 
> functions provided by a third-party source, I would like to have a mechanism 
> to deploy my own actual database application code.

I'd like for the extension facility to cover application code in the
database too, yes. Short of install time choice of schema I think we're
there, but please refer to the infamous "search_path vs extensions"
debate we had, that I wanted to consider as a pre-requisite for User
Extension Design:
 http://archives.postgresql.org/pgsql-hackers/2009-05/msg00912.php

After this, I'm considering that if we want to have anything, we'll have
to begin implementing extensions and find a schema relocation facility
later on. Unless you have one now? :)

> That is, after all, how I work with non-database deployments: I build a 
> package (deb, rpm) from the code, and install it on the target machine.  The 
> package system here functions as a deployment aid both for "extensions" of the 
> operating system and for local custom code.
>
> Applying this method to database code, with regard to your proposal, means 
> first of all that naming this thing "extension" is questionable, and that 
> installing everything by default into some schema like pg_extensions is 
> inappropriate.

I'll be happy to be provided a better name if we manage to implement
both ideas into the same facility, or see a way to get there in a near
future :)

>  And then, if 
> pg_restore could be taught to do upgrades instead of just overwriting (e.g., 
> ALTER TABLE ADD COLUMN instead of DROP TABLE + CREATE TABLE), this would all 
> fall into place nicely.

I'm not sure about this. What we want when using pg_restore is typically
an upgrade, of PostgreSQL itself but of the extensions too... and I
don't think we can manage from the metadata what the extension upgrading
needs are.

> What this needs below the surface is basically librpm: an interface to 
> describe and query which objects belong to which "package" and to associate 
> pre/post scripts with packages.  And I think that that interface is quite like 
> the CREATE/DROP EXTENSION stuff that you are describing.  (Pre/post scripts 
> could be functions, actually, instead of scripts.)

Yes, and we're having both an entry into pg_catalog.pg_extension
containing the metadata and pg_catalog.pg_depend entries to cook up a
query acting as either `dpkg -L` or `rpm -ql`.

Now, pre and post script if needed could also be pre_install.sql and
post_install.sql with some support at the CREATE EXTENSION level. 

I didn't want to add them on the first round to avoid being pointed at
doing over engineering, but now that it is you asking for it, let's do
that :)

> On the matter of schemas, I suggest that we consider two ideas that have 
> helped RPM in its early days, when everyone had their own very specific ideas 
> about what should be installed where:
>
> - file system hierarchy standard
> - relocations
>
> This means, we'd write up standard of where we think you *should* install 
> things.  And we expect that quality packages/bundles/extensions created for 
> wider distribution install themselves in the right place without additional 
> user intervention.  

The aim is for users to \i extension.sql which only contains the CREATE
EXTENSION command, then INSTALL EXTENSION extension, and be done with it.

> But the packaging tool would provide a way to override 
> this.  Then, something that is a true extension could in fact be set up to 
> install itself by default into pg_extensions, but a bundle containing local 
> custom code would be set up so that it installs into a different schema or 
> schemas by default.
>
> What do you think?

How do you implement relocate in a way to guarantee there's no security
disaster waiting to happen? Namely that a function foo() calling another
function foo_support_fn() from within the extension won't be calling a
(malicious?) user defined foo_support_fn() from another schema,
depending on run time search_path?

Having both extension function calls schema qualified and relocations is
the biggest problem we're facing, and it seems we're still short of a
solution for it... or did I just miss it?
-- 
dim