How to resolve the PgBouncer limitation: Unsupported startup parameter "search_path" with Entity Framework Core

Schemas is a great feature in PostgreSQL, allowing you to organize your database objects into namespaces (e.g. for different tenants). Using the search_path parameter in the connection string, you can define the order in which schemas is searched for objects:

Host=localhost;Port=5432;USER ID=myuser;Password=mypassword;Search Path=myschema

But, when using PgBouncer as a connection pooler, you might encounter the following error:

Unsupported startup parameter: search_path

This is because PgBouncer does not support the search_path parameter (e.g. due to its configuration).

So, how can you overcome this limitation? Especially when using Entity Framework Core and you are not able to change the PgBouncer configuration?

In the following, we will extract the schema from the connection string and set it manually in Entity Framework Core.

1. Create the NpgsqlConnectionStringProvider class

public sealed class NpgsqlConnectionStringProvider
{
    public string ConnectionString { get; }
    public string? Schema { get; init; }
    
    public NpgsqlConnectionString(string? connectionString)
    {
        var connection = new NpgsqlConnectionStringBuilder(connectionString);
        this.Schema = connection.SearchPath;
        
        connection.Remove(nameof(NpgsqlConnectionStringBuilder.SearchPath));
        this.ConnectionString = connection.ConnectionString;
    }
}

2. Register the NpgsqlConnectionStringProvider in the Program.cs

// Program.cs
// ..
builder.Services.TryAddSingleton(serviceProvider =>
{
    var configuration = serviceProvider.GetRequiredService<IConfiguration>();
    var connectionString = configuration.GetConnectionString("MyConnectionString");
    return new NpgsqlConnectionStringProvider(connectionString);
});
//..

3. Use the NpgsqlConnectionStringProvider in the DbContext

public class MyDbContext(DbContextOptions<OneConnectDbContext> options, 
        IOptions<OperationalStoreOptions> operationalStoreOptions, 
        NpgsqlConnectionStringProvider npgsqlConnectionString)
    : ApiAuthorizationDbContext<User>(options, operationalStoreOptions)
{    
    protected override void OnModelCreating(ModelBuilder builder)
    {
        // ...
        builder.HasDefaultSchema(npgsqlConnectionString.Schema);
        // ...
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // ..
        optionsBuilder.UseNpgsql(npgsqlConnectionString.ConnectionString);
        // ..
    }
}

That’s it! Now, you can use the schema from the connection string and set it in the DbContext. This way, you can overcome the PgBouncer limitation and still use schemas in PostgreSQL.