FastAPI Row Security 🚣‍♂️

Row-Level Security (RLS) in SQLAlchemy for PostgreSQL with Row Security Policies:

• Restrict access to specific rows 🔒 minimizing unauthorized data exposure.
• Perfect for Scalability and Multi-Tenancy: keep the data playground organized 🏢, ensuring each tenant plays in their own sandbox.

Warning Understand that the database superuser bypasses all permission checks, except the right to log in. This is a dangerous privilege and should not be used in combination with RLS.

Installation

Use pip to install from PyPI:

pip install fastapi-rowsecurity

Basic Usage

In your SQLAlchemy model, create an attribute named __rls_policies__ that is a list of Permissive or Restrictive policies:

from fastapi_rowsecurity import Permissive, register_rls
from fastapi_rowsecurity.principals import Authenticated, UserOwner

Base = declarative_base()
register_rls(Base) # <- create all policies


class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True)
    title = Column(String, index=True)
    owner_id = Column(Integer, ForeignKey("users.id"))
    owner = relationship("User", back_populates="items")


    __rls_policies__ = [
            Permissive(expr=Authenticated, cmd="SELECT"),
            Permissive(expr=UserOwner, cmd=["INSERT", "UPDATE", "DELETE"]),
        ]

The above implies that any authenticated user can read all items; but can only insert, update or delete owned items.

• expr: any Boolean expression as a string;
• cmd: any command of ALL/SELECT/INSERT/UPDATE/DELETE.

Next, attach the current_user_id (or other runtime parameters that you need) to the user session:

# ... def get_session() -> Session:
session.execute(text(f"SET app.current_user_id = {current_user_id}"))

Find a simple example in the .

Backlog first release

• Change policies when model changes (prio!!)
• Documentation

then ...

• Support for Alembic
• When item is tried to delete, no error is raised?
• Python 3.11
• Coverage report

Final note

At the moment this module is work-in-progress and therefore experimental. All feedback and ideas are 100% welcome! So feel free to contribute or reach out to me!