Why aren't databases recommended with PG?

[emilebosch]

The readme states that PG databases are not recommended. Why not?

[bradrobertson]

There's no benefit to it and it adds a bunch of maintenance overhead, plus it's not widely used.
We'll likely strip out connection based adapters to another gem in the future.

I've never actually tested this, but I also suspect there would be a performance overhead to establishing a new connection on a per request basis, rather than just switching the search_path on the same connection.

[emilebosch]

Actually i tend to disagree, for enterprise setups:

• Having a separate database means separation of data. I.e. with an XSS hack one cannot easily access someone's other data using its injection vector.
• Having different backup schemes per apartment.

Have you considered this?

[bradrobertson]

1. I think you mean SQL injection attacks. Nothing about XSS would be more or less safe based on the choice of logical sharding you make, and if you're vulnerable to those then you've got more serious problems than the choice of tenancy that you use
2. In myself there's literally no difference between connection/non-connection adapters. In both cases, the underlying tenants still live on the same physical database machine. Apartment just issues a use "tenant1" command rather than establishing a new connection to a db on the same machine.
3. On postgresql we're using postgresql schemas which is another form of logical sharding that still keeps your data separate in much the same way mysql would through separate databases. Again if you're vulnerable to SQL injection, your tenanting strategy does NOT help you from a security perspective.

As for different backups schemes, no, I never considered that as I think you probably just want to back up all your data all the time :)

[emilebosch]

Sorry, i mean SQL injection attacks indeed. If the database strategy does indeed use "USE" then indeed it's useless. But when when its actually making a new connection to another database. The if im not mistaken you'd be safe, as long as you disallow use, you could Let's say an attacker after a login is able to do an SQL injection attack, then there is no way he could access another one's data if i'm correct.

You could use the subdomain as a user for instance. Am i missing something?

[bradrobertson]

Ya in that case you're correct, we just don't currently have the mechanism for physical sharding. Interestingly, I'm currently chatting with someone else about a possible implementation for physical sharding using connections. May be what you're looking for?

[emilebosch]

Thanks! Ok, at least im not losing my mind Hahah. I'll check that out and if its the same. We'll head on the chat there! Thanks so much for building this!

[bradrobertson]

my pleasure!