[postgis-users] Schemas in 7.3

[C F]

I'm kinda ignorant on the inner-working of Postgres, but here are some 
thoughts... In my opinion, it would be ideal to be able to store PostGIS 
layers and utilize PostGIS functionality through any schema without (or with 
minimal) modification.  I'm basing my comments on my Oracle experience.... 
the same may or may not apply to Postgres..
Maybe the pgplsql packages could be loaded into the system schema and grant 
execute permissions to a default user role... and the same with the required 
PostGIS metadata tables..  I think it would just require adding a 'schema' 
column to the geom_tables (is that the right name?  If I don't use something 
for like 5 minutes, it's history) table... I guess it would also require 
additional logic on the part of PostGIS to disallow adding or deleting 
spatial columns that do not belong to your schema, etc.
Another option is to go the SDE route... which would mean creating a 
'special' PostGIS schema, then new schemas simply need the appropriate 
permissions to utilize PostGIS.  Kind of the same as what I said first, but 
this has the sight advantage of not modifying the system schema 
structure/content.

Anyway, I don't know that I've said anything that Paul hasn't already eluded 
to, but my vote is to have to load it only one time per database and give 
all schemas access.



>From: Paul Ramsey <pramsey at refractions.net>
>Reply-To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>To: postgis-users at postgis.refractions.net
>Subject: [postgis-users] Schemas in 7.3
>Date: Thu, 06 Feb 2003 12:35:55 -0800
>
>
>7.3 is starting to stabilize now (7.3.2 was just released) so more and more 
>people are going to be using it, and by extension using PostGIS with it.
>
>One of the new 7.3 features is support for "schemas". Schemas essentially 
>partition a single database into multiple namespaces, which can have 
>different priviledge levels. So it becomes possible to have multiple 
>"different" databases in the same simple database. The reason to do this is 
>so that "related" applications can share data while keeping their 
>application-specific data separate.
>
>So the "tax collection" branch can have all their tax table, but have 
>access to a general "citizen registry" table in another schema which they 
>have only read access to.
>
>Basically schemas are a means of managing complex relationships between 
>different users and user groups. Often PgSQL administrators would solve the 
>problem by partitioning different groups into different databases. But what 
>if the groups wanted to share data? It is not possible ot join tables 
>across databaes. Schemas are the answer.
>
>Right now, PostGIS gets loaded into the schema which psql has connected to. 
>If you are loading as the 'postgres' user, then your PostGIS support 
>functions and objects will get loaded into the system schema. If you are 
>loading as another user, then they'll end up somewhere else (like your 
>personal schema, for example). So, the question is: where should PostGIS 
>support get loaded? Should we enforce any particular loading location, or 
>just "let it all hang out" like we are doing now? The trouble with the 
>current way of doing things is that it is possible for people to mess up 
>their install in subtle ways, by loading PostGIS into some non-system 
>schema which ordinary users have no (or partial) access to.
>
>Food for healthy thoughts,
>Paul
>
>--
>       __
>      /
>      | Paul Ramsey
>      | Refractions Research
>      | Email: pramsey at refractions.net
>      | Phone: (250) 885-0632
>      \_
>
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users


_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail