9

We are creating SAAS where we will at most have 50.000 customers. We are considering creating a user in the Postgres database for each customer. We will map each user that logs into our service to a user in the database in order to be very sure that they only have access to their own data. We also want to implement an audit trail directly in the database by this solutions, which utilizes triggers. If each customer has its own database user, then it would be very easy to see who did what, even if two customers would share the same data.

Will we be running into some unexpected problems because we have 50.000 users in our database? Performance-wise or administration-wise. Maybe connection pooling would be more difficult, but I do not really know whether we would need it.

  • 2
    You won't be able to do any sort of connection pooling if you are using DB auth will you? For performance the important issue is the number of concurrent connections and how much resource they are using rather than the number of users in the DB. – Jack Douglas Jan 14 '15 at 14:14
  • 2
    @JackDouglas Yes, you can use connection pooling. Connect as "commonUser" then set role actualUser – Neil McGuigan Jan 14 '15 at 19:31
  • 2
    @Neil sure, but that's not DB auth. If you are authenticating using the database user's password you'll need to use some sort of external auth in postgres. – Jack Douglas Jan 14 '15 at 21:47
  • 2
    @JackDouglas you're right, it's proxy auth as opposed to db auth. – Neil McGuigan Jan 14 '15 at 23:05
  • The answers so far are assuming a high number of concurrent users, will this be the case? – Jack Douglas Jan 15 '15 at 5:31
11

Yes, it should be fine. You should use connection pooling though, as pg uses a fair amount of memory per connection (about 10MB AFAIK).

More than 500 simultaneous connections per box will be a problem though (like actively querying the database at the exact same time). More cpus/cores is better. Use SSDs with RAID 10.

Your SaaS application should connect as one user, then set role to the real user. This allows you to use connection pooling, as the connection string will be the same, but use different users. You should reset role when returning connection to the pool.

This is not really database authentication. It's proxy authentication (aka Impersonation).

You could also consider separate pools per company or per role.

To make admin easier, you can put users into groups and set permissions via groups. This is called RBAC.

Update: I was able to create 50,000 users in 2.4 seconds. PGAdmin is noticeably slower, due to the number of users. However connecting via JDBC is as fast as before. I was unable to drop 50,000 users at once, but could do about 10,000 at a time.

  • Thank you very much for your researching. Was it possible to work in PGAdmin at all? Was it a big problem with the performance there? – David Jan 16 '15 at 0:35
  • @David PGAdmin was okay, just slow. psql should be fine. Might be able to tweak PGAdmin to speed things up. – Neil McGuigan Jan 16 '15 at 0:52
2

Performance : thousands concurrent connection will eat up Your memory, approximately a value above 1,000 concurrent connections advised to use connection pooling, pgbouncer is a good one, developed by skype.

Administering : Administer 50,000 users will be a big job IMO. How about differentiate costumer with same data access using different application_name, so each costumer will connect to database using same user name.

Example :

using different user name, the connection string of each client would be : --user user1, --user user2, etc.

But using different application_name, the connection string of each client would be : --user user1 --application_name costumer1, --user user1 --aplication_name costumer2, etc.

The application_name is recorded in pg_stat_activity and could also be logged. I think that would be easier to implement. And the application_name is also logged in the audit trigger you want to apply. More details here.

Hope it helps.

  • 4
    How is administering 50,000 db users harder than 50,000 app users? – Neil McGuigan Jan 14 '15 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.