Migration fails on existing schema different than public with user with no schema creation ACL

[gioppoluca]

Steps to reproduce

• Have a PostgreSQL Db with an existing schema (thiis is needed since we need to add permissions also to view other tables in other schemas etc)
• Create a model with the schema set to the existing schema
• Execute the npm run migrate and get error on permission denied

Current Behavior

Migrating schemas (alter existing schema)
Cannot migrate database schema { error: permission denied for database micado
at Connection.parseE (/code/micado-backend/node_modules/pg/lib/connection.js:581:48)
at Connection.parseMessage (/code/micado-backend/node_modules/pg/lib/connection.js:380:19)
at Socket. (/code/micado-backend/node_modules/pg/lib/connection.js:116:22)
at Socket.emit (events.js:198:13)
at addChunk (_stream_readable.js:288:12)
at readableAddChunk (_stream_readable.js:269:11)
at Socket.Readable.push (_stream_readable.js:224:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
name: 'error',
length: 97,
severity: 'ERROR',
code: '42501',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'aclchk.c',
line: '3485',
routine: 'aclcheck_error' }

Expected Behavior

The migration operates with no error

Additional information

I've seen that it seems that the connector try to CREATE the schema, but the schema already exists and the operating user has no right to create a schema in the DB.

If this is the case we should need an option NOT to create the schema if this is existing OR add the "if not exists" option so that the operation goes on normally

Related Issues

See Reporting Issues for more tips on writing good issues

[gioppoluca]

If I add to the user the create privilege on the DB the migration works.

This could be a problem since there could be a case where the user does not have that privilege and the schema could be already existing.
Please take this into account with proper options/settings

[dhmlau]

In reality, as discussed in loopbackio/loopback-next#4757, the "migrate script" should be generating DDL for users to review, and pass it to someone who has the rights to run against the database. Please feel free to continue the discussion over there.

For now, I'm afraid this is the expected behavior that the specified credential connecting to the datasource needs to have the right level of access in order to perform table/schema creation.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been closed due to continued inactivity. Thank you for your understanding. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository.