Row-level security¶

This page lists Ferro's row-level security API: RowSecurity and RowPolicy, which declare a table's policies, and the session settings the policies read.

A model declares its policies on __ferro_rls__, and a session's settings= supplies the value they compare against. With the Invoice model below, a session scoped to one tenant reads that tenant's invoices:

async with engines.session("app", settings={"app.tenant_id": str(ACME)}):
    invoices = await Invoice.select().order_by(lambda invoice: invoice.total).all()
    assert [(invoice.customer, invoice.total) for invoice in invoices] == [
        ("Hooli", 120),
        ("Initech", 450),
    ]

Postgres returns Acme's two invoices; the policy filtered out Globex's. The examples on this page run against Postgres in the docs test suite. Scope rows by tenant walks through the setup, and Row-level security and session settings explains how it works.

RowSecurity¶

RowSecurity declares a table's row security, its policies and its table flags:

class RowSecurity:
    def __init__(self, *policies: RowPolicy, force: bool = True) -> None: ...

Assign it to the model's __ferro_rls__ class variable, annotated ClassVar:

class Invoice(Model):
    id: int | None = Field(default=None, primary_key=True)
    tenant_id: uuid.UUID
    customer: str
    total: int

    __ferro_rls__: ClassVar = RowSecurity(
        RowPolicy(column="tenant_id", setting="app.tenant_id")
    )

class Invoice(Model):
    id: Annotated[int | None, Field(primary_key=True)] = None
    tenant_id: uuid.UUID
    customer: str
    total: int

    __ferro_rls__: ClassVar = RowSecurity(
        RowPolicy(column="tenant_id", setting="app.tenant_id")
    )

Invoice gets ENABLE and FORCE ROW LEVEL SECURITY and one policy, rls_invoice_tenant_id.

Raises TypeError when a positional argument is not a RowPolicy or force is not a bool, and, when the model is defined, when two policies share a name.

RowPolicy¶

RowPolicy declares one policy, the rows a command may see or write:

@dataclass(frozen=True)
class RowPolicy:
    name: str | None = None
    command: str = "all"
    restrictive: bool = False
    column: str | None = None
    setting: str | None = None
    using: str | None = None
    with_check: str | None = None

It takes one of two forms, the shorthand (column= and setting=) or SQL (using= and with_check=); passing both, or neither, raises TypeError.

The shorthand¶

RowPolicy(column="tenant_id", setting="app.tenant_id") renders one comparison and uses it for both USING and WITH CHECK:

"tenant_id" = NULLIF(current_setting('app.tenant_id', true), '')::uuid

The cast comes from the column's storage type on Postgres:

setting must be dotted identifiers, matching [A-Za-z_][A-Za-z0-9_]*(\.[A-Za-z0-9_]+)+, or the constructor raises ValueError. A column the model does not have raises TypeError when the model is defined, listing the valid columns. Policy expressions explains the NULLIF.

The SQL form¶

Pass using=, with_check= or both, with name=, when one column compared with one setting is not enough: a function call, a subquery, boolean logic. Each expression must be a non-empty string. The constructor checks it against the clauses Postgres accepts for the command, and raises TypeError:

• when name= is missing;
• for using= on "insert", or with_check= on "select" or "delete" (see Commands);
• when an "insert" policy has no with_check=, or any other command has no using=.

Write the same NULLIF(current_setting(key, true), '') wrapper around a setting you read in SQL, so an unset setting matches no rows instead of raising.

Policy names¶

The live name is rls_<table>_<name>: RowPolicy(name="tenant", ...) on Document is rls_document_tenant. The name must match [a-z][a-z0-9_]* and must not start with rls_, or the constructor raises TypeError.

Names are unique per model. Postgres limits identifiers to 63 bytes, so Ferro truncates a long name; two names that are equal after truncation raise TypeError when the model is defined, as a repeated name does.

Ferro manages only the policies whose names start with rls_. It reports any other policy on a table and never changes it.

Commands¶

command= scopes a policy to one command, and each command takes the clauses Postgres accepts for it:

The table holds for the SQL form. The shorthand fills in every clause its command accepts.

Composing policies¶

Postgres combines a table's policies the same way Ferro declares them: permissive policies (the default) with OR, and restrictive ones (restrictive=True) with AND against the rest. A restrictive tenant fence plus permissive grants gives "within the tenant, the owner has full access and everyone reads published documents":

class Document(Model):
    id: int | None = Field(default=None, primary_key=True)
    tenant_id: uuid.UUID
    owner: str
    title: str
    published: bool = False

    __ferro_rls__: ClassVar = RowSecurity(
        # Restrictive: every other policy applies only inside the tenant.
        RowPolicy(
            name="tenant",
            column="tenant_id",
            setting="app.tenant_id",
            restrictive=True,
        ),
        # The owner reads and writes their own documents.
        RowPolicy(
            name="owner_all",
            using="\"owner\" = NULLIF(current_setting('app.user', true), '')",
            with_check="\"owner\" = NULLIF(current_setting('app.user', true), '')",
        ),
        # Everyone in the tenant reads a published document.
        RowPolicy(name="published_read", command="select", using='"published"'),
    )

class Document(Model):
    id: Annotated[int | None, Field(primary_key=True)] = None
    tenant_id: uuid.UUID
    owner: str
    title: str
    published: bool = False

    __ferro_rls__: ClassVar = RowSecurity(
        # Restrictive: every other policy applies only inside the tenant.
        RowPolicy(
            name="tenant",
            column="tenant_id",
            setting="app.tenant_id",
            restrictive=True,
        ),
        # The owner reads and writes their own documents.
        RowPolicy(
            name="owner_all",
            using="\"owner\" = NULLIF(current_setting('app.user', true), '')",
            with_check="\"owner\" = NULLIF(current_setting('app.user', true), '')",
        ),
        # Everyone in the tenant reads a published document.
        RowPolicy(name="published_read", command="select", using='"published"'),
    )

tenant is restrictive, so the two permissive policies apply only inside the tenant. The three policies render as:

CREATE POLICY "rls_document_tenant" ON "document" AS RESTRICTIVE FOR ALL USING ("tenant_id" = NULLIF(current_setting('app.tenant_id', true), '')::uuid) WITH CHECK ("tenant_id" = NULLIF(current_setting('app.tenant_id', true), '')::uuid);

CREATE POLICY "rls_document_owner_all" ON "document" FOR ALL USING ("owner" = NULLIF(current_setting('app.user', true), '')) WITH CHECK ("owner" = NULLIF(current_setting('app.user', true), ''));

CREATE POLICY "rls_document_published_read" ON "document" FOR SELECT USING ("published");

With four documents (Ada's "Q3 plan", Grace's published "Handbook" and her unpublished draft in Acme, and Ada's published "Globex roadmap" in Globex), Ada in Acme reads her own document and the published one:

async with engines.session(
    "app", settings={"app.tenant_id": str(ACME), "app.user": "ada"}
):
    documents = (
        await Document.select().order_by(lambda document: document.title).all()
    )
    assert [document.title for document in documents] == ["Handbook", "Q3 plan"]

Grace's draft fails both permissive policies, and the Globex roadmap passes published_read but fails the restrictive tenant fence.

Session settings¶

The session settings API lives on engines and Session; the API reference has the full signatures.

Both settings= and set_config check their arguments before touching a connection:

• a key or value that is not a str raises TypeError (convert a UUID with str());
• a key with no dot raises ValueError: built-in settings such as timezone or role cannot be set this way;
• settings= on a non-Postgres connection, or set_config on a session whose connection is not Postgres, raises RuntimeError; set_config on a session that is not open raises RuntimeError too.

Keys and values travel as bound parameters, never as SQL text.

SQLite¶

SQLite has no row-level security. A model with __ferro_rls__ still registers, and its table is created without policies, with one warning per table:

with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    await connect("sqlite::memory:", auto_migrate=True)
print(next(str(w.message) for w in caught if "'invoice'" in str(w.message)))
# ferro auto-migrate: Table 'invoice' declares __ferro_rls__, but row-level
# security is a PostgreSQL-only feature: the table is created without its
# policies and rows are NOT filtered on SQLite. Run against PostgreSQL for
# enforcement.

Rows on SQLite are not filtered. Declaring settings on a SQLite session raises, since nothing could honour them:

try:
    async with engines.session(settings={"app.tenant_id": str(ACME)}):
        pass
except RuntimeError as error:
    assert "Session settings require a Postgres connection" in str(error)
else:
    raise AssertionError("settings= on SQLite must be refused")

A session that only inherits settings from an outer Postgres session opens on SQLite without them.

Warnings¶

connect(migrate_updates=True) (or migrate_destructive=True) warns when the connecting role is neither a superuser nor BYPASSRLS and an existing table it migrates declares RowSecurity(force=True):

ferro auto-migrate: The connected role is neither a superuser nor BYPASSRLS, and this migration touches table(s) 'document', 'invoice' with FORCE ROW LEVEL SECURITY. Row policies apply to the migrating role too, so a backfill or data step can silently see and update zero rows. Migrate as a role with BYPASSRLS if this pass moves data.

It does not check whether the table's live FORCE flag was already set. A table created by the same connect has no rows to miss, so auto_migrate=True and create_tables() never print it.

The reconciliation pass also warns about a drifted policy written in SQL, a policy Ferro does not own, a removed declaration whose flags are still on, and an orphaned rls_* policy; Drift and ownership explains each.