fix!: Change export service account permission for external backups - roles/storage.objectAdmin

[DeLoWaN]

My GCP SQL Server exports stopped working a few days ago. The workflow fails with a missing permission storage.objects.delete on the Cloud SQL service account. After an exchange with the GCP support, they confirmed internal changes has been made and required new permissions for the Cloud SQL service account onto the bucket:

After thoroughly reviewing your issue and analyzing similar cases with the tools at my disposal, I can confirm that there have been internal changes to the export method due to ongoing improvements in Google Cloud Storage. Our internal team is diligently addressing this matter to enhance the overall user experience.

In the meantime, as a workaround while our team investigates, we recommend granting storage.objects.delete permissions to the service account on the GCS bucket you’re exporting to. It’s important to note the difference between predefined GCP roles, such as roles/storage.objectCreator, which encompass a collection of permissions, and using individual permissions on their own. When creating a custom IAM role in Terraform, you’ll need to specify the individual service-level permissions you wish to apply, such as storage.objects.create [1][2][3].

Thank you for your understanding, and please feel free to reach out if you have any further questions.

Best regards,
Josh
Google Cloud Platform Support
Working Hours (Monday to Friday): 3:00 PM to 1:00 AM PhST (UTC +8:00)

The documentation is not fully updated, but suggest to use the objectAdmin role.

This PR changes the role accordingly.

[google-cla]

Thanks for your pull request! It looks like this may be your first contribution to a Google open source project. Before we can look at your pull request, you'll need to sign a Contributor License Agreement (CLA).

View this failed invocation of the CLA check for more information.

For the most up to date status, view the checks section at the bottom of the pull request.

[imrannayer]

@DeLoWaN This will be a breaking change. Can you plz add a doc in docs folder for v23 upgrade will change IAM permission.

[imrannayer]

/gcbrun

[DeLoWaN]

@DeLoWaN This will be a breaking change. Can you plz add a doc in docs folder for v23 upgrade will change IAM permission.

Done.

[imrannayer]

/gcbrun

[jyoungs]

@DeLoWaN -- any updates from GCP? Are you replacing backups? Why would it need storage.objects.delete?

[DeLoWaN]

@DeLoWaN -- any updates from GCP? Are you replacing backups? Why would it need storage.objects.delete?

The case is closed on GCP support. They didn't tell any specifics about why it needed that additional permission.

Only detail you can find in the documentation is that you need that delete permission when using parallel MySQL exports (which I was not doing since I'm running SQL Server).

They probably made a mistake internally by requiring more permissions that initially required, but the module still has to be aligned to work properly.

Thank you for confirming that the issue is now resolved and the case can be closed.

Here's a brief summary of the case for your reference:

Issue Statement: Cannot export SQL Server databases with required permission on service account

Impacted Service: SQL Server on Google Cloud

Work Performed:

• Your concerns were understood and acknowledged thoroughly.
• A detailed investigation was conducted on the instance, along with the associated service accounts and their permissions.
• Relevant existing cases were reviewed to assist in resolving your issue.
• Additional information was gathered using the available tools at my disposal.
• Google documentation was researched to support the resolution of your issue.
• A meaningful response was sent for clarification and probing to efficiently troubleshoot the problem.
• Provided insights on recent internal changes to the export process in Google Cloud Storage that may have impacted functionality.
• I am confirming this with you so that it can justify your pull request.
• Recommended adding the storage.objects.delete permission to resolve the issue and facilitate successful exports.

Observations: The investigation revealed that the service account faced permission issues related to the storage.objects.delete permission, which was not explicitly stated in the documentation. > Adding this permission resolved the access denied error. > Additionally, recent internal changes to the export process in Google Cloud Storage may have contributed to the issue. This observation supports your pull request for adjustments to the service > account permissions. Thank you for your collaboration!

Conclusions: In conclusion, the insights gained from this case emphasize the importance of maintaining accurate permissions for service accounts and staying updated on any changes to the export > process. Your proactive engagement has been invaluable in addressing the issue. We are committed to ensuring your workflow operates smoothly moving forward.

Reason for Closing the Case: The issue is resolved now and no further action is needed from Support.

[imrannayer]

/gcbrun