Our aim is to implement the principle of least privilege with a defense in depth approach. In this particular case, this means that a query sent by an unprivileged user should not have admin rights on the database side. RDBMS such as PostgreSQL provide very powerful, expressive and well-tested access control mechanisms : RBAC, row-level security, parametrized views, etc. These controls, indeed, are usually totally ignored in web applications which use the paradigm "1 application == 1 user", this user has thus admin role. But heavy clients often use several different users on the database side (either one per final user or one per specific role) and thus benefit from the access control of the database.
Access control from the DB is an addition to access control in the web application. AC in the webapp will be more precise but may probably suffer from some bugs ; AC in the DB will be a bit more laxist but better enforced, limiting damages in case of an application bug.
So in our case, we want to create a DB user for every application user. Then, the connection to the database belongs to this specific user and the database can thus enforce that a simple user cannot execute admin operations. An intermediate possibility would be to drop some privileges before executing a query, but our preferred way is to connect to the database as the currently logged-in user. The login-password is sent by the user when he authenticates and we just pass it to the DBMS. Scalability is not (yet) an issue for our application, we can sacrifice some scalability for this type of security.
Would you have any hints to help us achieve this ?
