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
<[email protected]> 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?
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
