pg-promise one connection per user

Question

Dear community and vitaly-t hopefully,

I am building a website / server with pg-promise. I use postgre role/group login for authentification.

I don't know if I am doing the things correctly but I would like that each user use their own postgres connection to query the database.

So in practice, I create a connection for each user when they connect (if it is not already existing). To do so, I have created a Pool object with an ugly 'fake promise' and a pgUser object:

var pgPool = function(pg){
    var _this=this;
    var fakePromise = function(err){
        var _this=this;
        _this.err=err
        _this.then=function(cb){if(!err){cb();return _this}else{return _this};};
        _this.catch=function(cb){if(err){cb(_this.err)}else{return _this};};
        return _this;
            };

    _this.check= function(user){
        if (_this[user]){
            return _this[user].check();
        }else{
            return new fakePromise({error:'Echec de connection à la base de 
            données'})
        }


    }
    _this.add = function(user,password){
        var c={};
        c.host = 'localhost';
        c.port = 5432;
        c.database = 'pfe';
        c.poolSize = 10;
        c.poolIdleTimeout = 30000;
        c.user=user;
        c.password=password
        if (!_this[user]){
            _this[user] = new pgUser(c,pg);
            return _this[user].check();
        }else{
            _this[user].config.password=password;
            return _this[user].check();
        };
    };
    return _this;
};

var pgUser = function(c,pg){
    var _this=this
    _this.config = c    
    _this.db = new pg(_this.config)
    _this.check = function(){
        return _this.db.connect();
    };
    return _this;
}; 

And here is how I add a user to the 'pool' during the login POST handling

 pool.add(req.body.user,req.body.password).then(function(obj){
            obj.done();
            req.session.user = req.body.user;
            req.session.password = req.body.password;
            res.redirect("/");
            return;
        }).catch(function(err){
            options.error='incorect password/login';
            res.render('login', options);   
            return;
        });

I am sure it could irritate pro developpers and you would be kind if you could explain me the best way :

• is that a good idea to have one connection to the database per user (it seems legit to have a good security)?
• how can I use the pg-promise library better to avoid this ugly custom 'pool' object?

Sincerly thank you.

Answer 1

I have contacted the security responsible of my project, doing research as associate profressor in security (CITI lab)...here is his comment :

====================

Since it is my fault, I will try to explain ;-). First, to be clear, I work on the security side (notably access control and RDBMS security) but am not very familiar with JS or promises.

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 ?

==================