`ActiveRecord::ReadOnlyError` when using Rails read replicas

[timlkelly]

Rails raises an ActiveRecord::ReadOnlyError if the host Rails application uses Automatic Role Switching with a read-replica database when you attempt to re-authorize an application if there is an existing access token. This occurs because Doorkeeper is attempting to write to the database in a GET request, so Rails selects the read role automatically instead of the write role.

I've created a Rails project with the required configuration to showcase the error. Reproduction steps and error details are in the Readme. https://github.com/timlkelly/doorkeeper-db-replica

[fredplante]

Hey @timlkelly, we don't use exactly the same setup (we have something custom) but we ran into same issue. The fix we applied was to wrap calls to doorkeeper_authorize, something like:

connect_to_primary_db do
   doorkeeper_authorize! :foo
end

I presume you could fix your issue doing something similar.

We eliminated the problem completely by moving away from this behavior by dropping the previous_refresh_token column, it didn't scale well in our case.

In any case, I believe it's not an issue that should be handled by doorkeeper itself but more in your app code.

[tpiscitell]

I don't think that fix applies here. To clarify, "attempt to re-authorize" means re-entering the OAuth authorization code flow by visiting /oauth/authorize again. Since Doorkeeper::AuthorizationsController::matching_token? returns true, Doorkeeper proceeds to create a new authorization code instead of presenting the user with the authorize form.

[fredplante]

oh yes, sorry I was too fast 🙈

[nbulaj]

Hello @timlkelly . I'm new to Rails multiple DBs support. But what if you define custom classes for Doorkeeper models with smth like

connects_to database: { writing: :primary, reading: :primary_replica }

Or Rails still has to decide which DB it should use?

BTW WDYT about #1791 ?

[timlkelly]

Hello @timlkelly . I'm new to Rails multiple DBs support. But what if you define custom classes for Doorkeeper models with smth like

connects_to database: { writing: :primary, reading: :primary_replica }
Or Rails still has to decide which DB it should use?

I've tried this by adding the following to the example repo in OP.

# app/models/access_grant.rb
class AccessGrant < ApplicationRecord
  # ApplicationRecord includes `connects_to`
  include Doorkeeper::Orm::ActiveRecord::Mixins::AccessGrant
end

# config/initializers/doorkeeper.rb
Doorkeeper.configure do 
  ...
  access_grant_class "AccessGrant"
  ...
end

However, this does not resolve the problem because Rails still attempts to write to the database during a GET request, so it has already automatically selected the read replica.

---

BTW WDYT about #1791 ?

I tested this locally with the example repo, and the described workflow no longer errors. Though I don't feel knowledgeable enough with Doorkeeper or OAuth to comment on the solution.

---

Similar to @fredplante's suggestion, we've added this monkey patch as a workaround for now.

# lib/extensions/doorkeeper/authorizations_controller.rb

require 'doorkeeper'

module Extensions
  module Doorkeeper
    module AuthorizationsController
      def new
        ::ActiveRecord::Base.connected_to(role: :writing) do
          super
        end
      end
    end
  end
end

Rails.application.config.to_prepare do
  Doorkeeper::AuthorizationsController.prepend Extensions::Doorkeeper::AuthorizationsController
end

[nbulaj]

Hello @tpiscitell . Can you please try to use copilot/fix-active-record-readonly-error branch and test on your multiple DBs setup? 🙏

[nbulaj]

This one was closed automatically, but feel free to ping if we still have issues 👀