Horton
Menu

Thread

  1. Contrib Schemas

    David Fetter <david@fetter.org> — 2006-01-12T22:46:04Z

    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!

  2. Re: Contrib Schemas

    Mark Kirkwood <markir@paradise.net.nz> — 2006-01-12T23:16:21Z

    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

  3. Re: Contrib Schemas

    Andrew Dunstan <andrew@dunslane.net> — 2006-01-12T23:52:14Z

    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

  4. Re: Contrib Schemas

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-01-13T00:33:37Z

    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

  5. Re: Contrib Schemas

    Josh Berkus <josh@agliodbs.com> — 2006-01-13T01:11:39Z

    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

  6. Re: Contrib Schemas

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2006-01-13T02:03:43Z

    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

  7. Re: Contrib Schemas

    John DeSoi <desoi@pgedit.com> — 2006-01-13T04:34:49Z

    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

  8. Re: Contrib Schemas

    Simon Riggs <simon@2ndquadrant.com> — 2006-01-13T19:26:25Z

    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

  9. Re: Contrib Schemas

    Josh Berkus <josh@agliodbs.com> — 2006-01-13T19:34:20Z

    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

  10. Re: Contrib Schemas

    John DeSoi <desoi@pgedit.com> — 2006-01-13T21:17:29Z

    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