On Fri, Sep 30, 2016 at 6:06 AM, Rakesh Kumar
<rakeshkumar464@outlook.com> wrote:
> A typical fear mongering Q from
> them "what if due to a bug in your s/w, our competitors end up looking at our data" or
> something like that. That's why schema level vs db level discussion.
I've been reading this discussion with great interest, to see what other
Postgres experts think. :-)
I've almost always taken the customer_id approach, and I prefer it---but
I also agree it is easier to make mistakes, because you have to include
that condition in your code everywhere. With per-schema or per-database,
you can manage access simply by handing out connections.
If isolation is your goal, one drawback with one-database-many-schemas
is that there is no way to prevent users from listing all the schemas in
the database. In psql this is `\dn`, but you can also do it with SQL
against the system catalog. You can forbid querying the tables in the
schema, but anyone can see that the schema itself is there. So that
would leak some information---at the very least the number of customers
you have. You haven't said whether users will go through an application
tier or have direct SQL access, but if it's the latter, this is
something to be aware of. I believe it is possible to prevent, but only
by taking away access from important catalog tables that would also
break `\dt` or `\d foo`. (I would love to be corrected btw!) Also you
can't use RLS against the system catalog, so there's no solution there.
Good luck!
Paul