A production-tested guide to implementing PostgreSQL RLS in a multi-tenant Rails application. Extracted from a real SaaS codebase serving thousands of workspaces.
Data is isolated at two levels:
- Organization (billing, memberships, API keys)
- Workspace (all operational data — posts, comments, files, etc.)
Adapt these to your tenancy model. Single-tenant apps can drop the organization layer entirely and use just tenant_id.
Request flow:
Controller around_action → SET ROLE myapp_rls → SET app.current_*_id → yield → RESET
Job flow:
around_perform → infer tenant from args → SET ROLE → SET app.current_*_id → yield → RESET
Key concepts:
- A non-login PostgreSQL role (e.g.
myapp_rls) is used during requests/jobs. RLS policies apply to this role, not the superuser. - PostgreSQL session variables carry tenant context:
app.current_user_id,app.current_organization_id,app.current_workspace_id. - SQL functions (
current_user_id(),current_workspace_id(),current_organization_id()) read those variables and are referenced in policyUSING/WITH CHECKexpressions. FORCE ROW LEVEL SECURITYensures policies apply even to the table owner — without it, a superuser bypasses all policies.
A rake task creates the non-login role and grants it full DML access. Your superuser connection can SET ROLE to it during requests, activating the policies.
# lib/tasks/rls.rake
RLS_APP_ROLE = "myapp_rls"
namespace :db do
desc "Create and grant the RLS app role"
task setup_rls: :environment do
conn = ActiveRecord::Base.connection
# Create role if it doesn't exist
role_exists = conn.select_value("SELECT 1 FROM pg_roles WHERE rolname = '#{RLS_APP_ROLE}'")
unless role_exists
conn.execute("CREATE ROLE #{RLS_APP_ROLE} NOLOGIN NOSUPERUSER INHERIT")
puts "Created role '#{RLS_APP_ROLE}'"
end
# Grant DML on all current and future tables/sequences
conn.execute("GRANT ALL ON ALL TABLES IN SCHEMA public TO #{RLS_APP_ROLE}")
conn.execute("GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO #{RLS_APP_ROLE}")
conn.execute("ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO #{RLS_APP_ROLE}")
conn.execute("ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO #{RLS_APP_ROLE}")
# Allow the current DB user to SET ROLE to the RLS role
current_user = conn.select_value("SELECT current_user")
is_member = conn.select_value(
"SELECT pg_has_role(current_user, '#{RLS_APP_ROLE}', 'MEMBER')",
)
conn.execute("GRANT #{RLS_APP_ROLE} TO #{current_user}") unless is_member
puts "RLS role '#{RLS_APP_ROLE}' is ready"
end
end
# Auto-run after common db tasks so the role always has grants on new tables
%w[db:schema:load db:prepare db:migrate].each do |task_name|
Rake::Task[task_name].enhance do
Rake::Task["db:setup_rls"].reenable
Rake::Task["db:setup_rls"].invoke unless Rails.env.production?
end
endProduction note: In production, create the role and grants as part of your infrastructure provisioning (Terraform, Ansible, etc.), not via rake tasks.
A reusable module you include in migrations. Provides one-liners for common patterns and lower-level primitives for custom policies.
# lib/rls_helpers.rb
module RlsHelpers
# ── High-level: enable RLS + create a single policy ──
def enable_rls(table, policy:, using:, check: nil)
execute "ALTER TABLE #{table} ENABLE ROW LEVEL SECURITY;"
execute "ALTER TABLE #{table} FORCE ROW LEVEL SECURITY;"
create_rls_policy(table, policy: policy, using: using, check: check)
end
def disable_rls(table, policy:)
execute "DROP POLICY IF EXISTS #{policy} ON #{table};"
execute "ALTER TABLE #{table} NO FORCE ROW LEVEL SECURITY;"
execute "ALTER TABLE #{table} DISABLE ROW LEVEL SECURITY;"
end
# ── Low-level: create/drop individual policies ──
def create_rls_policy(table, policy:, using:, check: nil)
check_clause = check ? "WITH CHECK (#{check})" : "WITH CHECK (#{using})"
execute <<-SQL
CREATE POLICY #{policy} ON #{table}
USING (#{using})
#{check_clause};
SQL
end
def drop_rls_policy(table, policy:)
execute "DROP POLICY IF EXISTS #{policy} ON #{table};"
end
# ── Convenience: one-liner for workspace-scoped tables ──
# (the most common case — just checks workspace_id)
def enable_workspace_rls(table)
enable_rls(table,
policy: "#{table}_workspace_isolation_policy",
using: "workspace_id = current_workspace_id()")
end
def disable_workspace_rls(table)
disable_rls(table, policy: "#{table}_workspace_isolation_policy")
end
# ── Convenience: one-liner for organization-scoped tables ──
def enable_organization_rls(table)
enable_rls(table,
policy: "#{table}_organization_isolation_policy",
using: "organization_id = current_organization_id()")
end
def disable_organization_rls(table)
disable_rls(table, policy: "#{table}_organization_isolation_policy")
end
# ── Create the SQL functions that read session variables ──
def ensure_rls_functions!
%w[user workspace organization].each do |scope|
create_rls_setting_function("current_#{scope}_id", "app.current_#{scope}_id")
end
end
def create_rls_setting_function(function_name, setting_key)
execute <<-SQL
CREATE OR REPLACE FUNCTION #{function_name}() RETURNS INTEGER AS $$
SELECT NULLIF(current_setting('#{setting_key}', TRUE), '')::INTEGER;
$$ LANGUAGE SQL STABLE;
SQL
end
endRun ensure_rls_functions! once in your first RLS migration. It creates the three current_*_id() functions that all policies reference.
# db/migrate/XXXXXX_enable_row_level_security.rb
require_relative "../../lib/rls_helpers"
class EnableRowLevelSecurity < ActiveRecord::Migration[8.0]
include RlsHelpers
def up
# Create current_user_id(), current_workspace_id(), current_organization_id()
ensure_rls_functions!
# Organization-level tables with custom per-operation policies
enable_rls_on_organizations
enable_rls_on_memberships
# Simple organization-scoped tables (one-liner)
enable_organization_rls(:api_keys)
enable_organization_rls(:billing_subscriptions)
end
def down
%w[api_keys billing_subscriptions].each { |t| disable_organization_rls(t) }
disable_table_rls(:organization_memberships, %w[
org_memberships_isolation_policy
org_memberships_user_select_policy
org_memberships_owner_write_policy
])
disable_table_rls(:organizations, %w[
organizations_select_policy
organizations_insert_policy
organizations_update_policy
organizations_delete_policy
])
%w[current_user_id current_workspace_id current_organization_id].each do |fn|
execute "DROP FUNCTION IF EXISTS #{fn}();"
end
end
private
def disable_table_rls(table, policies)
policies.each { |p| drop_rls_policy(table, policy: p) }
execute "ALTER TABLE #{table} NO FORCE ROW LEVEL SECURITY;"
execute "ALTER TABLE #{table} DISABLE ROW LEVEL SECURITY;"
end
# ── Organizations: per-operation policies ──
# SELECT: user is a member or org matches current context
# INSERT: only the owner
# UPDATE: owner or admin
# DELETE: only the owner
def enable_rls_on_organizations
execute "ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;"
execute "ALTER TABLE organizations FORCE ROW LEVEL SECURITY;"
execute <<-SQL
CREATE POLICY organizations_select_policy ON organizations
FOR SELECT
USING (
id = current_organization_id()
OR (
current_user_id() IS NOT NULL
AND (
owner_id = current_user_id()
OR EXISTS (
SELECT 1 FROM organization_memberships om
WHERE om.organization_id = organizations.id
AND om.user_id = current_user_id()
)
)
)
);
SQL
execute <<-SQL
CREATE POLICY organizations_insert_policy ON organizations
FOR INSERT
WITH CHECK (
current_user_id() IS NOT NULL
AND owner_id = current_user_id()
);
SQL
execute <<-SQL
CREATE POLICY organizations_update_policy ON organizations
FOR UPDATE
USING (
current_user_id() IS NOT NULL
AND EXISTS (
SELECT 1 FROM organization_memberships om
WHERE om.organization_id = organizations.id
AND om.user_id = current_user_id()
AND om.role IN ('owner', 'admin')
)
);
SQL
execute <<-SQL
CREATE POLICY organizations_delete_policy ON organizations
FOR DELETE
USING (
current_user_id() IS NOT NULL
AND owner_id = current_user_id()
);
SQL
end
# ── Organization Memberships: compound policies ──
# Multiple permissive policies — PostgreSQL ORs them together.
def enable_rls_on_memberships
execute "ALTER TABLE organization_memberships ENABLE ROW LEVEL SECURITY;"
execute "ALTER TABLE organization_memberships FORCE ROW LEVEL SECURITY;"
# Organization-scoped access (all operations)
execute <<-SQL
CREATE POLICY org_memberships_isolation_policy ON organization_memberships
USING (organization_id = current_organization_id())
WITH CHECK (organization_id = current_organization_id());
SQL
# Users can always read their own memberships (e.g. org switcher)
execute <<-SQL
CREATE POLICY org_memberships_user_select_policy ON organization_memberships
FOR SELECT
USING (
current_user_id() IS NOT NULL
AND user_id = current_user_id()
);
SQL
# Org owners can create memberships (bootstrapping, invitations)
execute <<-SQL
CREATE POLICY org_memberships_owner_write_policy ON organization_memberships
FOR INSERT
WITH CHECK (
current_user_id() IS NOT NULL
AND EXISTS (
SELECT 1 FROM organizations
WHERE id = organization_memberships.organization_id
AND owner_id = current_user_id()
)
);
SQL
end
endFor any table that has a workspace_id column — one line does it all:
# db/migrate/XXXXXX_create_projects.rb
require_relative "../../lib/rls_helpers"
class CreateProjects < ActiveRecord::Migration[8.0]
include RlsHelpers
def up
create_table :projects do |t|
t.references :workspace, null: false, foreign_key: true
t.string :name, null: false
t.timestamps
end
enable_workspace_rls(:projects)
end
def down
disable_workspace_rls(:projects)
drop_table :projects
end
endThe workspaces table itself can't use the simple one-liner — it needs membership-based checks.
# db/migrate/XXXXXX_enable_workspace_rls.rb
class EnableWorkspaceRls < ActiveRecord::Migration[8.0]
def up
execute "ALTER TABLE workspaces ENABLE ROW LEVEL SECURITY;"
execute "ALTER TABLE workspaces FORCE ROW LEVEL SECURITY;"
# SELECT: org member or job context (current_workspace_id matches)
execute <<-SQL
CREATE POLICY workspace_select_policy ON workspaces
FOR SELECT
USING (
id = current_workspace_id()
OR (
current_user_id() IS NOT NULL AND
EXISTS (
SELECT 1 FROM organization_memberships om
WHERE om.organization_id = workspaces.organization_id
AND om.user_id = current_user_id()
)
)
);
SQL
# INSERT: org owner or admin
execute <<-SQL
CREATE POLICY workspace_insert_policy ON workspaces
FOR INSERT
WITH CHECK (
current_user_id() IS NOT NULL AND
EXISTS (
SELECT 1 FROM organization_memberships om
WHERE om.organization_id = workspaces.organization_id
AND om.user_id = current_user_id()
AND om.role IN ('owner', 'admin')
)
);
SQL
# UPDATE: org member or job context
execute <<-SQL
CREATE POLICY workspace_update_policy ON workspaces
FOR UPDATE
USING (
id = current_workspace_id()
OR (
current_user_id() IS NOT NULL AND
EXISTS (
SELECT 1 FROM organization_memberships om
WHERE om.organization_id = workspaces.organization_id
AND om.user_id = current_user_id()
)
)
);
SQL
# DELETE: org owner or admin only
execute <<-SQL
CREATE POLICY workspace_delete_policy ON workspaces
FOR DELETE
USING (
current_user_id() IS NOT NULL AND
EXISTS (
SELECT 1 FROM organization_memberships om
WHERE om.organization_id = workspaces.organization_id
AND om.user_id = current_user_id()
AND om.role IN ('owner', 'admin')
)
);
SQL
end
def down
%w[workspace_select_policy workspace_insert_policy
workspace_update_policy workspace_delete_policy].each do |policy|
execute "DROP POLICY IF EXISTS #{policy} ON workspaces;"
end
execute "ALTER TABLE workspaces NO FORCE ROW LEVEL SECURITY;"
execute "ALTER TABLE workspaces DISABLE ROW LEVEL SECURITY;"
end
endAn around_action switches to the RLS role and sets session variables before every action. Always resets after, even on exceptions.
# app/controllers/concerns/row_level_security.rb
module RowLevelSecurity
extend ActiveSupport::Concern
RLS_ROLE = "myapp_rls"
included do
class_attribute :_rls_enforcement_skipped, default: false
around_action :with_rls_context
end
class_methods do
# Call in controllers that should bypass RLS (e.g. admin panels)
def skip_rls_enforcement
self._rls_enforcement_skipped = true
end
end
private
def with_rls_context
if _rls_enforcement_skipped
yield
return
end
apply_rls_settings
begin
yield
ensure
reset_rls_settings
end
end
def apply_rls_settings
sql = ActiveRecord::Base.sanitize_sql_array([
"SET ROLE #{RLS_ROLE}; " \
"SET app.current_user_id = ?; " \
"SET app.current_organization_id = ?; " \
"SET app.current_workspace_id = ?",
Current.user&.id.to_s,
Current.organization&.id.to_s,
Current.workspace&.id.to_s,
])
ActiveRecord::Base.connection.execute(sql)
end
def reset_rls_settings
ActiveRecord::Base.connection.execute(
"RESET app.current_user_id; " \
"RESET app.current_organization_id; " \
"RESET app.current_workspace_id; " \
"RESET ROLE"
)
end
endInclude it in your base controllers:
class ApplicationController < ActionController::Base
include RowLevelSecurity
end
# Or be selective:
class App::BaseController < ApplicationController
include RowLevelSecurity
end
class Api::BaseController < ActionController::API
include RowLevelSecurity
endBypass for admin:
class Admin::BaseController < ApplicationController
skip_rls_enforcement
endImportant: This assumes
Current.user,Current.organization, andCurrent.workspaceare set by your authentication layer before thearound_actionfires. Usebefore_actionwith a higher priority, or set them in a middleware.
Jobs don't have a session. This concern infers the workspace_id from job arguments (works with ActiveRecord objects via GlobalID, hashes, or anything responding to .workspace_id).
# app/jobs/concerns/job_row_level_security.rb
module JobRowLevelSecurity
extend ActiveSupport::Concern
class MissingTenantContext < StandardError; end
RLS_ROLE = "myapp_rls"
included do
class_attribute :_bypass_rls, default: false
around_perform :with_rls_context
end
class_methods do
# Call in jobs that operate across tenants (e.g. billing, cleanup)
def bypass_rls!
self._bypass_rls = true
end
end
private
def with_rls_context(&)
return yield if self.class._bypass_rls
workspace_id = infer_workspace_id
raise MissingTenantContext, "#{self.class.name}: couldn't infer workspace_id from args. " \
"Pass a workspace-scoped record or add bypass_rls! to this job." unless workspace_id.present?
execute_with_rls(workspace_id, &)
end
def execute_with_rls(workspace_id)
connection = ActiveRecord::Base.connection
workspace = Workspace.find(workspace_id)
# Set Current attributes so app code works normally
Current.workspace = workspace
Current.organization = workspace.organization
enable_rls(connection, workspace_id, workspace.organization_id)
yield
ensure
disable_rls(connection)
end
def enable_rls(connection, workspace_id, organization_id)
connection.execute("SET ROLE #{RLS_ROLE}")
connection.execute(
ActiveRecord::Base.sanitize_sql_array(
["SET app.current_workspace_id = ?", workspace_id.to_s]
),
)
connection.execute(
ActiveRecord::Base.sanitize_sql_array(
["SET app.current_organization_id = ?", organization_id.to_s]
),
)
end
def disable_rls(connection)
connection&.execute("RESET app.current_workspace_id; RESET app.current_organization_id")
connection&.execute("RESET ROLE")
Current.workspace = nil
Current.organization = nil
end
# Walk job arguments looking for workspace_id
def infer_workspace_id
arguments.lazy.map { |arg| extract_workspace_id(arg) }.find(&:present?)
end
def extract_workspace_id(argument)
return argument[:workspace_id] if argument.is_a?(Hash) && argument.key?(:workspace_id)
return argument["workspace_id"] if argument.is_a?(Hash) && argument.key?("workspace_id")
return argument.workspace_id if argument.respond_to?(:workspace_id)
nil
end
endWire it up:
class ApplicationJob < ActiveJob::Base
include JobRowLevelSecurity
end
# Cross-tenant jobs opt out explicitly:
class BillingReconciliationJob < ApplicationJob
bypass_rls!
endWhen ActiveJob deserializes GlobalID references (e.g. PostJob.perform_later(@post)), the find call hits the database — which now has RLS. Without context, it returns nothing.
This custom locator reads a workspace_id param from the GlobalID and temporarily sets the RLS context so the record can be found.
# config/initializers/global_id_locator.rb
class TenantAwareGlobalIdLocator < GlobalID::Locator::UnscopedLocator
RLS_ROLE = "myapp_rls"
def locate(gid, options = {})
with_rls_context(gid) { super }
end
def locate_many(gids, options = {})
gids.map { |gid| locate(gid, options) }
end
private
def with_rls_context(gid)
workspace_id = gid.params["workspace_id"] if gid.params
return yield if workspace_id.blank?
ActiveRecord::Base.transaction do
ActiveRecord::Base.connection.execute("SET LOCAL ROLE #{RLS_ROLE}")
ActiveRecord::Base.connection.execute(
ActiveRecord::Base.sanitize_sql_array([
"SELECT set_config('app.current_workspace_id', ?, true)",
workspace_id.to_s,
]),
)
yield
end
end
end
# Register for your app's GlobalID namespace
GlobalID::Locator.use :myapp, TenantAwareGlobalIdLocator.newFor this to work, your models must include
workspace_idin GlobalID params. Overrideto_global_idor useGlobalID::Identificationwith custom params.
Sometimes you need to temporarily switch context within a request — e.g. an event subscriber writing an audit log for a specific workspace.
# app/subscribers/audit_subscriber.rb
class AuditSubscriber
def emit(event)
payload = event[:payload]
return unless payload.is_a?(Hash) && payload[:workspace_id]
with_rls_context(payload[:workspace_id]) do
Activity.create!(
workspace_id: payload[:workspace_id],
actor_id: payload[:actor_id],
action: event[:name],
metadata: payload[:metadata] || {},
)
end
end
private
def with_rls_context(workspace_id)
conn = ActiveRecord::Base.connection
previous = conn.select_value(
"SELECT current_setting('app.current_workspace_id', true)"
).to_s
target = workspace_id.to_s
switched = previous != target
conn.execute("SET app.current_workspace_id = #{conn.quote(target)}") if switched
yield
ensure
if switched
if previous.blank?
conn.execute("RESET app.current_workspace_id")
else
conn.execute("SET app.current_workspace_id = #{conn.quote(previous)}")
end
end
end
endRails' schema.rb doesn't know about PostgreSQL functions or RLS policies. This extension appends them to the dump so you can use schema.rb instead of structure.sql.
# lib/postgresql_rls_schema_dumper.rb
module PostgresqlRlsSchemaDumper
module SchemaDumperExtension
def trailer(stream)
dump_functions(stream)
dump_rls_policies(stream)
super
end
private
def dump_functions(stream)
functions = @connection.execute(<<-SQL).to_a
SELECT proname AS name, pg_get_functiondef(oid) AS definition
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace
AND prokind = 'f'
AND proname IN ('current_user_id', 'current_workspace_id', 'current_organization_id')
ORDER BY proname;
SQL
return if functions.empty?
stream.puts " # PostgreSQL Functions"
functions.each do |func|
stream.puts " execute(<<-SQL)"
func["definition"].each_line { |line| stream.puts " #{line.rstrip}" }
stream.puts " SQL"
stream.puts
end
end
def dump_rls_policies(stream)
policies = @connection.execute(<<-SQL).to_a
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;
SQL
return if policies.empty?
# Enable RLS on each table
stream.puts " # Row Level Security"
policies.map { |p| p["tablename"] }.uniq.each do |table|
stream.puts " execute(\"ALTER TABLE #{table} ENABLE ROW LEVEL SECURITY;\")"
stream.puts " execute(\"ALTER TABLE #{table} FORCE ROW LEVEL SECURITY;\")"
end
stream.puts
# Dump each policy
stream.puts " # RLS Policies"
policies.each do |policy|
stream.puts " execute(<<-SQL)"
stream.puts " CREATE POLICY #{policy['policyname']} ON #{policy['tablename']}"
stream.puts " FOR #{policy['cmd']}" unless policy["cmd"] == "ALL"
stream.puts " AS #{policy['permissive']}" if policy["permissive"] == "RESTRICTIVE"
stream.puts " TO #{policy['roles'].tr('{}', '')}" unless policy["roles"] == "{public}"
qual = policy["qual"]
with_check = policy["with_check"]
if qual && with_check
stream.puts " USING (#{qual})"
stream.puts " WITH CHECK (#{with_check});"
elsif qual
stream.puts " USING (#{qual});"
elsif with_check
stream.puts " WITH CHECK (#{with_check});"
end
stream.puts " SQL"
stream.puts
end
end
end
end
ActiveSupport.on_load(:active_record) do
ActiveRecord::SchemaDumper.prepend(PostgresqlRlsSchemaDumper::SchemaDumperExtension)
endRegister in an initializer:
# config/initializers/rls_schema_dumper.rb
require Rails.root.join("lib/postgresql_rls_schema_dumper")Helpers for setting RLS context in Minitest. Include in ActiveSupport::TestCase so every test has access.
# test/support/rls_test_helpers.rb
module RlsTestHelpers
RLS_ROLE = "myapp_rls"
# ── Context setters ──
def with_user_context(user, &block)
set_rls_var("app.current_user_id", user.id)
block.call
ensure
reset_rls_var("app.current_user_id")
end
def with_organization_context(organization, &block)
set_rls_var("app.current_organization_id", organization.id)
block.call
ensure
reset_rls_var("app.current_organization_id")
end
def with_workspace_context(workspace, &block)
set_rls_var("app.current_workspace_id", workspace.id)
block.call
ensure
reset_rls_var("app.current_workspace_id")
end
def with_tenant_context(user:, workspace:, organization: nil, &block)
organization ||= workspace.organization
set_rls_var("app.current_user_id", user.id)
set_rls_var("app.current_organization_id", organization.id)
set_rls_var("app.current_workspace_id", workspace.id)
block.call
ensure
clear_rls_context
end
def with_rls_role(&block)
conn = ActiveRecord::Base.connection
conn.execute("SET ROLE #{RLS_ROLE}")
block.call
ensure
conn&.execute("RESET ROLE")
end
# Full RLS setup with save/restore of previous context
def with_rls_setup(user:, workspace:, &block)
previous = {
user: current_rls_user_id,
workspace: current_rls_workspace_id,
organization: current_rls_organization_id,
}
set_rls_var("app.current_user_id", user.id)
set_rls_var("app.current_organization_id", workspace.organization_id)
set_rls_var("app.current_workspace_id", workspace.id)
block.call
ensure
if previous[:user] && previous[:workspace]
set_rls_var("app.current_user_id", previous[:user])
set_rls_var("app.current_organization_id", previous[:organization]) if previous[:organization]
set_rls_var("app.current_workspace_id", previous[:workspace])
else
clear_rls_context
end
end
# ── Introspection ──
def current_rls_user_id = rls_var_value("app.current_user_id")
def current_rls_organization_id = rls_var_value("app.current_organization_id")
def current_rls_workspace_id = rls_var_value("app.current_workspace_id")
def clear_rls_context
ActiveRecord::Base.connection.execute(
"SELECT set_config('app.current_user_id', '', true), " \
"set_config('app.current_organization_id', '', true), " \
"set_config('app.current_workspace_id', '', true)",
)
end
private
def set_rls_var(name, value)
ActiveRecord::Base.connection.execute(
ActiveRecord::Base.sanitize_sql_array(["SELECT set_config(?, ?, true)", name, value.to_s]),
)
end
def reset_rls_var(name)
ActiveRecord::Base.connection.execute(
ActiveRecord::Base.sanitize_sql_array(["SELECT set_config(?, '', true)", name]),
)
end
def rls_var_value(name)
result = ActiveRecord::Base.connection.execute("SELECT current_setting('#{name}', true)")
value = result.first["current_setting"]
value.present? ? value.to_i : nil
end
endBootstrap in test setup:
# test/test_helper.rb
require_relative "support/rls_test_helpers"
module TestDatabaseBootstrap
module_function
def ensure_rls_role!
conn = ActiveRecord::Base.connection
conn.execute(<<~SQL)
DO $$ BEGIN
CREATE ROLE myapp_rls WITH LOGIN;
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;
SQL
db = conn.quote_table_name(conn.current_database)
conn.execute("GRANT CONNECT ON DATABASE #{db} TO myapp_rls")
conn.execute("GRANT USAGE ON SCHEMA public TO myapp_rls")
conn.execute("GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO myapp_rls")
conn.execute("GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO myapp_rls")
conn.execute("ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO myapp_rls")
conn.execute("ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO myapp_rls")
rescue ActiveRecord::StatementInvalid => e
Rails.logger.warn "Could not provision myapp_rls role: #{e.message}"
end
end
TestDatabaseBootstrap.ensure_rls_role!
class ActiveSupport::TestCase
include RlsTestHelpers
# If using parallel tests, re-provision on each worker
parallelize_setup { |_worker| TestDatabaseBootstrap.ensure_rls_role! }
end- Ensure the table has a
workspace_id(ororganization_id) column withNOT NULL+ foreign key. - In your migration,
include RlsHelpersand callenable_workspace_rls(:table_name). - For tables needing per-operation policies (the tenant table itself, memberships), write custom
CREATE POLICYstatements. - Run
bin/rails db:migrate— the rake enhancement auto-grants the role access on new tables. - Verify: queries through controllers/jobs are automatically filtered;
rails consolebypasses RLS (superuser).
| Decision | Why |
|---|---|
FORCE ROW LEVEL SECURITY |
Without it, the table owner (your DB superuser) bypasses all policies. FORCE makes policies apply universally. |
NOLOGIN role |
The RLS role can't connect directly — only via SET ROLE from the superuser. This prevents accidental direct access. |
RESET ROLE in ensure |
Always restores the superuser role after the request, even on exceptions. Prevents a leaked restricted role on connection reuse. |
| Multiple permissive policies | PostgreSQL ORs all PERMISSIVE policies together. A row is visible if any policy passes. Use RESTRICTIVE for additional constraints that must all pass. |
| Avoid circular policy references | If a policy on table A references table B, and B's policy references A, you get infinite recursion. Use a third table (e.g. organization_memberships) to break the cycle. |
set_config(..., true) in tests |
The true parameter makes the setting local to the current transaction, playing nicely with transactional test fixtures. |
STABLE function volatility |
current_setting() is stable within a statement — PostgreSQL can cache the result, so RLS checks don't re-read the setting on every row. |
Single-tenant (just tenant_id): Drop the organization layer. Use two functions: current_user_id() and current_tenant_id(). Your controller concern sets two variables instead of three.
Different naming: Replace workspace with team, project, space, etc. The pattern is identical — just change the column name, function name, and session variable.
UUID primary keys: Change the function return type from INTEGER to UUID:
CREATE OR REPLACE FUNCTION current_tenant_id() RETURNS UUID AS $$
SELECT NULLIF(current_setting('app.current_tenant_id', TRUE), '')::UUID;
$$ LANGUAGE SQL STABLE;Multiple databases: RLS is per-database. If you use Rails' multi-database support, you need the role and functions in each database.
MIT. Use freely, adapt to your needs.