Production-Ready PostgREST: Complete Guide to PostgreSQL Role and Permission Configuration

Introduction

PostgREST has revolutionized API development by automatically generating RESTful APIs from PostgreSQL database schemas. However, this power comes with significant responsibility: improper role and permission configuration can lead to security vulnerabilities, data leaks, and production disasters. This comprehensive guide walks you through everything you need to know to deploy PostgREST securely in production environments.

Whether you're building a new API or securing an existing deployment, understanding PostgreSQL's role-based access control (RBAC) system in the context of PostgREST is essential for protecting your data while maintaining the developer productivity that makes PostgREST attractive.

Understanding PostgREST Security Model

How PostgREST Works

PostgREST acts as a thin layer between HTTP clients and your PostgreSQL database. When a request arrives:

Authentication: PostgREST validates the JWT token (if provided)
Role Selection: Determines which database role to use for the request
Query Translation: Converts the HTTP request into a SQL query
Execution: Runs the query under the selected database role
Response: Returns results as JSON

The critical security insight: PostgREST enforces permissions through PostgreSQL's native role system. This means your database roles and permissions are your API security.

The Security Chain

HTTP Request → JWT Token → PostgREST Role → PostgreSQL Permissions → Data Access

Each link in this chain must be properly configured. A weakness at any level compromises the entire system.

PostgreSQL Role Fundamentals

Role Types in PostgREST Context

Anonymous Role: Used for unauthenticated requests. Typically has minimal permissions.

Authenticated Role: Base role for authenticated users. Often grants basic permissions.

User-Specific Roles: Individual roles extracted from JWT claims for fine-grained access control.

Administrative Roles: High-privilege roles for management tasks, never exposed directly through PostgREST.

Creating Roles

-- Anonymous role (unauthenticated users)
CREATE ROLE anon NOLOGIN;

-- Authenticated role (base for logged-in users)
CREATE ROLE authenticator NOLOGIN;

-- Application user role
CREATE ROLE app_user NOLOGIN;

-- Grant authenticator to the PostgREST service account
GRANT authenticator TO postgrest;

Best Practice: Use NOLOGIN for roles that are only used through PostgREST. This prevents direct database connections with these roles.

Permission Architecture

Schema-Level Permissions

Before users can access tables, they need schema permissions:

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO anon;
GRANT USAGE ON SCHEMA public TO authenticator;
GRANT USAGE ON SCHEMA public TO app_user;

-- For custom schemas
GRANT USAGE ON SCHEMA api TO anon;
GRANT USAGE ON SCHEMA api TO app_user;

Table-Level Permissions

Table permissions control which operations users can perform:

-- Read-only access for anonymous users
GRANT SELECT ON TABLE public.products TO anon;

-- Full CRUD for authenticated users
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.orders TO app_user;

-- Specific column access
GRANT SELECT (id, name, price) ON TABLE public.products TO anon;
GRANT SELECT (id, name, price, cost, supplier_id) ON TABLE public.products TO app_user;

Security Note: Column-level permissions prevent unauthorized access to sensitive fields like costs, supplier information, or internal IDs.

Sequence Permissions

For tables with serial/auto-increment columns:

GRANT USAGE, SELECT ON SEQUENCE public.orders_id_seq TO app_user;

Function and Procedure Permissions

PostgREST can expose PostgreSQL functions:

-- Grant function execution
GRANT EXECUTE ON FUNCTION public.calculate_total(integer) TO app_user;

-- Revoke dangerous functions from public
REVOKE EXECUTE ON FUNCTION pg_sleep(double precision) FROM public;

Row-Level Security (RLS)

Why RLS Matters

Row-Level Security provides fine-grained access control at the row level. Even if a user has SELECT permission on a table, RLS can restrict which rows they can see or modify.

Critical for multi-tenant applications: RLS ensures customers only see their own data.

Enabling RLS

-- Enable RLS on a table
ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;

-- Create a policy for SELECT operations
CREATE POLICY select_own_orders ON public.orders
    FOR SELECT
    TO app_user
    USING (user_id = current_setting('request.jwt.claims', true)::json->>'sub');

-- Create a policy for INSERT operations
CREATE POLICY insert_own_orders ON public.orders
    FOR INSERT
    TO app_user
    WITH CHECK (user_id = current_setting('request.jwt.claims', true)::json->>'sub');

-- Create a policy for UPDATE operations
CREATE POLICY update_own_orders ON public.orders
    FOR UPDATE
    TO app_user
    USING (user_id = current_setting('request.jwt.claims', true)::json->>'sub')
    WITH CHECK (user_id = current_setting('request.jwt.claims', true)::json->>'sub');

-- Create a policy for DELETE operations
CREATE POLICY delete_own_orders ON public.orders
    FOR DELETE
    TO app_user
    USING (user_id = current_setting('request.jwt.claims', true)::json->>'sub');

RLS Policy Patterns

Tenant Isolation:

CREATE POLICY tenant_isolation ON public.data
    FOR ALL
    TO app_user
    USING (tenant_id = current_setting('request.jwt.claims', true)::json->>'tenant_id');

Role-Based Access:

CREATE POLICY managers_can_edit ON public.documents
    FOR UPDATE
    TO app_user
    USING (
        current_setting('request.jwt.claims', true)::json->>'role' = 'manager'
        OR owner_id = current_setting('request.jwt.claims', true)::json->>'sub'
    );

Time-Based Access:

CREATE POLICY business_hours_only ON public.sensitive_data
    FOR SELECT
    TO app_user
    USING (
        EXTRACT(HOUR FROM NOW()) BETWEEN 9 AND 17
        AND current_setting('request.jwt.claims', true)::json->>'department' = 'operations'
    );

RLS Best Practices

Always enable RLS for multi-tenant data: Don't rely on application-level checks
Test policies thoroughly: RLS bugs can expose data to wrong users
Use FORCE ROW LEVEL SECURITY: For table owners, RLS is bypassed by default
Monitor policy performance: Complex RLS policies can impact query performance

-- Force RLS even for table owners
ALTER TABLE public.orders FORCE ROW LEVEL SECURITY;

JWT Token Configuration

JWT Structure for PostgREST

PostgREST uses JWT tokens to determine user roles and claims:

{
  "sub": "1234567890",
  "role": "app_user",
  "tenant_id": "tenant_abc",
  "permissions": ["read", "write"],
  "exp": 1234567890
}

Configuring PostgREST JWT Settings

# postgrest.conf
db-anon-role = "anon"
db-jwt-secret = "your-secret-key-min-32-chars-long"
db-jwt-aud = "postgrest"
db-schemas = "public,api"
db-extra-search-path = "public, extensions"

Role Extraction from JWT

PostgREST extracts the database role from the JWT role claim:

{
  "sub": "user123",
  "role": "app_user",  -- This determines the PostgreSQL role
  "tenant_id": "acme"
}

Security Critical: Validate the role claim against an allowlist to prevent privilege escalation.

-- In your database, create a function to validate roles
CREATE OR REPLACE FUNCTION validate_role()
RETURNS text AS $$
DECLARE
    jwt_role text;
    allowed_roles text[] := ARRAY['anon', 'app_user', 'manager'];
BEGIN
    jwt_role := current_setting('request.jwt.claims', true)::json->>'role';
    
    IF NOT jwt_role = ANY(allowed_roles) THEN
        RAISE EXCEPTION 'Invalid role: %', jwt_role;
    END IF;
    
    RETURN jwt_role;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Production Configuration Patterns

Pattern 1: Simple Two-Role Setup

Suitable for small applications with basic authentication:

-- Anonymous users: read-only public data
CREATE ROLE anon NOLOGIN;
GRANT USAGE ON SCHEMA public TO anon;
GRANT SELECT ON TABLE public.products, public.categories TO anon;

-- Authenticated users: full access to their own data
CREATE ROLE app_user NOLOGIN;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Enable RLS on user-specific tables
ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

Pattern 2: Multi-Tenant SaaS Application

-- Base roles
CREATE ROLE anon NOLOGIN;
CREATE ROLE authenticated NOLOGIN;
CREATE ROLE tenant_admin NOLOGIN;

-- Anonymous: landing page and public info only
GRANT USAGE ON SCHEMA public TO anon;
GRANT SELECT ON TABLE public.tenant_info TO anon;

-- Authenticated: access to own tenant's data
GRANT USAGE ON SCHEMA public TO authenticated;
GRANT SELECT, INSERT, UPDATE ON TABLE public.projects TO authenticated;
GRANT SELECT, INSERT ON TABLE public.tasks TO authenticated;

-- Tenant admin: additional management capabilities
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.team_members TO tenant_admin;
GRANT SELECT, INSERT, UPDATE ON TABLE public.billing TO tenant_admin;

-- RLS Policies
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_projects ON public.projects
    FOR ALL
    TO authenticated
    USING (tenant_id = current_setting('request.jwt.claims', true)::json->>'tenant_id');

CREATE POLICY admin_full_access ON public.projects
    FOR ALL
    TO tenant_admin
    USING (true);  -- Admins can access all rows within their tenant

Pattern 3: API Versioning with Schema Separation

-- Separate schemas for API versions
CREATE SCHEMA api_v1;
CREATE SCHEMA api_v2;

-- Different roles for different API versions
CREATE ROLE api_v1_user NOLOGIN;
CREATE ROLE api_v2_user NOLOGIN;

-- v1 users access v1 schema
GRANT USAGE ON SCHEMA api_v1 TO api_v1_user;
GRANT SELECT ON ALL TABLES IN SCHEMA api_v1 TO api_v1_user;

-- v2 users access v2 schema (with new features)
GRANT USAGE ON SCHEMA api_v2 TO api_v2_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA api_v2 TO api_v2_user;

-- PostgREST configuration per version
# postgrest-v1.conf
db-schemas = "api_v1"
db-anon-role = "anon"

# postgrest-v2.conf
db-schemas = "api_v2"
db-anon-role = "anon"

Security Hardening

Principle of Least Privilege

Always grant minimum necessary permissions:

-- BAD: Overly permissive
GRANT ALL ON ALL TABLES IN SCHEMA public TO app_user;

-- GOOD: Specific permissions
GRANT SELECT ON TABLE public.products TO app_user;
GRANT SELECT, INSERT, UPDATE ON TABLE public.orders TO app_user;
GRANT SELECT, DELETE ON TABLE public.sessions TO app_user;

Preventing Common Attacks

SQL Injection: PostgREST parameterizes queries, but validate input in RLS policies.

Privilege Escalation: Validate JWT role claims against allowlist.

Data Enumeration: Implement rate limiting and pagination limits.

Information Leakage: Carefully control which columns are accessible.

-- Prevent access to sensitive columns
CREATE VIEW public.users_safe AS
SELECT id, username, email, created_at
FROM public.users;

GRANT SELECT ON public.users_safe TO app_user;
REVOKE SELECT ON public.users FROM app_user;

Audit Logging

Track access to sensitive data:

-- Create audit log table
CREATE TABLE public.audit_log (
    id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    operation TEXT NOT NULL,
    user_id TEXT,
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    old_data JSONB,
    new_data JSONB
);

-- Function to log changes
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO public.audit_log (table_name, operation, user_id, old_data)
        VALUES (TG_TABLE_NAME, TG_OP, current_setting('request.jwt.claims', true)::json->>'sub', to_jsonb(OLD));
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO public.audit_log (table_name, operation, user_id, old_data, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, current_setting('request.jwt.clams', true)::json->>'sub', to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO public.audit_log (table_name, operation, user_id, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, current_setting('request.jwt.claims', true)::json->>'sub', to_jsonb(NEW));
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Apply to sensitive tables
CREATE TRIGGER audit_users_changes
    AFTER INSERT OR UPDATE OR DELETE ON public.users
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

Performance Considerations

RLS Performance Impact

RLS adds overhead to queries. Optimize policies:

-- BAD: Complex expression evaluated for every row
CREATE POLICY slow_policy ON public.large_table
    FOR SELECT TO app_user
    USING (
        EXISTS (
            SELECT 1 FROM user_permissions 
            WHERE user_id = current_setting('request.jwt.claims', true)::json->>'sub'
            AND resource_id = large_table.id
        )
    );

-- BETTER: Use indexed column comparison
CREATE POLICY fast_policy ON public.large_table
    FOR SELECT TO app_user
    USING (
        owner_id = (current_setting('request.jwt.claims', true)::json->>'sub')::integer
    );

-- Ensure owner_id is indexed
CREATE INDEX idx_large_table_owner ON public.large_table(owner_id);

Connection Pooling

PostgREST benefits from connection pooling:

# postgrest.conf
db-pool-acquisition-timeout = 10
db-pool-max-size = 20
db-pool-usage-lifetime = 10m

Query Planning

Use EXPLAIN ANALYZE to understand RLS impact:

EXPLAIN ANALYZE
SELECT * FROM public.orders
WHERE user_id = '123';

Monitoring and Maintenance

Permission Auditing

Regularly audit permissions:

-- List all table permissions
SELECT 
    grantee,
    table_name,
    privilege_type
FROM information_schema.role_table_grants
WHERE grantee NOT IN ('postgres', 'pg_monitor');

-- List all RLS policies
SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,
    with_check
FROM pg_policies;

Testing Permissions

Create test scripts to verify permission boundaries:

-- Test as anon role
SET ROLE anon;
SELECT * FROM public.products;  -- Should work
SELECT * FROM public.orders;    -- Should fail

-- Test as app_user
SET ROLE app_user;
SELECT * FROM public.orders WHERE user_id = 'test';  -- Should work
SELECT * FROM public.orders WHERE user_id = 'other'; -- Should return empty (RLS)

Rotation Procedures

JWT Secret Rotation:

Generate new secret
Update PostgREST configuration
Restart PostgREST
Old tokens will fail, requiring re-authentication

Role Permission Updates:

Create new role with updated permissions
Update JWT to use new role
Test thoroughly
Deprecate old role after migration

Troubleshooting Common Issues

Issue 1: "Permission denied for table"

Cause: Missing GRANT permissions

Solution:

GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT ON TABLE public.your_table TO app_user;

Issue 2: RLS policy not working

Cause: RLS not enabled or policy not applied to correct role

Solution:

-- Verify RLS is enabled
SELECT relname, relrowsecurity, relforcerowsecurity 
FROM pg_class 
WHERE relname = 'your_table';

-- Enable if needed
ALTER TABLE public.your_table ENABLE ROW LEVEL SECURITY;

-- Verify policy exists
SELECT * FROM pg_policies WHERE tablename = 'your_table';

Issue 3: JWT role not being applied

Cause: JWT configuration incorrect or role claim missing

Solution:

# Verify postgrest.conf
db-jwt-secret = "your-secret"
db-anon-role = "anon"

-- Check JWT claims in session
SELECT current_setting('request.jwt.claims', true);

Deployment Checklist

Before deploying PostgREST to production:

[ ] All tables have appropriate RLS policies
[ ] Anonymous role has minimal permissions
[ ] JWT secret is strong (32+ characters)
[ ] Role allowlist is implemented
[ ] Sensitive columns are protected via views
[ ] Audit logging is enabled for critical tables
[ ] Connection pooling is configured
[ ] Rate limiting is in place
[ ] Monitoring and alerting configured
[ ] Backup and recovery procedures tested
[ ] Permission audit script created
[ ] Documentation updated

Conclusion

Properly configuring PostgreSQL roles and permissions for PostgREST is not optional—it's fundamental to API security. The guidelines in this article provide a foundation for secure deployments, but remember:

Security is iterative: Regularly audit and update permissions
Test thoroughly: Permission bugs can expose sensitive data
Document everything: Future you (and your team) will thank you
Stay updated: PostgREST and PostgreSQL continue to evolve

PostgREST's elegance lies in leveraging PostgreSQL's mature security model. By understanding and properly configuring roles, permissions, and RLS policies, you can build APIs that are both powerful and secure.

The investment in proper permission architecture pays dividends in reduced security incidents, easier compliance audits, and peace of mind. Your future self—and your users—will appreciate the effort.

Additional Resources

Official Documentation

PostgREST Documentation: https://postgrest.org
PostgreSQL RLS Documentation: https://www.postgresql.org/docs/current/ddl-rowsecurity.html
PostgreSQL Security Best Practices: https://www.postgresql.org/docs/current/security.html

Tools

pgAudit: Extended auditing for PostgreSQL
pgBadger: PostgreSQL log analyzer
PostgREST Admin: Monitoring dashboard

Community

PostgREST GitHub Discussions
PostgreSQL Security Mailing List
r/PostgreSQL community

Secure your PostgREST deployment today, and build APIs you can trust tomorrow.

Introduction

Understanding PostgREST Security Model

How PostgREST Works

The Security Chain

PostgreSQL Role Fundamentals

Role Types in PostgREST Context

Creating Roles

Permission Architecture

Schema-Level Permissions

Table-Level Permissions

Sequence Permissions

Function and Procedure Permissions

Row-Level Security (RLS)

Why RLS Matters

Enabling RLS

RLS Policy Patterns

RLS Best Practices

JWT Token Configuration

JWT Structure for PostgREST

Configuring PostgREST JWT Settings

Role Extraction from JWT

Production Configuration Patterns

Pattern 1: Simple Two-Role Setup

Pattern 2: Multi-Tenant SaaS Application

Pattern 3: API Versioning with Schema Separation

Security Hardening

Principle of Least Privilege

Preventing Common Attacks

Audit Logging

Performance Considerations

RLS Performance Impact

Connection Pooling

Query Planning

Monitoring and Maintenance

Permission Auditing

Testing Permissions

Rotation Procedures

Troubleshooting Common Issues

Issue 1: "Permission denied for table"

Issue 2: RLS policy not working

Issue 3: JWT role not being applied

Deployment Checklist

Conclusion

Additional Resources

Official Documentation

Tools

Community

Leave a Comment

Table of Contents