> ## Documentation Index
> Fetch the complete documentation index at: https://docs.curator.interworks.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Creating a Snowflake Connection

> Let HTML pages in Curator run their own SQL in Snowflake as the signed-in viewer, with no second sign-in and no stored credential.

## Overview

A Snowflake connection lets a page in Curator run SQL in Snowflake as the viewer who is signed in to Curator.
Curator signs a short-lived token for that viewer, Snowflake verifies it against the portal's published keys and
maps it to the viewer's own Snowflake user, and the query runs under the connection's one role and that user's
row access policies. The viewer never signs in to Snowflake, the page never sees a token, and Curator stores no
Snowflake password, key, or token.

Snowflake, not Curator, decides what a viewer can see. Curator does not inspect the SQL, and a signed-in viewer
can send any statement to the connection. Every query is pinned to one role, and Snowflake refuses a token for
any other role, so a viewer can never use the rights of their own default role through Curator. Two settings in
Snowflake therefore carry the security of the integration:

* **The role's grants** decide which tables and views a viewer can query at all. Grant only `SELECT` on what
  every viewer may see, and nothing that writes.
* **Row access policies** on `CURRENT_USER()` decide which rows of those tables each viewer gets. Do not rely on
  the page's SQL or its `bindings` to filter rows, because the viewer controls both.

## Prerequisites

* Every viewer needs a Snowflake user whose **login name** (`LOGIN_NAME`) equals their Curator username, with a
  default warehouse set on that user, and that user must be granted the connection's role. Curator matches on the
  username because it is unique in Curator and no platform sync rewrites it; an email can be shared or changed. To
  align an existing user, run `ALTER USER <name> SET LOGIN_NAME = $$<Curator username>$$`. The `$$` quotes keep
  the name exactly as typed, including the backslash in a domain username such as `CORP\jane`. The login name is
  also what that person types to sign in to Snowflake directly. Viewers without a matching user get a clear error,
  never someone else's rows.
* Snowflake must be able to fetch the portal's public keys at `https://<portal>/user/embedauth/jwks.json`. SaaS
  portals qualify as they are. An on-prem portal behind a firewall needs that one path reachable from Snowflake,
  or uses the pasted-key variant described below.
* Portals that use pass-through authentication cannot host a Snowflake connection, because every visitor there
  shares one identity.

## Creating the connection

1. In the backend, go to **Integrations** > **Connections** and click **New**.
2. Choose **Snowflake** as the platform and fill in the fields:

| Field | What to enter |
| - | - |
| **Account URL** | The `https` URL of the Snowflake account, for example `https://myorg-myaccount.snowflakecomputing.com`. Underscores in the account name become hyphens in the URL. No path or trailing slash. Tokens name this value as their audience. |
| **Role** | The one Snowflake role every query runs under, for example `CURATOR_READER`. The setup statement allows only this role. See [Setting up the role and row access](#setting-up-the-role-and-row-access). |
| **Statement timeout** | Seconds before Snowflake cancels a statement, 5 to 45. |
| **Row limit** | Results with more rows are refused with a clear error rather than truncated. |

3. Save the connection. One Snowflake connection exists per portal.

## Registering the portal in Snowflake

With the connection saved, the **Snowflake setup** section of the form shows two `CREATE SECURITY INTEGRATION`
statements with the portal's issuer, key source, and the account URL filled in. An ACCOUNTADMIN runs one of them
once in Snowsight:

* The **JWKS URL** variant fetches the portal's public keys from the portal. Keys can rotate on the portal
  without a change in Snowflake. Use this for portals Snowflake can reach.
* The **pasted public key** variant embeds the current key. Use it for portals Snowflake cannot reach, and run it
  again if the portal's signing key is ever regenerated.

Both map the token's `preferred_username` claim (the Curator username) to the Snowflake user's login name,
disable any-role mode, and set `EXTERNAL_OAUTH_ALLOWED_ROLES_LIST` to the connection's role. Snowflake then accepts a token only for that one role,
even a token signed with a leaked portal key. Run the statement again after changing the connection's role, or
Snowflake keeps refusing the new one.

The integration is named after the portal's URL, for example `CURATOR_OAUTH_1A2B3C4D`, and the form shows the exact
name. A staging and a production portal can therefore share one Snowflake account: running one portal's statement
replaces only that portal's integration. If the portal's URL changes, the statement gets a new name. Run it, then
drop the old integration with `DROP SECURITY INTEGRATION <old name>`.

## Setting up the role and row access

Run these once in Snowsight. Replace the database, schema, table, warehouse, and user names with your own.

```sql theme={null}
-- One read-only role for every query that comes from Curator.
CREATE ROLE CURATOR_READER;
GRANT USAGE ON WAREHOUSE curator_wh TO ROLE CURATOR_READER;
GRANT USAGE ON DATABASE governed TO ROLE CURATOR_READER;
GRANT USAGE ON SCHEMA governed.public TO ROLE CURATOR_READER;
GRANT SELECT ON TABLE governed.public.sales TO ROLE CURATOR_READER;

-- Every Curator viewer's Snowflake user holds the role.
GRANT ROLE CURATOR_READER TO USER jane;

-- Each viewer sees only the regions listed for them in a mapping table.
CREATE ROW ACCESS POLICY governed.public.sales_by_viewer
  AS (sales_region VARCHAR) RETURNS BOOLEAN ->
  EXISTS (
    SELECT 1 FROM governed.public.region_access a
    WHERE a.user_name = CURRENT_USER() AND a.region = sales_region
  );
ALTER TABLE governed.public.sales ADD ROW ACCESS POLICY governed.public.sales_by_viewer ON (region);
```

* Give the role `SELECT` only on tables and views every viewer may query, and put a row access policy on each one
  that holds rows some viewers must not see. A table the role can read without a policy is readable in full by
  every viewer.
* Prefer secure views and stored procedures over raw tables. Build one secure view (or a procedure granted with
  `USAGE`) per thing your pages show, and grant the role only those, not the tables beneath them. A viewer who
  writes their own SQL then reaches only the columns and shapes you chose, and cannot read a secure view's
  definition to learn the tables behind it.
* Do not grant the role ownership, `INSERT`, `UPDATE`, `DELETE`, `CREATE`, or any other role. Snowflake then
  refuses writes from Curator whatever a page sends.
* Use the **Verify** button with a test viewer to confirm Snowflake answers with the expected user and role.

## Verifying the connection

In the **Verify** section, enter the Curator username of a frontend user and click **Verify connection**. Curator
mints a token for that user, asks Snowflake who it is, and shows the Snowflake user, role, and warehouse Snowflake
answered with, or Snowflake's reason for rejecting the token. When the form holds the saved account URL and role,
the outcome is remembered on the connection, and a rejection by Snowflake raises the connection's outage alert to
the addresses on the form. A check of values you have not saved yet only shows its result. An unknown username is
reported but does not count as an outage.

Page queries report on the same outage alert. A query that cannot reach Snowflake raises it, and the next query
Snowflake answers clears it. A token Snowflake rejects does not raise it, because Snowflake gives the same answer
for one viewer without a Snowflake user as for a broken integration: run **Verify connection** after changing the
security integration or the portal's signing key.

If Snowflake reports an audience failure, the account URL and the integration's audience list differ, often by
an underscore or a trailing slash. A user-mapping failure means no Snowflake user has a login name equal to that Curator
username.

## Writing a page that queries Snowflake

Pages reach Snowflake through one JavaScript helper that Curator serves whenever a Snowflake connection is
active. Upload the HTML under **Content** > **Files**, then place it on a page with a page builder **File** element
set to **Embed**. The file renders in a same-origin frame that shares the viewer's Curator session, so the helper
can prove who is asking.

```html theme={null}
<script src="/plugins/interworks/snowflake/assets/js/curator-snowflake.js"></script>
<script>
  Curator.snowflake.query(
    'SELECT region, SUM(revenue) AS revenue FROM governed.sales WHERE year = ? GROUP BY region',
    { bindings: { 1: { type: 'FIXED', value: '2026' } } }
  ).then(function (result) {
    // result.columns: [{ name, type }], result.rows: arrays of strings, result.meta.user: the viewer's Curator username
  }).catch(function (error) {
    // error.status, error.code, error.message, and error.sqlState for SQL errors
  });
</script>
```

* Send one statement per call. Use `?` placeholders with `bindings` for values; binding types are Snowflake's
  (`FIXED`, `REAL`, `TEXT`, `BOOLEAN`, `DATE`, `TIME`, `TIMESTAMP_NTZ`, and so on). Values arrive as strings,
  exactly as the Snowflake SQL API returns them.
* Keep statements within the connection's timeout and row limit. A statement that runs past the timeout, or a
  result over the limit, is refused with a message; add a `LIMIT` or aggregate in SQL.
* Use fully qualified object names, since the connection sets no database or schema.

Pages created with the **Upload HTML** button render in a sandboxed frame isolated from the viewer's session,
by design, and cannot query Snowflake. Use the Files and File element route for pages that query data.

### Error codes

| Status | Code | Meaning |
| - | - | - |
| 401 | `unauthenticated` | Nobody is signed in to Curator. |
| 403 | `forbidden` | The portal uses pass-through authentication, or Snowflake rejected the viewer's token. |
| 422 | `invalid` | The statement or bindings broke a rule before anything was sent. |
| 422 | `sql_error` | Snowflake could not run the statement; `message` and `sqlState` carry Snowflake's answer. |
| 422 | `too_many_rows` | The result is over the connection's row limit. |
| 429 | `throttled` | The viewer ran more than 60 queries in a minute, or Snowflake is rate limiting the account. |
| 500 | `internal` | Curator failed while running the query; the system log has the details. |
| 502 | `upstream` | Snowflake did not answer. |
| 503 | `unconfigured` | No active Snowflake connection exists. |
| 504 | `still_running` | The statement ran past the timeout. |

## Good to know

* Every query is tagged in Snowflake's query history with the Curator user ID and the page it came from, and
  Curator writes one audit line per query with a hash of the statement, never the SQL text.
* Each viewer may run 60 queries per minute. Warehouse cost belongs to the queries a page runs, so a page with
  ten charts runs ten statements per view. The counter lives in the portal's database cache store, so it holds
  across requests whatever cache driver the portal uses for pages.
* The token Curator mints lives five minutes and exists only on the server for one request.
* To cap warehouse cost, attach a resource monitor to the warehouse Curator viewers use, for example
  `CREATE RESOURCE MONITOR curator_monitor WITH CREDIT_QUOTA = 100 TRIGGERS ON 90 PERCENT DO SUSPEND;` followed by
  `ALTER WAREHOUSE curator_wh SET RESOURCE_MONITOR = curator_monitor;`.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.