Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

export_sequence should recognize create_schema config #1813

Open
machinehum opened this issue Sep 11, 2024 · 4 comments
Open

export_sequence should recognize create_schema config #1813

machinehum opened this issue Sep 11, 2024 · 4 comments

Comments

@machinehum
Copy link

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})) {

@darold
Copy link
Owner

darold commented Sep 11, 2024

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.

@machinehum
Copy link
Author

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.

@darold
Copy link
Owner

darold commented Sep 11, 2024

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?

@machinehum
Copy link
Author

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants