How to Stop the Entity Framework Code First from running “CREATE SCHEMA IF NOT EXISTS”

Question

Here is info about our technical development environment :

• .NET Core 3.1
• PostgreSQL 14.2, compiled by Visual C++ build 1914, 64-bit
• EntityFramework.Functions Version=1.5.0
• Microsoft.EntityFrameworkCore.Design Version=5.0.17
• Microsoft.EntityFrameworkCore.Tools Version=5.0.17
• Npgsql.EntityFrameworkCore.PostgreSQL Version=5.0.10

We are using a PostgreSQL user account that is very restrictive because we Only want said account to create typical database objects (i.e. tables, views, functions, procedures, etc), but we do not want said account to create database schemas. It's just for security and make our environment more bullet-proof.

public class DatabaseContext : DbContext
{
    public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
         modelBuilder.HasDefaultSchema(Constants.SchemaName);
         base.OnModelCreating(modelBuilder);
         modelBuilder.ApplyConfiguration(new SuperMarketEntityTypeMap());
    } 

    public DbSet<SuperMarket> supermarkets { get; set; }

    public void SetCommandTimeout(int? timeout)
    {
         this.Database.SetCommandTimeout(timeout);
    }

    public override void Dispose()
    {
        base.Dispose();
    }
}

public class DatabaseContextFactory : IDesignTimeDbContextFactory<DatabaseContext>
{
     public DatabaseContext CreateDbContext(string[] args)
     {
         var optionsBuilder = new DbContextOptionsBuilder<DatabaseContext>();
         optionsBuilder.UseNpgsql(x => x.MigrationsHistoryTable(
         HistoryRepository.DefaultTableName, Constants.SchemaName));

         return new DatabaseContext(optionsBuilder.Options);
    }
}

From my PowerShell commandline, when I run an update like the following, it throws an error when trying to "CREATE SCHEMA IF NOT EXISTS" because the we have not granted the PostgreSQL user account the privileges to create database schemas. We only want said user account to create typical database objects (i.e. tables, views, functions, procedures, etc):

dotnet ef database update –connection “Blah Blah Blah Connection String Blah Blah Blah” 

Build started... 
Build succeeded.

Failed executing DbCommand (115ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE SCHEMA IF NOT EXISTS supermarkets_schema; CREATE TABLE sendgrid_status."__EFMigrationsHistory" (
 "MigrationId" character varying(150) NOT NULL,
 "ProductVersion" character varying(32) NOT NULL,
 CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId") );

Could someone please post a response with code and/or commandline execution arguments and/or configuration changes that will show how I can stop the Entity Framework code-first from running "CREATE SCHEMA IF NOT EXISTS" ?

Answer 1

Essentially, in the following post, the developer overrides the NpgsqlMigrationsSqlGenerator's Generate with an empty method in a customized Subclass of NpgsqlMigrationsSqlGenerator which is called MyMigrationsSqlGenerator

( Technical Reference: https://github.com/npgsql/efcore.pg/issues/1770 )

---Excerpt from posting above----------------------------

Had the same issue described here (we're mapping each application to a separate schema, with strict isolation between schemas). I was able to work around it thanks to the hints from Shay:

public class MyMigrationsSqlGenerator: NpgsqlMigrationsSqlGenerator
{
    public MyMigrationsSqlGenerator(MigrationsSqlGeneratorDependencies
dependencies, INpgsqlOptions npgsqlOptions) : base(dependencies,
npgsqlOptions)
    {
    }

    protected override void Generate(EnsureSchemaOperation operation, IModel model, MigrationCommandListBuilder builder)
    {
        // don't generate schema operations as they are not permitted with our current permissions setup
    }
}

then hooked this up with:

.ReplaceService<IMigrationsSqlGenerator, NpgsqlMigrationsSqlGenerator,
MyMigrationsSqlGenerator>()

---Excerpt from posting above----------------------------

To elaborate on where the ReplaceService line of code above goes, it is placed in my IDesignTimeDbContextFactory implementation here:

 public class DatabaseContextFactory :   IDesignTimeDbContextFactory<DatabaseContext>
    {
         public DatabaseContext CreateDbContext(string[] args)
         {
             var optionsBuilder = new     DbContextOptionsBuilder<DatabaseContext>();
             optionsBuilder.ReplaceService<IMigrationsSqlGenerator,     NpgsqlMigrationsSqlGenerator, MyMigrationsSqlGenerator>().UseNpgsql(
         x => x.MigrationsHistoryTable(
             HistoryRepository.DefaultTableName,
            "Blah Blah Blah Database Schema Name Blah Blah"));
            return new DatabaseContext(optionsBuilder.Options);

        }

  }