How to create the right authorization system?

Question

JavaScript

Hello comrades! Please help me figure it out. I want to understand the sequence of actions. I'm trying to create a project on the Node.js + Express.js + Vue.js + PostgreSQL
stack . The essence of the project is simple, the user enters the site. If before that he was not authorized, then he is met by the authorization page. After authorization, the user enters the main page. On the main page, depending on the role of the user, you need to show different messages. I read a lot of material on the topic of authorization, after which the porridge in my head associated with tokens. Most of the articles are related to MongoDB. I did not quite understand what is the best practice and how best to build everything. For example, in Node.js it is better to connect to the PostgreSQL database through the node-postgres package
or communicate via ORM ( sequelize package )? In the cases of the first package, I create a pool and execute native SQL queries. In the cases of the second package, you need to create a data model (model) and contact with it.
If you have come across ready-made solutions or good articles on this topic, I would be glad to take a look at them.

Answer 1

porridge in the head associated with tokens

It's good that you understand that. I suppose, like all noobs, have you read the paid articles from Auth0 and Firebase? Well, porridge on purpose so that you run to them to make these tokens) Marketing. Hype.
The second step is not to use tokens for authorization (as ALL real projects do, not Hello World tutorials). Instead of tokens - regular sessions. Tokens - if there is a public api and microservices.
The third step is not to use the Passport. Why is it needed if there is express-session, I don’t understand? Passport yuzay only if you fasten several authorizations of social networks and time in general back to back. And it's better to do it yourself.

Answer 2

// pgsql + sequelize + passport
// sessions will save in DB, after restart app entirel will save
// http://docs.sequelizejs.com

//////////////////////////////////////
//APP.js
const session = require('express-session');
const pgSession = require('connect-pg-simple')(session);
const pg = require('pg');
var passport = require('passport');
//sequelizejs - not necessary

// ... After
// app.use(bodyParser.json());
// app.use(cookieParser());
// ...


// native postgress client
var pgPool = new pg.Pool(Object.assign(config_db, {user: config_db.username}) );
// https://github.com/voxpelli/node-connect-pg-simple
app.use(session({
  store: new pgSession({
    pool : pgPool,                // Connection pool
    tableName : 'sessions'   // Use another table-name than the default "session" one
  }),
  secret: process.env.FOO_COOKIE_SECRET || 'oU80saf_Dwd48w9',
  resave: true,
  saveUninitialized: true,
  cookie: { maxAge: 7 * 24 * 60 * 60 * 1000 } // 7 days
}));


app.use(passport.initialize());
app.use(passport.session());

require('./routes/auth')(app,passport); // !!! this file in below


//////////////////////////////////////
// /routes/auth.js
//http://www.passportjs.org/docs/username-password/
const flash = require("connect-flash"); //https://www.npmjs.com/package/connect-flash cross redirect messages
var LocalStrategy = require('passport-local').Strategy;

function AUTH(app, passport){

  app.use(flash());
  
  passport.use(new LocalStrategy({
      usernameField: 'email',
      passwordField: 'password',
      passReqToCallback: true
    },
    async(req, username, password, done) =>{
  
      try{
        let user = await Users.Login(username, password); //Model Method
  
        if(!user){
          return done(null, false, { message1: 'Incorrect username or password.' });
        } else {
          //if auth
          return done(null, user);
        }
      } catch(ex){
        // return done(err); 
        return done(null, false, { message1: 'Incorrect data.' + ex.message });
      }
    }
  ));
  
  
  //new update
  app.post('/auth/login', function(req, res, next) {
    passport.authenticate('local', function(err, user, info) {
      if (err) { return next(err) }
      if (!user) {
        // *** Display message using Express 3 locals
        req.session.message = (info && info.message) || '';
        return res.redirect('/auth/?returnurl='+req.path);
      }
      //TODO: return url after relogin
      req.logIn(user, function(err) {
        if (err) { return next(err); }
        return res.redirect('/' + (req.query.returnurl || '') );
      });
    })(req, res, next);
  });
  
  //если просто зашли, перенаправить
  app.get('/auth/login', function(req,res,next){
    res.redirect('/auth/');
  });

  app.all('/auth/logout', function(req, res){
    req.logout();
    res.redirect('/auth/');
  });
  
  passport.serializeUser(function(user, done) {
    // done(null, user.id); //VARIANT 1
    done(null, user); //VARIANT 2
  });
  
  // passport.deserializeUser(function(id, done) { //VARIANT 1
  passport.deserializeUser(function(user, done) { //VARIANT 2
    // console.log('Deserialize user called.');
    let incapsuledUserData = {
      id: user.id,
      email: user.email,
      username: user.username,
      fullname: user.fullname,
    };

    global.user = { user: incapsuledUserData };
    return done(null, { user: incapsuledUserData });
  });
  ///
  
  //my
  app.use(function(req, res, next) {
    var isAuthPage = req.path.startsWith('/auth');
    var isApi = req.path.startsWith('/api/');
    
    if (isAuthPage || req.user || isApi) {
      // logged in
      return next(); 
    } else {
      // not logged in
      res.redirect('/auth/?returnurl='+req.path);
    }
  });

  ////////////////AUTH END

}

module.exports = AUTH;


///////////////////////////////////////
// Session Model
// http://docs.sequelizejs.com
sid = { type: Sequelize.STRING, allowNull: false, primaryKey: true };
sess = { type: Sequelize.JSON, allowNull: false };
expire = { type: Sequelize.DATE, allowNull: false };

///////////////////////////////////////
// Optional in API router
router.use(function(req,res,next){

    var haveToken = false;
    var isAllowedForUnauth = false;
    var tokens = [
        'q38ru8Jsd09_we0)weW', 
        //...
    ];

    var allowedPaths = [
        '/letters/feedback',
    ];

    var givedToken = req.headers.token || req.query.token;

    if(tokens.indexOf(givedToken)!==-1)
    haveToken = true;

    if(allowedPaths.indexOf(req.path)!==-1)
    isAllowedForUnauth = true;

    if(req.user || haveToken || isAllowedForUnauth)
        next();
    else {
        res.status(401).send('Access denied');
    }
    
});

Answer 3

You are trying to bite off too much of the pie. Decide what you want to learn, node, postgres or vue? To communicate with the database, I would not recommend using sequelize right away, first deal with the basic CRUD operations on sql queries, it's not difficult. Entering sequelize is easier if you understand what this add-on is on top of.
For authorization - sessions, for the frontend - any template engine. And start working in tandem with vue when everything is more or less clear with the express.