Re: Table partitioning for cloud service?

On 5/21/20 8:53 AM, Israel Brewster wrote:
>> On May 21, 2020, at 7:36 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>> On 5/21/20 8:29 AM, Israel Brewster wrote:
>>> I’m working on my first cloud service, which will be backed by a
>>> postgresql database. Currently I only have a single customer, but of
>>> course I want to design with the possibility of multiple customers in
>>> mind. In that vein, I’m wondering what is “typical” in terms of
>>> designing the DB structure to make sure that one customer doesn’t
>>> “accidentally" get data for another customer? At the moment I am
>>> leaning towards giving each customer their own set of tables, with a
>>> unique prefix for each. This would provide a “hard” separation for
>>> the data,
>> ^^^^^^^^^^^^ I think that is called a schema:)
>
> Ok. That’s probably an option. Although it looks from a cursory perusal
> that for that to work, I would have to have separate DB users with
> different permissions. Which would be fine, except that I don’t have
> permissions to create users.
>
>> Or set up a separate database for each in the cluster.
>
> Same as above - no permissions.
>
> At the moment, I am running on Heroku, which gives me a postgresql
> database, but not full control over the cluster. I may need to move to
> something more robust, if having completely separate databases is the
> best option. I was hoping to avoid SysAdmin stuff as much as possible,
> and focus on the software side, but obviously some sys admin is required.

You can't use this?:

https://devcenter.heroku.com/articles/heroku-postgresql-credentials

>
> ---
> Israel Brewster
> BrewsterSoft Development
> http://www.brewstersoft.com
> Home of EZPunch and Lyrics Presenter
>
>>
>>> but would also increase maintenance efforts, as if I needed to add a
>>> field I would have to add it to every table. On the other hand,
>>> keeping everything in the same set of tables would mean having to be
>>> VERY careful with my SQL to make sure no customer could access
>>> another’s data.
>>> How is this typically done?
>>> ---
>>> Israel Brewster
>>> BrewsterSoft Development
>>> http://www.brewstersoft.com <http://www.brewstersoft.com/>
>>> Home of EZPunch and Lyrics Presenter
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com