# PBAC BigQuery Integration — GCP Setup Guide

Step-by-step instructions to set up a real BigQuery environment for PBAC integration testing and demos. This creates all the GCP infrastructure needed to run `demo/pbac_bigquery_demo.py` and the integration tests in `tests/ops/service/pbac/`.

## Prerequisites

- A Google Cloud account with billing enabled
- `gcloud` CLI installed (`brew install --cask google-cloud-sdk` on macOS)
- A domain you own (for Google Groups via Cloud Identity)

## 1. Authenticate

```bash
gcloud auth login
```

## 2. Create a GCP project

```bash
gcloud projects create <PROJECT_ID> --name="PBAC BigQuery Tests"
gcloud config set project <PROJECT_ID>
```

Link a billing account at:
```
https://console.cloud.google.com/billing/linkedaccount?project=<PROJECT_ID>
```

## 3. Enable APIs

```bash
gcloud services enable bigquery.googleapis.com \
  cloudidentity.googleapis.com \
  cloudresourcemanager.googleapis.com \
  --project=<PROJECT_ID>
```

## 4. Create BigQuery datasets, tables, and seed data

### Customer data (compliant queries target this)

```bash
bq mk --description "Test dataset for PBAC" --dataset <PROJECT_ID>:pbac_test_dataset

bq mk --table <PROJECT_ID>:pbac_test_dataset.customers \
  email:STRING,phone:STRING,name:STRING,address:STRING,created_at:TIMESTAMP

cat <<'EOF' > /tmp/customers.csv
email,phone,name,address,created_at
alice@example.com,555-0101,Alice Smith,123 Main St,2026-04-01T00:00:00Z
bob@example.com,555-0102,Bob Jones,456 Oak Ave,2026-04-01T00:00:00Z
carol@example.com,555-0103,Carol White,789 Pine Rd,2026-04-01T00:00:00Z
EOF

bq load --source_format=CSV --skip_leading_rows=1 \
  <PROJECT_ID>:pbac_test_dataset.customers /tmp/customers.csv
```

### Financial data (violation queries target this)

```bash
bq mk --description "Financial data - marketing only" --dataset <PROJECT_ID>:financial_data

bq mk --table <PROJECT_ID>:financial_data.transactions \
  transaction_id:STRING,amount:FLOAT,email:STRING,card_last4:STRING,created_at:TIMESTAMP

cat <<'EOF' > /tmp/transactions.csv
transaction_id,amount,email,card_last4,created_at
txn-001,1500.00,alice@example.com,4242,2026-04-01T00:00:00Z
txn-002,250.00,bob@example.com,1234,2026-04-01T00:00:00Z
EOF

bq load --source_format=CSV --skip_leading_rows=1 \
  <PROJECT_ID>:financial_data.transactions /tmp/transactions.csv
```

### Unregistered dataset (gap queries target this)

```bash
bq mk --description "Unregistered dataset for gap testing" --dataset <PROJECT_ID>:unregistered_dataset

bq mk --table <PROJECT_ID>:unregistered_dataset.activity_log \
  user_id:STRING,event_type:STRING,timestamp:TIMESTAMP

cat <<'EOF' > /tmp/activity_log.csv
user_id,event_type,timestamp
user-1,page_view,2026-04-01T00:00:00Z
user-2,click,2026-04-01T00:00:00Z
EOF

bq load --source_format=CSV --skip_leading_rows=1 \
  <PROJECT_ID>:unregistered_dataset.activity_log /tmp/activity_log.csv
```

### Generate query history

```bash
bq query --use_legacy_sql=false \
  "SELECT email, name FROM \`<PROJECT_ID>.pbac_test_dataset.customers\`"

bq query --use_legacy_sql=false \
  "SELECT * FROM \`<PROJECT_ID>.pbac_test_dataset.customers\` WHERE email = 'alice@example.com'"

bq query --use_legacy_sql=false \
  "SELECT name, address FROM \`<PROJECT_ID>.pbac_test_dataset.customers\` LIMIT 2"
```

## 5. Create a service account

```bash
gcloud iam service-accounts create pbac-test-sa \
  --display-name="PBAC Integration Test SA" \
  --project=<PROJECT_ID>
```

### Grant IAM roles

```bash
SA_EMAIL="pbac-test-sa@<PROJECT_ID>.iam.gserviceaccount.com"

gcloud projects add-iam-policy-binding <PROJECT_ID> \
  --member="serviceAccount:$SA_EMAIL" \
  --role="roles/bigquery.dataViewer" --condition=None --quiet

gcloud projects add-iam-policy-binding <PROJECT_ID> \
  --member="serviceAccount:$SA_EMAIL" \
  --role="roles/bigquery.jobUser" --condition=None --quiet

gcloud projects add-iam-policy-binding <PROJECT_ID> \
  --member="serviceAccount:$SA_EMAIL" \
  --role="roles/bigquery.resourceAdmin" --condition=None --quiet

gcloud projects add-iam-policy-binding <PROJECT_ID> \
  --member="serviceAccount:$SA_EMAIL" \
  --role="roles/browser" --condition=None --quiet
```

| Role | Why |
|------|-----|
| `bigquery.dataViewer` | Read table data |
| `bigquery.jobUser` | Run queries |
| `bigquery.resourceAdmin` | List all users' jobs (needed for `all_users=True`) |
| `browser` | Read IAM policy (needed for IAM role resolution) |

### Generate keyfile

If your org has an `iam.disableServiceAccountKeyCreation` constraint, you'll need to override it first:

```bash
# Check if key creation is blocked
gcloud iam service-accounts keys create /tmp/pbac-test-sa-key.json \
  --iam-account=$SA_EMAIL --project=<PROJECT_ID>

# If blocked, override at the org level (requires orgpolicy.policyAdmin)
ORG_ID=$(gcloud organizations list --format="value(ID)")
gcloud organizations add-iam-policy-binding $ORG_ID \
  --member="user:<YOUR_EMAIL>" --role="roles/orgpolicy.policyAdmin" --quiet
gcloud resource-manager org-policies disable-enforce \
  iam.disableServiceAccountKeyCreation --organization=$ORG_ID

# Wait ~60 seconds for propagation, then retry
gcloud iam service-accounts keys create /tmp/pbac-test-sa-key.json \
  --iam-account=$SA_EMAIL --project=<PROJECT_ID>
```

### Verify the SA works

```bash
python3 -c "
from google.oauth2 import service_account
from google.cloud import bigquery
creds = service_account.Credentials.from_service_account_file('/tmp/pbac-test-sa-key.json')
client = bigquery.Client(credentials=creds, project='<PROJECT_ID>')
jobs = list(client.list_jobs(max_results=5, all_users=True, state_filter='done'))
print(f'OK — {len(jobs)} jobs visible')
"
```

## 6. Set up Cloud Identity and Google Groups

This section is only needed for Google Groups identity resolution. Without it, IAM role resolution and service account matching still work.

### Set up Cloud Identity Free

1. Go to https://workspace.google.com/signup/gcpidentity/welcome
2. Sign up with your domain
3. Verify domain ownership via DNS TXT record
4. Log in as the domain admin: `gcloud auth login --account=admin@<YOUR_DOMAIN>`

### Find your customer ID

```bash
TOKEN=$(gcloud auth print-access-token)
curl -s -H "Authorization: Bearer $TOKEN" \
  -H "X-Goog-User-Project: <PROJECT_ID>" \
  "https://admin.googleapis.com/admin/directory/v1/users?domain=<YOUR_DOMAIN>&maxResults=1" \
  | python3 -c "import sys,json; print(json.load(sys.stdin)['users'][0]['customerId'])"
```

Save the customer ID (format: `C0xxxxxxx`) — you'll need it for group creation.

### Create Google Groups

```bash
CUSTOMER_ID="<CUSTOMER_ID>"

gcloud identity groups create "analytics-team@<YOUR_DOMAIN>" \
  --customer="$CUSTOMER_ID" \
  --display-name="Analytics Team" \
  --labels="cloudidentity.googleapis.com/groups.discussion_forum" \
  --project=<PROJECT_ID>

gcloud identity groups create "data-engineering@<YOUR_DOMAIN>" \
  --customer="$CUSTOMER_ID" \
  --display-name="Data Engineering" \
  --labels="cloudidentity.googleapis.com/groups.discussion_forum" \
  --project=<PROJECT_ID>

gcloud identity groups create "marketing-data@<YOUR_DOMAIN>" \
  --customer="$CUSTOMER_ID" \
  --display-name="Marketing Data" \
  --labels="cloudidentity.googleapis.com/groups.discussion_forum" \
  --project=<PROJECT_ID>
```

### Add members to groups

```bash
# analytics-team: analyst + admin
gcloud identity groups memberships add \
  --group-email="analytics-team@<YOUR_DOMAIN>" \
  --member-email="analyst@<YOUR_DOMAIN>" \
  --roles=MEMBER --project=<PROJECT_ID>

# data-engineering: engineer + admin
gcloud identity groups memberships add \
  --group-email="data-engineering@<YOUR_DOMAIN>" \
  --member-email="engineer@<YOUR_DOMAIN>" \
  --roles=MEMBER --project=<PROJECT_ID>

# marketing-data: marketer + admin
gcloud identity groups memberships add \
  --group-email="marketing-data@<YOUR_DOMAIN>" \
  --member-email="marketer@<YOUR_DOMAIN>" \
  --roles=MEMBER --project=<PROJECT_ID>
```

The domain admin (whoever created Cloud Identity) is automatically an owner of each group.

### Configure domain-wide delegation

This allows the service account to impersonate a domain admin and read group memberships. Domain-wide delegation is required because Cloud Identity group permissions cannot be granted via standard GCP IAM roles at the org level — the Groups API requires Workspace-level authorization.

1. Get the SA's OAuth2 client ID:

```bash
gcloud iam service-accounts describe $SA_EMAIL \
  --project=<PROJECT_ID> --format="value(oauth2ClientId)"
```

2. Go to https://admin.google.com (sign in as your domain admin)
3. Navigate to **Security > Access and data control > API controls > Manage Domain-wide Delegation**
4. Click **Add new**
5. Fill in:
   - **Client ID**: the OAuth2 client ID from step 1
   - **OAuth scopes**: `https://www.googleapis.com/auth/cloud-identity.groups.readonly,https://www.googleapis.com/auth/cloud-platform.read-only`
6. Click **Authorize**

### Verify group access

```bash
python3 -c "
from google.oauth2 import service_account
from google.auth.transport.requests import Request as AuthRequest
import requests

creds = service_account.Credentials.from_service_account_file('/tmp/pbac-test-sa-key.json')
scoped = creds.with_scopes(['https://www.googleapis.com/auth/cloud-identity.groups.readonly'])
scoped = scoped.with_subject('admin@<YOUR_DOMAIN>')
scoped.refresh(AuthRequest())

resp = requests.get(
    'https://cloudidentity.googleapis.com/v1/groups:lookup?groupKey.id=analytics-team@<YOUR_DOMAIN>',
    headers={'Authorization': f'Bearer {scoped.token}'}, timeout=30)
group_name = resp.json().get('name', '')
resp2 = requests.get(
    f'https://cloudidentity.googleapis.com/v1/{group_name}/memberships',
    headers={'Authorization': f'Bearer {scoped.token}'}, timeout=30)
members = [m['preferredMemberKey']['id'] for m in resp2.json().get('memberships', [])]
print(f'analytics-team members: {members}')
"
```

## 7. Run the demo

```bash
export BIGQUERY_KEYFILE_PATH=/tmp/pbac-test-sa-key.json
export BIGQUERY_DELEGATION_SUBJECT=admin@<YOUR_DOMAIN>

# Start the dev stack
nox -s dev

# Run the demo
python demo/pbac_bigquery_demo.py
```

Without Google Groups (IAM + SA matching only):

```bash
export BIGQUERY_KEYFILE_PATH=/tmp/pbac-test-sa-key.json
# No BIGQUERY_DELEGATION_SUBJECT — groups resolution is skipped

python demo/pbac_bigquery_demo.py
```

## 8. Run the integration tests

### Via nox (recommended)

```bash
export BIGQUERY_KEYFILE_PATH=/tmp/pbac-test-sa-key.json
export BIGQUERY_DELEGATION_SUBJECT=admin@<YOUR_DOMAIN>

nox -s pytest -- tests/ops/service/pbac/ -v -m integration_bigquery
```

### Via Docker

```bash
export BIGQUERY_KEYFILE_PATH=/tmp/pbac-test-sa-key.json
export BIGQUERY_DELEGATION_SUBJECT=admin@<YOUR_DOMAIN>

# Copy keyfile into the container
docker cp /tmp/pbac-test-sa-key.json fidesplus-slim:/tmp/pbac-test-sa-key.json

# Run tests (pass credentials as env var to avoid permission issues)
KEYFILE_JSON=$(cat /tmp/pbac-test-sa-key.json)
docker exec \
  -e BIGQUERY_KEYFILE_CREDS="$KEYFILE_JSON" \
  -e BIGQUERY_DELEGATION_SUBJECT=admin@<YOUR_DOMAIN> \
  fidesplus-slim \
  bash -c "pytest --no-cov tests/ops/service/pbac/ -v -m integration_bigquery"
```

Without credentials, all integration tests skip automatically:

```bash
docker exec fidesplus-slim \
  bash -c "pytest --no-cov tests/ops/service/pbac/ -v -m integration_bigquery"
# All tests show SKIPPED
```

## 9. Customizing for your environment

The demo script and integration tests read all domain/group/member info from env vars. Set the `BIGQUERY_DOMAIN`, `BIGQUERY_TEST_ANALYTICS_GROUP`, `BIGQUERY_TEST_ENGINEERING_GROUP`, `BIGQUERY_TEST_GROUP_MEMBER`, and `BIGQUERY_TEST_ANALYTICS_MEMBER` env vars for your environment.

Consumer types use `scope: dict[str, str]` for identity:
- `google_group`: `{"domain": "your-domain.com", "group_email": "team@your-domain.com"}`
- `gcp_iam_role`: `{"domain": "your-domain.com", "project_id": "your-project", "role": "roles/bigquery.dataViewer"}`
- `gcp_service_account`: `{"domain": "your-domain.com", "email": "sa@your-project.iam.gserviceaccount.com"}`

## Teardown

To remove everything:

```bash
# Delete the GCP project (removes all resources inside it)
gcloud projects delete <PROJECT_ID>

# Delete Google Groups (if you want to keep Cloud Identity)
gcloud identity groups delete "analytics-team@<YOUR_DOMAIN>" --project=<PROJECT_ID>
gcloud identity groups delete "data-engineering@<YOUR_DOMAIN>" --project=<PROJECT_ID>
gcloud identity groups delete "marketing-data@<YOUR_DOMAIN>" --project=<PROJECT_ID>

# Close Cloud Identity entirely (optional)
# Go to admin.google.com > Account > Account settings > Delete account

# Clean up local files
rm /tmp/pbac-test-sa-key.json
rm /tmp/customers.csv /tmp/transactions.csv /tmp/activity_log.csv
```
