# Implementing Fine-Grained Postgres Permissions for Multi-Tenant Applications

## Introduction

[PostgreSQL](https://www.postgresql.org/) is a powerful, open-source relational database that offers great features for managing data in [multi-tenant applications](https://www.digitalguardian.com/blog/saas-single-tenant-vs-multi-tenant-whats-difference). Implementing proper access controls becomes vital for data security and isolation when serving multiple customers from a shared database instance. While PostgreSQL provides sophisticated permission mechanisms, configuring them correctly for complex multi-tenant applications requires careful planning.

This guide will walk you through implementing [fine-grained](/content/blog/what-is-fine-grained-authorization-fga/index.html) PostgreSQL permissions for multi-tenant applications, from basic role management to advanced row-level security. You’ll learn how to leverage PostgreSQL’s built-in features to create a secure and scalable permission architecture.

## What We’ll Build

This guide will walk you through the creation of a permission system for a SaaS application that serves multiple organizations (tenants) with different user roles. Our implementation will ensure:

- **Tenant isolation**: Users can only access data belonging to their organization
- **Role-based access control**: Different permission levels within each organization
- **Fine-grained permissions**: Column and row-level access restrictions
- **Scalable architecture**: A system that grows with your application

## Access Control Policies We’ll Implement

Here’s what our permission system will enforce:

- Users can only access data from organizations they belong to
- Within their organizations, users are restricted by their role (Admin/Member/Viewer)
- Admins can manage all organizational data and assign roles
- Members can create and modify projects and tasks
- Viewers can only read data, never modify it

## Prerequisites

Before diving into our implementation, you’ll need to have a few things in place:

- **Node.js Environment** - You’ll need a working [Node.js](https://nodejs.org/en/download) environment installed on your local machine.
- [**PostgreSQL 9.5 or higher**](https://www.postgresql.org/download/)
- **Docker Environment** — Our app relies on Docker to run the [decision point container](https://docs.permit.io/concepts/pdp/overview) locally. Make sure you have [Docker installed and configured](https://docs.docker.com/engine/install/).
- **Basic** [**SQL knowledge**](https://learnxinyminutes.com/sql/): Understanding of SQL queries, functions, and database concepts.
- **A PostgreSQL Client(pg)**
- **A** [**Permit.io account**](http://app.permit.io/)

## The PostgreSQL Permission System

Let’s start by understanding the fundamental building blocks of PostgreSQL’s permission system.

### Understanding Roles in PostgreSQL

In PostgreSQL, [a role](https://www.postgresql.org/docs/current/user-manag.html) is a database entity that can own database objects and have database privileges. Roles can represent individual users or groups of users.

Roles can:
1. Have the ability to log in (making it a “user”)
2. Include other roles as members (making it a “group”)
3. Own database objects like tables and functions
4. Be granted specific privileges on objects

This flexibility allows for powerful and granular permission management.

### Running Postgres With Docker

Before we can continue with exploring the Postgres permission system, let’s first run our Postgres database with Docker:

**Pull the PostgreSQL image**:

```bash
docker pull postgres
```

**Run a PostgreSQL container**:

```bash
docker run --name postgres-db -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres
```

**Connect to your PostgreSQL container**:

```bash
docker exec -it postgres-db psql -U postgres
```

### Creating Basic Roles

Let’s create some basic roles to see how this works:

```sql
-- Create application roles (groups)
CREATE ROLE app_admin NOLOGIN;
CREATE ROLE app_member NOLOGIN;
CREATE ROLE app_viewer NOLOGIN;

-- Create user roles (can login)
CREATE ROLE alice LOGIN PASSWORD 'secure_password1';
CREATE ROLE bob LOGIN PASSWORD 'secure_password2';
CREATE ROLE carol LOGIN PASSWORD 'secure_password3';

-- Assign application roles to users
GRANT app_admin TO alice;
GRANT app_member TO bob;
GRANT app_viewer TO carol;
```

### Role Inheritance

PostgreSQL supports role inheritance, where roles can inherit permissions from other roles:

```sql
-- Make app_admin inherit app_member permissions
GRANT app_member TO app_admin;
-- Make app_member inherit app_viewer permissions
GRANT app_viewer TO app_member;
```

### Basic Permission Types

Before diving into multi-tenancy, let’s review the basic permission types in PostgreSQL:

| Permission | Description | Example Use |
| --- | --- | --- |
| SELECT | Read data from a table | View projects list |
| INSERT | Add new rows to a table | Create new task |
| UPDATE | Modify existing rows | Edit project details |
| DELETE | Remove rows from a table | Delete a task |
| TRUNCATE | Empty a table completely | Clear all completed tasks |
| REFERENCES | Create foreign keys to a table | Link tasks to projects |
| TRIGGER | Create triggers on a table | Automatic task status updates |
| CREATE | Create new objects in a schema | Make new tables or views |
| USAGE | Access objects in a schema | Use custom data types |
| EXECUTE | Run functions or procedures | Call business logic functions |

You can grant these permissions using [the `GRANT` statement](https://www.postgresql.org/docs/current/sql-grant.html):

```sql
-- Grant SELECT permission on the projects table to app_viewer role
GRANT SELECT ON projects TO app_viewer;
-- Grant all permissions on the tasks table to app_member role
GRANT ALL PRIVILEGES ON tasks TO app_member;
```

## Planning Multi-Tenant RBAC Architecture

Before writing any code, let’s plan our multi-tenant [RBAC (Role-Based Access Control)](/content/blog/what-is-rbac/index.html) architecture.

### Data Model

We’ll use the following tables for our SaaS application:

```sql
-- Organizations (tenants)
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
-- Users
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL,
    full_name TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
-- Organization memberships and roles
CREATE TABLE org_members (
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    role TEXT NOT NULL CHECK (role IN ('admin', 'member', 'viewer')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    PRIMARY KEY (organization_id, user_id)
);
-- Projects
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
-- Tasks
CREATE TABLE tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    description TEXT,
    status TEXT DEFAULT 'todo',
    assigned_to UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
```

### Tenancy Model

For multi-tenant applications, there are three common approaches:

1. **Database per tenant**: Each tenant gets a separate database
2. **Schema per tenant**: Each tenant gets a separate schema within a shared database
3. **Row-based tenancy**: All tenants share tables, with a tenant ID column in each row

We’ll implement row-based tenancy because it’s the most resource-efficient and works well for applications with many tenants. Each table related to tenant data will have an `organization_id` field (either directly or through relationships).

## Implementing Row-Level Security for Tenant Isolation

The key to implementing strong tenant isolation in [PostgreSQL is Row-Level Security (RLS)](/content/blog/postgres-rls-implementation-guide/index.html). RLS allows us to define policies that filter which rows a user can see or modify.

### Enabling RLS on Tables

First, let’s enable RLS on our multi-tenant tables:

```sql
-- Enable RLS on tables that contain tenant data
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE org_members ENABLE ROW LEVEL SECURITY;
```

### Tracking User and Tenant Context

To make RLS work, we need a way to track the current user and their tenant context. PostgreSQL provides session variables that can store this information:

```sql
-- Function to set the current user context
CREATE OR REPLACE FUNCTION set_current_user_id(user_id UUID)
RETURNS VOID AS $$
BEGIN    PERFORM set_config('app.current_user_id', user_id::TEXT, false);
END;
$$ LANGUAGE plpgsql;
-- Function to set current organization context
CREATE OR REPLACE FUNCTION set_current_organization_id(org_id UUID)
RETURNS VOID AS $$
BEGIN    PERFORM set_config('app.current_organization_id', org_id::TEXT, false);
END;
$$ LANGUAGE plpgsql;
-- Function to get current user ID
CREATE OR REPLACE FUNCTION current_user_id()
RETURNS UUID AS $$
BEGIN    RETURN nullif(current_setting('app.current_user_id', true), '')::UUID;
END;
$$ LANGUAGE plpgsql;
-- Function to get current organization ID
CREATE OR REPLACE FUNCTION current_organization_id()
RETURNS UUID AS $$
BEGIN    RETURN nullif(current_setting('app.current_organization_id', true), '')::UUID;
END;
$$ LANGUAGE plpgsql;
```

Your application will use these functions to set the proper context when a user connects to the database.

### Creating RLS Policies for Tenant Isolation

Now, let’s create RLS policies that enforce tenant isolation:

```sql
-- Policy for projects: users can only see projects from their organization
CREATE POLICY tenant_isolation_policy ON projects
    FOR ALL    USING (organization_id = current_organization_id());
-- Policy for tasks: users can only see tasks from projects in their organization
CREATE POLICY tenant_isolation_policy ON tasks
    FOR ALL    USING (project_id IN (
        SELECT id FROM projects
        WHERE organization_id = current_organization_id()
    ));
-- Policy for org_members: users can only see members of their organization
CREATE POLICY tenant_isolation_policy ON org_members
    FOR ALL    USING (organization_id = current_organization_id());
```

With these policies in place, users will only see data from their own organization, regardless of their database role or privileges.

## Implementing Role-Based Permissions Within Tenants

Now that we have tenant isolation, let’s implement role-based permissions within each tenant.

### Checking User Roles

First, we need a function to check a user’s role within their current organization:

```sql
-- Function to get user's role in the current organization
CREATE OR REPLACE FUNCTION current_user_role()
RETURNS TEXT AS $$
DECLARE    user_role TEXT;
BEGIN    SELECT role INTO user_role
    FROM org_members
    WHERE organization_id = current_organization_id()
    AND user_id = current_user_id();
    RETURN user_role;
END;
$$ LANGUAGE plpgsql;
-- Function to check if user has a specific role or higher
CREATE OR REPLACE FUNCTION has_role(required_role TEXT)
RETURNS BOOLEAN AS $$
DECLARE    user_role TEXT;
BEGIN    user_role := current_user_role();
    RETURN CASE
        WHEN user_role = 'admin' THEN true
        WHEN user_role = 'member' AND required_role IN ('member', 'viewer') THEN true
        WHEN user_role = 'viewer' AND required_role = 'viewer' THEN true
        ELSE false
    END;
END;
$$ LANGUAGE plpgsql;
```

### Role-Based Policies

Now, let’s refine our RLS policies to incorporate role-based permissions:

```sql
-- Projects: viewing vs. modifying based on role
DROP POLICY IF EXISTS tenant_isolation_policy ON projects;
-- Everyone in the organization can view projects
CREATE POLICY projects_view_policy ON projects
    FOR SELECT    USING (organization_id = current_organization_id());
-- Only admins and members can insert projects
CREATE POLICY projects_insert_policy ON projects
    FOR INSERT    WITH CHECK (
        organization_id = current_organization_id()
        AND has_role('member')
    );
-- Only admins and members can update projects
CREATE POLICY projects_update_policy ON projects
    FOR UPDATE    USING (
        organization_id = current_organization_id()
        AND has_role('member')
    );
-- Only admins can delete projects
CREATE POLICY projects_delete_policy ON projects
    FOR DELETE    USING (
        organization_id = current_organization_id()
        AND has_role('admin')
    );
```

## Managing Organization Membership and Roles

Let’s create stored procedures to manage organization membership and roles:

```sql
-- Add a user to an organization with a specific role
CREATE OR REPLACE PROCEDURE add_user_to_organization(
    p_user_id UUID,
    p_organization_id UUID,
    p_role TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN    -- Check if the current user is an admin in this organization    IF NOT has_role('admin') THEN        RAISE EXCEPTION 'Permission denied: Admin role required';
    END IF;
    -- Add or update the membership    INSERT INTO org_members (organization_id, user_id, role)
    VALUES (p_organization_id, p_user_id, p_role)
    ON CONFLICT (organization_id, user_id)
    DO UPDATE SET role = p_role;
END;
$$;
-- Remove a user from an organization
CREATE OR REPLACE PROCEDURE remove_user_from_organization(
    p_user_id UUID,
    p_organization_id UUID
)
LANGUAGE plpgsql
AS $$
BEGIN    -- Check if the current user is an admin in this organization    IF NOT has_role('admin') THEN        RAISE EXCEPTION 'Permission denied: Admin role required';
    END IF;
    -- Remove the user    DELETE FROM org_members
    WHERE user_id = p_user_id AND organization_id = p_organization_id;
END;
$$;
```

## Integrating with Your Application

### Setting Context in a Node.js Application

Here’s how to integrate this in a Node.js application using [node-postgres](https://node-postgres.com/):

```javascript
const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

// Middleware to set database security context
async function setDbContext(req, res, next) {
  // Get user ID and organization ID from session or JWT
  const userId = req.user.id;
  const organizationId = req.params.organizationId || req.user.defaultOrganizationId;

// Store a client connection for this request
  req.dbClient = await pool.connect();
  try {
    // Set the security context
    await req.dbClient.query('SELECT set_current_user_id($1)', [userId]);
    await req.dbClient.query('SELECT set_current_organization_id($1)', [organizationId]);
    next();
  } catch (error) {
    req.dbClient.release();
    next(error);
  }

// Release the client when the response is sent
  res.on('finish', () => {
    if (req.dbClient) {
      req.dbClient.release();
    }
  });
}

// Example route that uses the context middleware
app.get('/api/organizations/:organizationId/projects',
  authenticate, // Your auth middleware
  setDbContext,
  async (req, res) => {
    try {
      // Thanks to RLS, this query will only return projects from the current organization
      const result = await req.dbClient.query('SELECT * FROM projects ORDER BY created_at DESC');
      res.json(result.rows);
    } catch (error) {
      console.error('Error fetching projects:', error);
      res.status(500).json({ error: 'Internal server error' });
    }
});
```

## Testing Your Permission System

Before going to production, thoroughly test your permission system.

### Creating Test Data

```bash
-- Create test organizations
INSERT INTO organizations (id, name) VALUES
    ('11111111-1111-1111-1111-111111111111', 'Acme Corp'),
    ('22222222-2222-2222-2222-222222222222', 'Globex Inc.');
-- Create test users
INSERT INTO users (id, email, full_name) VALUES    
    ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'alice@example.com', 'Alice Adams'),
    ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'bob@example.com', 'Bob Brown'),
    ('cccccccc-cccc-cccc-cccc-cccccccccccc', 'carol@example.com', 'Carol Chen');
-- Add users to organizations with roles
INSERT INTO org_members (organization_id, user_id, role) VALUES    
    ('11111111-1111-1111-1111-111111111111', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'admin'),
    ('11111111-1111-1111-1111-111111111111', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'member'),
    ('22222222-2222-2222-2222-222222222222', 'cccccccc-cccc-cccc-cccc-cccccccccccc', 'admin');
-- Add test projects
INSERT INTO projects (id, organization_id, name) VALUES    
    ('abcd1234-abcd-abcd-abcd-abcd12345678', '11111111-1111-1111-1111-111111111111', 'Website Redesign'),
    ('efgh5678-efgh-efgh-efgh-efgh87654321', '22222222-2222-2222-2222-222222222222', 'Mobile App Launch');
```

### Testing Tenant Isolation

```sql
-- Test as Alice (admin in Acme Corp)
SELECT set_current_user_id('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa');
SELECT set_current_organization_id('11111111-1111-1111-1111-111111111111');
SELECT * FROM projects; -- Should only see Acme Corp projects
-- Test as Carol (admin in Globex Inc)
SELECT set_current_user_id('cccccccc-cccc-cccc-cccc-cccccccccccc');
SELECT set_current_organization_id('22222222-2222-2222-2222-222222222222');
SELECT * FROM projects; -- Should only see Globex Inc projects
```

### Testing Role-Based Permissions

```sql
-- Test as Bob (member in Acme Corp)
SELECT set_current_user_id('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb');
SELECT set_current_organization_id('11111111-1111-1111-1111-111111111111');
-- Should succeed (members can view projects)
SELECT * FROM projects;
-- Should succeed (members can insert projects)
INSERT INTO projects (organization_id, name)
VALUES ('11111111-1111-1111-1111-111111111111', 'Bob''s Project');
-- Should fail (only admins can delete projects)
DELETE FROM projects WHERE name = 'Bob''s Project';
```

## Best Practices and Pitfalls

### Security Best Practices

1. **Apply the principle of least privilege**: Grant only the minimum necessary permissions
2. **Set secure defaults**: Start with restrictive policies and add permissions as needed
3. **Validate inputs**: Don’t trust client-provided organization IDs
4. **Implement connection pooling carefully**: Ensure connection pools don’t mix security contexts
5. **Create audit logs**: Track permission changes and access attempts

### Common Pitfalls

1. **Forgetting to enable RLS**: Tables without RLS enabled don’t enforce policies
2. **Missing tenant ID columns**: Ensure all tables have proper tenant ID relationships
3. **Leaking session context**: Reset context variables between requests
4. **Query performance degradation**: Monitor and optimize RLS policy performance
5. **Escalation through functions**: Be careful with `SECURITY DEFINER` functions

## Conclusion

PostgreSQL offers powerful tools for implementing fine-grained permissions in multi-tenant applications. By combining roles, grants, and Row-Level Security with custom functions and policies, you can create a comprehensive permission system that ensures proper data isolation and access control.