Contrib Schemas

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:

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