Brainforge Knowledge

rill-production-service-user-report

4 min read

Rill Cloud Production Credentials (service_user_report)

Purpose: Standardize production Snowflake credentials for Rill Cloud projects (delivery-analytics, finance-analytics) using service_user_report + role_report with key-pair auth.

This is the source of truth for:

  • where credentials live,
  • how to configure both production Rill projects,
  • how to rotate keys safely, and
  • how to onboard additional Rill Cloud projects.

1) Requirements and guardrails

  • Production Rill must use service_user_report with role_report (least privilege, read-only production marts database).
  • Never store Snowflake credentials (private keys, DSNs) in git.
  • Store secrets in:
    • 1Password (vault: Brainforge AI Team) for canonical secret material
    • Rill Cloud project variables (SNOWFLAKE_DSN) per project
  • Do not use personal user credentials for production dashboards.

Governance reference: snowflake/governance.md

2) Verify or create service_user_report in Snowflake

Run with a Snowflake admin connection (SECURITYADMIN):

USE ROLE SECURITYADMIN;
 
SHOW USERS LIKE 'SERVICE_USER_REPORT';
SHOW GRANTS TO USER SERVICE_USER_REPORT;
SHOW GRANTS OF ROLE ROLE_REPORT;

If missing, create from:

snow sql -c brainforge-internal -f standards/03-knowledge/engineering/setup/snowflake/create-service-users.sql

Then enforce production defaults:

USE ROLE SECURITYADMIN;
GRANT ROLE ROLE_REPORT TO USER SERVICE_USER_REPORT;
ALTER USER SERVICE_USER_REPORT SET DEFAULT_ROLE = ROLE_REPORT;
ALTER USER SERVICE_USER_REPORT SET DEFAULT_WAREHOUSE = WAREHOUSE_REPORT;

If legacy broader roles exist on this user, remove them after confirming no dependency:

-- Example cleanup if present:
REVOKE ROLE ROLE_PROD_READ FROM USER SERVICE_USER_REPORT;

3) Configure key-pair auth for service_user_report

Generate a dedicated key pair (do not reuse other service-user keys):

mkdir -p ~/.snowflake-keys-rill-prod
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out ~/.snowflake-keys-rill-prod/service_user_report_key.p8 -nocrypt
openssl rsa -in ~/.snowflake-keys-rill-prod/service_user_report_key.p8 -pubout -out ~/.snowflake-keys-rill-prod/service_user_report_key.pub
chmod 600 ~/.snowflake-keys-rill-prod/service_user_report_key.p8

Attach public key:

PUBLIC_KEY="$(python3 - <<'PY'
from pathlib import Path
text = Path.home().joinpath('.snowflake-keys-rill-prod/service_user_report_key.pub').read_text()
body = ''.join(line.strip() for line in text.splitlines() if '---' not in line)
print(body)
PY
)"
 
snow sql -c brainforge-internal -q "ALTER USER SERVICE_USER_REPORT SET RSA_PUBLIC_KEY='${PUBLIC_KEY}'"
snow sql -c brainforge-internal -q "ALTER USER SERVICE_USER_REPORT UNSET PASSWORD"

4) Store credentials (1Password + Rill Cloud)

Create or update a 1Password item in vault Brainforge AI Team, for example:

  • Snowflake service_user_report (Rill Prod)

Suggested fields:

  • account
  • username (service_user_report)
  • database (<PRODUCTION_MARTS_DB>)
  • schema (BRAINFORGE_RILL)
  • warehouse (WAREHOUSE_REPORT)
  • role (ROLE_REPORT)
  • private_key (full PEM content from service_user_report_key.p8)

Rill Cloud stores the assembled DSN in each project variable:

  • Project delivery-analytics → SNOWFLAKE_DSN
  • Project finance-analytics → SNOWFLAKE_DSN

5) Build DSN for Rill (key-pair auth)

Convert PEM private key to URL-safe string for DSN:

PRIVATE_KEY_URLSAFE="$(python3 - <<'PY'
from pathlib import Path
text = Path.home().joinpath('.snowflake-keys-rill-prod/service_user_report_key.p8').read_text()
body = ''.join(line.strip() for line in text.splitlines() if '---' not in line)
print(body.replace('+', '-').replace('/', '_'))
PY
)"

Build DSN:

SNOWFLAKE_DSN="service_user_report@<ACCOUNT_IDENTIFIER>/<PRODUCTION_MARTS_DB>/BRAINFORGE_RILL?warehouse=WAREHOUSE_REPORT&role=ROLE_REPORT&authenticator=SNOWFLAKE_JWT&privateKey=${PRIVATE_KEY_URLSAFE}"

6) Configure both Rill Cloud projects

Use a Rill user API token (service tokens cannot update project variables):

export PATH="$HOME/.rill:$PATH"
 
for project in delivery-analytics finance-analytics; do
  rill env set "$project" SNOWFLAKE_DSN "$SNOWFLAKE_DSN" \
    --org brainforge \
    --interactive=false \
    --api-token "$RILL_USER_TOKEN"
done

Alternative: set SNOWFLAKE_DSN in Rill Cloud UI → Project Settings → Variables.

7) Validation checklist (must pass for both projects)

7.1 Identity and role

for project in delivery-analytics finance-analytics; do
  echo "== $project =="
  rill query \
    --org brainforge \
    --project "$project" \
    --connector snowflake \
    --sql "select current_user() as current_user, current_role() as current_role, current_warehouse() as current_warehouse, current_database() as current_database" \
    --interactive=false \
    --api-token "$RILL_SERVICE_TOKEN"
done

Expected:

  • CURRENT_USER = SERVICE_USER_REPORT
  • CURRENT_ROLE = ROLE_REPORT
  • CURRENT_DATABASE = <PRODUCTION_MARTS_DB>

7.2 Data access check (<PRODUCTION_MARTS_DB>)

Run one project-appropriate query in each project that reads production marts and returns rows.

8) Rotate service_user_report key (zero downtime)

  1. Generate new key pair.
  2. Add new public key as secondary: 
    ALTER USER SERVICE_USER_REPORT SET RSA_PUBLIC_KEY_2='<new_public_key>';
  3. Build DSN with new private key.
  4. Update SNOWFLAKE_DSN in both projects.
  5. Validate both projects (Section 7).
  6. Promote/clean up key slots: 
    ALTER USER SERVICE_USER_REPORT SET RSA_PUBLIC_KEY='<new_public_key>';
ALTER USER SERVICE_USER_REPORT UNSET RSA_PUBLIC_KEY_2;
  7. Update 1Password item and remove retired private key from active use.

9) Add a new production Rill project

When adding a new project (for example ops-analytics):

  1. Deploy/create the project in Rill Cloud.
  2. Set project variable SNOWFLAKE_DSN using the same service_user_report DSN pattern.
  3. Validate with the identity query in Section 7.1.
  4. Add project-specific viewer groups/roles (RBAC doc in rill-setup.md).

Do not create new personal DSNs for production projects.

Graph View

Backlinks