BigQuery User Access Management

This guide explains how to manage BigQuery access through Terraform, including table-level access control to protect sensitive biometric data.

Overview

The BigQuery IAM module provides two levels of access control:

1. Admin Users: Dataset-level access to ALL tables (including sensitive biometric data)
2. Readonly Users: Table-level access to specific tables (excluding sensitive data like record)

This ensures that sensitive health data (biometric readings, hormone values) is protected while allowing broader access to operational data.

Important

🔑 Important: This module uses additive IAM members (not authoritative bindings), so it won't conflict with existing manually-granted permissions. No import needed!

Access Control Tiers

Tier 1: Admin Users (Full Dataset Access)

Admin users have dataset-level access to ALL tables, including sensitive biometric data. Use this for:

• Core team members who need access to all data
• Service accounts that process biometric data (e.g., KPI service)

# In production.tfvars
bigquery_admin_users = [
  "chip@eli.health",
  "iannick@eli.health",
  "thomas@eli.health",
  "fannie@eli.health"
]

bigquery_admin_service_accounts = [
  "kpi-service-us@eli-health-prod.iam.gserviceaccount.com"
]

Tier 2: Readonly Users (Table-Level Access)

Readonly users have table-level access to specific public tables only. They cannot access restricted tables like record which contains biometric data.

# In production.tfvars
bigquery_readonly_users = [
  "pious@eli.health",        # Customer support
  "media@videnglobe.com"     # Marketing team
]

Access Matrix (Production)

User Type	record (biometric)	user	reading	Other tables
Admin Users	✅ Full Access	✅	✅	✅
Admin Service Accounts	✅ Full Access	✅	✅	✅
Readonly Users	❌ No Access	✅	✅	✅

Quick Start

Adding an Admin User

Edit production.tfvars and add to bigquery_admin_users:

bigquery_admin_users = [
  "chip@eli.health",
  "iannick@eli.health",
  "thomas@eli.health",
  "fannie@eli.health",
  "new.admin@eli.health"  # Add new admin here
]

Adding a Readonly User (No Biometric Access)

Edit production.tfvars and add to bigquery_readonly_users:

bigquery_readonly_users = [
  "pious@eli.health",
  "media@videnglobe.com",
  "analyst@example.com"  # Add new readonly user here
]

2. Apply Terraform Changes

Run the following commands from the eli-devops/tf directory:

# Initialize and plan the changes
TMPDIR=~/terraform_tmp terraform init -backend-config=development.gcs.tfbackend
TMPDIR=~/terraform_tmp terraform plan -var-file=development.tfvars

# Apply the changes
TMPDIR=~/terraform_tmp terraform apply -var-file=development.tfvars -auto-approve

3. Verify Access

After applying, users will have access to:

• eli_health_biometricspublic dataset (default)
• Any additional datasets specified in bigquery_dataset_ids

Configuration Options

Available Variables

In production.tfvars, you can configure:

# Admin users - Full access to ALL tables including biometric data
bigquery_admin_users = [
  "chip@eli.health",
  "iannick@eli.health",
  "thomas@eli.health",
  "fannie@eli.health"
]

# Admin service accounts - Full access to ALL tables
bigquery_admin_service_accounts = [
  "kpi-service-us@eli-health-prod.iam.gserviceaccount.com"
]

# Readonly users - Access to public tables only (NOT restricted tables)
bigquery_readonly_users = [
  "pious@eli.health",        # Customer support
  "media@videnglobe.com"     # Marketing team
]

# Enable table-level access (required for biometric data protection)
bigquery_use_table_level_access = true

# Restricted tables - Only accessible by admin users
bigquery_restricted_tables = {
  record = {
    dataset_id       = "eli_health_biometricspublic"
    users            = []  # Access via bigquery_admin_users
    service_accounts = []  # Access via bigquery_admin_service_accounts
  }
}

# Public tables - Accessible by readonly users
bigquery_public_tables = [
  { dataset_id = "eli_health_biometricspublic", table_id = "user" },
  { dataset_id = "eli_health_biometricspublic", table_id = "reading" },
  { dataset_id = "eli_health_biometricspublic", table_id = "period" },
  # ... other public tables
]

# Optional: Grant metadata viewer access (recommended)
bigquery_grant_metadata_viewer = true

Permissions Granted

Admin Users

• Dataset-level Data Viewer - roles/bigquery.dataViewer - Full read access to all tables
• Job User - roles/bigquery.jobUser - Permission to run queries
• Metadata Viewer - roles/bigquery.metadataViewer - View dataset structure
• BigQuery User - roles/bigquery.user - Use BigQuery and Looker Studio

Readonly Users

• Table-level Data Viewer - roles/bigquery.dataViewer - Read access to public tables only
• Job User - roles/bigquery.jobUser - Permission to run queries
• Metadata Viewer - roles/bigquery.metadataViewer - View dataset structure
• BigQuery User - roles/bigquery.user - Use BigQuery and Looker Studio

Available Datasets

Based on your current setup, these datasets are available:

• eli_health_biometricspublic - Main health data from PostgreSQL including:
  • User profiles and settings
  • Reading table with test strip analysis results
  • Record table with full metadata (sensitive health measurements in JSON format)
  • Health goals, tags, and wakeup times
  • All application data replicated in real-time from PostgreSQL
• analytics_485791872 - Firebase Analytics data with user behavior and app usage statistics
• firebase_dev_export - Firebase export data including authentication and user management data

Biometric Data Protection

The record table in eli_health_biometricspublic contains sensitive biometric data including hormone values, classifications, and personal health interpretations.

Access Control (Implemented December 2024):

• This table is classified as restricted and is NOT accessible by readonly users
• Only admin users and admin service accounts can access this table
• Access is enforced at the GCP IAM level via Terraform
• Readonly users (e.g., board members, external analysts) are automatically blocked from querying this table

This implements the principle of least privilege for PHI/biometric data.

tip

To see all available datasets, check the BigQuery console or ask a team member with admin access.

User Instructions

Once access is granted, users can:

1. Access BigQuery Console: Go to BigQuery Console
2. Select Project: Choose "eli-health-dev" project
3. View Datasets: See granted datasets in the left sidebar
4. Run Queries: Use the SQL editor to query data

Sample Query for Users

-- View recent measurements from the health data
SELECT 
  id,
  created_at,
  measurement_type,
  value
FROM `eli-health-dev.eli_health_biometricspublic.measurements`
WHERE created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY created_at DESC
LIMIT 100;

Removing Access

1. Remove User Email - Remove their email from bigquery_readonly_users in development.tfvars
2. Apply Changes - Run terraform apply again to revoke access

Troubleshooting

User Can't See Datasets

• Verify the user email is correctly added to bigquery_readonly_users
• Ensure bigquery_grant_metadata_viewer = true is set
• Check that terraform apply completed successfully

User Can't Run Queries

• Verify roles/bigquery.jobUser is granted (automatic with this module)
• User must select the correct project (eli-health-dev) in BigQuery console

Access to Additional Datasets

• Add dataset IDs to bigquery_dataset_ids in the configuration
• Run terraform apply to grant access

Environment Considerations

• Development: Use development.tfvars and development.gcs.tfbackend
• Staging: Use staging.tfvars and staging.gcs.tfbackend (when available)
• Production: Use production.tfvars and production.gcs.tfbackend (when available)

warning

Always verify you're working with the correct environment before applying changes.

Existing Manual Permissions

Coexistence

Q: What happens to users I've already granted access manually?
A: Nothing! They keep their access.

This Terraform module uses additive IAM members (google_project_iam_member and google_bigquery_dataset_iam_member) instead of authoritative bindings.

Coexistence Strategy

You can have both:

• ✅ Terraform-managed users: Defined in bigquery_readonly_users
• ✅ Manually-managed users: Added directly in GCP Console

The module will only add/remove users that are defined in your Terraform configuration. Manual users remain untouched.