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

MikelAlejoBR commented 3 years ago

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:

Create a new empty Postgres database with version 13.1

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;

Create a new Symfony installation with symfony new --full mybugtest

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

Configure the URLs in the .env file:

DATABASE_URL="postgresql://app_user:app_user@127.0.0.1:5432/test_db"
DATABASE_URL_MANAGER="postgresql://manager:manager@127.0.0.1:5432/test_db"

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'

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 commented 3 years ago

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 commented 1 year ago

did anyone manage to resolve this?

doctrine / migrations