Enabling multi-tenant Solidus using RLS on Postgres

20230517213020_create_rls_functions.rb

class CreateRlsFunctions < ActiveRecord::Migration[7.0]
  def self.up
    connection.execute <<-SQL
      -- This function returns the id of the current user or NULL if not set
      -- this varaible is set on a connection basis before every action
      -- If it is not set, it will return NULL and all non-public tables will seem empty.
      CREATE FUNCTION current_user_id() RETURNS BIGINT AS $$
      SELECT NULLIF(current_setting('rls.user_id', TRUE), '')::BIGINT;
      $$ LANGUAGE sql SECURITY DEFINER STABLE COST 10;
      -- This function returns the id of the current tenant or NULL if not set
      -- this varaible is set on a connection basis before every action
      -- If it is not set, it will return NULL and all non-public tables will seem empty.
      CREATE FUNCTION current_tenant_id() RETURNS BIGINT AS $$
        SELECT NULLIF(current_setting('rls.tenant_id', TRUE), '')::BIGINT;
      $$ LANGUAGE sql SECURITY DEFINER STABLE COST 10;
      
      -- This function sets an variable on a per connection (session) basis to signal
      -- that RLS should be ignore, this all policies should evaluate to true
      -- If this varaible is not set, the function returns FALSE.
      CREATE FUNCTION rls_disabled() RETURNS BOOLEAN AS $BODY$
        BEGIN
          -- Raise an error of RLS is not set up
          IF current_tenant_id()::BIGINT IS NULL AND current_setting('rls.disable', TRUE)::BOOLEAN IS NOT TRUE THEN
            RAISE EXCEPTION 'RLS not set up!' USING HINT = 'Please set rls.tenant_id or set rls.disable to FALSE.';
          END IF;
  
          RETURN COALESCE(current_setting('rls.disable', TRUE)::BOOLEAN, FALSE);
        END
      $BODY$ LANGUAGE plpgsql SECURITY DEFINER STABLE COST 12;
    SQL
  end

  def self.down
    connection.execute <<-SQL.squish
      DROP FUNCTION IF EXISTS current_tenant_id CASCADE;
      DROP FUNCTION IF EXISTS rls_disabled CASCADE;
    SQL
  end
end

20230517214130_create_tenants.rb

class CreateTenants < ActiveRecord::Migration[7.0]
  def change
    create_table :tenants do |t|
      t.string :name, index: {unique: true}
      t.string :subdomain, index: {unique: true}

      t.timestamps
    end
  end
end

20230517214135_add_tenant_to_tables.rb

class AddTenantToSpreeOrders < ActiveRecord::Migration[7.0]
  def change
    ActiveRecord::Base.connection.tables.each do |table|
      Rails.logger.debug { "Found table: #{table}" }

      if !Tenant::HasTenants.new(table_name: table).result
        disable_rls(table, force: true)
        next
      end

      unless column_exists?(table, :tenant_id)
        Rails.logger.debug { "Creating tenant_id column on table: #{table}" }
        add_column table, :tenant_id, :integer
      end
      Rails.logger.debug { "Enabling RLS on table: #{table}" }
      enable_rls(table, force: true)
      create_policy(table, version: 1)
    end
  end
end

20230518163718_update_indexes_to_allow_for_multiple_tenants.rb

class UpdateIndexesToAllowForMultipleTenants < ActiveRecord::Migration[7.0]
  def change
    remove_index(:spree_roles, :name)
    add_index(:spree_roles, [:tenant_id, :name])

    remove_index(:spree_users, :email)
    add_index "spree_users", [:tenant_id, "email"], name: "email_idx_unique", unique: true
  end
end

add_tenant_id.rb

# app/overrides/spree/base/add_tenant_id.rb

Spree::Base.class_eval do
  before_validation :associate_tenant

  def associate_tenant
    raise "Tenant not found" unless RLS.current_tenant

    self.tenant_id = RLS.current_tenant_id
    Rails.logger.info("Set thread for #{self.class.name}")
  end
end

application_controller.rb

class ApplicationController < ActionController::Base
  before_action :init_row_level_security

  def current_user
  end
  helper_method :current_user
  def init_row_level_security
    ActionController::Base.perform_caching = false

    tenant = Tenant::CurrentFromSubdomain.new(request: request).result
    RLS.set_tenant(tenant)
  end
end

current_from_subdomain.rb

# app/queries/tenant/current_from_subdomain.rb
class Tenant
  class CurrentFromSubdomain
    class TenantNotFound < StandardError; end

    def initialize(request:)
      @request = request
    end

    def result
      current_tenant
    end

    private

    attr_reader :request

    def current_tenant
      Tenant.find_by!(subdomain: subdomain)
    rescue ActiveRecord::RecordNotFound
      raise TenantNotFound, "Could not find tenant for subdomain #{subdomain}"
    end

    private

    def subdomain
      request.host.split(".").first
    end
  end
end

database.yml

  # You need to connect as a non-superadmin in order to have RLS applied to you!
  username: rails
  password: rails

db_policies.rake

# lib/tasks/db_policies.rake

if Rails.env.development?
  desc "Create database policies"
  task create_policies_for_tenanted_tables: [:environment] do
    ActiveRecord::Base.connection.tables.each do |table|
      directory = "db/policies/#{table}"
      file_name = "#{directory}/#{table}_v01.rb"

      if !Tenant::HasTenants.new(table_name: table).result
        if File.exist?(file_name)
          puts "Deleting policy for #{table} because it does not have tenants"
          File.delete(file_name)
          next
        end

        puts "Ignoring table #{table} because it does not have tenants"
        next
      end

      puts "Creating policy for table: #{table}"

      FileUtils.mkdir_p directory
      File.write(file_name, "RLS.policies_for :#{table} do
  using_tenant
end")
    end
  end

  desc "Disable RLS"
  task disable_RLS: [:environment] do
    RLS.disable!
  end
end

Gemfile

gem "rls_rails", github: "sbiastoch/rls_rails"

has_tenants.rb

# app/queries/tenant/has_tenants.rb

class Tenant
  class HasTenants
    def initialize(table_name:)
      @table_name = table_name
    end

    def result
      tables_without_tenants.exclude?(table_name)
    end

    private

    attr_reader :table_name

    def tables_without_tenants
      [
        "ar_internal_metadata",
        "roles",
        "schema_migrations",
        "tenants"
      ]
    end
  end
end

rls_rails.rb

# config/initializers/rls_rails.rb

require "application_record"
require "tenant"

RLS.configure do |config|
  config.tenant_class = Tenant
  config.tenant_fk = :tenant_id
  config.policy_dir = "db/policies"
  config.verbose = true
end

seeds.rb

# This file should contain all the record creation needed to seed the database with its default values.
# The data can then be loaded with the bin/rails db:seed command (or created alongside the database with db:setup).
#
# Examples:
#
#   movies = Movie.create([{ name: "Star Wars" }, { name: "Lord of the Rings" }])
#   Character.create(name: "Luke", movie: movies.first)

def create_tenants
  (1..3).each do |index|
    tenant = Tenant.find_by(subdomain: "marina-#{index}")
    next log_message("Found marina #{tenant.name}") if tenant

    log_message "Creating tenant: #{Tenant.create!(name: "Marina #{index}", subdomain: "marina-#{index}").name}"
  end
end

def create_admin_user(tenant)
  admin_role = Spree::Role.find_or_create_by(name: "admin", tenant_id: tenant.id)

  if Spree::User.admin.any?
    log_message "Admin user for tenant #{tenant.name} already exists."
    return
  end

  email = ENV["ADMIN_EMAIL"] || "[email protected]"
  password = ENV["ADMIN_PASSWORD"] || "test123"

  log_message "Creating admin user with:"
  log_message "  - email: #{email}"
  log_message "  - password: #{password}"
  log_message "(please use the ADMIN_EMAIL and ADMIN_PASSWORD environment variables to control how the default admin user is created)"

  if Spree::User.find_by(email: email)
    warn "WARNING: There is already a user with the email: #{email}, so no account changes were made."
    return
  end

  admin = Spree::User.new(
    password: password,
    password_confirmation: password,
    email: email,
    login: email,
    tenant_id: tenant.id
  )

  if admin.save
    log_message "Before: #{admin.spree_roles}"
    admin.spree_roles << admin_role
    log_message "After: #{admin.spree_roles}"
    admin.save
    admin.generate_spree_api_key!
  else
    warn "There were some problems while creating the admin user:"
    warn(admin.errors.full_messages.map { |m| "- #{m}" })
    warn "(attributes: #{admin.attributes.inspect})"
  end
end

def create_countries
  require "carmen"

  # Insert Countries into the spree_countries table, checking to ensure that no
  # duplicates are created, using as few SQL statements as possible (2)

  connection = Spree::Base.connection

  country_mapper = ->(country) do
    name = connection.quote country.name
    iso3 = connection.quote country.alpha_3_code
    iso = connection.quote country.alpha_2_code
    iso_name = connection.quote country.name.upcase
    numcode = connection.quote country.numeric_code
    states_required = connection.quote country.subregions?
    tenant_id = RLS.current_tenant_id

    [name, iso3, iso, iso_name, numcode, states_required, tenant_id].join(", ")
  end

  country_values = -> do
    carmen_countries = Carmen::Country.all

    # find entires already in the database (so that we may ignore them)
    existing_country_isos =
      Spree::Country.where(iso: carmen_countries.map(&:alpha_2_code)).pluck(:iso)

    # create VALUES statements for each country _not_ already in the database
    carmen_countries
      .reject { |c| existing_country_isos.include?(c.alpha_2_code) }
      .map(&country_mapper)
      .join("), (")
  end

  country_columns = %w[name iso3 iso iso_name numcode states_required tenant_id].join(", ")
  country_vals = country_values.call

  if country_vals.present?
    # execute raw SQL (insted of ActiveRecord.create) to use a single
    # INSERT statement, and to avoid any validations or callbacks
    connection.execute <<-SQL.squish
    INSERT INTO spree_countries (#{country_columns})
    VALUES (#{country_vals});
    SQL
  end
end

def create_solidus_seeds
  gem_dir = Gem::Specification.find_by_name("solidus_core").gem_dir

  require "thor"
  shell = Thor::Base.shell.new

  %w[
    stores
    store_credit
    return_reasons
    states
    stock_locations
    zones
    refund_reasons
    roles
    shipping_categories
  ].each do |seed|
    shell.say_status :seed, seed
    load "#{gem_dir}/db/default/spree/#{seed}.rb"
  end
end

def log_message(message)
  Rails.logger.info(message)
end

create_tenants

RLS.enable!

RLS.run_per_tenant do |tenant|
  log_message "Setting RLS to tenant: #{tenant.name}"

  create_admin_user(tenant)

  log_message "Setting up Solidus store for tenant #{tenant.name}"

  create_countries

  create_solidus_seeds
end

tenant.rb

# app/models/tenant.rb

# == Schema Information
#
# Table name: tenants
#
#  id         :bigint           not null, primary key
#  name       :string
#  subdomain  :string
#  created_at :datetime         not null
#  updated_at :datetime         not null
#
# Indexes
#
#  index_tenants_on_name       (name) UNIQUE
#  index_tenants_on_subdomain  (subdomain) UNIQUE
#
class Tenant < ApplicationRecord
  validates :name,
    uniqueness: true,
    presence: true
  validates :subdomain,
    uniqueness: true,
    presence: true
end

tenant_factory.rb

# spec/factories/tenant.rb

FactoryBot.define do
  factory :tenant do
    sequence(:name) { |n| "Business #{n}" }
    sequence(:subdomain) { |n| "business-#{n}" }
  end
end