6

I'm working on SaaS application that uses the one DB per client model. It also has common "accounts" database where some basic information about the account is kept and also provides log-in functionality.

My question - is it worth creating new database user for each client database that has permissions only on that database or a single database user with access to all client databases makes more sense (i.e. "account\_%.*")?

arnaslu
  • 1,044
  • 3
  • 11
  • 20

3 Answers3

4

If security is the concern, user per database is a way to go.

Konstantin Tarkus
  • 37,618
  • 14
  • 135
  • 121
  • How can this scale to a large application with millions of users? How do you ensure deployments are done uniformly across all databases? How can you use connection pooling? Also, what's the security advantage of creating an entirely new database vs simply creating a new db user? – Jacek Trociński Apr 06 '22 at 08:06
  • @JacekTrociński if you're building a multi-tenant app with multiple databases, the recommended approach is to use a single database with multiple `schemas` + separate user accounts per schema or grant different roles at the application level (at least, in the case with PostgreSQL). – Konstantin Tarkus Apr 06 '22 at 13:45
3

It's easy to think about creating all those databases.

But also please think about how you are going to maintain them all in the long run.

  • Will you have to run your database scripts on an ever-increasing number of databases?
  • You will have a script to run when you add a new client's database, and that will have to be continuously updated.

I'm not saying don't create multiple databases. I'm just suggesting that you think about the consequences.

DOK
  • 32,337
  • 7
  • 60
  • 92
2

I would create new databases, but it depends. Basically whatever floats your boat :)

one database per user:
+ security is easier
+ async parallel requests (if your server can handle it)
- a bit heavier on disk

one database:
+ one file to handle instead of a bunch (if that's even a +)
+ little bit more space efficient
- slow when data reaches big amounts
- no simultaneous connections meaning a heavy sql request from one user will dos all other

Filip Haglund
  • 13,919
  • 13
  • 64
  • 113
  • Thank you for your answer, but we're already using one database per client. My question was, not that we'll have a lot of client databases, if we should create single database user that has access to all these databases, or create new database user for each client database. – arnaslu Jun 07 '11 at 06:45