Express.js - Passport authentication using postgreSQL

Lets try to implement 'passport-local' authentication using postgreSQL datastore.

first create table
    create table pass_auth_user
    (
        id  SERIAL primary key,
        username varchar(50),
        pass varchar(50),
        createdon timestamp without time zone   
    )

insert a user against which we can check login

    insert into pass_auth_user(username,pass,createdon) values('sangram','abdc1234',now())

lets write a postgres function that will query database for valid user & password combination.

    CREATE OR REPLACE FUNCTION public.fn_autheticate_local(    p_username varchar(50),p_pass varchar(50))
    RETURNS TABLE(id integer, username varchar(50),pass varchar(50),createdon timestamp without time zone)
    LANGUAGE 'plpgsql'
    AS $BODY$

    begin
        RETURN QUERY
        select       
        a.id,
        a.username,
        a.pass,
        a.createdon
        from
        pass_auth_user a
        where
            a.username = p_username
        and a.pass = p_pass
        limit 1;
    end

    $BODY$;


lets check our postgres function by running following query

    select * from fn_autheticate_local('sangram','abdc1234')


we need to install pg,connect-ensure-login,passport & passport-local library using npm.

lets create a pgConnection.js add generic function that will run query & provide result.

model/pgConnection.js

    var pg = require('pg');

    const dbConfig = {
        user: 'xdba',
        database: 'xplay',
        password: 'sangram',
        port: 5432
    };
    const pool = new pg.Pool(dbConfig);



    module.exports = {
        ExecuteQuery: function (query, callback) {
        pool.connect(function (err, client, done) {
            if (err) {
                callback(err, null);
            }
            client.query(query, function (err, result) {
                done();
                if (err) {
                    callback(err, null);
                }
                callback(null, result.rows);
            })
        })
        }
    }

I am writing a userService.js that will keep all database queries through function in one place for our table 'pass_auth_user'

service/userService.js

    var pgConnection = require('../model/pgConnection');

    module.exports = {

        pg_auth: function (username, password, callback) {


        var _selectQuery = "select * from fn_autheticate_local('" + username + "','" + password + "')";
        console.log('query:' + _selectQuery);

        try {
            pgConnection.ExecuteQuery(_selectQuery, function (err, result) {
                if (err) {
                    callback(err, null);
                }
                else {
                    callback(null, result);
                }
            });
        } catch (exp) {
            callback(exp, null);
        }
        },

        getById: function (userid, callback) {


        var _selectQuery = "select * from pass_auth_user where Id =" + userid;
        console.log('query:' + _selectQuery);

        try {
            pgConnection.ExecuteQuery(_selectQuery, function (err, result) {
                if (err) {
                    callback(err, null);
                }
                else {
                    callback(null, result);
                }
            });
        } catch (exp) {
            callback(exp, null);
        }
        }
    }

service/index.js
    var services = {
        userService: require('../service/userService.js')
     };
   
     module.exports = services;

route/index.js
    var express = require('express');
    var router = express.Router();
    var passport = require('passport')
    var ensureLoggedIn = require('connect-ensure-login').ensureLoggedIn;

    /* GET home page. */
    router.get('/', function (req, res, next) {
      res.render('index', { title: 'Express' });
    });

    router.get('/login', function (req, res, next) {
      res.render('login')
    });

    router.get('/authhome',ensureLoggedIn('/login'), function (req, res, next) {
      console.log(req.user);
      res.render('authhome');
    });

    router.post('/login', passport.authenticate('local', { successRedirect: '/', failureRedirect: '/login', failureFlash: true })
    );

    router.get('/logout', function (req, res) {
      req.logout();
      res.redirect('/');
    });

    module.exports = router;


app.js

    var express = require('express');
    var path = require('path');
    var favicon = require('serve-favicon');
    var logger = require('morgan');
    var cookieParser = require('cookie-parser');
    var bodyParser = require('body-parser');
    var services = require('./service');


    //passport-local
    var passport = require('passport')
    var LocalStrategy = require('passport-local').Strategy;

    var index = require('./routes/index');
    var users = require('./routes/users');

    var app = express();

    // view engine setup
    app.set('views', path.join(__dirname, 'views'));
    app.set('view engine', 'ejs');



    //passport
    app.use(require('express-session')({ secret: 'keyboard cat', resave: true, saveUninitialized: true }));
    app.use(passport.initialize());
    app.use(passport.session());


    passport.use(new LocalStrategy(
      function (username, password, cb) {
        services.userService.pg_auth(username, password, function (err, result) {

          if (err) {
        return cb(err);
          }
          var user = result[0];
          console.log('Result' + JSON.stringify(user));
          if (!user) {
        return cb(null, false);
          }

          if (user.pass != password) {
        return cb(null, false);
          }
          return cb(null, user);
        })
      }));


    passport.serializeUser(function (user, cb) {
      cb(null, user.id);
    });

    passport.deserializeUser(function (id, cb) {
      services.userService.getById(id, function (err, result) {
        if (err) {
          return cb(err);
        }

        var user = result[0];
        cb(null, user);
      });
    });


    // uncomment after placing your favicon in /public
    //app.use(favicon(path.join(__dirname, 'public', 'favicon.ico')));
    app.use(logger('dev'));
    app.use(bodyParser.json());
    app.use(bodyParser.urlencoded({ extended: false }));
    app.use(cookieParser());
    app.use(express.static(path.join(__dirname, 'public')));

    app.use('/', index);
    app.use('/users', users);

    // catch 404 and forward to error handler
    app.use(function (req, res, next) {
      var err = new Error('Not Found');
      err.status = 404;
      next(err);
    });

    // error handler
    app.use(function (err, req, res, next) {
      // set locals, only providing error in development
      res.locals.message = err.message;
      res.locals.error = req.app.get('env') === 'development' ? err : {};

      // render the error page
      res.status(err.status || 500);
      res.render('error');
    });

    module.exports = app;


Here we need to write two functions one for serialization & other for deserialization of user object.Writing two method one that takes username/password and return user record from postgre while second take userid and return corresponding user record.

After login user get redirected to root route i.e. '/'.to guard certain route from un authenticated users we are using 'connect-ensure-login' package that  provide 'ensureLoggedIn' method which is used as middleware to guard home page of autheticated user.

passport saves serialized user in req.user which can be viewed by printing same over console.