EctoAdapter doesn't work with Ecto.Adapters.SQL.Sandbox in nested transactions

[sushilbansal]

Problem

The EctoAdapter fails when used within a Repo.transaction block in test environments using Ecto.Adapters.SQL.Sandbox. The EnforcerServer runs in a separate process and cannot access the database connection owned by the test process, even when using Sandbox.allow/3.

This is a specific case of the broader issue described in #31, where the problem manifests when Casbin operations are wrapped in application-level transactions.

Error Message

** (DBConnection.ConnectionError) could not checkout the connection owned by #PID<0.462.0>. 
When using the sandbox, connections are shared, so this may imply another process is using a connection. 
Reason: connection not available and request was dropped from queue after 973ms.

Reproduction Steps

1. Setup
# config/test.exs
config :my_app, MyApp.Repo,
  pool: Ecto.Adapters.SQL.Sandbox

# test/support/casbin_case.ex
defmodule MyApp.CasbinCase do
  use ExUnit.CaseTemplate

  setup do
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(MyApp.Repo)
    
    # This doesn't work because EnforcerServer runs in separate process
    case Registry.lookup(Acx.EnforcerRegistry, "my_enforcer") do
      [{enforcer_pid, _}] ->
        Ecto.Adapters.SQL.Sandbox.allow(MyApp.Repo, self(), enforcer_pid)
      [] ->
        :ok
    end
    
    :ok
  end
end


2. Application Code (with transaction)
defmodule MyApp.Authorization.Roles do
  def create_role(role_name, domain, permissions) do
    # Wrapping in transaction for rollback safety
    Repo.transaction(fn ->
      Enum.each(permissions, fn %{resource: resource, action: action} ->
        case Policy.add(role_name, domain, resource, action) do
          :ok -> :ok
          {:error, reason} -> Repo.rollback(reason)
        end
      end)
      
      {:ok, :created}
    end)
  end
end


3. Test (fails)
defmodule MyApp.RolesTest do
  use MyApp.DataCase
  use MyApp.CasbinCase

  test "create role" do
    permissions = [
      %{resource: "orgs", action: "read"},
      %{resource: "users", action: "read"}
    ]

    # This fails with connection error
    assert {:ok, :created} = Roles.create_role("analyst", "*", permissions)
  end
end

When Repo.transaction is called in the test process, it acquires the sandbox connection. When the transaction then calls Policy.add(), which sends a message to the EnforcerServer (a separate process), that server tries to insert into the database using repo.insert(). Even though we've called Sandbox.allow(Repo, test_pid, enforcer_pid), the enforcer cannot access the connection because:

1. The test process owns the connection in a transaction
2. The transaction has locked the connection
3. The EnforcerServer cannot access a connection locked by another process's transaction

[github-actions]

🎉 This issue has been resolved in version 1.6.0 🎉

The release is available on GitHub release

Your semantic-release bot 📦🚀