Busk: Created page with "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 ch..."

2025-09-18T15:08:26Z

Created page with "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 ch..."

New page

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.



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

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

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

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

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

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

RLS example 2 - Revision history

Busk: Created page with "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 ch..."