Thread: Multiple customers sharing one database?
I'm developing an application that will be used by several independent customers. Conceptually, the data from one customer has no relation at all to another customer. In fact, each customer's data is private, and you would never combine data from different customers. I'm trying to decide whether to: a) give each customer a separate database or b) put everyone in the same database, but take steps to ensure that customer #1 cannot see data from customer #2. I was talking to an Oracle expert who suggested (based on his oracle background) option b where each customer has their own schema. His feeling was that each database has significant memory overhead, which then gets multiplied with option a. At least it does with oracle. Does anyone have any suggestions?
On Jun 1, 2007, at 13:27 , Rick Schumeyer wrote: > I'm developing an application that will be used by several > independent customers. Conceptually, the data from one customer > has no relation at all to another customer. In fact, each > customer's data is private, and you would never combine data from > different customers. I'm trying to decide whether to: Unless the database schema is shared by the different customers, I'd set up a separate database for each. There's better security with separate databases, and since different customer's data would never be combined, there's no advantage to putting them in the same one. Per database overhead is probably going to be negligible compared to the infrastructure you'd want to put in place for security. Michael Glaesemann grzm seespotcode net
Rick Schumeyer wrote: >> I'm developing an application that will be used by several independent >> customers. Conceptually, the data from one customer has no relation >> at all to another customer. In fact, each customer's data is private, >> and you would never combine data from different customers. Michael Glaesemann wrote: > Unless the database schema is shared by the different customers, I'd set > up a separate database for each. There's better security with separate > databases, and since different customer's data would never be combined, > there's no advantage to putting them in the same one. Per database > overhead is probably going to be negligible compared to the > infrastructure you'd want to put in place for security. I am always confused by the overloaded term "database" in such discussions. Do we mean the RDBMS engine, i.e., run separate instances of PG? I sure would recommend against that. Or do we mean it as the PG documentation does, e.g., <http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html> > A PostgreSQL database cluster contains one or more named databases. Users and groups of users are shared across the entirecluster, but no other data is shared across databases. Any given client connection to the server can access only thedata in a single database, the one specified in the connection request. That seems to be PostgreSQL's answer to the OP's question. > Note: Users of a cluster do not necessarily have the privilege to access every database in the cluster. Sharing ofuser names means that there cannot be different users named, say, joe in two databases in the same cluster; but the systemcan be configured to allow joe access to only some of the databases. In the OP's case, "some of the databases" is "one of the databases". -- Lew
On Jun 3, 2007, at 12:45 , Lew wrote: > Michael Glaesemann wrote: > >> Unless the database schema is shared by the different customers, >> I'd set up a separate database for each. There's better security >> with separate databases, and since different customer's data would >> never be combined, there's no advantage to putting them in the >> same one. Per database overhead is probably going to be negligible >> compared to the infrastructure you'd want to put in place for >> security. > > I am always confused by the overloaded term "database" in such > discussions. Do we mean the RDBMS engine, i.e., run separate > instances of PG? I sure would recommend against that. Or do we > mean it as the PG documentation does, e.g., > <http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html> I likewise try to be careful in my usage of database and database server as they are different things. I meant (and used) database, not database server. Michael Glaesemann grzm seespotcode net
On 06/03/07 12:45, Lew wrote: [snip] > > I am always confused by the overloaded term "database" in such > discussions. Do we mean the RDBMS engine, i.e., run separate instances > of PG? I sure would recommend against that. Or do we mean it as the PG > documentation does, e.g., > <http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html> A (relational) database is a database, and an RDBMS is what manipulates that (relational) database. "The" schema "defines" the database. "A" schema defines a specific logical sub-set of "the" schema. -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good!