> Many apps I know simply have 1 user "myappuser" (or even default user) to access its DB.
Sure; either those apps don't need to differentiate access between their users, in which case one role is sufficient, or they reimplement their own auth system, in which case you'd use Postgres' own rather robust auth system instead. It comes down to the needs of the domain; you'll solve the problems differently depending on what approach you take, but you need to solve the same problems.
Yes -- I've found it very tiring, as you put it, to keep reimplementing the same boilerplate in every API server just to lift the operations my database can already support out to an HTTP frontend. Postgres' auth means I don't have to make or press into service a separate auth system, and there are multiple ways to handle business logic orthogonally.
Stored procedures and triggers work well, but are synchronous within the current transaction, and sometimes simply don't map well to the domain needs. You can also use the AWAIT and NOTIFY statements to set up asynchronous external workers. I find this has a positive effect on the data model, as you're forced to consider what states a system will pass through during an asynchronous flow.
Sure; either those apps don't need to differentiate access between their users, in which case one role is sufficient, or they reimplement their own auth system, in which case you'd use Postgres' own rather robust auth system instead. It comes down to the needs of the domain; you'll solve the problems differently depending on what approach you take, but you need to solve the same problems.
Yes -- I've found it very tiring, as you put it, to keep reimplementing the same boilerplate in every API server just to lift the operations my database can already support out to an HTTP frontend. Postgres' auth means I don't have to make or press into service a separate auth system, and there are multiple ways to handle business logic orthogonally.
Stored procedures and triggers work well, but are synchronous within the current transaction, and sometimes simply don't map well to the domain needs. You can also use the AWAIT and NOTIFY statements to set up asynchronous external workers. I find this has a positive effect on the data model, as you're forced to consider what states a system will pass through during an asynchronous flow.