Thread: Contrib Schemas
Folks, For the same reason they feel that *n*x installs shouldn't be messing around in /usr/local, some people also feel that contrib modules shouldn't install in the public schema by default. Before I go a-patching, I'd like to see what people think about how to handle this. A few of possibilities come to mind: 1. One schema per contrib module. In some sense, this is a clean way to do things because the modules can't step on each other's namespaces, and it makes it easy to remove a module cleanly. A downside of this is the increased maintenance overhead of adding those schemas to search_paths as appropriate, although this might be worked around by having the install script generate some search_path modifications that the person installing could choose to run. 2. One big contrib schema. This avoids the above search_path problem, but leaves the namespace collision of putting things in public. 3. Name a schema on installation. This could help people decide exactly where they want things to go. I'm picturing something like this: make install # Option 0 Leave as-is make install --auto_schema [--generate_path_mod] # Option 1 make install --schema=contrib # Option 2 make install --schema=foo # Option 3 What do you think? Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 415 235 3778 Remember to vote!
David Fetter wrote: > Folks, > > I'm picturing something like this: > > make install # Option 0 Leave as-is > make install --auto_schema [--generate_path_mod] # Option 1 > make install --schema=contrib # Option 2 > make install --schema=foo # Option 3 > > What do you think? > I like the idea of being able to specify a schema as an option, but would prefer the default schema to be 'public'. i.e. make install [--schema=foo] # Option 4 Cheers Mark
David Fetter said: > I'm picturing something like this: > > make install # Option 0 Leave > as-is make install --auto_schema [--generate_path_mod] # Option 1 > make install --schema=contrib # Option 2 > make install --schema=foo # Option 3 > Or maybe set it up at configure time, c.f. installation directories. cheers andrew
David Fetter <david@fetter.org> writes:
> What do you think?
I think it's not broken and doesn't need fixing. I have not seen any
groundswell of demand for moving the contrib stuff out of the public
schema. On the other hand, doing so *will* break many if not all
existing applications that use contrib modules.
People who want the contrib stuff segregated can easily make it
happen by modifying the contrib .sql files before they run them.
The .sql files already have a skeleton for this, eg-- Adjust this setting to control where the objects get created.SET
search_path= public;
I don't really see a need to go further than that.
regards, tom lane
Tom, > People who want the contrib stuff segregated can easily make it > happen by modifying the contrib .sql files before they run them. > The .sql files already have a skeleton for this, eg > -- Adjust this setting to control where the objects get created. > SET search_path = public; > I don't really see a need to go further than that. Tangentally, I filed a but with Tsearch2 because that SET statement is outside the transaction in the .sql file, which means that stuff will end up in the public schema if the admin typos the schema name. Not sure if other contrib modules have the same issue. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Default schema really has to be public to help the newbies out there. All contribs should come with some sort of standard uninstall.sql script though. Chris Mark Kirkwood wrote: > David Fetter wrote: > >> Folks, >> >> I'm picturing something like this: >> >> make install # Option 0 Leave as-is >> make install --auto_schema [--generate_path_mod] # Option 1 >> make install --schema=contrib # Option 2 >> make install --schema=foo # Option 3 >> >> What do you think? >> > > I like the idea of being able to specify a schema as an option, but > would prefer the default schema to be 'public'. > > i.e. > > > make install [--schema=foo] # Option 4 > > Cheers > > Mark > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
On Jan 12, 2006, at 9:03 PM, Christopher Kings-Lynne wrote: > Default schema really has to be public to help the newbies out > there. All contribs should come with some sort of standard > uninstall.sql script though. Speaking of contrib newbies and install/uninstall, I recently had the need to install tsearch2 in a shared hosting environment (where, of course, I don't have superuser access). Since there are C language functions, I could not install it. The hoster's solution was to install it in template1 which seems bad because (1) now everyone gets it whether they want it or not, (2) restoring the database is going to have permission problems. Would it be reasonable for there to be a way for the super user to grant access to load "approved" modules and/or C language functions? John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
On Thu, 2006-01-12 at 19:33 -0500, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > What do you think? > > I think it's not broken and doesn't need fixing. I have not seen any > groundswell of demand for moving the contrib stuff out of the public > schema. On the other hand, doing so *will* break many if not all > existing applications that use contrib modules. This is the kind of thing that makes *me* break between releases. :-) My memory is bad enough already... do we need this? Best Regards, Simon Riggs
John, > Would it be reasonable for there to be a way for the super user to > grant access to load "approved" modules and/or C language functions? I can't see a way to do this except individually, in which case the superuser might as well load the functions. We *have* to be restrictive about this because a C function can do anything, including overwriting whatever parts of the filesystem "postgres" has access to. Look over our patch releases for the last 2 years and you'll see a host of patches designed specifically to prevent regular users from gaining access to superuser priveleges. What you want isn't impossible, but it would be a lot of work and testing to engineer such a mechanism and keep PostgreSQL's "most secure" status. So far, everyone has found it easier to work around the issue, especially since for most sites backup/restore is done by the superuser anyway. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Hi Josh, On Jan 13, 2006, at 2:34 PM, Josh Berkus wrote: > I can't see a way to do this except individually, in which case the > superuser might as well load the functions. We *have* to be > restrictive > about this because a C function can do anything, including overwriting > whatever parts of the filesystem "postgres" has access to. Look > over our > patch releases for the last 2 years and you'll see a host of patches > designed specifically to prevent regular users from gaining access to > superuser priveleges. > > What you want isn't impossible, but it would be a lot of work and > testing > to engineer such a mechanism and keep PostgreSQL's "most secure" > status. > So far, everyone has found it easier to work around the issue, > especially > since for most sites backup/restore is done by the superuser anyway. I suspected it was out of the question for security reasons, but I wanted to bring it up to make sure I was not missing some alternative solution. I backup and restore all the time for hosted web sites running with PostgreSQL as a content management system. This is critical for doing site upgrades and you certainly can't depend on the super user in a hosted environment. Maybe the best solution here would be some web interface setup by the hoster to perform specific approved tasks like tsearch install. This is already the mechanism used to allow users to create their own databases. Thanks for taking the time to respond. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL