search

influitive / apartment

Database multi-tenancy for Rack (and Rails) applications
2.67k stars 463 forks source link

Security benefits of tenant management via schema vs column #561

Closed turgs closed 5 years ago

turgs commented 6 years ago

Is my assumption correct that managing tenancy in the way Apartment gem does provides many security benefits over other methods?

Is this listed out anywhere?

If an account (an account may be a company with many users that can login) on my app is "taken over" and that malicious user finds an SQL Injection vulnerability and manipulates SQL queries sent to my DB server, will "separating accounts to have their own schema" mitigate that risk?

I.e. could they write SQL that queries a different schema to try and get a copy of the whole database? I want to avoid the malicious party doing SELECT * FROM users and having all my data breached.

Will separate schemas contain the scope of breach to a single schema? I'm talking about a breach at the application level, not where a the db server is breached (i think the best that can be done therethere is using very restricted firewalls and at-rest encryption).

s01ipsist commented 5 years ago

If the application is able to switch tenants and schemas, and the application is compromised, there is a risk every schema could be compromised.

mikecmpbll commented 5 years ago

i suspect it depends on how your grants are configured on postgresql, but i'll defer to postgres folks who know better than i. closing but feel free to continue discussion.