Re: Multi tenancy : schema vs databases

On Sat, Oct 01, 2016 at 07:21:47PM -0400, Melvin Davidson wrote:

> *I would like to comment on the multiple schema vs databases situation.
> First of all, 1000's of databases is insanity and just asking for trouble.
> Next, 1000's of schemas is a nightmare to maintain. I understand the
> requirement for client data to be "isolated", but in reality, data is never
> really separated. Once it's on the server, any good hacker with a knowledge
> of SQL can find it. So, IMHO, the best solution is to isolate by a client
> ID in the tables of one database. Then make sure you have sufficient and
> correct security on those tables.*

The concern was raised that if tenant data is separated only
by client_id within one and the same table a simple client_id
related error in the app would expose another tenants data.

Would not RLS help in avoiding this sort of thing ? Tie RLS
based row visibility to the logged in user and hand out
different accounts to tenants. That way, the app cannot see
anything beyond what the user types into the application in
terms of credentials. The app can't really do it wrong -- the
user provides credentials and the database provides data
based on those credentials.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346