Postgres RLS Implementation Guide - Best Practices, and Common Pitfalls

As web applications grow more sophisticated, so do their security requirements. Users expect their data to be secure and isolated, especially in multi-tenant environments.

Postgres RLS (Row Level Security) offers powerful capabilities for implementing data access controls directly at the database level, but is this PostgreSQL feature enough on its own?

In this article, I’ll walk you through Postgres RLS features, show you how to implement basic security policies, explain why PostgreSQL’s Row Level Security alone isn’t sufficient as a production-ready authorization solution, and demonstrate how combining Postgres RLS with an application-level authorization service like Permit.io can create a truly comprehensive RLS solution.

What is Postgres Row Level Security (RLS)?

Row-level security is a feature introduced in PostgreSQL 9.5 that allows database administrators to define security policies restricting which rows users can view or modify. These policies are applied automatically whenever the table is accessed, regardless of how the query was initiated.
Think of RLS like a security checkpoint at each table in your database. This checkpoint checks every row against a set of rules before letting your query see or modify the data. This happens at the database level, not in your application code.

Why RLS Matters
Before RLS, if you wanted to restrict access to specific rows in a database table, you had two main options:

1. Write custom filtering logic in your application code (e.g., adding
2. WHERE tenant_id = current_tenant()
3. to every query)Create separate views for different access patterns and grant permissions on those views

Both approaches had significant drawbacks: application filtering can be easily forgotten in some queries, creating security holes, and views can become unwieldy as access patterns grow more complex.

RLS solves these problems by embedding security policies directly in the table definition, guaranteeing they’re applied consistently regardless of how the data is accessed.

The Limitations of RLS

PostgreSQL’s RLS has several limitations when used alone:

Coarse-Grained Control
RLS works well for simple row filtering but struggles with complex permission models based on user attributes, resource properties, or environmental factors. It operates at the row level, not the column level, and context-aware decisions like time-based access are difficult to implement.

Security Vulnerabilities
RLS can create security gaps if functions, like current_organization_id() , rely on user-supplied input, making SQL injection possible. If credentials are leaked, direct database access tools might circumvent RLS.

Practical Implementation Challenges
Complex RLS policies can significantly impact query performance. Debugging becomes difficult when queries don’t return expected results due to RLS policies. Some business rules don’t translate well into SQL expressions.

No Centralized Authorization
Most applications need unified authorization across multiple systems. With RLS alone, authorization rules are split between application and database with no automatic audit trail, which is especially problematic when applications use multiple data stores.

Example Implementation: ProjectHub - A Project Management App

Let’s walk through a practical example of implementing RLS in a multi-tenant application. This will help us understand how RLS works, its limitations, and how we can extend it further.

This article's example is a SaaS product called ProjectHub. This project management application provides teams with tools to organize and track their work.

In our app, we want to create a fine-grained permission system so that a user can edit a project if they are a project manager in the same organization as the project and the project is in ‘draft’ status.

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 environment installed on your local machine.
• PostgreSQL 9.5 or higher: RLS was introduced in PostgreSQL 9.5, but I recommend using PostgreSQL 12+ for the best performance and feature set.
• Docker Environment: Our app relies on Docker to run the PDP container locally. Make sure you have Docker installed and configured.
• Basic SQL knowledge: Understanding of SQL queries, functions, and database concepts.
• A PostgreSQL Client(pg)
• A Permit.io account

Setting Up Basic RLS

Let’s walk through a practical example of implementing RLS in our multi-tenant ProjectHub application. We’ll focus on the projects table to demonstrate the key concepts.

Let’s first run Postgres with Docker:

Pull the PostgreSQL image:

docker pull postgres

Run a PostgreSQL container:

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

Connect to your PostgreSQL container:

docker exec -it postgres-db psql -U postgres

For more information on Docker commands, check the official Docker documentation.

Step 1: Create Tables with Tenant Identifiers
Now that we have our PostgreSQL container running, we’ll create our database tables. Copy each SQL command below and paste it into your PostgreSQL CLI. Press Enter after each command to execute it.

CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL);
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,
    status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'review', 'published')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
-- Create a test organizationINSERT INTO organizations (name) VALUES ('Acme Corp');
-- Insert test projectsINSERT INTO projects (organization_id, name, description, status)
SELECT
    (SELECT id FROM organizations WHERE name = 'Acme Corp'),
    name,
    description,
    status
FROM (
    VALUES
        ('Marketing Campaign', 'Q4 marketing initiatives', 'draft'),
        ('Website Redesign', 'Company website overhaul', 'review'),
        ('Mobile App', 'Customer mobile application', 'published'),
        ('Data Migration', 'Legacy system migration', 'draft')
) AS data(name, description, status);

Step 2: Create Application Roles

    -- Create a role for application users    CREATE ROLE app_user;
    -- Create a specific user for testing    CREATE ROLE alice LOGIN PASSWORD 'secure_password';
    GRANT app_user TO alice;

Step 3: Enable RLS on Tables

    -- Enable RLS on the projects table    ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
    -- By default, tables with RLS enabled block all access    -- We need to create policies to allow specific access

Step 4: Create an RLS Policy
Now let’s create a policy that only allows users to see projects from their organization:

    -- First, we need a way to know which organization a user belongs to    -- We'll use a database function that retrieves this information    CREATE OR REPLACE FUNCTION current_organization_id() RETURNS UUID AS $$
    BEGIN        -- In a real application, you would look this up based on the current user        -- For our example, we'll just return the Acme Corp ID (implement your lookup logic here)        RETURN (SELECT id FROM organizations WHERE name = 'Acme Corp');
    END;
    $$ LANGUAGE plpgsql SECURITY DEFINER;
    -- Now create the policy    CREATE POLICY projects_isolation_policy ON projects
        FOR ALL        USING (organization_id = current_organization_id());

Step 5: Test the Policy

    -- Insert some test data    INSERT INTO projects (organization_id, name)
    VALUES
        (current_organization_id(), 'Project Alpha'),
        (current_organization_id(), 'Project Beta');
    -- Test as a superuser - will see all rows    SELECT * FROM projects;
    -- Test as alice    SET ROLE alice;
    SELECT * FROM projects;

When we switch roles to Alice, we see an error when we try to access or list the projects table:

This simple example demonstrates the basics of RLS. Users can only see rows that match their organization context, no matter how they query the table.

Enhancing RLS: Building a Complete Authorization Layer

By combining RLS with Permit.io, we can create a more robust authorization system without relying solely on it. Let’s examine how this integration works and why it’s beneficial.

Permit.io is a full-stack authorization-as-a-service platform that allows you to build and manage permissions for your application with a friendly SDK and API, providing flexible, attribute-based access control (ABAC) for applications. Its key features include:

• Real-time policy enforcement through local Policy Decision Points (PDPs)
• Fine-grained access control based on user attributes, resource properties, and context
• Centralized policy management with audit logging
• SDK support for multiple programming languages
• Built-in support for RBAC, ABAC, and ReBAC models

Implementation Plan

Before we start writing code, let’s plan our authorization implementation:

Resources:

• Project: The main resource users will interact with

Roles:

• Project Manager: Can view and edit draft projects in their organization
• Viewer: Can only view projects in their organization

Attributes:

• User Attributes:
  • organization_id: Which organization they belong to.
  • Department: Which department do they belong to?
• Resource Attributes:
  • organization_id: Which organization does the project belong to?
  • status: Current project status (draft, published, archived)

Policies in Plain English:

• Users can only view projects in their organization
• Project managers can edit projects only if they’re in draft status

Architecture: Combining Permit.io with PostgreSQL RLS

Let’s walk through how policy synchronization works in our project management application:

Developer/PM:

• A Product Manager configures a policy in the Permit.io dashboard
• Example: “Project Managers can edit projects in draft status within their organization.”

The Permit.io Policy:

{  "resource": "project",  "action": "edit",  "role": "project_manager",  "attributes": {    "status": "draft",    "organization_id": "${user.organization_id}"  }}

The ProjectHub Application:

• Built-in policy synchronization service
• Monitors Permit.io for policy changes
• Converts ABAC/RBAC rules into PostgreSQL RLS format
• Generated SQL for RLS policy:

CREATE POLICY project_edit_policy ON projects
  FOR UPDATE  USING (
    status = 'draft'
    AND organization_id = current_organization_id()
    AND EXISTS (
      SELECT 1 FROM user_roles
      WHERE role = 'project_manager'
      AND user_id = current_user
    )
  );

Policy Change Events
Two ways to handle updates:

• Passive Check: Periodically poll Permit.io for policy changes
• Active Monitoring: Listen to webhooks from Permit.io when policies change

This flow ensures that any permission changes made in Permit.io are automatically reflected in the database’s row-level security, maintaining a single source of truth for authorization rules.

Implementing Our Policies

Let’s start the setup.

Before we enforce our permissions, we need to create our Policies with Permit.io.

If you haven’t already, you can create a free Permit.io account to follow along.

Once you have your account set up, let’s configure our policies:

Step 1: Add User Attributes
In the Permit.io dashboard, click on the "Directory" tab:

Click on settings and then click on the "User Attributes" tab.

Add all the user attributes needed for the example. Here is what our user attributes look like:

Step 2: Define Resources and Actions
In Permit.io, we’ll define our resource model and actions. To add a resource, click “Create a Resource” in the “Resources” tab.

Step 3: Create Roles
We need to create roles that have the privileges in our application. Click on the “Roles” tab and click “Add Role”.

Step 4: Create a Resource Set
This is where it gets interesting, we can setup fine-grained ABAC (Attribute-Based Access Control) permission for our application by creating user sets and resource sets, but first, we need to consider the condition “a user can edit a project if they are a project manager, and the project is in ‘draft’ status.”

For this, we need to create a resource set called “projects in draft”.

First, go to the “ABAC Rules” tab in the “Policy” tab, and click on “Create New” on the “ABAC Resource Sets” card.

We are done setting up our policies. Let’s dive into some code.

Enforcing Our Permissions

Let’s implement the application and connect it to both Permit.io and PostgreSQL. We’ll jump into our editor and write some code.

Step 1: Project Setup
Initialize a new project and install dependencies:

pnpm init
pnpm add express@5.1.0 permitio@2.7.2-rc pg@8.11.3 -E && pnpm add nodemon@3.0.3 -D -E

This command will install all the dependencies we need to run our example:

• Express: Web application framework
• permitio: Permit.io client library for authorization
• pg: PostgreSQL client for Node.js
• nodemon: A Development tool for auto-restarting the server

Next, we set environment variables:

export DATABASE_URL="postgresql://postgres:pgsecretpassword@localhost:5432/postgres"export PERMIT_API_KEY=<your-permit-key>

This command will set up our database connection string and our Permit API key from the Projects dashboard:

Step 2: Deploy Local PDP
After exporting our env variables, we need to start a local Permit Policy Decision Point (PDP):

    docker pull permitio/pdp-v2:latest
    docker run -it \\         -p 7766:7000 \\         --env PDP_API_KEY=<YOUR_API_KEY> \\         --env PDP_DEBUG=True \\         permitio/pdp-v2:latest

The local PDP provides significant advantages:

• Zero-latency policy decisions without network calls
• Local testing without relying on cloud services
• Reduced API calls to Permit.io’s cloud services

Step 3: Implement Application Code
Create an app.js file with our Express application:

Imports and Basic Setup

import express from "express";import { Permit } from "permitio";import pkg from "pg";const { Pool } = pkg;// Express middlewareconst app = express();app.use(express.json());

This code imports the necessary libraries and creates an Express application. It imports Express for web server functionality, the Permit class from permitio, and the PostgreSQL Pool class for database connection management. It then initializes Express and adds middleware to parse JSON request bodies.

Database and Permit.io Configuration

// Database setupconst pool = new Pool({
  connectionString: process.env.DATABASE_URL,});// Permit.io setupconst permit = new Permit({
  token: process.env.PERMIT_API_KEY,  pdp: "<http://localhost:7766>",});

This section initializes connections to both PostgreSQL and Permit.io. It creates a connection pool for the database using the environment variable and configures the Permit client to use the local Policy Decision Point running on port 7766.

Middleware Functions

    // Authentication middleware    const authenticate = async (req, res, next) => {
      try {
        req.user = { id: "sunghin@gmail.com" }; // Replace with actual user data        next();      } catch (error) {
        res.status(401).json({ error: "Invalid authentication" });      }
    };    // Error handling middleware    const errorHandler = (err, req, res, next) => {
      console.error(err.stack);      res.status(500).json({ error: "Internal server error" });    };

This code defines two middleware functions. The authenticate middleware is a simplified placeholder that assigns a hardcoded user to the request. In a real application, this would verify tokens or session data. The errorHandler middleware provides centralized error handling for the application, logging errors, and returning a standard 500 response.

List Projects Endpoint

    app.get("/projects", authenticate, async (req, res, next) => {
      try {
        const user = req.user;        // Check if user has permission to list projects        const permitted = await permit.check(user.id, "view", "project");        console.log(permitted);        if (!permitted) {
          return res.status(403).json({ error: "Permission denied" });        }
        // Continue with database query - RLS will still apply        
        const { rows } = await pool.query("SELECT * FROM projects");        res.json(rows);      } catch (error) {
        next(error);      }
    });

The code above:

• Uses the authenticate middleware to ensure a user is present (simplified for demonstration)
• Permission Check: Calls permit.check() with:
  • User ID: user.id (the user’s email in this example)
  • Action: "view" (the permission being checked)
  • Resource: "project" (the resource type being accessed)Checks the permission result and returns 403 if denied
  • If permitted, queries the database for all projects

Even after Permit.io authorizes the user, PostgreSQL’s RLS will filter the results to only show projects the user should see.

Get Specific Project Endpoint

    app.get("/projects/:id", authenticate, async (req, res, next) => {
      try {
        const user = req.user;        const projectId = req.params.id;        if (!projectId) {
          return res.status(400).json({ error: "Missing project ID" });        }
        // First fetch the project metadata        const {
          rows: [project],        } = await pool.query(
          "SELECT id, name, organization_id FROM projects WHERE id = $1",          [projectId]
        );        if (!project) {
          return res.status(404).json({ error: "Project not found" });        }
        // Check if user has permission to edit this specific project        const permitted = await permit.check(user.id, "edit", {
          type: "project",          attributes: {
            status: "draft",          },        });        if (!permitted) {
          return res.status(403).json({ error: "Permission denied" });        }
        // Fetch complete project details        const {
          rows: [fullProject],        } = await pool.query("SELECT * FROM projects WHERE id = $1", [projectId]);        res.json(fullProject);      } catch (error) {
        next(error);      }
    });

The code above:

• Extracts the project ID from the request parameters
• Two-Phase Query Pattern:
  • First, it fetches basic project metadata to check if it exists
  • Then, it performs the permission check before fetching full details.
• Resource Attributes: The permission check includes attributes:
  • status: "draft" - This corresponds to the ABAC rule we created in Permit.io that only allows editing of projects in “draft” status.
• If permitted, queries for the full project details
• This endpoint demonstrates attribute-based access control (ABAC) by checking not just the action and resource, but also considering the project’s status.

Syncing Policies into the PostgreSQL RLS

So far, we’ve been maintaining our authorization rules in two places: Permit.io for application-level authorization and PostgreSQL RLS for database-level security. This duplication can lead to maintenance overhead and potential inconsistencies. Let’s improve our architecture by making Permit.io our single source of truth and automatically syncing its policies to PostgreSQL RLS.

The synchronization process works as follows:

• Policies are configured in Permit.io’s dashboard
• Our ProjectHub application monitors for policy changes
• When changes occur, the application converts Permit.io policies to PostgreSQL RLS format
• The converted policies are automatically applied to PostgreSQL

Implementing the Policy Sync

Let’s create a policy synchronization service that translates Permit.io policies to PostgreSQL RLS:

import { Permit } from "permitio";
import pg from "pg";

export class PolicySyncService {
  constructor() {
    this.permit = new Permit({
      token: process.env.PERMIT_API_KEY,
    });

    this.pool = new pg.Pool({
      connectionString: process.env.DATABASE_URL,
    });
  }

  // Convert Permit.io policy to RLS format
  convertToRLS(permitPolicy) {
    // conversion for project access policy
    if (permitPolicy.resource.name === "project") {
      return {
        policyName: `${permitPolicy.resource.name}_access_policy`,
        using: `organization_id = current_organization_id()
                AND status = '${permitPolicy.conditions.allOf[0].allOf[0]["resource.status"].equals}'`,
      };
    }
    return null;
  }

  // Apply RLS policy to PostgreSQL
  async applyRLSPolicy(rlsPolicy) {
    const query = `
      DROP POLICY IF EXISTS ${rlsPolicy.policyName} ON projects;
      CREATE POLICY ${rlsPolicy.policyName} ON projects
        FOR ALL
        USING (${rlsPolicy.using});
    `;

    await this.pool.query(query);
  }

  // Main sync function
  async syncPolicies() {
    try {
      // Fetch policies from Permit.io
      const policies = await this.permit.api.conditionSets.list();

      // Convert and apply each policy
      for (const policy of policies) {
        const rlsPolicy = this.convertToRLS(policy);
        if (rlsPolicy) {
          await this.applyRLSPolicy(rlsPolicy);
        }
      }
      console.log("Policies synchronized successfully");
    } catch (error) {
      console.error("Policy sync failed:", error);
    }
  }
}

With the sync service we created, let’s say we want to update a policy in Permit.io to allow project managers to edit projects if they are in “review” status.

We head over to our dashboard, and in the ABAC rules resource sets tab, we change the status to review:

Once we save and run the sync service, the following PostgreSQL RLS policy is created:

-- Resulting RLS policy
CREATE POLICY project_edit_policy ON projects
  FOR ALL
  USING (
    organization_id = current_organization_id()
    AND status = 'review'
  );

Running the Sync Service

You can run the sync service by periodically syncing policies on a schedule using a cron job:

import cron from 'node-cron';import { PolicySyncService } from './policy-sync-service.js';

// Create singleton instance

const syncService = new PolicySyncService();

// Schedule cron job to run every hour
// '0 * * * *' = At minute 0 of every hour

cron.schedule('0 * * * *', async () => {
  try {
    console.log(`Starting policy sync at ${new Date().toISOString()}`);    await syncService.syncPolicies();    console.log('Policy sync completed successfully');  } catch (error) {
    console.error('Policy sync failed:', error);
    // You might want to add error reporting here (e.g., Sentry, logging service)  
    }
});

You can run this as a separate process using PM2 or Docker

The Security Benefits of Adding Permit.io with RLS

This dual-layer approach provides several key security advantages:

• Fine-grained, attribute-based policies: While RLS can only filter rows based on simple conditions, Permit.io’s ABAC system allows complex rules based on user roles, attributes, resource properties, and contextual factors like time or location.
• Centralized policy management: Permit.io provides a unified dashboard to manage all authorization policies across your entire system, not just PostgreSQL, but any data store or service your application uses.
• Real-time policy updates: Change permissions instantly through Permit.io’s UI or API without modifying database schemas or deploying code changes, unlike RLS, which requires database administration privileges.
• Comprehensive audit logs: Permit.io automatically captures detailed audit trails of all access decisions, something PostgreSQL RLS doesn’t provide natively.

Conclusion

PostgreSQL’s Row Level Security provides a powerful foundation for data security, but it’s most effective when combined with application-level authorization. By implementing in-depth defense with Permit.io and RLS working together, you can:

1. Express complex, attribute-based authorization policies
2. Centrally manage permissions across your entire application
3. Protect against both application-level and data-level attack vectors
4. Ensure consistent enforcement regardless of how data is accessed

Further Reading

• Authorization Policy Showdown: RBAC vs. ABAC vs. ReBAC
• Configure your first RBAC policy
• Set up Attribute-Based Access Control
• Data Filtering