Project 11: Multi-tenant Security with RLS

Enforce tenant isolation at the database layer with RLS.

Quick Reference

Attribute Value
Difficulty Level 4
Time Estimate 24-40 hours
Main Programming Language SQL
Alternative Programming Languages Any language using roles
Coolness Level See REFERENCE.md
Business Potential See REFERENCE.md
Prerequisites PostgreSQL installed, psql available
Key Topics Security, RLS

1. Learning Objectives

  1. Explain the core ideas behind Multi-tenant Security with RLS and why they matter.
  2. Demonstrate the workflow with a repeatable PostgreSQL session transcript.
  3. Identify and explain key metrics or evidence from the run.
  4. Document findings in a short operational report.

2. All Theory Needed (Per-Concept Breakdown)

Security, Roles, and Row Level Security

Fundamentals PostgreSQL security is based on roles, privileges, and policies. Roles can represent users or groups. Privileges control access to tables, schemas, and functions. Row level security (RLS) policies restrict which rows a role can see or modify. These mechanisms let you implement multi-tenant isolation and least privilege access at the database level, not just in application code. Security design is therefore part of schema design, not an afterthought. It also shapes how applications authenticate and connect. Small mistakes here have large consequences and can be difficult to detect. Strong defaults prevent silent data exposure and reduce blast radius today.

Deep Dive Roles are the foundation of PostgreSQL security. A role can have LOGIN and can be granted membership in other roles. Group roles simplify privilege management. Privileges can be granted on schemas, tables, and functions, and are inherited depending on role settings. This allows fine-grained control over who can read or write data.

Row level security builds on this by filtering rows based on policies. Policies are expressions that are applied to queries, ensuring that even if a role has access to a table, it can only see the rows allowed by the policy. Policies can differ for SELECT, INSERT, UPDATE, or DELETE. This makes RLS ideal for multi-tenant systems, where each tenant should only see their own data.

RLS is powerful but also subtle. Policies are applied as security barriers, and they depend on session context, such as the current user. Misconfigured policies can lead to data leaks or blocked writes. You must design and test policies carefully. Also, superusers bypass RLS by default, which means testing policies with superuser accounts can give misleading results.

Security design also intersects with functions. SECURITY DEFINER functions run with the privileges of their owner. This can be used to expose controlled operations without granting broad privileges, but it is also a risk if not carefully controlled. Role membership and inheritance interact with policies, so you must understand both to reason about security.

Security is not just about correctness; it is also about compliance and auditability. PostgreSQL provides system catalogs and logs that let you audit role memberships and privileges. A secure PostgreSQL deployment includes documented role models, explicit grants, and least privilege defaults.

RLS is especially powerful in multi-tenant architectures. It allows you to keep a single physical table while isolating rows logically. This can simplify operational overhead and reduce schema sprawl. The tradeoff is that application logic must set the correct context (for example, tenant identifiers) and must never bypass those policies. A common pattern is to use a secure function to set session-level variables and then enforce them in policies.

Privilege management also influences performance and developer workflows. If you grant blanket access to a role, you simplify development but weaken security. If you lock down everything, you may slow development and encourage unsafe workarounds. A good role model balances safety and productivity, often by creating separate roles for migrations, read-only analytics, and application services. This keeps production safe while still enabling operational tasks.

Security also includes authentication and connection policies. PostgreSQL supports password authentication, certificates, and host-based access rules. These are defined in configuration and enforced before a session even starts. If you treat these as defaults, you might accidentally expose your database to the network. A secure deployment includes explicit network rules, limited role privileges, and dedicated accounts for each service. Finally, auditing is a practical requirement. While PostgreSQL does not include full audit logging by default, you can use logs, extensions, or external monitoring to track changes and access patterns. Knowing who changed what, and when, is often required for compliance and incident response. Security is therefore not just access control; it is also traceability.

Definitions and key terms

  • Role: account or group entity with privileges.
  • Privilege: permission to access an object.
  • RLS: row level security policy applied to rows.

Mental model diagram

Role -> Privileges -> Table
Role + Policy -> Visible rows subset

How it works (step-by-step, include invariants and failure modes)

  1. Create roles and group roles (invariant: no circular membership).
  2. Grant privileges (failure: excessive grants cause leaks).
  3. Enable RLS and define policies (failure: policy blocks writes).
  4. Test as non-superuser (failure: superuser bypasses policies).

Minimal concrete example (pseudocode, config, protocol transcript, or CLI output)

Policy idea (pseudo):
ALLOW SELECT on <tenant_data> WHERE tenant_id = current_user

Common misconceptions

  • “RLS is just a view” -> It is enforced as a security barrier for all queries.
  • “Superuser testing is enough” -> Superusers bypass RLS.

Check-your-understanding questions

  1. Why do group roles simplify privilege management?
  2. What is the biggest risk of misconfigured RLS?

Check-your-understanding answers

  1. Privileges can be granted to a group and inherited by members.
  2. Data leakage or blocked updates across tenants.

Real-world applications

  • Multi-tenant SaaS databases.
  • Role-based access for analytics vs. write services.

Where you will apply it

  • See Section 3.7 Real World Outcome and Section 5.10 Implementation Phases in this file.
  • Also used in: P07-stored-logic-lab

References

  • PostgreSQL Role Membership (postgresql.org/docs/current/role-membership.html)
  • PostgreSQL Row Security Policies (postgresql.org/docs/current/ddl-rowsecurity.html)

Key insight PostgreSQL security is enforceable at the data layer, not just in application code.

Summary Roles and RLS policies let you encode access control directly in the database.

Homework/exercises to practice the concept

  1. Design a role hierarchy for read-only analytics and write services.
  2. Write a policy rule in words that enforces tenant isolation.

Solutions to the homework/exercises

  1. Create roles for analytics, service, and admin, with explicit grants.
  2. Allow access only when tenant_id matches session tenant context.

Relational Model, Schemas, and Constraints

Fundamentals PostgreSQL is a relational database, which means data is stored in tables with rows and columns and related through keys. A schema is a namespace for organizing tables, views, and functions. Constraints enforce data integrity: primary keys ensure uniqueness, foreign keys enforce relationships, unique constraints prevent duplicates, and check constraints enforce domain rules. This model is about correctness first. If you internalize the relational model, you will design schemas that enforce business rules at the database level rather than only in application code. This is critical for preventing inconsistent data, especially in multi-user systems. It also supports auditing and reliable reporting.

Deep Dive The relational model treats data as sets of tuples, where each tuple conforms to a defined structure. In PostgreSQL, tables implement this model. A schema groups related objects and helps with organization and permission boundaries. The database enforces constraints at write time, which prevents invalid states from ever entering the database. For example, a foreign key constraint means you cannot insert a row that references a missing parent. This ensures referential integrity across the system.

Designing schemas is about expressing invariants explicitly. Primary keys identify rows; surrogate keys provide stable identifiers; natural keys reflect domain identity. You must decide which constraints belong in the database and which belong in application logic. PostgreSQL is strong at enforcing constraints, so the default posture should be to enforce as much as possible at the database level. Check constraints provide a powerful way to enforce domain logic, such as non-negative quantities or allowed status transitions.

Schema design also affects performance and maintainability. Normalization reduces redundancy and avoids update anomalies, but too much normalization can lead to complex joins. Denormalization can improve read performance but risks inconsistency. PostgreSQL supports both approaches, but you must choose based on workload patterns. It is common to keep core entities normalized while allowing denormalized read models for specific reporting use cases.

Constraints also interact with concurrency. For example, uniqueness constraints require index checks that can create contention under high write load. Foreign keys require lookups in referenced tables, which can be expensive if indexes are missing. Therefore, relational design cannot be separated from indexing strategy.

Understanding schemas and constraints prepares you to interpret error messages correctly, design safe migrations, and reason about how data integrity is enforced. It also lets you reason about why an insert fails or why a cascade update behaves a certain way. The relational model is not just theory; it is how PostgreSQL ensures data correctness in production systems.

Schema evolution is part of real systems. Adding a column is easy, but changing constraints or data types often requires careful backfills and validation steps. PostgreSQL supports adding constraints as NOT VALID and later validating them, which allows you to introduce integrity rules without blocking traffic. This is a key operational pattern: you evolve the schema while maintaining uptime. If you ignore this, you end up with migrations that lock tables or fail under load.

Naming and dependency management also matter. Schema-qualified names prevent accidental collisions, and consistent naming helps with clarity and tooling. Dependencies between tables and views are tracked by PostgreSQL, which means dropping objects can have cascading effects. A good schema design includes explicit dependency awareness, and a migration plan that preserves compatibility. This is not just about SQL correctness; it is about long-term maintenance and developer sanity.

Another subtlety is search_path. PostgreSQL resolves unqualified names based on search_path, which can lead to confusing behavior if multiple schemas contain similarly named objects. Good practice is to either set search_path explicitly per application role or to always use schema-qualified names in critical paths. This reduces ambiguity and prevents accidental writes to the wrong schema.

Definitions and key terms

  • Schema: namespace for database objects.
  • Primary key: unique identifier for rows.
  • Foreign key: relationship constraint between tables.
  • Check constraint: rule enforced on column values.

Mental model diagram

Schema
  |
  +-- table A (primary key)
  |
  +-- table B (foreign key -> A)
  |
  +-- constraints (unique, check)

How it works (step-by-step, include invariants and failure modes)

  1. Define tables and keys (invariant: primary key uniqueness).
  2. Insert rows (failure: constraint violation raises error).
  3. Update rows (failure: check constraint fails).
  4. Delete rows (failure: foreign key references prevent deletion unless cascaded).

Minimal concrete example (pseudocode, config, protocol transcript, or CLI output)

SQL-like pseudocode:
TABLE <orders>
  PRIMARY KEY (<order_id>)
  FOREIGN KEY (<customer_id>) REFERENCES <customers>
  CHECK (<total> >= 0)

Common misconceptions

  • “Constraints slow everything” -> Constraints prevent corruption and often save more time than they cost.
  • “Foreign keys are optional” -> Without them, data integrity is fragile.

Check-your-understanding questions

  1. Why do foreign keys require indexes to perform well?
  2. What anomaly does normalization help prevent?

Check-your-understanding answers

  1. Foreign key checks must look up parent rows efficiently.
  2. Update anomalies and inconsistent duplicates.

Real-world applications

  • Designing ecommerce or billing schemas with strict integrity.
  • Building analytics pipelines that rely on consistent keys.

Where you will apply it

References

  • PostgreSQL Chapter 8 Data Types (postgresql.org/docs/current/datatype.html)
  • SQL standard overview (ISO/IEC 9075)

Key insight Constraints are not optional; they are your database-level safety net.

Summary The relational model gives you structure and invariants; PostgreSQL enforces them with schemas and constraints.

Homework/exercises to practice the concept

  1. Sketch a schema for a library system with books, authors, and loans.
  2. Identify at least three constraints that prevent invalid data.

Solutions to the homework/exercises

  1. Tables: books, authors, book_authors, loans, members.
  2. Examples: unique ISBN, loan must reference existing member, due_date after checkout_date.

3. Project Specification

3.1 What You Will Build

Enforce tenant isolation at the database layer with RLS. You will produce a repeatable transcript and a short evidence report for the workflow.

3.2 Functional Requirements

  1. Provide a deterministic scenario with a known outcome.
  2. Capture at least two key pieces of evidence (metrics, plans, or outputs).
  3. Produce a short report that another engineer could verify.

3.3 Non-Functional Requirements

  • Performance: Queries should complete within a few seconds on a small dataset.
  • Reliability: The workflow should be repeatable across multiple runs.
  • Usability: Outputs should be readable and structured.

3.4 Example Usage / Output

$ psql -d <database>
<database>=# SET ROLE <tenant_a>;
<database>=# SELECT <pseudo-query>;
 <tenant_a_rows>

3.5 Data Formats / Schemas / Protocols

Use a simple evidence log format:

SESSION_START
command: <psql or SQL-like command>
result: <summary line>
KEY_EVIDENCE: name=value, name=value
SESSION_END

3.6 Edge Cases

  • Missing permissions for a required command.
  • Stale statistics leading to unexpected results.
  • Misconfiguration that hides expected data.

3.7 Real World Outcome

This section is the gold standard for verification.

3.7.1 How to Run (Copy/Paste)

  • Use a local PostgreSQL instance or container.
  • Load a minimal dataset with predictable values.
  • Execute the command sequence exactly as described.

3.7.2 Golden Path Demo (Deterministic)

Tenant roles see different row sets. Exit code: 0

3.7.3 If CLI: exact terminal transcript

$ psql -d <database>
<database>=# SET ROLE <tenant_a>;
<database>=# SELECT <pseudo-query>;
 <tenant_a_rows>

3.7.4 Failure Demo (Deterministic)

Policy blocks inserts due to missing tenant context. Exit code: 1

4. Solution Architecture

4.1 High-Level Design

+--------------------+     +----------------------+     +-------------------+
| Dataset + Schema   | --> | PostgreSQL Workflow  | --> | Evidence Report   |
+--------------------+     +----------------------+     +-------------------+

4.2 Key Components

Component Responsibility Key Decisions
Dataset Provide deterministic inputs Keep small and fixed
Workflow Execute queries or commands Use explicit steps
Evidence Report Capture outcomes Use consistent format

4.4 Data Structures (No Full Code)

EvidenceRecord:
  - step
  - command
  - output_summary
  - key_metrics

Report:
  - summary
  - evidence
  - conclusions

4.4 Algorithm Overview

Key Algorithm: Evidence Capture Loop

  1. Prepare dataset and baseline metrics.
  2. Execute the workflow step by step.
  3. Capture outputs and compare with expected outcomes.
  4. Write a short report with conclusions.

Complexity Analysis:

  • Time: O(Q) where Q is number of queries.
  • Space: O(R) for report size.

5. Implementation Guide

5.1 Development Environment Setup

psql --version

5.2 Project Structure

project-root/
|-- notes/
|   `-- report.md
|-- transcripts/
|   `-- session.txt
`-- README.md

5.3 The Core Question You’re Answering

“Can I guarantee tenant isolation inside PostgreSQL itself?”

Write the evidence you need before you start.

5.4 Concepts You Must Understand First

Stop and research these before debugging:

  1. Core PostgreSQL mechanics
    • What does PostgreSQL guarantee at this layer?
    • Book Reference: “PostgreSQL: The Definitive Guide” - Ch. 3
  2. Planning and observation
    • Which signals confirm success?
    • Book Reference: “SQL Performance Explained” - Ch. 1
  3. Data integrity rules
    • Which invariants must always hold?
    • Book Reference: “Database System Concepts” - Ch. 2

5.5 Questions to Guide Your Design

Before implementing, think through these:

  1. Evidence capture
    • Which outputs prove correctness?
    • What is the smallest set of metrics that matters?
  2. Repeatability
    • How will you ensure the same results each run?
    • Which inputs must be fixed?

5.6 Thinking Exercise

Sketch a timeline of actions and identify the key evidence at each step.

5.7 The Interview Questions They’ll Ask

  1. “How do you verify correctness in PostgreSQL workflows?”
  2. “What evidence shows a query plan change?”
  3. “How do you ensure isolation and consistency?”
  4. “Why do you need VACUUM?”
  5. “How do you validate a backup?”

5.8 Hints in Layers

Hint 1: Start with a small dataset Keep the dataset minimal and predictable.

Hint 2: Record every step Use a transcript so you can compare runs.

Hint 3: Compare against expected outputs Use a golden path to validate correctness.

Hint 4: Use system catalogs for verification Confirm results with pg_stat and catalog views.

5.9 Books That Will Help

Topic Book Chapter
PostgreSQL fundamentals “PostgreSQL: The Definitive Guide” Ch. 3
SQL performance “SQL Performance Explained” Ch. 1-4
Data modeling “Database System Concepts” Ch. 2

5.10 Implementation Phases

Phase 1: Foundation (2-4 hours)

Goals:

  • Set up the environment.
  • Define the minimal dataset.

Tasks:

  1. Install and connect to PostgreSQL.
  2. Load a deterministic dataset.

Checkpoint: You can run a basic query and see expected output.

Phase 2: Core Workflow (4-8 hours)

Goals:

  • Execute the core steps.
  • Capture evidence.

Tasks:

  1. Run the main workflow commands.
  2. Capture outputs and metrics.

Checkpoint: Evidence matches the golden path.

Phase 3: Polish and Edge Cases (2-6 hours)

Goals:

  • Validate edge cases.
  • Document failures and fixes.

Tasks:

  1. Trigger a failure scenario.
  2. Document how you detected and fixed it.

Checkpoint: Failure case and fix are documented.

5.11 Key Implementation Decisions

Decision Options Recommendation Rationale
Dataset size Small or large Small Easier to verify outputs
Evidence format Transcript or report Both Transcript for detail, report for summary

6. Testing Strategy

6.1 Test Categories

Category Purpose Examples
Unit Tests Verify single steps One query or command
Integration Tests End-to-end workflow Full golden path
Edge Case Tests Validate failures Missing permissions

6.2 Critical Test Cases

  1. Primary path: expected output is produced.
  2. Failure path: error is detected and documented.
  3. Repeatability: results match across two runs.

6.3 Test Data

Input: deterministic dataset with fixed values
Expected: same outputs each run

7. Common Pitfalls & Debugging

7.1 Frequent Mistakes

Pitfall Symptom Solution
Wrong database Output references unexpected objects Verify connection and search_path
Missing privileges Permission denied errors Grant least-privilege access for the task
Stale stats Planner chooses unexpected plan Run ANALYZE and re-check

7.2 Debugging Strategies

  • Capture before changing: record outputs first.
  • Verify connection context: ensure correct database and schema.

7.3 Performance Traps

If dataset size grows, some commands may appear slow. Keep tests small and repeatable.

8. Extensions & Challenges

8.1 Beginner Extensions

  • Add one extra table or metric and document it.
  • Create a short checklist for future runs.

8.2 Intermediate Extensions

  • Add a second scenario and compare results.
  • Automate part of the workflow with a script.

8.3 Advanced Extensions

  • Integrate the workflow into a CI or scheduled job.
  • Create a dashboard or long-term trend report.

9. Real-World Connections

9.1 Industry Applications

  • Operational runbooks for production PostgreSQL systems.
  • Data platform reliability and incident response.
  • PostgreSQL core project - open source database engine.
  • pg_stat_statements - query statistics extension.

9.3 Interview Relevance

  • Transaction semantics and MVCC.
  • Index selection and query planning.

10. Resources

10.1 Essential Reading

  • PostgreSQL documentation (current) - reference for commands and behavior.
  • SQL Performance Explained - reasoning about indexes and plans.
  • Designing Data-Intensive Applications - reliability patterns.

10.2 Video Resources

  • PostgreSQL conference talks on operations and performance
  • Database reliability talks from systems conferences

10.3 Tools & Documentation

  • psql - interactive CLI for PostgreSQL.
  • pg_stat_* views - built-in monitoring tables.
  • pg_dump and pg_basebackup - backup tooling.
  • Previous Project: P10-logical-replication-pipeline.md
  • Next Project: P12-performance-observability.md

11. Self-Assessment Checklist

11.1 Understanding

  • I can explain the key concept without notes.
  • I can justify the evidence I collected.
  • I understand the tradeoffs in this workflow.

11.2 Implementation

  • All functional requirements are met.
  • The golden path transcript matches expected output.
  • Failure modes are documented with evidence.

11.3 Growth

  • I documented lessons learned.
  • I can explain this project in a job interview.

12. Submission / Completion Criteria

Minimum Viable Completion:

  • A working transcript that matches the golden path.
  • A short report with evidence and conclusions.
  • A failure case documented with explanation.

Full Completion:

  • All minimum criteria plus:
  • Comparison across at least two runs.
  • Notes on tradeoffs and possible improvements.

Excellence (Going Above & Beyond):

  • Automated capture of transcripts.
  • A reusable checklist for future runs.