Re: Table partitioning for cloud service?

Hi,

On Thu, 21 May 2020 at 18:14, Christopher Browne <cbbrowne(at)gmail(dot)com> wrote:

> On Thu, 21 May 2020 at 11:53, Israel Brewster <israel(at)brewstersoft(dot)com>
> wrote:
>
>>
>> - Table-based tenancy (e.g. - each table has a "tenant_id" and queries
> need to specify the tenant)
>

The database/schema per tenant solution can be tedious when you want to
modify something on the structure and you have numerous tenants.
Therefore I used the "tables with tenant_id" version in a similar situation
but with a slight twist. One of the biggest issue of this solution is that
if you forget to add the tenant_id to the where clause you are going to
reveal one tenant's data to another.
I came up with the solution that the database user have no privileges for
accessing the base tables. Instead of that I generate views for each tenant
and they can access their own data in the underlying table through these
views. Now if forget to address the right tenant in my client code(it still
happens sometimes) and try to directly access the base tables I get a
strongly worded reminder from the server.

Regards,
Sándor