I'm re evaluating a few design choices I made a while back, and one that keeps coming to the forefront is data separation. We store sensitive information for clients. A database for each client isn't really workable, or at least I've never though of a way to make it workable, as we have several thousand clients and the databases all have to be accessed through a limited number of web applications where performance is important and things like persistant connections are a must. I've always been paranoid about a programmer error in an application resulting in data from multiple clients getting mixed together. Right now we create a schema for each client, with each schema having the same tables. The connections to the database are from an unprivileged user, and everything goes through functions that run at the necessary privileges. We us set_search_path to public,user. User data is in schema user and the functions are in the public schema. Every table has a client_id column. This has worked well so far but it's a real pain to manage and as we ramp up I'm not sure it's going to scale that well. So anyways my questions is this. Am I being too paranoid about putting all the data into one set of tables in a common schema? For thousands of clients what would you do? Chris
