Thread: schemas to limit data access

Hi,
I was looking for opinions on performance for a design involving schemas.  We have a 3-tier system with a lot of
hand-writtenSQL in our Java-based server, but we want to start limiting the data that different users can access based
oncertain user properties.  Rather than update hundreds of queries throughout our server code based on these user
propertieswe were thinking that instead we would do the following: 

1. Build a schema for each user.
2. Reset the users search path for each database connection so it accesses their schema first, then the public schema
3. Inside that users schema create about 5 views to "replace" tables in the public schema with the same name.  Each of
theseviews would provide only a subset of the data for each corresponding table in the public schema based on the users
properties.
4. Provide rules for each of these views so they would act as insertable/updateable/deleteable views.

Does anyone have any thoughts on how this may perform over the long-haul?  Database cleanup or maintenance problems?

We currently only handle about 50 users at a time, but expect it to potentially handle about 150-200 users within a
yearor two. 

Running PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.3

Thanks!


--------------------------------------------------------

Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are
notthe addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or
copyingof this communication is strictly prohibited. If you have received this e-mail in error, please notify the
senderof the error. 

On 8/31/07, Brennan, Sean (IMS) <BrennanS@imsweb.com> wrote:
> Hi,
> I was looking for opinions on performance for a design involving schemas.  We have a 3-tier system with a lot of
hand-writtenSQL in our Java-based server, but we want to start limiting the data that different users can access based
oncertain user properties.  Rather than update hundreds of queries throughout our server code based on these user
propertieswe were thinking that instead we would do the following: 


> 1. Build a schema for each user.
> 2. Reset the users search path for each database connection so it accesses their schema first, then the public schema
> 3. Inside that users schema create about 5 views to "replace" tables in the public schema with the same name.  Each
ofthese views would provide only a subset of the data for each corresponding table in the public schema based on the
usersproperties. 
> 4. Provide rules for each of these views so they would act as insertable/updateable/deleteable views.
>
> Does anyone have any thoughts on how this may perform over the long-haul?  Database cleanup or maintenance problems?

This will work relatively ok if the main tables in the public schema
do not change very much...otherwise you have to drop all the views,
change tables, and re-make.  Even still, that's a lot of rules flying
around, and excessive use of rules is asking for trouble.

You may want to explore trying to do it using a single view for each
underlying table, and drop the schemas approach (which I would be
looking at for separate physical tables, not views).  A very simple
way to do this that might work for you is:

create view foo_view select * from foo where owner_col = current_user;
plus update, delete rules, etc.

you can then rename the tables in place for seamless app integration.
You could replace the current_user item with an expression but the
performance issues could be large...owner_col could be an array though
as long as it's relatively small (you may want to look at array
indexing techniques if you go the array route).

merlin