Not able to use a different connection for the database when using Postgres: Insufficient privilege

[MikelAlejoBR]

Bug Report

Q	A
BC Break	yes/no
Version	3.0.2

Summary

Even if many connections are configured, Doctrine doesn't use the specified connection in the doctrine_migrations.yaml file. Possibly related to https://github.com/doctrine/migrations/issues/1062 .

Current behavior

When running php bin/console doctrine:migrations:migrate, the command returns the following error:

An exception occurred while executing 'CREATE TABLE doctrine_migration_versions (version VARCHAR(191) NOT NULL, executed_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL, execution_time INT DEFAULT NULL, PRIMARY KEY(version))':  
                                                                                                                                                                                                                                     
  SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for schema application                                                                                                                                        
  LINE 1: CREATE TABLE doctrine_migration_versions (version VARCHAR(19...  

I have tried allowing the regular user to create the doctrine_migration_versions table, as I suspected that maybe the issue had something to do with that. But with no luck. Reproducible with the following commands:

• GRANT ALL PRIVILEGES ON SCHEMA application TO all privileges on schema application to app_user
• php bin/console doctrine:migrations:migrate
• REVOKE ALL PRIVILEGES ON SCHEMA application FROM app_user;
• php bin/console doctrine:migrations:migrate

How to reproduce

• Minimum working repository at https://github.com/MikelAlejoBR/doctrinebug . Just do docker-compose up and try to run php bin/console doctrine:migrations:migrate.

Otherwise, the manual steps are:

1. Create a new empty Postgres database with version 13.1
2. Create a pair of users in that database, one with only USAGE privileges with the following script:

    CREATE SCHEMA application;

    CREATE USER app_user WITH PASSWORD '${DB_APP_USER_PASSWORD}';
    CREATE USER manager WITH PASSWORD '${DB_MANAGER_PASSWORD}';

    GRANT USAGE ON SCHEMA application TO app_user;
    GRANT ALL PRIVILEGES ON SCHEMA application TO manager;

    ALTER ROLE app_user SET search_path TO application;
    ALTER ROLE manager SET search_path TO application;

3. Create a new Symfony installation with symfony new --full mybugtest
4. Configure the connections in config/packages/doctrine.yaml:

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                url: '%env(resolve:DATABASE_URL)%'
                driver: 'pdo_pssql'
                server_version: '13.1'
                charset: utf8
            manager:
                # configure these for your database server
                url: '%env(resolve:DATABASE_URL_MANAGER)%'
                driver: 'pdo_pssql'
                server_version: '13.1'
                charset: utf8

        # IMPORTANT: You MUST configure your server version,
        # either here or in the DATABASE_URL env var (see .env file)
        #server_version: '13'
    orm:
        auto_generate_proxy_classes: true
        naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
        auto_mapping: true
        mappings:
            App:
                is_bundle: false
                type: annotation
                dir: '%kernel.project_dir%/src/Entity'
                prefix: 'App\Entity'
                alias: App

5. Configure the URLs in the .env file:

DATABASE_URL="postgresql://app_user:[email protected]:5432/test_db"
DATABASE_URL_MANAGER="postgresql://manager:[email protected]:5432/test_db"

6. Set the manager connection in config/packages/doctrine_migrations.yaml:

doctrine_migrations:
    connection: manager
    migrations_paths:
        # namespace is arbitrary but should be different from App\Migrations
        # as migrations classes should NOT be autoloaded
        'DoctrineMigrations': '%kernel.project_dir%/migrations'

7. Try to php bin/console doctrine:migrations:migrate

Expected behavior

The doctrine_migration_versions table is created, and the corresponding migrations run with the specified connection in the doctrine_migrations.yml file.

[goetas]

hmm this is interesting. it seems that we should always prefer the connection setting when running migrations, while we should only consider the entity manager connection while generating migrations.

[oussama123-alt]

did anyone manage to resolve this?