RLS example 2

Of course! To set up Postgres Row-Level Security (RLS) based on group membership, you need a schema that links users to groups and resources to groups. The RLS policy then checks if the current user is a member of the group that owns the row.

Here’s a complete, step-by-step example.

---

## 1. The Database Schema 🏛️

First, we need tables to manage users, groups, their relationships, and the projects we want to protect.

• groups: A simple table for our user groups.
• users: Stores user information. The id will match the JWT sub claim.
• user_groups: A junction table that links users and groups in a many-to-many relationship. This is the key to the whole setup.
• projects: The resource we want to secure. Each project belongs to one group.

-- A table for user roles or groups
CREATE TABLE groups (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

-- A table for users, where 'id' corresponds to the JWT 'sub'
CREATE TABLE users (
    id TEXT PRIMARY KEY, -- Stores the JWT 'sub' claim
    username TEXT NOT NULL UNIQUE
);

-- The junction table linking users to groups
CREATE TABLE user_groups (
    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, group_id) -- Ensures a user can't be in the same group twice
);

-- The table with data we want to protect with RLS
CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE
);

---

## 2. Populating with Sample Data 📝

Let's add some data to make the example tangible.

-- Create groups
INSERT INTO groups (name) VALUES ('Admins'), ('Developers'), ('Viewers');

-- Create users
INSERT INTO users (id, username) VALUES
('auth0|user123', 'alice'), -- Alice is a Developer
('auth0|user456', 'bob'),   -- Bob is an Admin and a Developer
('auth0|user789', 'charlie'); -- Charlie is a Viewer

-- Assign users to groups
INSERT INTO user_groups (user_id, group_id) VALUES
('auth0|user123', 2), -- Alice is in 'Developers'
('auth0|user456', 1), -- Bob is in 'Admins'
('auth0|user456', 2), -- Bob is also in 'Developers'
('auth0|user789', 3); -- Charlie is in 'Viewers'

-- Create projects and assign them to groups
INSERT INTO projects (name, group_id) VALUES
('Project Alpha', 2),     -- Belongs to 'Developers'
('Project Omega', 2),     -- Belongs to 'Developers'
('Admin Dashboard', 1),   -- Belongs to 'Admins'
('Public Analytics', 3);  -- Belongs to 'Viewers'

---

## 3. Creating the RLS Policy 🛡️

Now, we'll enable RLS on the projects table and create a policy. The policy will check if the current user's ID exists in the user_groups table associated with the project's group_id.

This is the most important step. We'll use a subquery in the USING clause.

-- 1. Enable Row-Level Security on the 'projects' table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- 2. Force RLS for the table owner as well (good practice)
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

-- 3. Create the policy
CREATE POLICY project_access_based_on_group
ON projects
FOR ALL  -- The policy applies to SELECT, INSERT, UPDATE, and DELETE
USING (
    -- This subquery checks if a link exists in our junction table
    -- between the current user and the project's group.
    EXISTS (
        SELECT 1
        FROM user_groups
        WHERE
            -- Match the user_id in the junction table with the current user's JWT sub
            user_groups.user_id = current_setting('request.jwt.claim.sub', true)
            -- And match the group_id with the group of the project row being checked
            AND user_groups.group_id = projects.group_id
    )
);

What this policy does: For any query on the projects table, Postgres will evaluate each row against the USING clause. A row is only visible or modifiable if the EXISTS subquery returns true, meaning the current user is a member of the group that owns that specific project.

---

## 4. Testing the Policy ✅

We can simulate requests from different users by setting the request.jwt.claim.sub variable.

Test 1: As Alice (Developer)

Alice is in the 'Developers' group (ID 2). She should only see 'Project Alpha' and 'Project Omega'.

-- Simulate a request from Alice
SET request.jwt.claim.sub = 'auth0|user123';

-- Query the projects table
SELECT * FROM projects;

Result:

id	name	group_id
1	Project Alpha	2
2	Project Omega	2

Test 2: As Bob (Admin & Developer)

Bob is in two groups. He should see the Admin Dashboard and the Developer projects.

-- Simulate a request from Bob
SET request.jwt.claim.sub = 'auth0|user456';

-- Query the projects table
SELECT * FROM projects;

Result:

id	name	group_id
1	Project Alpha	2
2	Project Omega	2
3	Admin Dashboard	1

Test 3: As a user not in any groups

If we simulate a user who hasn't been added to user_groups, they should see nothing.

-- Simulate a request from an unassigned user
SET request.jwt.claim.sub = 'auth0|unassigned999';

-- Query the projects table
SELECT * FROM projects;

Result: (0 rows)

This pattern provides a powerful and scalable way to manage permissions for your application directly within the database.