Connecting to Snowflake

Prerequisites

PuppyGraph version 1.0 or higher.
The Snowflake instance is accessible over the network from the PuppyGraph instance.

Overview

PuppyGraph supports connecting to Snowflake in four ways:

Connecting with key-pair authentication
Connecting with OAuth 2.0 client credentials
Connecting with a programmatic access token
Connecting with a JDBC connection string

Running PuppyGraph as a Snowflake Native App?

You can skip all of this. Install PuppyGraph from the Snowflake Marketplace and it connects to the data you grant it with a token Snowflake issues to the app, so there are no credentials to set up or rotate. Launching PuppyGraph from Snowflake Marketplace walks you through it.

Snowflake query impersonation can be enabled on top of these connection methods. PuppyGraph still connects to Snowflake through one service account, but before sending SQL queries to Snowflake it establishes the current user's identity on the connection through a stored procedure you provide, in one of two modes: proxyUser calls a proxy-user procedure with the SSO user's name, and sessionClaims calls a claim-set procedure with claims taken from the user's verified token, one Snowflake session variable per claim. Your access controls then read that session state. See Query impersonation for SSO users.

Snowflake strong authentication

Snowflake is retiring single-factor password sign-ins. Human users who sign in with a password must add a second factor, and service users lose password authentication altogether: a user whose TYPE is SERVICE cannot sign in with a password at all. Snowflake enforces this per account on a date it sends to account administrators, and lists the account's outstanding violations in Snowsight under Governance & security, Trust Center, in the Strong Authentication Hub. See Planning for the deprecation of single-factor password sign-ins for the schedule.

PuppyGraph connects to Snowflake as a service user, so the connection method decides whether a catalog keeps working after that date.

Key-pair authentication, OAuth 2.0 client credentials, programmatic access tokens, and the Snowflake Native App are unaffected. None of them sends a password. The Native App connects with an OAuth token that Snowflake issues to the app and rotates itself, so it has no credential to migrate.
A JDBC connection string is unaffected only when the string itself selects a non-password method, for example authenticator=oauth_client_credentials. A catalog that supplies a Snowflake username and password stops connecting once the account is enforced.

Auditing existing catalogs is worthwhile before the enforcement date. Any catalog whose Snowflake Auth Type is JDBC authentication is connecting with a password when its JDBC URL has no authenticator parameter or sets authenticator=snowflake, names no private key (private_key_file or private_key_base64), and its password field is populated.

End users are not affected by this change, because they never authenticate to Snowflake. PuppyGraph holds one service-account credential and establishes each user's identity on the connection through a stored procedure. See Query impersonation for SSO users.

Preparing a Snowflake service user

The following creates a service user that PuppyGraph can use with key-pair authentication. Generate the key pair first, as described in Querying Snowflake Data as a Graph.

USE ROLE USERADMIN;

CREATE USER puppygraph_svc
  TYPE = SERVICE
  RSA_PUBLIC_KEY = '<contents of the public key file, without the header and footer lines>'
  DEFAULT_WAREHOUSE = <warehouse>
  DEFAULT_ROLE = <role>
  COMMENT = 'PuppyGraph catalog service account';

USE ROLE SECURITYADMIN;
GRANT ROLE <role> TO USER puppygraph_svc;

To migrate a user that currently connects with a password, add the public key first, switch the catalog to key-pair authentication with the matching private key, confirm that PuppyGraph reconnects, and only then remove the password and change the type. Removing the password is a separate step, and a user that keeps one is still reported as a violation even after the key pair works.

ALTER USER puppygraph_svc SET RSA_PUBLIC_KEY = '<public key>';
-- switch the catalog to key-pair authentication, confirm it reconnects, then:
ALTER USER puppygraph_svc UNSET PASSWORD;
ALTER USER puppygraph_svc SET TYPE = SERVICE;

An authentication policy keeps the user on key-pair authentication, and a network policy restricts where the key can be used from. Snowflake recommends pairing long-lived credentials with a network policy and rotating them regularly.

An authentication policy is a schema-level object, so create it in a database and schema where your role has the CREATE AUTHENTICATION POLICY privilege. Assigning it to a user requires APPLY AUTHENTICATION POLICY on the account, which SECURITYADMIN has by default. Creating a network policy requires CREATE NETWORK POLICY on the account, which SECURITYADMIN also has.

USE ROLE SECURITYADMIN;

CREATE AUTHENTICATION POLICY <database>.<schema>.puppygraph_service_auth
  AUTHENTICATION_METHODS = ('KEYPAIR')
  CLIENT_TYPES = ('DRIVERS');

ALTER USER puppygraph_svc SET AUTHENTICATION POLICY <database>.<schema>.puppygraph_service_auth;

CREATE NETWORK POLICY puppygraph_egress
  ALLOWED_IP_LIST = ('<PuppyGraph egress address>');

ALTER USER puppygraph_svc SET NETWORK_POLICY = puppygraph_egress;

Grant the service account's role USAGE on the warehouse and on the database and schema that contain the graph's tables, SELECT on exactly the tables the graph schema references, and USAGE on any stored procedure used for query impersonation. SELECT on a table is not enough without USAGE on its database and schema. A table the account can read is fully visible through it unless a Snowflake policy narrows it.

USE ROLE SECURITYADMIN;

GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE <role>;
GRANT USAGE ON DATABASE <database> TO ROLE <role>;
GRANT USAGE ON SCHEMA <database>.<schema> TO ROLE <role>;
GRANT SELECT ON TABLE <database>.<schema>.<table> TO ROLE <role>;

Rotating the key pair

Snowflake holds two public keys per user, which allows rotation without a window of downtime. Publish the new key to the spare slot, point PuppyGraph at the new private key file, and clear the old slot once queries succeed. The next rotation uses the slots in the opposite order.

ALTER USER puppygraph_svc SET RSA_PUBLIC_KEY_2 = '<new public key>';
-- update the catalog to the new private key file and confirm queries work, then:
ALTER USER puppygraph_svc UNSET RSA_PUBLIC_KEY;

Connecting with key-pair authentication

See Querying Snowflake Data as a Graph for a complete demo. The demo includes setting up the connection to a Snowflake database with key-pair authentication.

Connecting with OAuth 2.0 client credentials

This option is suitable if your Snowflake instance already has an OAuth 2.0 integration.

Configuration

Configuration	Explanation
Snowflake Auth Type	OAuth (client credentials)
Snowflake Server URL	`<account_identifier>.snowflakecomputing.com`
Snowflake Database Name	Name of the Snowflake database to connect to.
Snowflake Warehouse Name	Name of the Snowflake warehouse to connect to.
OAuth Client ID	The client ID issued by the OAuth 2.0 identity provider.
OAuth Client Secret	The client secret issued by the OAuth 2.0 identity provider.
OAuth Token Request URL	The token endpoint URL of the OAuth 2.0 identity provider.
OAuth Scope (optional)	The OAuth 2.0 scope to request. Required only if the identity provider enforces scope.

Connecting with a programmatic access token

A programmatic access token (PAT) is a credential Snowflake issues for a specific user. It suits deployments that prefer an expiring credential over a key pair, and deployments whose secret store already distributes tokens. PuppyGraph passes the token to the Snowflake driver as token, alongside authenticator=programmatic_access_token, the way the driver expects it under that authenticator. The token is redacted wherever a catalog is displayed or exported, as the key-pair passphrase and the OAuth client secret are.

Snowflake records these logins with FIRST_AUTHENTICATION_FACTOR = PROGRAMMATIC_ACCESS_TOKEN, so a PAT catalog does not appear as a password sign-in in the Trust Center.

Note

The programmatic access token auth type is available in PuppyGraph 1.16.0 and later.

Snowflake setup

The user must be subject to a network policy before it can generate or use a token. That is the default, PAT_POLICY = (NETWORK_POLICY_EVALUATION = ENFORCED_REQUIRED), and Snowflake rejects the token otherwise. Attach the policy first, then issue the token.

USE ROLE SECURITYADMIN;

CREATE NETWORK POLICY puppygraph_egress
  ALLOWED_IP_LIST = ('<PuppyGraph egress address>');

ALTER USER puppygraph_svc SET NETWORK_POLICY = puppygraph_egress;

ALTER USER puppygraph_svc
  ADD PROGRAMMATIC ACCESS TOKEN puppygraph_catalog
  ROLE_RESTRICTION = '<role>'
  DAYS_TO_EXPIRY = 90;

An authentication policy on the user or the account must also allow PROGRAMMATIC_ACCESS_TOKEN, or Snowflake refuses to issue or accept the token. A user prepared as in Preparing a Snowflake service user has a key-pair-only policy, so add the method before issuing the token, and keep KEYPAIR until the catalog connects with the token:

ALTER AUTHENTICATION POLICY <database>.<schema>.puppygraph_service_auth
  SET AUTHENTICATION_METHODS = ('KEYPAIR', 'PROGRAMMATIC_ACCESS_TOKEN');

Set ROLE_RESTRICTION to the role that holds the grants in Preparing a Snowflake service user. Sessions opened with the token act only as that role, so it needs USAGE on the warehouse, database and schema as well as SELECT on the tables.

Scope the network policy to the addresses PuppyGraph actually connects from. A policy of 0.0.0.0/0 satisfies the requirement while allowing the whole internet, which leaves the token as the only thing standing between a leak and your data.

The token value is returned once, by the ADD PROGRAMMATIC ACCESS TOKEN statement, and cannot be read back afterwards. Copy it straight into the catalog form.

Without an authentication policy, tokens work as they are. Once the catalog connects with the token, a policy can restrict the user to tokens, so nobody can quietly add a password later. For a user that already has a policy, set its AUTHENTICATION_METHODS to ('PROGRAMMATIC_ACCESS_TOKEN') instead. As with key-pair authentication, the policy is a schema-level object and needs the privileges listed in Preparing a Snowflake service user.

CREATE AUTHENTICATION POLICY <database>.<schema>.puppygraph_pat_auth
  AUTHENTICATION_METHODS = ('PROGRAMMATIC_ACCESS_TOKEN')
  CLIENT_TYPES = ('DRIVERS');

ALTER USER puppygraph_svc SET AUTHENTICATION POLICY <database>.<schema>.puppygraph_pat_auth;

Tokens expire, so plan a rotation. Rotating issues a new secret and keeps the old one valid for a grace period, which is long enough to update the catalog without an outage. The grace period defaults to 24 hours.

ALTER USER puppygraph_svc
  ROTATE PROGRAMMATIC ACCESS TOKEN puppygraph_catalog
  EXPIRE_ROTATED_TOKEN_AFTER_HOURS = 24;

Configuration

Configuration	Explanation
Snowflake Auth Type	Programmatic access token (PAT)
Snowflake Server URL	`<account_identifier>.snowflakecomputing.com`
Snowflake User Name	The Snowflake user the token was issued for.
Snowflake Database Name	Name of the Snowflake database to connect to.
Snowflake Warehouse Name	Name of the Snowflake warehouse to connect to.
Snowflake programmatic access token	The token value returned when it was created.

Editing a saved catalog shows the token masked. Leave it masked to keep the stored value, or type a new token to replace it.

Connecting with a JDBC connection string

If you prefer to supply the full connection string manually, choose JDBC authentication as the Auth Type and provide a JDBC URL. The example below configures OAuth 2.0 client credentials:

jdbc:snowflake://<account_identifier>.snowflakecomputing.com/?
authenticator=oauth_client_credentials
&oauthClientId=<client_id>
&oauthClientSecret=<client_secret>
&oauthTokenRequestUrl=<token_endpoint_url>
&oauthScope=<scope>
&db=<DATABASE>
&warehouse=<WAREHOUSE>
&role=<ROLE>

The example above authenticates through OAuth, so it leaves the username and password fields empty. A JDBC URL that selects neither a non-password authenticator nor a private key signs in with the password field instead, which is the password sign-in that Snowflake blocks for service users. See Snowflake strong authentication.

Data Type Mapping

Snowflake Type	PuppyGraph Type	Description
`BOOLEAN`	`Boolean`	True or false
`NUMBER(P, 0)`, `DECIMAL(P, 0)`, `NUMERIC(P, 0)`	`Long`	64-bit signed integer. Includes `INT`, `INTEGER`, `BIGINT`, `SMALLINT`, `TINYINT` and `BYTEINT`, which are all `NUMBER(38, 0)` in Snowflake
`NUMBER(P, S)`, `DECIMAL(P, S)`, `NUMERIC(P, S)` with S above 0	`Decimal(38, 9)`	Fixed-point decimal, always read as precision 38, scale 9
`FLOAT`, `DOUBLE`, `REAL`	`Double`	64-bit IEEE 754 floating point
`CHAR`	`String`	Fixed-length character string
`VARCHAR`, `STRING`, `TEXT`	`String`	Character strings
`VARIANT`, `OBJECT`	`String`	Semi-structured value as JSON text
`ARRAY`, `GEOGRAPHY`, `GEOMETRY`	`String`	Value as text
`DATE`	`Date`	Calendar date without time or timezone
`TIMESTAMP_NTZ`, `TIMESTAMP_LTZ`	`DateTime`	Date and time

A scale-0 NUMBER holds up to 38 digits, but Long covers only the signed 64-bit range (about 19 digits). Values outside that range cannot be represented.
A NUMBER column with more than 9 digits after the decimal point loses the digits beyond the ninth. Decimal(38, 9) also leaves room for only 29 digits before the decimal point, so larger values cannot be represented.
Snowflake returns VARIANT and OBJECT values as pretty-printed JSON, so the text contains line breaks and indentation.
Columns of any other type, such as TIMESTAMP_TZ, TIME or BINARY, are not supported. They are listed in Schema Builder, but you cannot add them to a node or edge as attributes.

If you need a data type that is not supported, or one that is not listed here, contact us.

Query impersonation for SSO users

Use Snowflake query impersonation when the same PuppyGraph graph should return different Snowflake rows for different SSO users. In mode proxyUser the pattern is:

PuppyGraph authenticates the user through SSO.
PuppyGraph reads the configured SSO user claim, such as preferred_username.
Before sending SQL queries to Snowflake, PuppyGraph calls the configured proxy-user stored procedure with that user value.
Snowflake evaluates the query under the user context set by that procedure and returns the corresponding result.

In mode sessionClaims steps 1 to 3 differ: the user authenticates with a bearer token from a trusted issuer, PuppyGraph projects the claims the catalog lists out of that verified token, and before every scan it calls the configured claim-set procedure with them so Snowflake sets one session variable per claim. See Session claims.

For a complete runnable example with Keycloak, Snowflake, and validation queries, see Setting Up Snowflake User Impersonation for SSO.

Snowflake requirements

Prepare these objects and privileges in Snowflake before enabling the catalog:

Requirement	Details
Service account	The Snowflake user that PuppyGraph uses to connect. It can authenticate with key-pair authentication, OAuth, a programmatic access token, or a manually supplied JDBC URL.
Proxy-user stored procedure	In mode `proxyUser`, the Snowflake stored procedure configured by `identityPropagation.proxyUserProcedure`. It accepts one string parameter: the current PuppyGraph SSO user value. The procedure should set the Snowflake user context used by your access-control logic.
Claim-set stored procedure	In mode `sessionClaims`, the procedure configured by `identityPropagation.claimSetProcedure`. It accepts one `VARCHAR` holding a JSON object such as `{"set": {"TENANT_ID": "t-42"}, "unset": ["TENANT_ID", "ADVISOR_ID"]}` (`unset` lists every allow-listed variable, `set` the ones the token carries), must `UNSET` every listed variable before it `SET`s the present ones, and returns a JSON array naming the variables it set.
Procedure privilege	The service account's role needs `USAGE` on the configured stored procedure (Snowflake's procedure privilege; there is no `EXECUTE` grant) and `USAGE` on the database and schema that contain it, never ownership.
Table privileges	The service account must be able to query the Snowflake tables referenced by the graph schema. Scope `SELECT` to exactly those tables: policies narrow what the service account can see, so a table without a policy is fully visible through it.
Warehouse privilege	The service account must be able to use the configured warehouse.
Snowflake access controls	Snowflake policies, secure views, or equivalent controls should read the session state the configured procedure sets: the proxy-user variable in mode `proxyUser`, the claim variables (`GETVARIABLE('TENANT_ID')` for claim `tenant_id`) in mode `sessionClaims`.

For example, if PuppyGraph should call:

CALL "PUPPYGRAPH_SECURITY"."UTIL"."SET_PROXY_USER"(?);

then the procedure must accept the current user value as its only argument and must be executable by the service account.

PuppyGraph requirements

In mode proxyUser, enable SSO for PuppyGraph and choose the claim that should be passed to Snowflake (in mode sessionClaims this setting plays no part: users present a trusted issuer's bearer token, and the catalog's claims list selects what is projected):

SSO_ENABLED: "true"
SSO_CLAIM_AS_USER_ID: preferred_username

With this configuration, if the SSO token contains preferred_username: alice, PuppyGraph passes alice to the Snowflake procedure.

Schema configuration

Add an identityPropagation block to the Snowflake catalog, beside jdbc, with mode set to proxyUser and the fully qualified Snowflake proxy-user stored procedure name as proxyUserProcedure. Schemas from earlier releases carry the same setting as jdbc.queryImpersonation.proxyUserProcedure; they keep working and read as mode proxyUser.

Use the identifier form that matches how the Snowflake procedure was created. Snowflake resolves unquoted identifiers in uppercase, so a procedure created with regular unquoted identifiers can be configured without double quotes:

"proxyUserProcedure": "PUPPYGRAPH_SECURITY.UTIL.SET_PROXY_USER"

If a customer omits double quotes for a procedure created with regular unquoted identifiers, the configuration still works because Snowflake resolves each identifier in uppercase. Double quotes are needed only when Snowflake must receive the exact quoted identifier, for example because the procedure was created with case-sensitive, mixed-case, lowercase, or special-character identifiers. Because the schema value is a JSON string, each double quote that should be sent to Snowflake must be escaped as \". If the configured value does not match the Snowflake identifier, Snowflake cannot find the procedure.

{
  "catalog": [
    {
      "name": "snowflake_data",
      "type": "snowflake",
      "jdbc": {
        "username": "<service_account_username>",
        "jdbcUri": "jdbc:snowflake://<account_identifier>.snowflakecomputing.com/?db=<DATABASE>&warehouse=<WAREHOUSE>&private_key_file=/home/keys/snowflake_rsa_key.p8&private_key_file_pwd=<private_key_password>",
        "driverClass": "net.snowflake.client.jdbc.SnowflakeDriver"
      },
      "identityPropagation": {
        "mode": "proxyUser",
        "proxyUserProcedure": "\"PUPPYGRAPH_SECURITY\".\"UTIL\".\"SET_PROXY_USER\""
      }
    }
  ]
}

Metadata caching (jdbc.enableMetaCache) can stay at its default with either mode: Snowflake policies filter rows, not metadata, so table and column metadata is the same for every user. jdbc.enableDataCache cannot be "true" with sessionClaims.

After the schema is uploaded, sign in through SSO before running data queries. Local PuppyGraph users do not have an SSO identity to pass to Snowflake, so queries against an impersonation-enabled Snowflake catalog are rejected.

Session claims

Mode sessionClaims passes the claims of the user's verified JWT to Snowflake as session variables instead of one proxy-user name. The user presents a trusted issuer's token as a bearer token on the HTTP API, the REST Cypher endpoint, Bolt or the Gremlin WebSocket (a browser SSO session does not carry the claims yet); PuppyGraph verifies it against the issuer registered with POST /api/trusted-issuers, keeps only the claims listed on the catalog, and before every scan calls the claim-set procedure on the connection that runs it. Each claim becomes the session variable UPPER(claim), so a policy reads GETVARIABLE('TENANT_ID') for the tenant_id claim. A query whose user lacks a required claim fails with error IDP-02 before any scan; an optional claim the user lacks is simply not set.

"identityPropagation": {
  "mode": "sessionClaims",
  "claimSetProcedure": "\"PUPPYGRAPH_SECURITY\".\"UTIL\".\"SET_SESSION_CLAIMS\"",
  "claims": [
    { "claim": "tenant_id", "required": true },
    { "claim": "advisor_id", "required": false }
  ]
}

Claim names must map onto distinct valid variable names (^[A-Z_][A-Z0-9_]*$ after upper-casing), registered JWT claims such as iss and exp cannot be listed, and a catalog lists between 1 and 16 claims. For the procedure contract and a worked example, see Setting Up Snowflake User Impersonation for SSO.