On Fri, Sep 30, 2016 at 6:06 AM, Rakesh Kumar <rakeshkumar464@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
