How to Configure BigQuery Backup User
You set up a bigquery backup user by making a dedicated service account. Then you grant that account read roles on datasets and write roles on storage. Data teams often rush this setup when they build their first export jobs. Many admins assign broad project owner or editor roles just to make backups run. Overly broad roles create a major risk if an attacker steals that service key. A compromised admin key lets an attacker delete production tables or steal private records. A safe backup setup l
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 a bigquery backup user by making a dedicated service account. Then you grant that account read roles on datasets and write roles on storage.
Data teams often rush this setup when they build their first export jobs. Many admins assign broad project owner or editor roles just to make backups run. Overly broad roles create a major risk if an attacker steals that service key. A compromised admin key lets an attacker delete production tables or steal private records. A safe backup setup limits the account to the exact tasks it must do.
How to set up a bigquery backup user
Start by creating a distinct service account inside your cloud project for backup jobs. Never reuse an account that runs web apps or daily analyst queries. This separation keeps your audit logs clean and makes permission reviews much simpler.
Next, assign the right roles to your new bigquery backup user. The account needs permission to read table metadata and rows from your source datasets. It also needs permission to start export jobs inside the project. If you use table snapshots or clones, the account needs write access on a backup dataset. If you export data to cloud buckets, grant write permissions on that target bucket only.
Grant these permissions at the dataset level instead of the whole project level. Dataset roles stop the backup account from reading unrelated tables in other workspaces. You should also avoid static JSON key files whenever your setup allows it. Use workload identity federation or attached service accounts for your scheduled backup runners. Short-lived credentials cut down the risk of leaked keys sitting on developer laptops.
What native roles solve and where gaps remain
Cloud IAM roles do a good job of deciding who can log in. They verify the identity of the backup account and check its high-level resource permissions. Native controls also block the account from changing billing settings or deleting whole projects. This setup handles the basic identity layer for your scheduled automated jobs.
Still, static cloud roles leave a wide operational gap during live sessions. Once an account passes the login check, IAM does not inspect individual SQL statements. A backup role with dataset read rights can pull every column in every table by default. That includes sensitive fields like emails, phone numbers, card numbers, and national IDs. If an engineer uses those backup credentials to debug a job, they get full read power.
User Provisioning (SCIM) + BigQuery IAM: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Native roles also lack approval workflows for ad-hoc backup runs or manual data exports. You cannot require a peer check before someone runs a huge table export. You also cannot mask sensitive fields in flight when a backup script reads a table. Managing dozens of custom roles across many projects also creates heavy work for platform teams. Admins often fall back to broad roles when dataset permissions become too hard to track.
Separating automated backup jobs from human debug access
Many security gaps happen when humans share accounts meant for automated backup pipelines. When a nightly export fails, an engineer often grabs the service account key to test queries. Now a human operator is running ad-hoc queries under a machine identity. Cloud logs will show the service account name instead of the actual person's email.
You can fix this by separating machine pipelines from human fixing sessions. Automated schedulers should run with scoped machine identities that humans never touch directly. When engineers need to check backup tables or test export queries, they should use their own identities. Routing human sessions through a central control point lets you apply query rules and data masking. Masking rewrites matched fields like card numbers or emails in query results before they reach the screen. You also get clear logs that tie every query to a specific person.
Check database and API queries on the wire
Identity tools, roles, and network rules decide who opens a session. Setup alone cannot check what each query does or mask sensitive values in a response once a session starts. When workflows touch Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, or internal HTTP APIs, hoop.dev sits inline in the wire protocol beside those services as an open-source proxy, a sidecar for your Postgres or API. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.
Your login details and client tools stay as they are while hoop.dev guardrails refuse bad deletes or drops before they run, and hoop.dev data masking rewrites matched sensitive values in responses in memory. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and writes every query and decision to an audit trail that generates evidence for HIPAA, GDPR, and PCI DSS. See the source on GitHub.
Frequently asked questions
Should a bigquery backup user have project editor permissions?
No, project editor permissions are much too broad for a backup account. Grant read access on source datasets, job runner rights, and write access on the backup target.
How should we authenticate scheduled backup scripts?
Avoid downloading static JSON keys whenever you can. Use attached service accounts on cloud compute runners or workload identity federation for external schedulers.
How do we protect sensitive data during backup troubleshooting?
Do not let engineers share the backup service account key for manual debugging. Require engineers to log in with personal accounts and apply dynamic data masking on sensitive columns.
Related guides
In this post