[postgis-users] Install postgis on a different schema

[Stephen Woodbridge]

As an add on to this thread and you questions about projects, sharde and 
public schemas, You could also set up each project with a separate 
username, and you can set the search_path different for each username

so user1 might have:

search_path=project1, shared, public;

and user2 might have:

search_path=project2, shared, public;

etc.

If you want to secure you data you can add grants so all users can 
access shared and public and their projectN schema, but not have access 
to other schemas.

I {heart} postgresql!

-Steve

P Kishor wrote:
> Following up on an older thread...
> 
> On Sat, Oct 3, 2009 at 11:06 AM, Stephen Woodbridge
> <woodbri at swoodbridge.com> wrote:
> ..
>> I take a slightly different approach in my databases, which is to allow
>> postgis to install in public, then install all my data and stored procedures
>> in another schema like "work".
>>
>> createdb -U postgres -h localhost -T template_postgis mydatabase
>> psql -U postgres -h localhost mydatabase
>> -- create my "work" schema
>> create schema work;
>> -- set the session path to default to "work"
>> set search_path to work, public;
>> -- alter the database so future connections default to "work"
>> alter database mydatabase set search_path to work, public;
>> ...
>> \q
>>
>>
>> This has the benefit that when I dump my schemas, I can reload them without
>> pulling all the postgis stuff along with my data and processes. So, upgrades
>> are greatly simplified, migrating the database to another instance of
>> postgresql which might be a different version is not a headache.
>>
> 
> 
> I have multiple future projects that have project-specific data, but
> also use shared data. Inspired by the above post, I was thinking the
> following variation --
> 
> 1. One database called 'mydb'
> 
> 2. A schema called 'public' that holds the 'spatial_ref_sys' table and
> all the PostGIS functions
> 
> 3. A schema called 'shared' that holds all the tables that are shared
> by the projects
> 
> 4. A schema for each project, hence, 'project1', 'project2', etc.
> 
> Questions:
> 
> a. Is the above strategy sound?
> 
> b. Does every spatial db instance have to have its own copy of PostGIS
> functions and 'spatial_ref_sys' table? The reason I ask -- if I need
> to update the PostGIS functions, do I need to do that in one place, or
> in every db?
> 
> c. Will multiple schemas in the same db be able to use the PostGIS
> functions installed in the 'public' schema of that db? Seems so from
> the above strategy.
> 
> d. The notes above refer to setting the search_path and the
> session_path. Where is that done?
>