darold
export_sequence should recognize create_schema config
When CREATE_SCHEMA config is disabled, the sequence export still tries to create the schema, resulting in a permission failure if the Postgres user can't create the schema. This is an issue in environments where the person using Ora2Pg has rights to create tables etc. and insert data, but not create schemas (schema creation is done by a DBA, for example).
This should be an easy fix by changing lib/Ora2Pg.pm:5472 from:
if ($self->{export_schema} && ($self->{pg_schema} || $self->{schema})) {
to
if ($self->{export_schema} && $self->{create_schema} && ($self->{pg_schema} || $self->{schema})) {
Sequences need sometimes to be created before the tables because they are used. Actually the schema creation use CREATE SCHEMA IF NOT EXISTS ... which doesn't throw error. In other term this is intentional.
CREATE SCHEMA IF NOT EXISTS ... does throw an error if the Postgres user doesn't have sufficient GRANTs to create a schema in that database. Grants get checked before the ... IF NOT EXISTS.
Right, but I normally the user importing sequences or other objects migrated by Ora2Pg might be the database owner or have enough privileges to create a schema. What is your use of Ora2Pg?
We would like to migrate various databases from Oracle to Postgres. In our organization, only DBAs can create schemas (for compliance reasons; we work with sensitive data and there are many separations of power). So the DBA creates the schema and a user account, and then someone else (me in this case) has access to create tables and other objects, insert data, etc. within that schema, as that user.
Won't be fixed, I prefer the current behavior, you might find a solution to disable this line either by post export script or manually.
I'm curious why you "prefer" the current behavior? I don't see that it has any advantages, and it seems just wrong to me. If there's a config option called CREATE_SCHEMA, continuing to try to create a schema when that option is disabled is broken behavior in terms of user expectations. This could cause all sorts of failure modes beyond the specific one I reported.
I'd suggest at least that the name of the config option should be changed if the behavior is going to stay the same, because it doesn't do what it says it does.
I have reviewed this problem and change my mind. Actually we are already adding the CREATE SCHEMA in table export only if CREATE_SCHEMA is enabled, so fo sequences it must be the same. Let me know if commit 211f9b4 solves your issue.