> On May 21, 2020, at 7:36 AM, Adrian Klaver <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. --- Israel Brewster BrewsterSoft Development http://www.brewstersoft.com <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 >
