NLQueries GitHub

Blog  / 

Natural-language BigQuery without service account keys: workload identity federation and impersonation

Published . Written for nlqueries-core 0.4.

bigquery security google-cloud

BigQuery workload identity federation lets a program outside Google Cloud reach BigQuery without a service account key, and from nlqueries-core 0.4 the BigQuery connector accepts it. This post covers the three ways the connector can authenticate without a key file: Application Default Credentials, a federation credential configuration, and impersonating one narrowly granted service account per connector. It also covers the IAM grant, which on BigQuery is the main control over what a generated query can do.

Why a key file is the wrong default

A service account key is a long-lived secret. It does not expire unless someone rotates it. It works from any machine that holds a copy, and it tends to end up on several: a laptop, a CI variable, a container image somebody built in a hurry. Whoever has the file is the service account until the key is deleted.

For a tool that turns questions into SQL, that matters more than usual. The connector runs statements a model wrote, and BigQuery gives it nothing to undo them with. A single query job has no transaction, so the connector logs a statement type other than SELECT after the job has run, which is an audit signal and not a control. The hardening guide says it plainly: on BigQuery, the privilege granted to the identity is the whole defence. So it is worth knowing exactly which identity that is, and making it one that cannot leak as a file.

How the connector chooses its credentials

The choice is made by what the service_account_json key holds in the connector's entry in ~/.nlqueries/connectors.yaml. BigQuery: authentication has the table:

  • Omitted: Application Default Credentials (ADC). The Google client library finds them, and the connector reads nothing itself.
  • A service account key ("type": "service_account"): that service account.
  • A workload identity federation configuration ("type": "external_account"): the federated identity, with no key.

The value may be a path to the JSON file, which is what --service-account-json stores, or the parsed JSON itself. The connector reads the file's type to decide. Any other type goes to the service-account loader, which refuses it.

Inside Google Cloud: Application Default Credentials

On a workstation, a GKE pod or a Cloud Run service, you usually need no file at all. Leave out --service-account-json:

pip install "nlqueries-core[bigquery]"
nlqueries connect bigquery --project-id acme-prod --dataset-id analytics --alias bq

On a workstation, gcloud auth application-default login supplies the credentials. On GKE or Cloud Run, the workload identity of the pod or service supplies them. GOOGLE_APPLICATION_CREDENTIALS is honoured too. BigQuery has no password, and --project-id is the only required flag. connect tests the connection before it prints the connector ID it registered.

Set --dataset-id if your data is outside the US multi-region. Query history is read from one region of INFORMATION_SCHEMA.JOBS_BY_PROJECT: the dataset's location when one is given, region-us otherwise. With a dataset set, process-history also keeps only jobs that read a table in that dataset (query history region).

Outside Google Cloud: workload identity federation

Workload identity federation is a Google Cloud feature. You create a workload identity pool that trusts an external identity provider, such as AWS, Azure, GitHub Actions, or another OIDC or SAML provider. A workload there then exchanges the token it already has for a short-lived Google one. Google's workload identity federation documentation covers setting up the pool and the provider.

The result, on the nlqueries side, is a credential configuration file. Generate it with gcloud iam workload-identity-pools create-cred-config, then pass it where a key would go:

nlqueries connect bigquery --project-id acme-prod --dataset-id analytics \
  --service-account-json /etc/nlqueries/bq-federation.json --alias bq

The file holds no secret of its own. What it holds is credential_source: where to fetch the external token when the connector first authenticates. That can be a local file, a URL or an executable, and google-auth acts on it. So treat the configuration as you would a key, and use only one you generated yourself. An executable source runs only when GOOGLE_EXTERNAL_ACCOUNT_ALLOW_EXECUTABLES=1 is set. A configuration that names a command is a way to run that command, and the library will not do it unless you opt in.

The bigquery extra requires google-auth 2.20 or later in 0.4, the first release that can load a federation configuration from parsed JSON.

One account for every query: impersonation

Federation and ADC answer the question "who is starting this?". That might be your own Google account, a CI job, or a VM. Often you want every query to run as the same narrowly granted account, whoever started it. That is what impersonate_service_account does.

Register the connector, then add the key to the entry connect wrote:

bigquery:localhost:acme-prod:
  db_type: bigquery
  # ... the other keys `connect` wrote ...
  project_id: acme-prod
  impersonate_service_account: [email protected]

The connector then takes whichever credential it resolved (key, federation configuration or ADC) and uses it only to mint short-lived tokens for that account. The tokens carry only the BigQuery scope. The starting identity needs roles/iam.serviceAccountTokenCreator on the target service account.

Two details from the connector's code are worth knowing:

  • An empty or malformed value is refused, not ignored. If impersonate_service_account is present but empty, or is not an email, the connector refuses to open. Ignoring it would run every query as the starting identity, which is usually broader than the account you meant.
  • connect rewrites the whole entry. There is no connect flag for impersonation in 0.4, so running nlqueries connect bigquery again for the same connector drops the key. Add it back afterwards.

Then check it:

nlqueries health --connector bq
nlqueries ask bq "How many orders were placed last month?"

ask generates and validates SQL without running it, which is a good first step with a new identity.

What to grant

The identity that queries run as needs:

  • roles/bigquery.dataViewer on the datasets you want answered. Grant it per dataset, not on the project.
  • Permission to run query jobs in the project (bigquery.jobs.create, which roles/bigquery.jobUser carries). Every question is a query job, so this one cannot be left out. Together with dataViewer it reads and does not write.
  • bigquery.jobs.listAll if you run process-history, for example through roles/bigquery.resourceViewer. Reading history from JOBS_BY_PROJECT requires it.

Do not give it anything that can create, load or copy tables. The SQL policy in front of the connector refuses writes and DDL, but it is application code. On BigQuery, the grant is the only control on the database side.

Two budgets bound what a permitted query can cost:

  • CONNECTOR_STATEMENT_TIMEOUT_SECONDS (120 by default) becomes the job's job_timeout_ms.
  • BIGQUERY_MAX_BYTES_BILLED, new in 0.4, is carried on every job that answers a question as maximum_bytes_billed. BigQuery then fails a job that would bill more, before it runs. It is unset by default, which means no cap, so set it. Schema and history reads are not capped (cost cap).

Summary

  1. Inside Google Cloud, omit --service-account-json and use ADC.
  2. Outside it, use a workload identity federation configuration you generated, in place of a key.
  3. Add impersonate_service_account so every query runs as one reader account.
  4. Grant that account dataViewer per dataset and the right to run jobs, and nothing that writes.
  5. Set BIGQUERY_MAX_BYTES_BILLED.

The BigQuery page has the quick start. For the layers that sit in front of the grant, see Letting an LLM query production safely. To try it, pip install "nlqueries-core[bigquery]" and start with nlqueries ask.

All posts · RSS feed · Documentation