# snowflake

One connection is one Snowflake account. toolpass authenticates as a service user with a key pair
through the SQL REST API, finds the user with `SHOW USERS`, lists the roles granted to the user with
`SHOW GRANTS TO USER`, walks the role hierarchy with `SHOW GRANTS TO ROLE` (database roles
included), and looks for the privilege the question needs on the object, with `OWNERSHIP` counting
for everything, plus `USAGE` on the object's database and schema. Only `SHOW` commands run, which
need no warehouse. Nothing is written.

## Credential

A **service user** with an RSA key pair: the public key registered on the user
(`ALTER USER TOOLPASS SET RSA_PUBLIC_KEY = '...'`), the unencrypted private key as `credential`.
toolpass signs a key-pair JWT (`iss` `<ACCOUNT>.<USER>.SHA256:<fingerprint>`, `sub`
`<ACCOUNT>.<USER>`, 50-minute lifetime) the way the official drivers do.

The user's `role` must be able to run `SHOW GRANTS TO USER` and `SHOW GRANTS TO ROLE` for **other**
users and roles, which takes the **MANAGE GRANTS** privilege: `SECURITYADMIN`, or a custom role:

```sql
CREATE ROLE TOOLPASS_READER;
GRANT MANAGE GRANTS ON ACCOUNT TO ROLE TOOLPASS_READER;
GRANT ROLE TOOLPASS_READER TO USER TOOLPASS;
```

`MANAGE GRANTS` also lets its holder grant and revoke; there is no read-only equivalent for live
grants. The `SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS` and `GRANTS_TO_ROLES` views are read-only but
lag up to two hours and need a warehouse; a `source: account_usage` mode reading them is not
implemented here.

`toolpass probe` lists toolpass's own roles and warns when none holds `MANAGE GRANTS`.

Signing with the key pair needs `cryptography`: install `toolpass[crypto]` (the Docker image has it).

## Connection

```yaml
  - id: snowflake-prod
    integration: snowflake
    account: myorg-myaccount
    user: TOOLPASS
    role: SECURITYADMIN
    credential: file:/secrets/snowflake-toolpass.p8
    # url: https://myorg-myaccount.snowflakecomputing.com
```

| Key | Meaning |
|---|---|
| `account` | the account identifier (`myorg-myaccount`, or a legacy locator `xy12345.us-east-1`) |
| `user` | the service user |
| `role` | the role statements run as |
| `credential` | the RSA private key (PEM, PKCS#1 or PKCS#8, unencrypted), `env:` or `file:` |
| `url` | the account URL; default `https://<account>.snowflakecomputing.com` (underscores in the account become hyphens); https, or http on loopback only |

### Identity

`SHOW USERS LIKE '<email>'` first (SCIM-provisioned users are named by their address; the `LIKE`
wildcards are escaped), then a page-by-page `SHOW USERS LIMIT 10000 [FROM '<name>']` scan matching
`name`, `login_name` or `email` exactly (ignoring case). A match on `name` or `login_name`, which
only the user's owner can set, outranks a match on `email`, which users may set on themselves; none
is `user_not_found`, two of equal rank are `user_ambiguous`. A disabled user is denied every action.
The identity's groups are the roles `SHOW GRANTS TO USER` lists (both the classic `role` column and
the 2025 shape with `granted_on ROLE` are read). Groups sent by the caller are ignored.

### Roles

The answer is the union of every role granted to the user, directly or through other roles and
database roles, plus `PUBLIC`, which every user holds: a user can activate any of them, and with
`DEFAULT_SECONDARY_ROLES = ('ALL')` a session holds all of them at once. `SHOW GRANTS TO ROLE <r>`
rows with `granted_on ROLE` (or `DATABASE_ROLE`) and privilege `USAGE` are followed; the walk stops
at 500 roles and is cached for two minutes per role.

## Resources

| Resource | Name |
|---|---|
| `table:<db>.<schema>.<name>` | a table, view, materialized view, dynamic, external, event, Iceberg or hybrid table |
| `schema:<db>.<schema>` | a schema |
| `database:<db>` | a database |
| `warehouse:<name>` | a warehouse |
| `role:<name>` | a role |
| `account` | the account |

Identifiers follow Snowflake's rules: unquoted parts resolve to upper case, `"quoted"` parts keep
their case and may hold any character but control characters (`""` for a quote). Every part is
validated and re-quoted before it appears in a statement, and the caller's name never reaches a
statement at all: it is compared against `SHOW GRANTS` output.

## Actions

| Action | Resource | Privilege |
|---|---|---|
| `table.select` / `insert` / `update` / `delete` / `truncate` | `table:` | `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE` |
| `schema.usage` / `schema.create_table` / `schema.create_view` | `schema:` | `USAGE`, `CREATE TABLE`, `CREATE VIEW` |
| `database.usage` / `database.create_schema` | `database:` | `USAGE`, `CREATE SCHEMA` |
| `warehouse.usage` / `operate` / `modify` | `warehouse:` | `USAGE`, `OPERATE`, `MODIFY` |
| `role.use` | `role:` | the role is granted, directly or through other roles |
| `account.create_database` / `account.manage_grants` | `account` | `CREATE DATABASE`, `MANAGE GRANTS` |
| `raw:<PRIVILEGE>` | any typed resource | the privilege, spelled with underscores (`raw:CREATE_STAGE`) |

`OWNERSHIP` of the object answers every question on it. An object in a schema also needs `USAGE`
(or `OWNERSHIP`) on its database and schema, which toolpass checks; a grant on the object without
them answers `denied` and says which `USAGE` is missing.

## Decisions

| Code | When |
|---|---|
| `allowed` | a reachable role holds the privilege (or `OWNERSHIP`) on the object, and `USAGE` on its parents |
| `denied` | no reachable role holds it (or the object does not exist: `SHOW GRANTS` says nothing about existence); `USAGE` on a parent is missing; the user is disabled |
| `unsupported` | `SHOW USERS` did not report `disabled`; more than 500 roles |
| `resource_not_visible` | a granted role's grants cannot be read by toolpass's role (Snowflake error 002003) |
| `user_not_found` / `user_ambiguous` | the user search |
| `credential_rejected` | the key-pair token is rejected (401, error 390144), the private key does not parse, or toolpass's role lacks the privilege for a `SHOW` (error 003001) |
| `invalid_request` | a malformed identifier, the wrong number of name parts, a resource type the action does not take |
| `upstream_*` | 5xx, 429, a statement that does not finish, other Snowflake errors (reported by code only; the message may echo identifiers) |

## Intercepted requests

A toolpass tool's requests to the SQL API (`POST /api/v2/statements`) and the Python connector
(`POST /queries/v1/query-request`) are read statement by statement
(`src/toolpass/integrations/snowflake/routes.py`, `toolpass routes snowflake`). One statement on one
table is checked: `SELECT ... FROM <table>` as `table.select`, `INSERT INTO <table> ... VALUES` as
`table.insert`, `UPDATE` as `table.update`, `DELETE FROM` as `table.delete`, `TRUNCATE` as
`table.truncate`, on `table:DB.SCHEMA.NAME`. On the SQL API an unqualified name takes the body's
`database` and `schema` (upper-case names only); through the connector the name must be fully
qualified, because the session's defaults are not in the request.

Everything else is refused: several statements, comments, joins, subqueries and set operations,
`IDENTIFIER()` and `TABLE()`, stages, variables, functions other than a list of built-ins (a
user-defined function reads with its owner's rights), a non-reserved word before `(` outside the one
place it is not a call (a type such as `NUMBER(10, 2)` only after `::` or after `AS` inside `CAST`
/ `TRY_CAST`; `OVER (` after a built-in's arguments; `EXCLUDE (` / `RENAME (` after the select list's
`*`), quoted names with lower-case letters, a backslash in a string, session parameters other than
output formats and tags, and a connector request whose `describedJobId` is set (the query context the
connector echoes between queries is accepted). A `SELECT` with no table needs no check. The warehouse a
statement runs on is not checked.

Results are read only from the response to the statement itself. Reading a statement's results or
status by handle (`GET /api/v2/statements/{handle}`, the connector's `GET /queries/{id}/result` and
`GET /monitoring/queries/{id}`) is refused: the agent's credential is shared, so a handle may name
any statement run with it, and toolpass cannot tie it to one it checked. A statement must therefore
finish within its own request: on the SQL API, without `async=true` and within the wait the API
gives a synchronous call (a statement still running comes back `202` with a handle that cannot be
read), and with its rows in the first partition (later partitions are read by handle; use `LIMIT`).
Through the connector, use synchronous execution (`execute`, not `execute_async`) on queries that
finish before the server tells the connector to poll. Cancelling by handle is not checked.

## What it cannot see

- **Future grants** before an object exists, **masking and row access policies**, **secure
  views'** underlying access, **network policies**, **session policies** and **authentication
  policies**: a `SELECT` grant may still return masked or filtered data.
- **Which role a session activates**: the answer is the union over all granted roles.
- **Object existence**: `SHOW GRANTS` does not say whether the object exists.
- **Application roles, share grants and the `ACCOUNT_USAGE` views**.
- **A user changing their own `EMAIL`** can make an address match two users; the owner-set `name`
  and `login_name` win, and an address that matches only by `email` is trusted.

## Unverified

Written from the SQL API specification, the official drivers' key-pair code and the documentation
of `SHOW GRANTS`, `SHOW USERS` and access control; not run against a live account. Marked
`UNVERIFIED` in the code where it matters:

- `USAGE` on the database and schema being required to reach an object in them (standard behaviour,
  not quoted from the docs).
- The `name` column's spelling of database roles (`<database>.<role>`) in `SHOW GRANTS TO ROLE`.
- The exact `SHOW GRANTS TO USER` columns after the 2025_01 change bundle; both shapes are read.

## Test

`python -m pytest tests/integrations/snowflake` (in `toolpass-py`) runs a fake SQL API validated against the
specification when `TOOLPASS_SPECS_DIR` holds `snowflake-sqlapi.spec` (`test/specs/fetch.sh`). The
fake verifies the key-pair JWT's signature and claims with the test key, answers `SHOW USERS` (with
`LIKE`, `LIMIT` and `FROM` paging), `SHOW GRANTS TO USER` in both shapes, `SHOW GRANTS TO ROLE` and
`SHOW GRANTS TO DATABASE ROLE`, and can answer asynchronously (202 and polling) and in partitions.
Property tests (`test_fuzz_parse_target`, `test_fuzz_identifier`) check that only well-formed,
safely quoted identifiers are accepted.