How to Configure Cloud SQL Drop Table Protection

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up cloud sql drop table protection with strict role rules and event triggers. You also separate table ownership from the accounts that run daily queries. Many engineering teams start by giving developers the default admin account for fast fixes. Broad database roles let anyone delete production tables with a single bad command. When every engineer shares the owner role, the database cannot stop destructive schema changes. How native cloud sql drop table protection works Cloud SQL for

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up cloud sql drop table protection with strict role rules and event triggers. You also separate table ownership from the accounts that run daily queries. Many engineering teams start by giving developers the default admin account for fast fixes. Broad database roles let anyone delete production tables with a single bad command. When every engineer shares the owner role, the database cannot stop destructive schema changes.

How native cloud sql drop table protection works

Cloud SQL for PostgreSQL is a managed service with its own role rules. Customers do not get a true database superuser account on this platform. A Google-managed system user named cloudsqladmin holds the actual superuser privileges. Customers cannot delete or change any of these built-in system roles. Instead, you get the cloudsqlsuperuser role, which holds specific high-level privileges. The default postgres admin user belongs to this group and can create roles and databases. It does not have full superuser or replication attributes, and it starts without a password. You must set a password for the default user before you can log in.

To protect tables natively, you must control who owns the database objects. In PostgreSQL, only the table owner or a privileged admin role can drop a table. You can build a dedicated migration role that owns all production schemas and tables. Then you create separate user accounts for your team and your app. You grant those daily accounts permission to read and write rows, but not ownership. Because the daily accounts do not own the tables, the database blocks their drop statements.

Using event triggers for schema rules

Ownership separation works well for standard users, but admins still need a safety net. The cloudsqlsuperuser role has the specific privilege needed to create database event triggers. An event trigger fires when someone runs a schema change command in the database. You can write a trigger function that checks for drop table commands and fails the transaction. This stops accidental table deletions even when an engineer logs in with an admin role.

Still, an admin with the cloudsqlsuperuser role can disable or drop the event trigger itself. Native database roles also apply the same static rules to every session from that user. If a deployment tool needs to drop a temporary table, you must grant ownership back. That opens a window where a flawed script can drop the wrong table.

End-to-End Encryption + SQL Query Filtering: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Where static database roles fall short

Native role setups decide who can log in and what objects they own. They do not inspect individual statements in flight or ask for human approval. When an engineer needs to fix a broken index, they often use the admin role. Once logged in as the table owner, nothing stops a typo from dropping critical data. Teams also struggle to protect sensitive columns when engineers run manual queries during an incident. Masking rules that rewrite emails, phone numbers, card numbers, and national IDs in results are missing. To close this gap, teams need controls that check each command before it runs.

Govern statements and responses across databases and APIs

Good config and scoped roles are needed setup. They do not check single queries in flight or mask sensitive fields in returned rows. Across Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, and internal HTTP endpoints, hoop.dev sits inline in the protocol beside each supported service as an open-source proxy, a sidecar for your database or API. For every query or command an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human approval.

First, hoop.dev guardrails block banned queries before they run or hold risky ones for review, and hoop.dev data masking rewrites matched sensitive values in responses in memory. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

Can the default postgres user drop any table in Cloud SQL?

Yes, the default postgres user belongs to the cloudsqlsuperuser role. It can drop user tables across the database unless an active event trigger blocks the command.

Do customers get a true PostgreSQL superuser in Cloud SQL?

No, customers cannot create or use accounts with true superuser attributes. Google reserves the cloudsqladmin system user for managed maintenance, while customers use cloudsqlsuperuser.

How can I stop apps from dropping tables in production?

Create a separate role to own your tables and run schema migrations. Give your app user role permission to read and write data without granting table ownership.

The query runs. The emails stay hidden.