What is the best strategy for using node-postgres in multiuser CRUD application

I'm currently refactoring a LAPP (Linux+Apache+PHP+Postgres) CRUD application into a Node+Express+node-postgres one. Most of the business logic is made within the Postgres server (eg, user permissions, stored procedures, materialized views, etc). This application is a sort of a RESTful API. By "sort of", I mean an API that is not totally stateless, as it uses sessions to keep users names/passwords in the server during the whole duration of the session, which are then used in each access to the DB (a typical PHP pattern). The LAPP stack works well in a multiuser environment, and the postgres server is responsible for maintaining data integrity, even when conflicting commands are issued by different users simultaneously! The frontend is a JQuery+W2UI app that is supposed to work well with node (eg, it can define REST API endpoints that match routes in node for submit events).After reading a lot of documentation on using 'pg' (node-postgres) module in express (or any other RDBMS module, for that matter), I feel that most examples are tailored to a single DB user, even though they appear to be multiuser environments (like many blog or todo applications, where each 'user' actually uses an common/hidden login:password pair to commit data to the DB). The documentation of node-postgres is also not very clear about the issue I report below...As I'm quite new to node, I was able to setup a small server with express + sessions, but I stumbled in the DB interface, especially because of the intricacies of client pooling! My question is: what is the best strategy to implement a true multiuser CRUD application in node+express+node-postgres, using all the facilities offered by the PostgreSQL engine, like user credentials, permissions, etc?a) use a global pg instance and change the connectionString in pg.connect (with user:password stored in session) for each request (API endpoint).b) use a new pgClient for each request (with user:password stored in session) and avoid pg connection pooling altogether (eventually using connection pooling in the server, eg. pgbouncer or pgpool).c) use a local pg instance in a private thread (app clusters) for each authorized user, maintaining pg's connection poolingI feel that the last option is the least feasible, as each thread (run on a dedicated CPU) should use a single server, limiting the number of users logged to the number of CPUs. But what about the other two? I've seen a few posts demonstrating both patterns, but they are quite old and maybe not implementing the best practices of node and pg. They can also be moved to middleware that is "called" on reaching each of the API endpoitns.

Submitted February 12, 2016 at 02:32PM by sairum