Row-level security

Every organization-owned query in a RailsFast app is scoped in Ruby: current_organization.projects. One forgotten scope, one Project.find(params[:id]), and a customer reads another customer's rows. PostgreSQL row-level security puts the same rule in the database: a table with policies shows and accepts only the rows of the organization the database session names, whatever the query says.

RailsFast ships it opt-in, since v1.8.0. Off, nothing changes. The template's docs/row-level-security.md is the full reference.

Turn it on

1. In config/railsfast/railsfast.yml, set row_level_security: true.

2. Give tables policies in a migration:

   class CreateProjects < ActiveRecord::Migration[8.1]
     include RowLevelSecurity::Migration
   
     def change
       create_tenant_table :projects do |t|
         t.string :name, null: false
       end
       create_tenant_table :tasks do |t|
         t.uuid :project_id, null: false
       end
       add_tenant_foreign_key :tasks, :project_id, :projects
   
       enable_row_level_security :projects
       enable_row_level_security :tasks
     end
   end

3. Connect the app to PostgreSQL as a role that is neither a superuser nor BYPASSRLS. PostgreSQL never holds those to policies, and a stock Kamal deploy connects as the postgres superuser. Until you switch, the app logs a warning at boot in production.

enable_row_level_security works on an existing table too: any table with an organization_id column. It takes a brief lock and rewrites nothing.

What the helpers do

• create_tenant_table adds organization_id (required, a foreign key to the organizations table), timestamps, and a unique index on (organization_id, id).
• add_tenant_foreign_key makes the reference composite: (organization_id, project_id) points at (organization_id, id), so PostgreSQL refuses a task that points at another organization's project.
• enable_row_level_security enables and forces row-level security with two policies. tenant covers reads and writes: the row's organization is the session's. control covers reads only (below). It is reversible.

Where the organization comes from

• Requests: current_organization, resolved before every action.
• Jobs, scripts, the console: Organizations.with_organization(organization) { ... }. Without it a job sees no rows of those tables and can write none.
• Rails resets it before every request and every job, so a pooled database connection never carries one organization into the next.

Before each statement, the adapter brings the session setting in line with the current organization, in one round trip, and skips it when the connection already holds that value. It forgets what it applied after every transaction completion, raw SQL statement and reconnect. When the organization changes inside a transaction, a rollback could restore the earlier one, so until that transaction ends the setting is applied before every statement, and SQL with a ; followed by more text is refused (RowLevelSecurity::BatchRefused): send statements one by one. Each switch, and each reset, clears the query cache.

Reading across organizations

A job that enumerates every organization, or an admin screen, reads under control:

due = RowLevelSecurity.with_control(reason: "nightly invoice run") { Invoice.due.to_a }
due.each { |invoice| Organizations.with_organization(invoice.organization) { invoice.charge! } }

The block reads every organization's rows and writes none, even when entered from inside an organization. Each entry is logged with its reason and its caller.

Limits

The database does not scope code that uses raw_connection directly, SQL that changes the setting itself, or tables without policies: keep scoping those in Ruby. With row-level security on, the app refuses to boot while config.active_record.async_query_executor is set, because a load_async query may run under another organization: remove the executor (without one, load_async runs at once).