Skip to main content

Why Atlas reports GRANT or REVOKE for the postgres user on Cloud SQL

Why does atlas schema inspect against Cloud SQL list GRANT ... TO "postgres" on every object, and why does schema diff or migrate diff with a local dev database plan a REVOKE from postgres per object?

Answer​

Exclude the managed admin role and its grants in the environment that targets Cloud SQL:

atlas.hcl
env "staging" {
  url = getenv("DATABASE_URL")
  dev = "docker://postgres/16/dev"
  exclude = [
    "postgres[type=role|user]",
    "postgres[type=role|user].permission",
  ]
}

The first pattern removes only the role object, since many projects exclude roles but still manage grants to them. The second removes the grants.

Why it happens​

On Cloud SQL for PostgreSQL, the bootstrap superuser is Google's cloudsqladmin. The postgres user is created afterwards as an ordinary role: not a superuser, a member of cloudsqlsuperuser for CREATEROLE and CREATEDB, with an OID above the system range. On a local container, postgres is the bootstrap superuser.

Atlas skips the superuser's implicit grants on a local dev database, but on Cloud SQL postgres is a regular role, so its grants show up in inspection and in every diff.

Managing grants to the admin user​

Grants to postgres cannot be planned through a local dev database, because PostgreSQL makes them implicit for a superuser. To manage them, use a dev database where postgres is an ordinary role, such as a second Cloud SQL instance.

Other managed services​

Any managed service whose admin user is not a real superuser behaves the same: the master user on Amazon RDS and Aurora, a member of rds_superuser, the admin on Azure Database for PostgreSQL Flexible Server, a member of azure_pg_admin, and the admin on AlloyDB, a member of alloydbsuperuser. Use the same two patterns with that name.