> On May 21, 2020, at 7:57 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 5/21/20 8:53 AM, Israel Brewster wrote: >>> On May 21, 2020, at 7:36 AM, Adrian Klaver <adrian.kla...@aklaver.com >>> <mailto:adrian.kla...@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 > <https://devcenter.heroku.com/articles/heroku-postgresql-credentials>
Wasn’t aware of that. I *did* mention this is my first cloud project. Done plenty of DB/web/application development, but not cloud/multi-customer. Thanks for the pointer. > >> --- >> 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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >>> <mailto:adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> >>> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
