Build Your Own Multi-User Authentication on Postgres with Row-Level Security

Nadeesha Cabral

•02-12-2024

Disclaimer: This is an educational demo to understand RLS concepts. Don't use this code in production as it lacks proper security measures, connection pooling optimizations, and other essential features.

What is Row-Level Security (RLS)?

Row-level security is a feature of PostgreSQL that allows you to control access to rows in a table based on the user executing the query. It's like having a virtual partition per user, but without the maintenance overhead of multiple tables. When a user queries the table, PostgreSQL automatically adds security conditions to ensure they only see their own rows.

Popular Backend-as-a-Service (BaaS) platforms like Supabase use RLS to provide a secure way for clients to directly access their own data without the need for a backend server.

What We're Building

We'll create a simple key-value store where:

• Each user has their own private data space
• Users can only read/write their own data
• Authentication is handled through database roles

Setting Up the Database

First, let's look at how we set up our database with RLS:

1. Create the key_value table with a username column

CREATE TABLE key_value (
  id SERIAL PRIMARY KEY,
  username TEXT NOT NULL,
  key TEXT NOT NULL,
  value TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

2. Enable row level security

ALTER TABLE key_value ENABLE ROW LEVEL SECURITY;

3. Create RLS policy based on current_user

This policy ensures that only the user who created the row can read/write to it.

CREATE POLICY manage_own_data ON key_value
FOR ALL
USING (username = current_user)
WITH CHECK (username = current_user);

The Express Server

Our server needs to handle two main operations:

1. Registering new users (which creates database roles)
2. Executing queries with proper user context

User Registration

Here's how we handle user registration:

1. Create a database role for the user
2. Grant necessary permissions

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

  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    // Create a database role for the user
    await client.query(
      `CREATE ROLE "${username}" WITH LOGIN PASSWORD '${password}'`
    );
    // Grant necessary permissions
    await client.query(`GRANT USAGE ON SCHEMA public TO "${username}"`);
    await client.query(
      `GRANT SELECT, INSERT, UPDATE, DELETE ON key_value TO "${username}"`
    );
    await client.query("COMMIT");
    res.json({ message: "User registered successfully" });
  } catch (err) {
    await client.query("ROLLBACK");
    res.status(500).json({ error: err.message });
  }
});

Executing Queries

When executing queries, we create a new connection pool for each user to ensure proper role context. This is not a production-ready approach as we're creating a new connection pool for each query.

app.post("/query", async (req, res) => {
  // Create a new pool for this specific user
  const userPool = new Pool({
    user: username,
    password: password,
    host: process.env.DB_HOST || "localhost",
    database: process.env.DB_NAME || "your_database",
  });

  try {
    const result = await userPool.query(query, params);
    res.json(result.rows);
  } finally {
    await userPool.end();
  }
});

Testing the Implementation

We've created a test suite that verifies our RLS implementation works correctly. Here's what it tests:

1. Creating two users (Alice and Bob)
2. Inserting data for each user
3. Verifying that each user can only see their own data

Here's a snippet from our test file:

// Test 4: Verify Alice can only see their data
console.log("Test 4: Verifying Alice's data isolation");
const aliceData = await makeRequest("/query", "POST", alice, {
  query: "SELECT * FROM key_value",
});
console.log("Alice's Data:", aliceData);

// Test 5: Verify Bob can only see their data
console.log("Test 5: Verifying Bob's data isolation");
const bobData = await makeRequest("/query", "POST", bob, {
  query: "SELECT * FROM key_value",
});
console.log("Bob's Data:", bobData);

When we run these tests, we can see that:

• Alice can only see her own key-value pairs
• Bob can only see his own key-value pairs
• Neither user can access or modify the other's data

The complete source code for this demo is available here.