Whether the Source Database User Has Sufficient Permissions

Whether the source database user has sufficient permissions

The source database user must have required permissions for full and incremental migrations.

• In a full migration, the source database user must have the SELECT, SHOW VIEW, and EVENT permissions.
• In an incremental migration, the source database user must have the following permissions: SELECT, SHOW VIEW, EVENT, LOCK TABLES, REPLICATION SLAVE, and REPLICATION CLIENT.

If the permissions are insufficient, the migration will fail.

Failure cause: In a full migration, the source database user must have the SELECT, SHOW VIEW, and EVENT permissions.

Handling suggestions: Grant the source database user the corresponding permissions.

Failure cause: In an incremental migration, the source database user must have the following permissions: SELECT, SHOW VIEW, EVENT, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, In the DR scenario, the following permissions are required: CREATE, ALTER, DROP, DELETE, INSERT, UPDATE, and INDEX.

Handling suggestions: Grant the source database user the corresponding permissions.

Failure cause: Insufficient user permissions

Handling suggestion: Check whether the database user permissions meet the migration requirements.

Whether the source database user has sufficient permissions

Specific permissions are required for different synchronization task types.

• Full synchronization: CONNECT permission for databases, USAGE permission for schemas, SELECT permission for tables, SELECT permission for sequences, and SELECT permission for system table catalog pg_catalog.pg_authid (used for synchronizing user passwords)

• Full+incremental synchronization: CONNECT permission for databases, USAGE permission for schemas, SELECT permission for tables, SELECT permission for sequences, SELECT permission for system table pg_catalog.pg_authid (used for synchronizing user passwords), UPDATE, DELETE, and TRUNCATE permissions for tables that do not have primary keys, and the permission to create a replication connection

If the permissions are insufficient, the migration will fail.

Failure cause: This item cannot be checked because the source database fails to be connected.

Handling suggestion: Check whether the source database is connected.

Failure cause: Insufficient user permissions

Handling suggestion: Check whether the database user permissions meet the migration requirements.

Failure cause: In a full migration, the source database user must have the SELECT, REFERENCES, TRIGGER, EXECUTE, and USAGE permissions.

Handling suggestion: Change or authorize the migration account.

Failure cause: The replication permission is not configured in pg_hba.conf for the replication instance and database user.

Handling suggestion:

Grant the replication permission to the user.

Add the following to pg_hba.conf, and restart the database for the modification to take effect:

host replication XXX(dbuser) 0.0.0.0/0 method

After the migration is complete, delete this record and restart the database again.

Failure cause: The max_wal_senders value in the source database is too small.

Handling suggestion: In the postgresql.conf file, change the max_wal_senders value to a larger one, such as increasing it by 5 or 10.

Failure cause: The database is unavailable.

Handling suggestion: Contact technical support.

Item to be confirmed: The source database contains objects that can only be created by a superuser. The destination user is not a superuser, so the objects will be ignored.

Handling suggestion: Use a superuser of the destination database or confirm that these objects can be ignored.

Whether the source database user has sufficient permissions

Check whether the source database user permissions meet the migration requirements. If the permissions are insufficient, the migration will fail.

Failure cause: This item cannot be checked because the source database fails to be connected.

Handling suggestion: Check whether the source database is connected.

Failure cause: The source database user must have the readAnyDatabase permission for the admin database and the read permission for the local database.

Handling suggestion: Grant permissions to the source database user.

db.grantRolesToUser("Username",[{role:"readAnyDatabase",db:"admin"}, {role:"read",db:"local"}])

Failure cause: The source database user must have the readAnyDatabase permission for the admin database and the read permission for the config database.

Handling suggestion: Grant permissions to the source database user.

db.grantRolesToUser("Username",[{role:"readAnyDatabase",db:"admin"}, {role:"read",db:"config"}])

Failure cause: The source mongos node user must have the readAnyDatabase permission for the admin database, and the read permission for the config database. The source shard node user must have the readAnyDatabase permission for the admin database, and the read permission for the local database.

Handling suggestion: Grant permissions to the source database user.

• Statement for mongos:

  db.grantRolesToUser("Username",[{role:"readAnyDatabase",db:"admin"}, {role:"read",db:"config"}])

• Statement for shard:

  db.grantRolesToUser("Username",[{role:"readAnyDatabase",db:"admin"}, {role:"read",db:"local"}])

Failure cause: The source database user must have the readAnyDatabase permission for the admin database.

Handling suggestion: Grant permissions to the source database user.

db.grantRolesToUser("Username",[{role:"readAnyDatabase",db:"admin"}])

Failure cause: The source database user must have the read permission for the local database.

Handling suggestion: Grant permissions to the source database user.

db.grantRolesToUser("Username",[{role:"read",db:"local"}])

Failure cause: The source database user must have the read permission for the config database.

Handling suggestion: Grant permissions to the source database user.

db.grantRolesToUser("Username",[{role:"read",db:"config"}])