Troubleshooting Google Cloud SQL “Misleading” ERROR during restore
Google Cloud SQL Misleading ERROR: (gcloud.sql.backups.restore) HTTPError 403: The client is not authorized to make this request.
ERROR: (gcloud.sql.backups.restore) HTTPError 403: The client is not authorized to make this request.
If you are getting above error while restoring Cloud SQL backup, it definitely points to that relevant IAM user lacking required permissions to restore the backup but sometimes this error message is misleading and root cause may be something else.
On prima facie it looks like a permission issue — The user restoring to a different project must have the cloudsql.instances.restoreBackup permission for the target project and the cloudsql.backupRuns.get permission for the source instance. These permissions are included in the Cloud SQL Admin role.
Scope of blog is limited to same project, so lets recreate scenario on existing Cloud SQL instances:
$ gcloud sql instances listNAME DATABASE_VERSION LOCATION TIER PRIMARY_ADDRESS PRIVATE_ADDRESS STATUSpg-db11 POSTGRES_10 asia-south1-a db-custom-8–32768 XX.XXX.XX.XXX 10.45.193.230 RUNNABLEmymart2 MYSQL_5_7 asia-south1-a db-custom-4–26624–10.45.193.3 STOPPED$
Let us say I would restore backup of pg-db11 to another instance pg-db22, then lets list out of the successful backup and pick one of them backup to restore.
$ gcloud sql backups list — instance pg-db11ID WINDOW_START_TIME ERROR STATUS INSTANCE1660312099586 2022–08–12T13:48:19.586+00:00 — SUCCESSFUL pg-db111659712251075 2022–08–05T15:10:51.075+00:00 — SUCCESSFUL pg-db11
Lets verify if the instances exist:
$ gcloud sql instances list|grep pg-db22$
Above result indicate that the target instance does not exist and what if we try to restore the instance:
$ gcloud sql backups restore 1660312099586 \> — restore-instance=pg-db22 \> — backup-instance=pg-db11All current data on the instance will be lost when the backup is restored.Do you want to continue (Y/n)? YERROR: (gcloud.sql.backups.restore) HTTPError 403: The client is not authorized to make this request.$
Above error indicate that current gcloud user lacks the required permission. So, lets check if user do have required permission:
First determine the user and service account of current gcloud environment:
$ gcloud auth listCredentialed AccountsACTIVE ACCOUNT* 664290125703-compute@developer.gserviceaccount.comadmin@shkm.altostrat.comTo set the active account, run:$ gcloud config set account `ACCOUNT`$
Check if above user have cloud sql admin role or not. Below is the extract of above output indicate that it already has required permission:
$ gcloud projects get-iam-policy shailesh-1[output is truncated here for better visibility]
.
.
.
.
.role: roles/cloudsql.admin- members:- serviceAccount:664290125703-compute@developer.gserviceaccount.com- user:admin@shkm.altostrat.comrole: roles/cloudsql.editor- members:- user:admin@shkm.altostrat.comrole: roles/cloudsql.instanceUser- members:- serviceAccount:664290125703-compute@developer.gserviceaccount.com[output is truncated here for better visibility]
.
.
.
.
.
.version: 1$
Above output confirms that there is no permission issue since current user is part of “role: roles/cloudsql.admin”.
Let’s see if we already have a target instance as per best practices. When you are restoring a backup to a different instance, keep in mind the following restrictions and best practices:
- The target instance must have the same database version and edition as the instance from which the backup was taken.
- The storage capacity of the target instance must be at least as large as the capacity of the instance being backed up. The amount of storage being used does not matter. You can see the storage capacity of the instance in the console Cloud SQL instances page
- The target instance must be in the RUNNABLE state.
- The target instance can have a different number of cores or amount of memory than the instance from which the backup was taken.
- The target instance can be in a different region from the source instance.
- During an outage, you can still retrieve a list of backups in a particular project.
Lets validate if target Cloud SQL SQL instance pg-db22 exist or not:
$ gcloud sql instances list|grep pg-db22$
Above output indicate that this instance does not exist, so let’s create per above specified best practice: The target instance must have the same database version and edition as the instance from which the backup was taken.
$ gcloud sql instances create pg-db22 — database-version=POSTGRES_10 — cpu=4 — memory=16GiB — zone=asia-south1-a — root-password=Welcome0Creating Cloud SQL instance…done.Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/shailesh-1/instances/pg-db22].NAME DATABASE_VERSION LOCATION TIER PRIMARY_ADDRESS PRIVATE_ADDRESS STATUSpg-db22 POSTGRES_10 asia-south1-a db-custom-4–16384 XX.XXX.XX.XXX — RUNNABLE$
Lets verify:
$ gcloud sql instances list|grep pg-db22pg-db22 POSTGRES_10 asia-south1-a db-custom-4–16384 XX.XXX.XX.XXX — RUNNABLE$
Now, lets restore with same previous command where we got the error:
$ gcloud sql backups restore 1660312099586 — restore-instance=pg-db22 — backup-instance=pg-db11All current data on the instance will be lost when the backup is restored.Do you want to continue (Y/n)? YRestoring Cloud SQL instance…done.Restored [https://sqladmin.googleapis.com/sql/v1beta4/projects/shailesh-1/instances/pg-db22].$
YAY, it is successful now:
$ gcloud sql instances listNAME DATABASE_VERSION LOCATION TIER PRIMARY_ADDRESS PRIVATE_ADDRESS STATUSpg-db22 POSTGRES_10 asia-south1-a db-custom-4–16384 XX.XXX.XX.XXX — RUNNABLEpg-db11 POSTGRES_10 asia-south1-a db-custom-8–32768 XX.XXX.XX.XXX 10.45.193.230 RUNNABLEmymart2 MYSQL_5_7 asia-south1-a db-custom-4–26624–10.45.193.3 STOPPED$
Hope this helps.
