How to Extend Single User WebApp for Multiple Users
There are similar streams without a concrete solution and I thought it would be better to start a new one.
I ran into a situation where I have a WebApp hosted in RESIN (just like Tomcat, I think). So far I have been developing an application using db4o, since I am alone and I needed to terminate an ASAP application, I have a DB for users and another DB for application data for one user (me), now that the application is almost I'm going to go to postgresql and I take DB per user seriously, even if the DB stores data for multiple applications, as it will handle curious sensitive data and I thought a separate DB would be the best (secure) one. There is already a rudimentary session management that stores user data such as the ID in the browser. But I was wondering how I can extend it to multiple users / db.
I was thinking of extending the listener class that stores the context data to pass the right db object to the application instance, or perhaps install a filter for this purpose.
.update.
I wanted to give a little more information about what I have.
I have:
A context that contains a link to some objects, one of these objects connects to the database and checks the user and password.
The view servlet (HttpServlet) mapped to "/" which has a registration form where POST / login.
The input servlet (HttpServlet) mapped to "/ login", which checks the httpSession user's password attributes against the corresponding object in the Context, if there is a match, sets the httpSession attribute, which contains the USERID, and redirects the user to the application located at / index -debug.html if it doesn't create a new html page with login form again.
Authorization and authentication filters mapped to / index -debug.html, which checks HTTPServletRequest for the USERID attribute and checks if the user has permission to access the application.
Finally, the DB bean, which is responsible for reading and writing to the WebApp user's database. When I execute a specific method in webApp CP2JAVAWS, this method matches the corresponding method in the bean, the problem is that this bean has a static database and so far it only allows one user at a time.
What I would like to do is somehow let this DB bean instantiate once per user and read and store the appropriate data depending on the currently logged in user.
The idea of one DB per user is currently being dropped, but I don't know how exactly to do this.
source to share
You mentioned Postgres as a database database and it has a feature called schemas. This is where you have one physical database and multiple schemas within the database. My experience with this is from Rails, but the concepts are the same. This technique avoids blurring people data in one set of tables, which sounds like your main problem. I know you are using Java, but watch this talk about multi-tenant applications in Rails for a background from Guy Naor on how it works, tradeoffs, etc.
Below are some specific steps to get you started this way to use Postgres schemas:
- Postgres has a public schema which is the default. In this case, you put your user authentication tables and any other general metadata tables about user logins, etc. See Postgres docs for more information on how schemas work
-
Come up with a naming convention for each schema you create (e.g. user_001, user_002, etc.). Pre-allocate a bunch of empty schemas with all the table settings and when a user logs in or logs in for the first time, you assign a schema to them and store the schema name in your custom entry in the public schema and in the user object that you have HttpSession. There would be no need to run table creation scripts for the first user - that would be a drag and drop of performance in a web application. You just need to stay ahead of the pace of new users. For example, you might have an empty empty schema user_standby_1 ... user_standby_100, and then when someone logs in or registers, you run this sql:
myquery = "ALTER SCHEMA user_standby_? RENAME TO user_?"; myquery.setString(1,standby_id); myquery.setString(2,user_id);
-
When you create your DB bean (use a superclass for that, see below) pass the schema name from the User object from the HttpSession, then execute this SQL before each operation to isolate them just the schema:
myquery2 = "SET search_path TO ?";
myquery2.setString(1,user.search_path);
-
If you have an empty full schema publicly, then you want to omit the post from the search path, otherwise you will have 2 tables with the same name in the search path. If you want the user search path to include
SET search_path TO user_001,public
, then after creating the tables, drop all of the non-user public data tables and any meta information you need. - For maintenance, write a script, you can run through the command line to remove empty user_standby schemas, create new user_standby schemas, and do the Rails Migration equivalent for Java . for minor table changes.
- For large service operations it is best to create new schemas, for example. user_v2_001, for each user, and then write scripts to migrate their data. It depends on how complex the changes to your tables are.
If you are taking an alternate route and have all user data in one set of tables, the best approach is to have a user_id in each table and write your SQL every time. If you are using traditional normalization and join to get your user_id, then you better make sure you don't accidentally miss the connection, or users will start seeing each other's data.
The Postgres schema feature allows you to block user access only to their own data. After figuring out the basics, use the superclass in Java to write step 3 above, so each MyTableDBBean exits the MasterDBBean and uses the superclass's constructor to isolate the search path to the custom schema. Then you only have 1 place in your code where this is done, and you do not need to remember more than business logic for each table or query.
source to share
Resin! I haven't heard or worked with Resin for quite some time. =)
I saw the idea that one database for each user of the system often occurs on a stack overflow. The reaction is usually the same - not a good idea.
There are many reasons why, but I'm just sticking to scale, maintainability, and volatility.
Scale
Some databases have limits on the number of databases they can have. I don't know how many databases one Postgres instance can have.
This link ( https://dba.stackexchange.com/questions/23971/maximum-number-of-databases-for-single-instance-of-postgresql-9 ) says someone got 10,000 databases on one copy.
I would say that over time it is not uncommon to get a million users for a site (of course not all active ones). In other words, I would bet your user account will break Postgres at some point with one database per user.
maintainability
Suppose you only want 10,000 users someday, and you can make 10,000 databases. What happens if you want to update a table in each database? It hurts to give up these changes.
What usually happens is you write a script to touch each database, and even if you've tested it, halfway through the script dies and now you're stuck for a few desperate minutes with half the tables in one state and half in some other state.
Or worse, the database is out of sync and has a different schema than the rest of the databases. You now potentially have more than one live version of a "user" database.
Volatility
Users are fickle. They will sign today and then never come back. They will register and then log back in two years later. They will be making multiple accounts because they forgot their password.
This will quickly lead to lost databases. You should (or want to) write a script to clean them up periodically.
Also some more modern databases (such as MongoDB and Couchbase) actually pre-allocate large chunks of disk / memory when the database is created. I don't believe Postgres does this, but it is something to consider.
Safety
If someone hacks the Postgres box, dividing users across the database won't help you. They can move between databases as easily as moving between records in a table. Your best bet is to just lock the database machine very well and then let the users live together in the same table. It's easier to scale, easier to maintain, and you can manage volatility.
source to share