Peter Eisentraut <pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers