I have a fully working SaaS with 30+ companies paying, with 20 employees each on average. I wrote it as a self taught programmer via YouTube and stackoverflow. It is hosted on a $2 a month namecheap sharedhosting. Php back, jQuery front. I use cpanel to set things up.
It runs faster than 90% of webapps on the internet.
As a former php dev I absolutely believe this. I can't get over how slow and bloated stuff is these days. I can't even do front-end anymore.
We once did an app ages ago where the database was used to create a materialized view of code (PHP) + web pages for everyone and everything. We then rsynced that to 6 machines. This is ancient times, but this thing FLEW -> click click click click you could fly through the pages. It was a read heavy workload, but still, even the front-end (just hand coded) was somehow faster than all the latest and greatest going on these days.
Yeah, no, I meant that 30 orgs with an average of 20 employees is (likely) a trivial load but it begins to have an interesting complexity on the amount of code for RBAC.
Not OP, but like any other database? Even if the database supports it, you usually should not have users directly logged in the database anyways, so whenever the database "has" or "lacks" access control, it doesn't matter.
What does matter however, is enforcing parametrized queries everywhere. Unless all the db handles you pass to the client handling code are read-only, chaos will ensure from the DDL permissions.
I start by assuming a server-side bridge between the client and the database, and it's a given the queries are parameterized. So only the bridge is doing the "logging in".
Why is it superior to put all of the (bespoke) access control logic in the server side bridge rather than use what's available in the database (accessed by the bridge, not the client)?
I have been watching like a hawk for 6 months but I haven't stumbled upon a clear reason why this is done, except for "it helps source code db portability".
For a multiorg/multiuser application this seems like the crucial distinction between sqlite and postgresql.
Again I have no experience here, talk to me like I'm stupid (I really am!).
For a multiorg approach, you probably want isolated DBs anyways.
Within a single org, multiuser approach, there are 2 big problems that I remember with attempting to shoehorn DB auth into application auth:
* assuming you use a connection pool, you might run out of TCP connections/ports if you need to handle too much stuff;
say for example that your load balancer need 3 application nodes behind it - you will need 2 (connections per user) x 3 (application nodes) connections just to handle a user - 6 connections/user. That will eat your database connection limit very fast, for no good reason.
* assuming you don't use a connection pool, you now have horrible latency on every connection (bad) and need to handle plain text passwords (assuming you use scram-sha-256), or md5 non-replay-resistant hashes of user passwords in, either sent in every client request, or in a shared session system. No matter what you pick, you have a security disaster in the making (very bad).
In Postgres at least you can use SET ROLE when getting a connection from the pool and RESET ROLE when returning a connection to the pool, and avoid the problems you describe.
Ah, thank you very much. I will happily investigate these and ditch postgresql for sqlite if still true, for my planned use case.
sqlite looks like great technology to me (as is postgresql) but I am a bit of a fanatic for keeping the overall system as understandable as possible, so these questions are important (for me, I'm stupid).
That pain also comes if you're already deeply invested in cloud products. Nothing scales without effort. And pain points always arise where you don't expect them. But the simpler the setup, the simpler it's often to scale out the parts that need scaling. In this example maybe a dedicated server or a separate database server at some point. But there can be many customers before that's necessary.
Preemptive scaling never really works and most projects never scale enough to warrant more than one server (unless you write very inefficient code).
It runs faster than 90% of webapps on the internet.