Implementing User Authentication and Authorization in a psql-powered Chat Application

In this tutorial, we'll learn how to implement user authentication and authorization in a chat application powered by PostgreSQL, Node.js, and Express. By the end of this guide, you'll be able to create a secure chat application that allows users to register, log in, and participate in conversations.

Prerequisites

Before we start, ensure you have the following installed on your system:

Setting up the Project

Create a new directory for your chat application:
```
mkdir psql-chat-app
cd psql-chat-app
```
Initialize a new Node.js project:
```
npm init -y
```

Install the required dependencies:

npm install express pg bcrypt jsonwebtoken passport passport-jwt

Setting up PostgreSQL

Create a new PostgreSQL database and user:

CREATE DATABASE chatapp;
CREATE USER chatappuser WITH ENCRYPTED PASSWORD &#39;yourpassword&#39;;
GRANT ALL PRIVILEGES ON DATABASE chatapp TO chatappuser;

Create the users and messages tables:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL
);

CREATE TABLE messages (
  id SERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  user_id INTEGER REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW()
);

Implementing User Registration

Create a new file called auth.js and add the following code:

const express = require('express');
const router = express.Router();
const bcrypt = require('bcrypt');
const jwt = require('jsonwebtoken');
const passport = require('passport');
const { Pool } = require('pg');

const pool = new Pool({
  connectionString: 'postgres://chatappuser:yourpassword@localhost/chatapp'
});

router.post('/register', async (req, res) => {
  const { username, password } = req.body;

  try {
    const hashedPassword = await bcrypt.hash(password, 10);
    const result = await pool.query(
      'INSERT INTO users (username, password) VALUES ($1, $2) RETURNING id, username',
      [username, hashedPassword]
    );

    res.status(201).json(result.rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// ... (other routes will be added later)

module.exports = router;

Implementing User Login

Add the following code to auth.js:

router.post('/login', async (req, res) => {
  const { username, password } = req.body;

  try {
    const user = (
      await pool.query('SELECT * FROM users WHERE username = $1', [
        username
      ])
    ).rows[0];

    if (!user) {
      return res.status(400).json({ error: 'Invalid username or password' });
    }

    const isPasswordValid = await bcrypt.compare(password, user.password);

    if (!isPasswordValid) {
      return res.status(400).json({ error: 'Invalid username or password' });
    }

    const jwtPayload = { id: user.id, username: user.username };
    const jwtSecret = 'your_jwt_secret';
    const jwtOptions = { expiresIn: '1d' };

    const token = jwt.sign(jwtPayload, jwtSecret, jwtOptions);

    res.json({ token });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

Implementing User Authorization

Add the following code to auth.js:

const JwtStrategy = require('passport-jwt').Strategy;
const ExtractJwt = require('passport-jwt').ExtractJwt;

const jwtOptions = {
  jwtFromRequest: ExtractJwt.fromAuthHeaderAsBearerToken(),
  secretOrKey: 'your_jwt_secret'
};

passport.use(
  new JwtStrategy(jwtOptions, async (jwtPayload, done) => {
    try {
      const user = (
        await pool.query('SELECT * FROM users WHERE id = $1', [
          jwtPayload.id
        ])
      ).rows[0];

      if (user) {
        done(null, user);
      } else {
        done(null, false);
      }
    } catch (err) {
      done(err, false);
    }
  })
);

router.use(passport.initialize());

Create a new file called chat.js:

const express = require('express');
const router = express.Router();
const passport = require('passport');
const { Pool } = require('pg');

const pool = new Pool({
  connectionString: 'postgres://chatappuser:yourpassword@localhost/chatapp'
});

router.get(
  '/',
  passport.authenticate('jwt', { session: false }),
  async (req, res) => {
    try {
      const messages = await pool.query(
        'SELECT * FROM messages ORDER BY created_at DESC LIMIT 50'
      );

      res.json(messages.rows);
    } catch (err) {
      res.status(500).json({ error: err.message });
    }
  }
);

router.post(
  '/',
  passport.authenticate('jwt', { session: false }),
  async (req, res) => {
    const { content } = req.body;

    try {
      const result = await pool.query(
        'INSERT INTO messages (content, user_id) VALUES ($1, $2) RETURNING *',
        [content, req.user.id]
      );

      res.status(201).json(result.rows[0]);
    } catch (err) {
      res.status(500).json({ error: err.message });
    }
  }
);

module.exports = router;

Finally, create a new file called app.js:

const express = require('express');
const app = express();
const authRoutes = require('./auth');
const chatRoutes = require('./chat');

app.use(express.json());
app.use('/auth', authRoutes);
app.use('/chat', chatRoutes);

app.listen(3000, () => {
  console.log('Server listening on port 3000');
});

Testing the Application

Start your Node.js server:
```
node app.js
```
Use a tool like Postman or curl to test the endpoints:
- Register a user: POST http://localhost:3000/auth/register
- Log in: POST http://localhost:3000/auth/login
- Get messages: GET http://localhost:3000/chat
- Send a message: POST http://localhost:3000/chat

Conclusion

You have now successfully implemented user authentication and authorization in a PostgreSQL-powered chat application using Node.js and Express. You can further enhance your application by adding real-time messaging capabilities using technologies like Socket.IO and improving the user experience with a front-end framework like React or Vue.js.