DBAL-474: SchemaManager / Connection on PostgreSQL platform does not respect filterExpression for sequences #1674

Open
doctrinebot opened this Issue Mar 27, 2013 · 12 comments

2 participants

@doctrinebot

Jira issue originally created by user josdewitte:

Dear Symfony team,

the filterExpression on AbstractSchemaManager seems not to work for sequences.

This only happens under postgres.

It seems the way the sequences are handled are the culprit: It tries to get min_value etc of sequences without matching sequence names to the filter expression in advance.

If for example access to the sequences is denied, (Different schema without permissions for the current entity manager), any higher-level ORM operations like generating migration versions fail.

--------------------- UPDATE

the context is when using migrations. Positive regexp expressions do not limit the migration to a single schema. eg ^schemaname.$
Instead, all sequences on the current database are returned.
When trying to limit a migration to a single schema consecutively this doesn't work.
We are using a per-schema connection, so this results in a lot of hassle for us.

@doctrinebot

Comment created by @beberlei:

Can you paste an exception trace? I see that filtering is applied to sequences, but your description seems to indicate this happens due to an SQL query much earlier?

@doctrinebot

Comment created by josdewitte:

Dear Benjamin,

the context is when using migrations. Positive regexp expressions do not limit the migration to a single schema. eg ^schemaname.$
Instead, all sequences on the current database are returned.
When trying to limit a migration to a single schema consecutively this doesn't work.
We are using a per-schema connection, so this results in a lot of hassle for us.

@doctrinebot

Comment created by @deeky666:

[~josdewitte] I think your issue got fixed in commit: 8beb732
This patch was first introduced in 2.3. Can you please confirm that this is fixed? Otherwise can you please provide a concrete example so that we can reproduce you issue?

@doctrinebot

Comment created by wwwald:

I believe we are bumping into the same issue. Our webapp uses Migrations, but for our webapp we are limited to a certain schema within a bigger PostgreSQL database. We only have permissions on our own schema and public.
Now, listSequences in AbstractSchemaManager does filter the asset names correctly with the mentioned fix.

But the problem is with the step before, \_getPortableSequencesList (see line 135 of AbstractSchemaManager):

        return $this->filterAssetNames($this->_getPortableSequencesList($sequences));

This function goes out and does a \*getPortableSequenceDefinition on every sequence in the unfiltered list. For every sequence, the \*getPortableSequenceDefinition in PostgreSqlSchemaManager performs a SELECT:

        $data = $this->*conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->*platform->quoteIdentifier($sequenceName));

Now, our user role in the database doesn't have SELECT permissions on these sequences in other schemas, so the migration fails with a privilege error.

There should be some kind of filtering on the sequence list BEFORE the SELECT statement in the _getPortableSequenceDefinition function are performed, no?

@doctrinebot

Comment created by wwwald:

I currently have a workaround running locally, which filters the sequences list before creating the PortableSequence's. This is probably hackish and a poor workaround, just posting here as a temporary solution and further illustration of the problem.

Altered line 135 in AbstractSchemaManager:

        return $this->_getPortableSequencesList($this->filterSequenceNames($sequences));

I added function filterSequenceNames() in AbstractSchemaManager for appropriate sequence filtering:

    /****
     * Filters sequence names if they are configured to return only a subset of all
     * the found elements.
     *5
     * @param array $sequenceNames
     *
     * @return array
     */
    protected function filterSequenceNames($sequenceNames)
    {
        $filterExpr = $this->getFilterSchemaAssetsExpression();
        if ( ! $filterExpr) {
            return $sequenceNames;
        }

        return array*values ( array*filter($sequences, function ($sequenceName) use ($filterExpr) {
                $sequenceName = $sequenceName["schemaname"].".".$sequenceName["relname"];
                return preg_match($filterExpr, $sequenceName);
            })
        );

    }

After these modifications, doctrine:migrations:migrate operations complete succesfully, even with our limited-permission database account.

@doctrinebot

Comment created by @deeky666:

[~wwwald] Your fix looks promising and reasonable. Can you create a PR on the DBAL repo for that?

@doctrinebot

Comment created by viktor.sidochenko:

Why this fix is not in master?

@doctrinebot

Comment created by @deeky666:

[~viktor.sidochenko] because nobody has fixed it yet ;) Feel free to provide a patch on GitHub.

@doctrinebot

Comment created by wwwald:

I haven't gotten around to doing the PR on GitHub yet, I'm not yet too familiar with that.
I'll try to find some time for this the coming days.

@doctrinebot

Comment created by viktor.sidochenko:

Will be good. I`m not professional developer to make patches to upstream. So just voted for this issue.

@doctrinebot

Comment created by @deeky666:

Patch supplied in PR: #546
[josdewitte], [wwwald], [~viktor.sidochenko] can you please test if the supplied PR fixes the problem?

@doctrinebot

Comment created by @doctrinebot:

A related Github Pull-Request [GH-546] was closed:
#546

@doctrinebot doctrinebot added the Bug label Dec 6, 2015
@deeky666 deeky666 was assigned by doctrinebot Dec 6, 2015
@deeky666 deeky666 added this to the 2.5 milestone Jan 5, 2016
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment