If you are using PostgreSQL for storing data of multiple users, you might want to apply row-level security, or RLS. It’s good practice even if you are manually writing all the queries you send to your database but it’s 

 important if you have any type of LLM or similar generating queries for you!

Let’s create a trivial data model. Users and items, whatever that might be. Each item belongs to a user.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL UNIQUE
);

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    content TEXT NOT NULL
);

Now, per default, if you ask the database about any users items, it will just tell you. By introducing RLS, you can limit what the responses will be to add a layer of protection. Even if you should create a buggy query, you will not accidentally get the items belonging to someone else, just like you cannot accidentally change or delete items belonging to someone else. We do that like this:

ALTER TABLE items ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_items ON items
    USING (user_id = current_setting('app.current_user_id')::INTEGER);

 isn’t something PostgreSQL knows about per default, it’s a variable that we have introduced.

, before making some user-specific query. Or if you have a transaction, you could use 

 which will make the transaction scope function as “auth scope” as well.

Depending on your setup, there are going to be different ways to set the 

 setting. In this example, I will assume a setup based on Express or similar, and Knex for creating queries, but the pattern can easily be changed to fit whatever your setup requires.

 needs to be set only once per session. This session variable is local to the database connection, meaning each connection has its own set of session variables. When using Knex or similar to execute a query, it acquires a connection from the pool. The session variables set in that connection are isolated from other connections.

Now, with Express and similar frameworks, you will typically use the 

 variable to store state that is related to a given request. You might already have some code in middleware that checks a token and fetches the corresponding user from the database. What we could do is to put a 

 instance into this context, and make sure that instance has set the user id. However, this forces us to do quite a bit of “prop drilling” as we need to pass this instance around as a parameter to any function that needs to access the database. What we can do instead is to use a class called 

 which allows us to store state related to a specific async context. This is similar to “thread-local storage” in other languages, only we don’t have threads in NodeJS but async contexts.

import { AsyncLocalStorage } from 'node:async_hooks';

// Assuming you made a function that connects to your PostgreSQL database
import createKnexInstance from './knex';

const asyncLocalStorage = new AsyncLocalStorage();

const dbMiddleware = (req, res, next) => {
  const user = req.user; // Assuming user is set by authentication middleware

  asyncLocalStorage.run(new Map(), async () => {
    const store = asyncLocalStorage.getStore();
    const knex = createKnexInstance();
    store.set('knex', knex);

    try {
      await knex.raw(`SET app.current_user_id = '??'`, [user.id]);
      next();
    } catch (err) {
      next(err);
    }
  });
};

export const getKnexInstance = () => {
  const store = asyncLocalStorage.getStore();
  const knex = store.get('knex');

  if (!knex) {
    throw new Error('Knex instance not found in AsyncLocalStorage');
  }

  return knex;
};

export default dbMiddleware;

 to your server whereever you set up other middleware, and now you can use the 

 to get the authenticated database connection. Here is an example of a very simple server:

import express from 'express';
import dbMiddleware, { getKnexInstance } from './authMiddleware';

const app = express();

app.use(/* Some auth middleware that sets ctx.user */);
app.use(dbMiddleware);

app.get('/items', async (req, res) => {
  try {
    const knex = getKnexInstance();
    const items = await knex('items').select('*');
    res.json(items);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

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

 items row. I don’t recommend this, you should still create your queries as you normally do, but even if you released this, no items belonging to other users would leak anywhere.

Mastering Row Level Security in NodeJS doesn't have to be intimidating. With this guide as a base, you can embark on your journey toward something bigger. To enhance your skills further, check out my course 

Fullstack Typescript with TailwindCSS and tRPC Using Modern Features of PostgreSQL

<h1 data-node-type="titlePlaceholder" class="titlePlaceholder">Row Level Security in&nbsp;NodeJS</h1><p>If you are using PostgreSQL for storing data of multiple users, you might want to apply row-level security, or RLS. It’s good practice even if you are manually writing all the queries you send to your database but it’s <em>especially</em> important if you have any type of LLM or similar generating queries for you!</p><p>Let’s create a trivial data model. Users and items, whatever that might be. Each item belongs to a user.</p><div style="
          min-height: 256px;
          max-width: 100%;
            "><textarea data-iscodemirror="true" class="d-none" data-mode="javascript" data-uniqueid="cm-HMUSZ" id="cm-HMUSZ">CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL UNIQUE
);

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    content TEXT NOT NULL
);</textarea></div><p>Now, per default, if you ask the database about any users items, it will just tell you. By introducing RLS, you can limit what the responses will be to add a layer of protection. Even if you should create a buggy query, you will not accidentally get the items belonging to someone else, just like you cannot accidentally change or delete items belonging to someone else. We do that like this:</p><div style="
          min-height: 112px;
          max-width: 100%;
            "><textarea data-iscodemirror="true" class="d-none" data-mode="javascript" data-uniqueid="cm-T0ZKK" id="cm-T0ZKK">ALTER TABLE items ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_items ON items
    USING (user_id = current_setting('app.current_user_id')::INTEGER);</textarea></div><p>Note that <code>app.curren</code><em><code>user</code></em><code>id</code> isn’t something PostgreSQL knows about per default, it’s a variable that we have introduced.</p><p>Now basically, we need to execute <code>SET app.curren</code><em><code>user</code></em><code>id = '(user_id)'</code>, before making some user-specific query. Or if you have a transaction, you could use <code>SET LOCAL</code> which will make the transaction scope function as “auth scope” as well.</p><h2 class="heading-anchor-text" id="using-this-from-nodejs"><span>Using this from&nbsp;NodeJS</span><a class="d-inline heading-anchor" title="Direct link to heading" href="#using-this-from-nodejs">#</a></h2><p>Depending on your setup, there are going to be different ways to set the <code>app.curren</code><em><code>user</code></em><code>id</code> setting. In this example, I will assume a setup based on Express or similar, and Knex for creating queries, but the pattern can easily be changed to fit whatever your setup requires.</p><p>The <code>app.curren</code><em><code>user</code></em><code>id</code> needs to be set only once per session. This session variable is local to the database connection, meaning each connection has its own set of session variables. When using Knex or similar to execute a query, it acquires a connection from the pool. The session variables set in that connection are isolated from other connections.</p><p>Now, with Express and similar frameworks, you will typically use the <code>context</code> variable to store state that is related to a given request. You might already have some code in middleware that checks a token and fetches the corresponding user from the database. What we could do is to put a <code>knex</code> instance into this context, and make sure that instance has set the user id. However, this forces us to do quite a bit of “prop drilling” as we need to pass this instance around as a parameter to any function that needs to access the database. What we can do instead is to use a class called <code>AsyncLocalStorage</code> which allows us to store state related to a specific async context. This is similar to “thread-local storage” in other languages, only we don’t have threads in NodeJS but async contexts.</p><div style="
          min-height: 880px;
          max-width: 100%;
            "><textarea data-iscodemirror="true" class="d-none" data-mode="javascript" data-uniqueid="cm-KcEuK" id="cm-KcEuK">import { AsyncLocalStorage } from 'node:async_hooks';

// Assuming you made a function that connects to your PostgreSQL database
import createKnexInstance from './knex';

const asyncLocalStorage = new AsyncLocalStorage();

const dbMiddleware = (req, res, next) =&gt; {
  const user = req.user; // Assuming user is set by authentication middleware

  asyncLocalStorage.run(new Map(), async () =&gt; {
    const store = asyncLocalStorage.getStore();
    const knex = createKnexInstance();
    store.set('knex', knex);

    try {
      await knex.raw(`SET app.current_user_id = '??'`, [user.id]);
      next();
    } catch (err) {
      next(err);
    }
  });
};

export const getKnexInstance = () =&gt; {
  const store = asyncLocalStorage.getStore();
  const knex = store.get('knex');

  if (!knex) {
    throw new Error('Knex instance not found in AsyncLocalStorage');
  }

  return knex;
};

export default dbMiddleware;</textarea></div><p>Apply the <code>dbMiddleware</code> to your server whereever you set up other middleware, and now you can use the <code>getKnexInstance</code> to get the authenticated database connection. Here is an example of a very simple server:</p><div style="
          min-height: 520px;
          max-width: 100%;
            "><textarea data-iscodemirror="true" class="d-none" data-mode="javascript" data-uniqueid="cm-wBb8V" id="cm-wBb8V">import express from 'express';
import dbMiddleware, { getKnexInstance } from './authMiddleware';

const app = express();

app.use(/* Some auth middleware that sets ctx.user */);
app.use(dbMiddleware);

app.get('/items', async (req, res) =&gt; {
  try {
    const knex = getKnexInstance();
    const items = await knex('items').select('*');
    res.json(items);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.listen(3000, () =&gt; {
  console.log('Server is running on port 3000');
});</textarea></div><p>As you can see, this endpoint selects <em>every</em> items row. I don’t recommend this, you should still create your queries as you normally do, but even if you released this, no items belonging to other users would leak anywhere.</p><p>Mastering Row Level Security in&nbsp;NodeJS doesn't have to be intimidating. With this guide as a base, you can embark on your journey toward something bigger. To enhance your skills further, check out my course <a href="https://www.newline.co/courses/fullstack-typescript-with-tailwindcss-and-trpc-using-modern-features-of-postgresql?code=newlinefriends50" target="_blank">Fullstack Typescript with TailwindCSS and tRPC Using Modern Features of PostgreSQL</a>. Happy learning!</p>

Learn

The newline Guide to Building Your First GraphQL Server with Node and TypeScript

Teach

Amelia Wattenberger

Author of Fullstack D3

Community

Free Tools

Row Level Security in NodeJS

Tags

Responses (0)

Masterclasses

Tutorials

Fullstack React with TypeScript