ec-pg

Multi-tenancy for Rails + PostgreSQL. Supports three isolation strategies — schema-per-tenant, database sharding, and row-level security (RLS) — with thread-safe context management and optional Rack middleware for automatic per-request switching.

Requirements

• Ruby >= 3.2.0
• Rails / ActiveRecord >= 7.1
• PostgreSQL adapter (pg)

Installation

gem 'ec-pg'

Strategies

Strategy	Isolation level	Best for
Schema-per-tenant	PostgreSQL schema (search_path)	Strong isolation, moderate tenant count
Sharding	ActiveRecord multi-database	Horizontal scale, data locality
Row-Level Security	PostgreSQL RLS policies	Lightweight isolation in a shared schema

The three strategies can be used independently or combined.

---

Configuration

# config/initializers/ec_pg.rb
Ec::Pg.configure do |c|
  c.default_schema    = 'public'        # schema when none is active
  c.shared_schemas    = ['public']      # always appended to search_path
  c.number_of_shards  = 4              # total shards in the cluster
  c.rls_mode          = :local         # :local (per-transaction) or :session

  ##### BEGIN: Specific for ContextSwitcher middleware
  # Return a hash with :shard and/or :schema keys.
  c.get_context_method = ->(request) {
    tenant = Tenant.find_by(host: reques.host)
    { schema: tenant.schema_identifier, shard: tenant.shard_identifier }
  }

  # Paths that bypass context switching (substring match)
  c.context_switch_exclude_paths = ['health', 'status', 'metrics']

  c.logger = Logger.new($stdout)
  ##### END
end

---

Schema-per-tenant

Each tenant lives in its own PostgreSQL schema. The gem sets search_path for the duration of a block and restores it automatically.

Model setup

class ApplicationRecord < ActiveRecord::Base
  include Ec::Pg::SchemaMixin
  acts_as_namespaced
end

Usage

# Block form — preferred
Ec::Pg::SchemaManager.with_schema('acme') do
  User.all   # queries acme.users
end

# Stateful form
Ec::Pg::SchemaManager.apply!('acme')
User.all
Ec::Pg::SchemaManager.reset!

# Inspect current schema
Ec::Pg::SchemaManager.current_schema  # => 'acme'
Ec::Pg::SchemaManager.active?         # => true

Schema names are validated (alphanumeric + underscores only) to prevent SQL injection.

---

Sharding

Wraps ActiveRecord's connected_to to route queries to the right shard.

database.yml

production:
  primary: &primary
    adapter: postgresql
    # ...
  shard_one:
    <<: *primary
    database: app_shard_one
  shard_two:
    <<: *primary
    database: app_shard_two

Model setup

class ApplicationRecord < ActiveRecord::Base
  include Ec::Pg::ShardMixin

  # :solo — multiple shards pointing to the same DB (dev/test)
  # :sharded — separate database per shard
  acts_as_sharded(
    mode: :sharded,
    writing_database_identifier: :shard_one,
    reading_database_identifier: :shard_one_replica   # optional
  )
end

Usage

Ec::Pg::ShardManager.with_shard(:shard_one) do
  User.all   # routed to shard_one
end

Ec::Pg::ShardManager.with_shard(:shard_two, role: :reading) do
  Report.all
end

Ec::Pg::ShardManager.current_shard   # => :shard_one (or :default)

---

Row-Level Security

Sets PostgreSQL session variables that your RLS policies can read (e.g. current_setting('app.tenant_id')).

PostgreSQL policy example

CREATE POLICY tenant_isolation ON users
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Model setup

class ApplicationRecord < ActiveRecord::Base
  include Ec::Pg::RlsMixin

  acts_as_rls(
    mode: :local,    # variables reset when transaction ends
    variables: {
      tenant_id: 'app.tenant_id',
      user_id:   'app.user_id'
    }
  )
end

Usage

User.with_rls(variables: { tenant_id: 'acme-uuid', user_id: 42 }) do
  User.all   # policy restricts to acme rows
end

Modes

Mode	Variable lifetime
:local (default)	Reset at transaction end
:session	Persist until explicitly cleared

---

Combining strategies

Use Ec::Pg.switch to set both shard and schema in one call:

Ec::Pg.switch(shard: :shard_two, schema: 'acme') do
  User.all
end

# Inspect
Ec::Pg.current_shard   # => :shard_two
Ec::Pg.current_schema  # => 'acme'

---

Schema lifecycle (create / drop)

SchemaRegistry handles PostgreSQL schema DDL — separate from SchemaManager which only handles search_path switching.

# Create a schema (raises SchemaAlreadyExists if it exists)
Ec::Pg::SchemaRegistry.create!("tenant_abc")

# Drop a schema
Ec::Pg::SchemaRegistry.drop!("tenant_abc")
Ec::Pg::SchemaRegistry.drop!("tenant_abc", cascade: true)  # drop with all objects

# Check existence
Ec::Pg::SchemaRegistry.exists?("tenant_abc")   # => true / false

# List all non-system schemas
Ec::Pg::SchemaRegistry.all   # => ["public", "tenant_abc", ...]

Rake tasks

rake 'ec_pg:schema:create[tenant_abc]'
rake 'ec_pg:schema:drop[tenant_abc]'
rake 'ec_pg:schema:drop[tenant_abc,cascade]'
rake  ec_pg:schema:list

---

Migrations

Migrator runs ActiveRecord migrations inside a specific schema or shard context. Schema migrations track schema_migrations in the tenant schema (not the public schema), so tenants can be at different versions.

Schema migrations

# Migrate to latest
Ec::Pg::Migrator.migrate_schema("tenant_abc")

# Migrate to a specific version
Ec::Pg::Migrator.migrate_schema("tenant_abc", version: 20240101120000)

# Migrate a list of schemas
Ec::Pg::Migrator.migrate_each_schema(["tenant_a", "tenant_b"])

# Roll back
Ec::Pg::Migrator.rollback_schema("tenant_abc")          # 1 step
Ec::Pg::Migrator.rollback_schema("tenant_abc", steps: 3)

Shard migrations

# Migrate a single shard
Ec::Pg::Migrator.migrate_shard(:shard_one)

# Migrate all shards (derives :shard_1..:shard_N from configuration.number_of_shards)
Ec::Pg::Migrator.migrate_each_shard

# Migrate an explicit list of shards
Ec::Pg::Migrator.migrate_each_shard([:shard_one, :shard_two])

# Roll back
Ec::Pg::Migrator.rollback_shard(:shard_one)
Ec::Pg::Migrator.rollback_shard(:shard_one, steps: 2)

Custom migration paths can be passed to any method via paths::

Ec::Pg::Migrator.migrate_schema("tenant_abc", paths: ["db/tenant_migrate"])

Rake tasks

rake 'ec_pg:migrate:schema[tenant_abc]'
rake 'ec_pg:migrate:schemas[tenant_a,tenant_b]'
rake 'ec_pg:migrate:rollback_schema[tenant_abc]'
rake 'ec_pg:migrate:rollback_schema[tenant_abc,3]'

rake 'ec_pg:migrate:shard[shard_one]'
rake  ec_pg:migrate:shards
rake 'ec_pg:migrate:shards[shard_one,shard_two]'
rake 'ec_pg:migrate:rollback_shard[shard_one]'
rake 'ec_pg:migrate:rollback_shard[shard_one,2]'

---

Rack middleware

Add ContextSwitcher to automatically resolve tenant context from each request:

# config/application.rb
config.middleware.use Ec::Pg::Middleware::ContextSwitcher

get_context_method (configured above) is called on every request. If it raises, the middleware returns 422 Unprocessable Entity with a JSON error body. Paths matching any entry in context_switch_exclude_paths bypass the switcher entirely.

---

Thread safety

Context is stored in thread-local variables. Each thread/fiber has its own isolated context, and block forms (with_schema, with_shard, with_rls) always restore the previous context — even when an exception is raised.

---

Development

bin/setup       # install dependencies
rake spec       # run test suite
bin/console     # interactive prompt

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/binnablus/ec-pg. Contributors are expected to follow the code of conduct.

License

MIT — see LICENSE.txt.