Importing dumps into Cloud SQL without errors: DEFINER and SUPER (ERROR 1227) on MySQL, extensions and owners on PostgreSQL

TL;DR

MySQL, on the source server:

1
2
3
4

mysqldump --databases app -h db.example -u dumper -p \
  --hex-blob --single-transaction --set-gtid-purged=OFF \
  --default-character-set=utf8mb4 --routines --triggers --events \
  | sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' | gzip > app.sql.gz

PostgreSQL, on the source server:

1
2

pg_dump app -h db.example -U dumper --format=plain --no-owner --no-acl \
  | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1 EXTENSION/g' | gzip > app.sql.gz

And in both cases:

1
2
3
4
5
6
7

SA=$(gcloud sql instances describe my-instance --format='value(serviceAccountEmailAddress)')
gcloud storage buckets add-iam-policy-binding gs://my-dumps \
  --member="serviceAccount:$SA" --role=roles/storage.objectAdmin
gcloud storage cp app.sql.gz gs://my-dumps/

gcloud sql import sql my-instance gs://my-dumps/app.sql.gz --database=app                 # MySQL
gcloud sql import sql my-instance gs://my-dumps/app.sql.gz --database=app --user=postgres  # PostgreSQL

gcloud sql import sql takes plain SQL or .gz: the custom format of pg_dump goes through pg_restore (below).

The errors and where they come from

On Cloud SQL nobody has SUPER (MySQL) or is a superuser (PostgreSQL); the admin is root@% or postgres, a member of cloudsqlsuperuser. Everything a normal dump takes for granted on your own server breaks right there:

MySQL: what to do with DEFINER clauses

First see how many there are on the source, and whose:

1
2
3
4

SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.VIEWS;
SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.TRIGGERS;
SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.ROUTINES;
SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.EVENTS;

Three ways out, from most to least faithful to the original:

1. Create the users on the instance before importing, with the same user@host: gcloud sql users create app --host=% --instance=my-instance --password=…. This is what the documentation recommends, and it keeps views and procedures exactly as they were. root@localhost is the exception: it cannot be created on Cloud SQL, so a DEFINER with that value must be changed no matter what.
2. Strip the DEFINER with the sed above: objects get created with the importing user as definer. Enough when the source users will never exist on Cloud SQL.
3. Switch to SQL SECURITY INVOKER (sed -E 's/SQL SECURITY DEFINER/SQL SECURITY INVOKER/g' on top of the previous one): the view or procedure runs with the caller’s privileges and the definer stops mattering. It changes the permission model; check that the application users can read the underlying tables.

The sed is deliberately broad because DEFINER= also shows up inside conditional comments (/*!50013 DEFINER=… SQL SECURITY DEFINER */); deleting just the DEFINER= leaves the rest intact and valid.

MySQL: the flags, one by one

If the user you dump with lacks PROCESS, mysqldump 8.0.21+ fails right away with “Access denied; you need (at least one of) the PROCESS privilege(s)” while querying tablespaces: add --no-tablespaces.

PostgreSQL: plain or custom

--no-owner --no-acl make everything imported belong to --user, with no GRANT to phantom roles. If the application connects as another role, create the role first and use --user=app, or reassign afterwards:

1

REASSIGN OWNED BY postgres TO app;   -- connected as postgres, in the imported database

Create the extensions the database needs (pg_trgm, postgis, uuid-ossp…) before importing, as postgres, in the target database: CREATE EXTENSION IF NOT EXISTS pg_trgm;. Only the ones on Cloud SQL’s supported extensions list work; plpgsql is already there. With that in place, commenting out the dump’s CREATE EXTENSION lines removes nothing.

If you prefer the custom format (-Fc), the extension filter goes into the pg_restore table of contents, exactly as Google documents it, and you restore through the Cloud SQL Auth Proxy:

1
2
3

pg_dump app -h db.example -U dumper --format=custom --no-owner --no-acl > app.dmp
pg_restore --list app.dmp | sed -E 's/(.*EXTENSION )/; \1/g' > app.toc
pg_restore --use-list=app.toc --no-owner --no-acl -j 4 -h 127.0.0.1 -U postgres -d app app.dmp

-j 4 restores in parallel, which the import from the bucket does not do.

Traps

• The bucket role goes to the instance’s service account, not yours: the docs ask for storage.objectAdmin (for import alone storage.objects.get and list, that is objectViewer, are enough). Without it, “The service account does not have the required permissions for the bucket” even though you can read the object yourself.
• One operation at a time per instance: with a backup or export running, the import fails with “operation already in progress”. gcloud sql operations list --instance=my-instance shows it.
• Large dumps: the import is serial and slow; launch it with --async and watch it with gcloud sql operations wait OPERATION_ID --timeout=unlimited, or gcloud dies on you along the way.
• pg_dump version: use the same major version as the target or lower. pg_dump 17 writes SET transaction_timeout = 0;, which a PostgreSQL 16 rejects with “unrecognized configuration parameter” and aborts the import.
• utf8mb4 on the instance: --default-character-set=utf8mb4 in the dump does not fix an instance created with latin1; check character_set_server in the instance flags before importing.
• The DEFINER sed also touches data: if some row literally contains DEFINER=`x`@`y` (rare, but a CMS that stores SQL can), skip it with a pattern anchored to CREATE, or review the dump diff.

See also

• PostgreSQL: admin cheat sheet — roles, pg_dump, REASSIGN OWNED and friends.
• Import and export using SQL dump files (MySQL), DEFINER clause and pg_dump / pg_restore (PostgreSQL) in the Cloud SQL documentation.