I agree. But I am thinking about making a database role for each user. The user becomes that role after signing into the front end, like Apache.
db.exec('set role to $1', req.remote_user);
> The biggest downside is that there are no tools to help to debug or measure the performance of the code you wrote.
Isn't there the explain command (https://www.postgresql.org/docs/current/static/using-explain...) and the \timing option for the command-line client, psql (https://www.postgresql.org/docs/10/static/app-psql.html)?
I figured. If you isolate the data well enough, it could work. I'm always paranoid when it comes to DB.
> Isn't there the explain command and the \timing option for the command-line client, psql? Yes, there is. This however doesn't help you with triggers and UDF, which is how you usually create the logic in the DB.
I would also encourage to introduce a proxy in between (e.g. pgbouncer [1]) to help you to handle connections and deal with the authentication.
We're using PG as "the brain" of our service since 2014 and it never failed us. The biggest downside is that there are no tools to help to debug or measure the performance of the code you wrote. However once you have functional logic, you can write fairly lightweight application layer around it and change it as often as you want.
You can also scale this setup almost indefinitely thanks to extension like citus [2] and still keep your application layer fairly thin.
[0] https://www.postgresql.org/docs/current/static/auth-methods....
[1] https://pgbouncer.github.io
[2] https://www.citusdata.com