EF Core update does not works since playing with some Login/Roles in PostgreSQL

Question

I try to update an already existing database (code first). Table __EFMigrationsHistory already exists and it should only be an update.

This is the error I have since I did some changes on my database in order to create a ReadOnlyUser.

NOTE (French to English)

• French: 42P07: la relation « __EFMigrationsHistory » existe déjà
• English: 42P07: the relation « __EFMigrationsHistory » already exists

It sounds to me, according the the first select, and because the table "__EFMigrationsHistory" already exists THAT'S the first parameter "1" that sounds like not being replaced by the appropriate value. I never sees a "SELECT 1" of my life, it sounds strange to me. Could it be it does not find the appropriate Database/Schema? I ca do any select from DBeaver without any problem.

ERROR:

PM> update-database -Context MicroReseauDbContext
Build started...
Build succeeded.
2023-11-06 17:13:56.987 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure) 
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
    SELECT 1 FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
    WHERE n.nspname='public' AND
          c.relname='__EFMigrationsHistory'
)
    SELECT 1 FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
    WHERE n.nspname='public' AND
          c.relname='__EFMigrationsHistory'
)
Failed executing DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "__EFMigrationsHistory" (
    migration_id character varying(150) NOT NULL,
    product_version character varying(32) NOT NULL,
    CONSTRAINT pk___ef_migrations_history PRIMARY KEY (migration_id)
);
fail: 2023-11-06 17:13:57.495 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command) 
      Failed executing DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "__EFMigrationsHistory" (
          migration_id character varying(150) NOT NULL,
          product_version character varying(32) NOT NULL,
          CONSTRAINT pk___ef_migrations_history PRIMARY KEY (migration_id)
      );
Npgsql.PostgresException (0x80004005): 42P07: la relation « __EFMigrationsHistory » existe déjà
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|234_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
  Exception data:
    Severity: ERREUR
    SqlState: 42P07
    MessageText: la relation « __EFMigrationsHistory » existe déjà
    File: heap.c
    Line: 1202
    Routine: heap_create_with_catalog
42P07: la relation « __EFMigrationsHistory » existe déjà

Update 2023-11-07, I just updated with success another database (exact same procedure) where the only difference is in this case I did not created a "readonly" user on that Database. When I created the "readonly" user, I removed "PUBLIC" access to the database and set rights directly by users.

Answer 1

I fixed my problem by adding "SuperUser" privileges to the user used to connect to the database. That privilege was removed. My database also belongs to another "User", not to "postgres" or the connecting user. That user also has "SuperUser" privilege.

The first Select (The "SELECT 1..." in sample code) is used to know if the table exists. So it was 100% normal to do it, either if I do not knew that syntax. But the result was suppose to be a table with one row because the queried table should already exists. It was supposed to exists because I want to update the Database, it was not the creation of it. But the result was an error instead (table not found) because the user was missing required privilege to query for the existence of the table. I was missing appropriate right. Either if I was able to query the database and create tables with it while using DBeaver. Using it through EFCore won't work at all ???