**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](https://supabase.com/docs/guides/database/postgres/row-level-security) 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

```sql
   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

```sql
   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.

```sql
   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

```typescript
   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.

```typescript
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](https://github.com/inferablehq/postgres-rls-demo/blob/main/test.ts):

```typescript
// 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](https://github.com/inferablehq/postgres-rls-demo).