Skip to content

Database Defense-in-Depth: RBAC, Least Privilege & Row-Level Security (RLS)

Created: 2026-11-08  ·  Last updated: 2026-11-08  ·  Nejat Hakan

Executive Summary: Connecting web applications to production databases using the postgres or root superuser is an architectural anti-pattern that violates fundamental compliance and defense-in-depth principles. A single SQL injection flaw or misconfigured ORM query under superuser privileges allows attackers to read private keys, drop tables, or execute arbitrary operating system commands via COPY ... PROGRAM. This guide details how to implement a three-tier Role-Based Access Control (RBAC) architecture, configure default schema privileges, and enforce bulletproof Row-Level Security (RLS) to isolate multi-tenant customer data directly at the database engine level.

  • Three-Tier RBAC Architecture

    Enforce least privilege: separate Migrator roles (DDL only), Runtime roles (DML only), and Read-Only roles (BI analytics).

  • Default Schema Privileges

    Eliminate manual grant maintenance: leverage ALTER DEFAULT PRIVILEGES so newly created tables inherit secure access rights automatically.

  • Row-Level Security (RLS)

    Prevent cross-tenant data leaks: mandate that queries can physically only scan rows matching the session's authenticated tenant_id.

  • Session Context & Testing

    Bind application user context securely in connection pools via SET LOCAL app.current_tenant_id and test policies via SET ROLE.

1. The Three-Tier Role-Based Access Control (RBAC) Model

A production database should divide operational access into three distinct role tiers:

%%{init: {'flowchart': {'wrappingWidth': 600}}}%%
flowchart TB
    SUPER["PostgreSQL Superuser (postgres)<br/>(Emergency Maintenance Only)"]

    subgraph Tier1["🏗️ 1. Schema Owner / Migrator (DDL)"]
        MIG["app_migrator Role (NOLOGIN)<br/>Owns tables, runs migrations in CI/CD"]
    end

    subgraph Tier2["⚙️ 2. Application Runtime (DML)"]
        RUN["app_runtime Role (LOGIN)<br/>SELECT, INSERT, UPDATE, DELETE only<br/>Cannot DROP, TRUNCATE, or ALTER!"]
    end

    subgraph Tier3["📊 3. Read-Only Analytics (DQL)"]
        READ["app_readonly Role (LOGIN)<br/>SELECT only on specific schemas<br/>For Grafana, Metabase, and BI exports"]
    end

    SUPER -.->|"Provisions"| MIG
    SUPER -.->|"Provisions"| RUN
    SUPER -.->|"Provisions"| READ

Privileges by Tier

Role Tier Allowed Commands Forbidden Commands Used By
Tier 1: Migrator CREATE, ALTER, DROP, TRUNCATE Direct application runtime queries CI/CD deployment pipelines (Flyway, Liquibase, Alembic).
Tier 2: Runtime SELECT, INSERT, UPDATE, DELETE CREATE TABLE, DROP TABLE, TRUNCATE Backend microservices, web apps, background workers.
Tier 3: Read-Only SELECT INSERT, UPDATE, DELETE, all DDL BI reporting tools, audit dashboards, data engineers.

2. Implementing Tiered RBAC in PostgreSQL

Execute this administrative script as postgres to establish the security hierarchy and configure default privileges:

-- File: /opt/db-ops/setup_tiered_rbac.sql
BEGIN;

-- 1. Create Tier 1: Schema Migration Role (Owns the objects)
CREATE ROLE app_migrator WITH NOLOGIN;

-- 2. Create Tier 2: Application Runtime Role
CREATE ROLE role_runtime WITH NOLOGIN;
CREATE USER app_runtime_user WITH LOGIN PASSWORD 'Runtime_Secret_2026!';
GRANT role_runtime TO app_runtime_user;

-- 3. Create Tier 3: Read-Only Reporting Role
CREATE ROLE role_readonly WITH NOLOGIN;
CREATE USER app_bi_user WITH LOGIN PASSWORD 'Reporting_Secret_2026!';
GRANT role_readonly TO app_bi_user;

-- 4. Grant connection permissions on the target database
GRANT CONNECT ON DATABASE enterprise_prod TO role_runtime, role_readonly;

-- 5. Schema permissions (allow usage of public schema)
GRANT USAGE ON SCHEMA public TO role_runtime, role_readonly;

-- 6. Grant DML rights to runtime role, read-only to analytics
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO role_runtime;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO role_runtime;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO role_readonly;

-- 7. MANDATORY: Default Privileges for Future Tables
-- When app_migrator creates new tables during deployments, automatically grant permissions:
ALTER DEFAULT PRIVILEGES FOR ROLE app_migrator IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role_runtime;

ALTER DEFAULT PRIVILEGES FOR ROLE app_migrator IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO role_runtime;

ALTER DEFAULT PRIVILEGES FOR ROLE app_migrator IN SCHEMA public
    GRANT SELECT ON TABLES TO role_readonly;

COMMIT;

3. Multi-Tenant Isolation with Row-Level Security (RLS)

In multi-tenant SaaS platforms, storing all customer data in shared tables is cost-effective, but relying on application developers to remember WHERE tenant_id = :id in every SQL query invites severe cross-tenant data leaks.

Row-Level Security (RLS) moves tenant isolation into the database engine: the database automatically and invisibly injects a security filter into every SELECT, UPDATE, and DELETE query executed by the session.

%%{init: {'flowchart': {'wrappingWidth': 600}}}%%
flowchart TB
    APP["Client Web Request (Tenant 42)"]
    POOL["Connection Pooler (Sets Session Variable)"]
    SQL["Incoming SQL: SELECT * FROM customer_invoices"]
    RLS{"PostgreSQL RLS Engine<br/>Injects Policy Predicate"}
    DATA["Physical Table Heap<br/>Contains Invoices for Tenants 10, 42, 99"]
    RES["Result: Strictly Tenant 42 Invoices!<br/>Tenants 10 and 99 are completely invisible!"]

    APP --> POOL
    POOL -->|"SET LOCAL app.current_tenant_id = '42'"| SQL
    SQL --> RLS
    RLS --> DATA
    DATA --> RES

Production RLS Setup Workflow

-- File: /opt/db-ops/configure_row_level_security.sql
BEGIN;

-- 1. Create a multi-tenant orders table
CREATE TABLE customer_invoices (
    invoice_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id    BIGINT NOT NULL,
    amount_cents BIGINT NOT NULL CHECK (amount_cents >= 0),
    issued_at    TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

-- Index the tenant_id column (CRITICAL for RLS query performance!)
CREATE INDEX idx_invoices_tenant_id ON customer_invoices (tenant_id);

-- 2. ENABLE Row-Level Security on the table
ALTER TABLE customer_invoices ENABLE ROW LEVEL SECURITY;

-- 3. FORCE RLS even for table owners (prevents accidental bypass)
ALTER TABLE customer_invoices FORCE ROW LEVEL SECURITY;

-- 4. Define the security policy using a session configuration setting
CREATE POLICY tenant_isolation_policy ON customer_invoices
    AS PERMISSIVE
    FOR ALL
    TO role_runtime
    USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::bigint)
    WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::bigint);

COMMIT;

4. Testing RLS Isolation & Context Passing

In a connection pool (such as PgBouncer or an application pool), connections are shared between different tenants. When a request starts, the application sets the tenant ID for the duration of that single transaction using SET LOCAL:

-- File: /opt/db-ops/test_rls_simulation.sql

-- Insert sample multi-tenant records as admin
INSERT INTO customer_invoices (tenant_id, amount_cents) VALUES
    (101, 15000),
    (101, 35000),
    (202, 99000);

-- Switch execution context to the unprivileged application role
SET ROLE app_runtime_user;

-- Transaction A: Authenticated as Tenant 101
BEGIN;
SET LOCAL app.current_tenant_id = '101';

-- The query does NOT specify tenant_id, but RLS automatically enforces it!
SELECT invoice_id, tenant_id, amount_cents FROM customer_invoices;
-- Returns: Only the two rows for Tenant 101! Tenant 202 is completely invisible!

-- Attempting to insert a row with an invalid tenant_id will be rejected by WITH CHECK:
-- INSERT INTO customer_invoices (tenant_id, amount_cents) VALUES (202, 5000);
-- Fails with: ERROR: new row violates row-level security policy for table "customer_invoices"

COMMIT;

-- Reset role back to admin
RESET ROLE;