On 5/21/20 8:53 AM, Israel Brewster wrote:
On May 21, 2020, at 7:36 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
