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).
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).