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.
