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.klaver@aklaver.com 
>> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com