Skip to content

Setting Up Snowflake User Impersonation for SSO

Summary

Snowflake query impersonation lets PuppyGraph connect through one shared Snowflake service account while asking Snowflake to evaluate queries for the current SSO user.

In this tutorial, you will:

  • Create Snowflake demo tables, a proxy-user stored procedure, and Snowflake access policies.
  • Start PuppyGraph and a local Keycloak identity provider with Docker Compose.
  • Sign in as the local PuppyGraph administrator and upload a graph schema.
  • Sign in through SSO as alice and bob.
  • Run Cypher and PageRank queries and verify that Snowflake returns only the data authorized for the current SSO user.
  • Optionally switch the catalog to session claims, where the user's verified JWT claims become Snowflake session variables instead of a proxy-user name.

See Connecting to Snowflake for the catalog reference and OAuth / OIDC Single Sign-On for the SSO configuration reference.

Prerequisites

docker compose version
  • A Snowflake account and a role that can create databases, schemas, tables, stored procedures, and row access policies.
  • A Snowflake service-account user configured for key-pair authentication.
  • The service-account role can use the configured warehouse, read the demo tables, and call the proxy-user stored procedure.
  • The private key for that service account in PKCS #8 format.
  • A browser for the PuppyGraph Web UI.

Setup

Configure Snowflake

Run the complete script in a Snowflake worksheet using a role with the required setup privileges.

Administrative role

The script starts with USE ROLE ACCOUNTADMIN. Replace that role if your organization uses a more limited administrative role for this setup.

Demo object replacement

The script creates the PUPPYGRAPH_SECURITY and PUPPYGRAPH_DEMO databases if they do not already exist. It also uses CREATE OR REPLACE for the proxy-user stored procedure, the PUPPYGRAPH_DEMO.MODERN tables, and the row access policies, so existing objects with those names will be replaced. Run it in a sandbox account, or confirm these database and object names are safe to use in your Snowflake account.

The script:

  • Creates the demo database, schema, and modern graph tables.
  • Creates a proxy-user stored procedure that stores the current SSO user in a Snowflake session variable.
  • Creates row access policies that read that session variable.
  • Attaches the policies to the demo tables.

It uses CREATE OR REPLACE for the demo objects, so you can rerun the script after fixing a worksheet mistake.

snowflake.sql 
USE ROLE ACCOUNTADMIN;

-- ============================================================
-- 0. Create the security/utility database and schema.
--    PuppyGraph calls:
--    CALL "PUPPYGRAPH_SECURITY"."UTIL"."SET_PROXY_USER"(?)
-- ============================================================

CREATE DATABASE IF NOT EXISTS PUPPYGRAPH_SECURITY;
CREATE SCHEMA IF NOT EXISTS PUPPYGRAPH_SECURITY.UTIL;

-- ============================================================
-- 1. Create the test database and schema.
-- ============================================================

CREATE DATABASE IF NOT EXISTS PUPPYGRAPH_DEMO;
CREATE SCHEMA IF NOT EXISTS PUPPYGRAPH_DEMO.MODERN;

-- ============================================================
-- 2. Create the proxy-user stored procedure.
-- ============================================================

CREATE OR REPLACE PROCEDURE "PUPPYGRAPH_SECURITY"."UTIL"."SET_PROXY_USER"(USER_NAME STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
  if (USER_NAME === null || USER_NAME.trim() === "") {
    throw "USER_NAME cannot be null or empty";
  }

  var normalizedUser = USER_NAME.trim().toLowerCase();

  snowflake.createStatement({
    sqlText: "SET PG_PROXY_USER = ?",
    binds: [normalizedUser]
  }).execute();

  return "PG_PROXY_USER set to: " + normalizedUser;
$$;

-- ============================================================
-- 3. Recreate the PUPPYGRAPH_DEMO.MODERN test tables.
-- ============================================================

CREATE OR REPLACE TABLE PUPPYGRAPH_DEMO.MODERN.PERSON (
    id STRING,
    name STRING,
    age INTEGER
);

INSERT INTO PUPPYGRAPH_DEMO.MODERN.PERSON (id, name, age) VALUES
    ('v1', 'marko', 29),
    ('v2', 'vadas', 27),
    ('v4', 'josh', 32),
    ('v6', 'peter', 35);

CREATE OR REPLACE TABLE PUPPYGRAPH_DEMO.MODERN.SOFTWARE (
    id STRING,
    name STRING,
    lang STRING
);

INSERT INTO PUPPYGRAPH_DEMO.MODERN.SOFTWARE (id, name, lang) VALUES
    ('v3', 'lop', 'java'),
    ('v5', 'ripple', 'java');

CREATE OR REPLACE TABLE PUPPYGRAPH_DEMO.MODERN.CREATED (
    id STRING,
    from_id STRING,
    to_id STRING,
    weight DOUBLE
);

INSERT INTO PUPPYGRAPH_DEMO.MODERN.CREATED (id, from_id, to_id, weight) VALUES
    ('e9', 'v1', 'v3', 0.4),
    ('e10', 'v4', 'v5', 1.0),
    ('e11', 'v4', 'v3', 0.4),
    ('e12', 'v6', 'v3', 0.2);

CREATE OR REPLACE TABLE PUPPYGRAPH_DEMO.MODERN.KNOWS (
    id STRING,
    from_id STRING,
    to_id STRING,
    weight DOUBLE
);

INSERT INTO PUPPYGRAPH_DEMO.MODERN.KNOWS (id, from_id, to_id, weight) VALUES
    ('e7', 'v1', 'v2', 0.5),
    ('e8', 'v1', 'v4', 1.0);

-- ============================================================
-- 4. Create row access policies.
-- ============================================================

CREATE OR REPLACE ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_PERSON_READ
AS (id STRING)
RETURNS BOOLEAN ->
  CASE LOWER(GETVARIABLE('PG_PROXY_USER'))
    WHEN 'alice' THEN id IN ('v1', 'v2', 'v4')
    WHEN 'bob' THEN id IN ('v4', 'v6')
    ELSE FALSE
  END;

CREATE OR REPLACE ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_KNOWS_READ
AS (id STRING)
RETURNS BOOLEAN -> LOWER(GETVARIABLE('PG_PROXY_USER')) = 'alice';

CREATE OR REPLACE ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_SOFTWARE_READ
AS (id STRING)
RETURNS BOOLEAN ->
  CASE LOWER(GETVARIABLE('PG_PROXY_USER'))
    WHEN 'alice' THEN id = 'v5'
    WHEN 'bob' THEN id = 'v3'
    ELSE FALSE
  END;

CREATE OR REPLACE ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_CREATED_READ
AS (id STRING)
RETURNS BOOLEAN ->
  CASE LOWER(GETVARIABLE('PG_PROXY_USER'))
    WHEN 'alice' THEN id = 'e10'
    WHEN 'bob' THEN id = 'e12'
    ELSE FALSE
  END;

-- ============================================================
-- 5. Attach row access policies to the tables.
-- ============================================================

ALTER TABLE PUPPYGRAPH_DEMO.MODERN.PERSON
ADD ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_PERSON_READ
ON (id);

ALTER TABLE PUPPYGRAPH_DEMO.MODERN.KNOWS
ADD ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_KNOWS_READ
ON (id);

ALTER TABLE PUPPYGRAPH_DEMO.MODERN.SOFTWARE
ADD ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_SOFTWARE_READ
ON (id);

ALTER TABLE PUPPYGRAPH_DEMO.MODERN.CREATED
ADD ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_CREATED_READ
ON (id);

The row access policies return no rows when PG_PROXY_USER is absent or when the user is not allowed to read a table. This makes the Snowflake policy itself fail closed.

Create the local deployment

Create a working directory:

mkdir puppygraph-snowflake-impersonation
cd puppygraph-snowflake-impersonation

Copy the Snowflake service account's private key into this directory as snowflake_rsa_key.p8.

Create the Keycloak realm

Create puppygraph-realm.json:

puppygraph-realm.json 
{
  "realm": "puppygraph",
  "enabled": true,
  "sslRequired": "none",
  "registrationAllowed": false,
  "loginWithEmailAllowed": true,
  "duplicateEmailsAllowed": false,
  "resetPasswordAllowed": false,
  "editUsernameAllowed": false,
  "bruteForceProtected": false,
  "accessTokenLifespan": 300,
  "ssoSessionIdleTimeout": 1800,
  "ssoSessionMaxLifespan": 36000,
  "clients": [
    {
      "clientId": "puppygraph-app",
      "name": "PuppyGraph SSO Client",
      "enabled": true,
      "clientAuthenticatorType": "client-secret",
      "secret": "puppygraph-sso-secret",
      "redirectUris": [
        "http://localhost:8081/sso_callback",
        "http://localhost:8081/*"
      ],
      "webOrigins": [
        "http://localhost:8081"
      ],
      "standardFlowEnabled": true,
      "directAccessGrantsEnabled": false,
      "publicClient": false,
      "protocol": "openid-connect",
      "attributes": {
        "pkce.code.challenge.method": "S256",
        "post.logout.redirect.uris": "http://localhost:8081/*"
      },
      "defaultClientScopes": [
        "openid",
        "profile",
        "email"
      ]
    }
  ],
  "users": [
    {
      "username": "alice",
      "enabled": true,
      "email": "alice@puppygraph.local",
      "emailVerified": true,
      "firstName": "Alice",
      "lastName": "Engineering",
      "credentials": [
        {
          "type": "password",
          "value": "alice",
          "temporary": false
        }
      ]
    },
    {
      "username": "bob",
      "enabled": true,
      "email": "bob@puppygraph.local",
      "emailVerified": true,
      "firstName": "Bob",
      "lastName": "Sales",
      "credentials": [
        {
          "type": "password",
          "value": "bob",
          "temporary": false
        }
      ]
    }
  ]
}

Create the Docker Compose file

Create docker-compose.yaml:

docker-compose.yaml 
services:
  keycloak:
    image: quay.io/keycloak/keycloak:26.0
    command:
      - start-dev
      - --import-realm
      - --http-port=18080
      - --hostname=http://localhost:18080
      - --health-enabled=true
    environment:
      KEYCLOAK_ADMIN: admin
      KEYCLOAK_ADMIN_PASSWORD: admin
    ports:
      - "127.0.0.1:18080:18080"
    volumes:
      - ./puppygraph-realm.json:/opt/keycloak/data/import/puppygraph-realm.json:ro
    healthcheck:
      test: ["CMD-SHELL", "exec 3<>/dev/tcp/localhost/18080"]
      interval: 5s
      timeout: 3s
      retries: 30
      start_period: 20s

  puppygraph:
    image: puppygraph/puppygraph:latest
    pull_policy: always
    ports:
      - "127.0.0.1:8081:8081"
      - "127.0.0.1:8083:8083"
      - "127.0.0.1:8182:8182"
      - "127.0.0.1:7687:7687"
    environment:
      PUPPYGRAPH_USERNAME: puppygraph
      PUPPYGRAPH_PASSWORD: puppygraph123

      SSO_ENABLED: "true"
      SSO_CLIENT_ID: puppygraph-app
      SSO_CLIENT_SECRET: puppygraph-sso-secret
      SSO_ISSUER: http://localhost:18080/realms/puppygraph
      SSO_URL: http://localhost:18080/realms/puppygraph/protocol/openid-connect/auth
      SSO_ACCESS_TOKEN_URL: http://keycloak:18080/realms/puppygraph/protocol/openid-connect/token
      SSO_JWKS_URL: http://keycloak:18080/realms/puppygraph/protocol/openid-connect/certs
      SSO_CALLBACK_URL: http://localhost:8081/sso_callback
      SSO_CLAIM_AS_USER_ID: preferred_username

      RBAC_ENABLED: "true"
      RBAC_DEFAULT_ROLE: Analyst
      BOLTSERVER_AUTHENTICATION_ENABLED: "true"
      GREMLINSERVER_AUTHENTICATION_ENABLED: "true"
      AUTHENTICATION_JWT_SECRETKEY: snowflake-impersonation-demo-secret

      SNOWFLAKE_ACCOUNT_IDENTIFIER: <account_identifier>
      SNOWFLAKE_SERVICE_ACCOUNT_USER: <service_account_username>
      SNOWFLAKE_WAREHOUSE: <warehouse>
      SNOWFLAKE_PRIVATE_KEY_PASSWORD: <private_key_password>
    volumes:
      - ./snowflake_rsa_key.p8:/home/keys/snowflake_rsa_key.p8:ro
    depends_on:
      keycloak:
        condition: service_healthy

Replace the Snowflake environment variable values before starting the containers. schema.json references these variables using the ${ENV:...} syntax, so secrets such as the private key password do not need to be written directly into the schema file.

Start Keycloak and PuppyGraph:

docker compose up -d

Wait until PuppyGraph is ready:

curl --user "puppygraph:puppygraph123" http://localhost:8081/status

Create the graph schema

Create schema.json:

schema.json 
{
  "catalog": [
    {
      "name": "snowflake_puppygraph_demo",
      "type": "snowflake",
      "jdbc": {
        "username": "${ENV:SNOWFLAKE_SERVICE_ACCOUNT_USER}",
        "jdbcUri": "jdbc:snowflake://${ENV:SNOWFLAKE_ACCOUNT_IDENTIFIER}.snowflakecomputing.com/?db=PUPPYGRAPH_DEMO&warehouse=${ENV:SNOWFLAKE_WAREHOUSE}&private_key_file=/home/keys/snowflake_rsa_key.p8&private_key_file_pwd=${ENV:SNOWFLAKE_PRIVATE_KEY_PASSWORD}",
        "driverClass": "net.snowflake.client.jdbc.SnowflakeDriver"
      },
      "identityPropagation": {
        "mode": "proxyUser",
        "proxyUserProcedure": "\"PUPPYGRAPH_SECURITY\".\"UTIL\".\"SET_PROXY_USER\""
      }
    }
  ],
  "node": [
    {
      "label": "Person",
      "dataSourceGroup": {
        "externalDataSource": {
          "enabled": true,
          "catalog": "snowflake_puppygraph_demo",
          "schema": "MODERN",
          "table": "PERSON",
          "mappedField": [
            {"sourceFieldName": "ID", "targetFieldName": "id"},
            {"sourceFieldName": "NAME", "targetFieldName": "name"},
            {"sourceFieldName": "AGE", "targetFieldName": "age"}
          ]
        }
      },
      "id": [{"name": "id", "type": "STRING"}],
      "attribute": [
        {"name": "name", "type": "STRING"},
        {"name": "age", "type": "LONG"}
      ]
    },
    {
      "label": "Software",
      "dataSourceGroup": {
        "externalDataSource": {
          "enabled": true,
          "catalog": "snowflake_puppygraph_demo",
          "schema": "MODERN",
          "table": "SOFTWARE",
          "mappedField": [
            {"sourceFieldName": "ID", "targetFieldName": "id"},
            {"sourceFieldName": "NAME", "targetFieldName": "name"},
            {"sourceFieldName": "LANG", "targetFieldName": "lang"}
          ]
        }
      },
      "id": [{"name": "id", "type": "STRING"}],
      "attribute": [
        {"name": "name", "type": "STRING"},
        {"name": "lang", "type": "STRING"}
      ]
    }
  ],
  "edge": [
    {
      "label": "Knows",
      "fromNodeLabel": "Person",
      "toNodeLabel": "Person",
      "dataSourceGroup": {
        "externalDataSource": {
          "enabled": true,
          "catalog": "snowflake_puppygraph_demo",
          "schema": "MODERN",
          "table": "KNOWS",
          "mappedField": [
            {"sourceFieldName": "ID", "targetFieldName": "id"},
            {"sourceFieldName": "FROM_ID", "targetFieldName": "from_id"},
            {"sourceFieldName": "TO_ID", "targetFieldName": "to_id"},
            {"sourceFieldName": "WEIGHT", "targetFieldName": "weight"}
          ]
        }
      },
      "id": [{"name": "id", "type": "STRING"}],
      "fromKey": [{"name": "from_id", "type": "STRING"}],
      "toKey": [{"name": "to_id", "type": "STRING"}],
      "attribute": [{"name": "weight", "type": "DOUBLE"}]
    },
    {
      "label": "Created",
      "fromNodeLabel": "Person",
      "toNodeLabel": "Software",
      "dataSourceGroup": {
        "externalDataSource": {
          "enabled": true,
          "catalog": "snowflake_puppygraph_demo",
          "schema": "MODERN",
          "table": "CREATED",
          "mappedField": [
            {"sourceFieldName": "ID", "targetFieldName": "id"},
            {"sourceFieldName": "FROM_ID", "targetFieldName": "from_id"},
            {"sourceFieldName": "TO_ID", "targetFieldName": "to_id"},
            {"sourceFieldName": "WEIGHT", "targetFieldName": "weight"}
          ]
        }
      },
      "id": [{"name": "id", "type": "STRING"}],
      "fromKey": [{"name": "from_id", "type": "STRING"}],
      "toKey": [{"name": "to_id", "type": "STRING"}],
      "attribute": [{"name": "weight", "type": "DOUBLE"}]
    }
  ]
}

The identityPropagation block sits on the catalog, beside jdbc. Schemas written for earlier releases carried the same setting as jdbc.queryImpersonation.proxyUserProcedure; they still load and mean the same thing. Metadata caching can stay at its default: Snowflake policies filter rows, not table and column metadata, so there is no reason to set enableMetaCache to "false" on an impersonated catalog.

Upload the schema as the local administrator

  1. Open http://localhost:8081.
  2. Use the local username and password form to sign in as puppygraph / puppygraph123. Do not use Sign in with SSO for this step.
  3. Open Graph from the left navigation.
  4. Choose Upload Schema and select schema.json.

The local administrator is used only to create the graph. A local user has no SSO identity, so PuppyGraph intentionally rejects data queries against an impersonation-enabled Snowflake catalog.

Run regular Cypher as an SSO user

Sign out, choose Sign in with SSO, and log in to Keycloak as alice / alice.

Alice can read marko, vadas, and josh from PERSON. Run:

MATCH (p:Person)
RETURN p.name AS name
ORDER BY name

Expected result:

name
josh
marko
vadas

Alice can also read the josh to ripple Created edge:

MATCH (p:Person)-[:Created]->(software:Software)
RETURN p.name AS person,
       software.name AS software
ORDER BY software

Expected result:

person software
josh ripple

Sign out and sign in through SSO as bob / bob. Bob sees a different slice of the same PERSON table:

MATCH (p:Person)
RETURN p.name AS name
ORDER BY name

Expected result:

name
josh
peter

Bob can read Peter's Created edge to lop:

MATCH (p:Person)-[:Created]->(software:Software)
RETURN p.name AS person,
       software.name AS software
ORDER BY software

Expected result:

person software
peter lop

To verify that Bob cannot read Alice's authorized software row, run:

MATCH (p:Person)-[:Created]->(software:Software {name: 'ripple'})
RETURN p.name AS person,
       software.name AS software

Expected result: 0 rows.

Run a graph algorithm as an SSO user

Graph algorithms use the same query identity and Snowflake authorization as regular Cypher scans.

As Alice, run PageRank over the Knows edges Alice can read:

CALL algo.paral.pagerank({
  labels: ['Person'],
  relationshipTypes: ['Knows'],
  maxIterations: 5
})
YIELD id, score
RETURN id, score
ORDER BY id

The result contains these node IDs:

Person[v1]
Person[v2]
Person[v4]

Switch to session claims

Mode proxyUser hands Snowflake one string, the SSO account name, and leaves the policy to look the user's entitlements up. Mode sessionClaims instead hands Snowflake the claims of the user's verified JWT as session variables, so a policy can read GETVARIABLE('TENANT_ID') directly and no lookup table is needed. The two modes use procedures with different signatures, so moving a catalog from one to the other is a deliberate change rather than an upgrade.

Give the users claims and trust the issuer

In Keycloak, add a user attribute tenant_id to alice (t-42) and to bob (t-1), create a public client puppygraph-api with Direct access grants enabled, and give it two protocol mappers: an audience mapper that adds puppygraph to the token's aud, and a user-attribute mapper that puts the tenant_id attribute into the access token as the tenant_id claim.

A browser SSO session does not carry the token's claims yet, so this part of the tutorial presents Keycloak's token directly to the REST Cypher endpoint. Register Keycloak as a trusted issuer first, as the local administrator:

curl -u puppygraph:puppygraph123 -X POST http://localhost:8081/api/trusted-issuers \
  -H 'Content-Type: application/json' \
  -d '{
    "alias": "keycloak",
    "issuer": "http://localhost:18080/realms/puppygraph",
    "jwksUrl": "http://keycloak:18080/realms/puppygraph/protocol/openid-connect/certs",
    "allowedAudiences": ["puppygraph"],
    "allowedAlgorithms": ["RS256"],
    "principalClaim": "preferred_username",
    "defaultRole": "Analyst",
    "isDefault": true
  }'

issuer is the URL the token carries, jwksUrl the address at which the PuppyGraph container reaches Keycloak.

Create the claim-set procedure and tenant-aware policies

The procedure receives one JSON argument, {"set": {"TENANT_ID": "t-42"}, "unset": ["TENANT_ID"]}. It must UNSET every variable listed under unset before it SETs the ones under set, because PuppyGraph pools connections and a variable the previous user carried must not survive for the next one. It returns a JSON array naming the variables it set; PuppyGraph fails the query closed when that list differs from what it asked for.

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE PROCEDURE PUPPYGRAPH_SECURITY.UTIL.SET_SESSION_CLAIMS(CLAIMS VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
  var spec = JSON.parse(CLAIMS);
  var validName = /^[A-Z_][A-Z0-9_]*$/;
  var unset = spec.unset || [];
  var set = spec.set || {};
  var names = Object.keys(set).sort();
  unset.concat(names).forEach(function (name) {
    if (!validName.test(name)) { throw "invalid session variable name: " + name; }
  });
  if (unset.length > 0) {
    // UNSET fails for a variable that was never set on this session, so give each one a value first.
    var nulls = unset.map(function () { return "NULL"; });
    snowflake.createStatement({sqlText: "SET (" + unset.join(", ") + ") = (" + nulls.join(", ") + ")"}).execute();
    snowflake.createStatement({sqlText: "UNSET (" + unset.join(", ") + ")"}).execute();
  }
  if (names.length > 0) {
    var marks = names.map(function () { return "?"; });
    var values = names.map(function (name) { return String(set[name]); });
    snowflake.createStatement({sqlText: "SET (" + names.join(", ") + ") = (" + marks.join(", ") + ")", binds: values}).execute();
  }
  return JSON.stringify(names);
$$;

-- The proxy-user policies read PG_PROXY_USER, which the claim-set procedure
-- neither sets nor clears: a pooled connection can still carry the variable a
-- previous proxy-user call left, so a table left on such a policy could answer
-- from that stale value. Replace every proxy-user policy. A row access policy
-- also restricts the rows DML can reach, so detach each one before the
-- backfill or the UPDATE touches nothing.
ALTER TABLE PUPPYGRAPH_DEMO.MODERN.PERSON DROP ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_PERSON_READ;
ALTER TABLE PUPPYGRAPH_DEMO.MODERN.KNOWS DROP ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_KNOWS_READ;
ALTER TABLE PUPPYGRAPH_DEMO.MODERN.SOFTWARE DROP ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_SOFTWARE_READ;
ALTER TABLE PUPPYGRAPH_DEMO.MODERN.CREATED DROP ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_CREATED_READ;

-- A tenant partition of the demo data, deliberately different from the
-- overlapping proxy-user slices above: every row belongs to exactly one tenant.
-- t-42 (alice): marko, vadas, ripple and the edges leaving marko;
-- t-1 (bob): josh, peter, lop and the edges leaving josh and peter.
ALTER TABLE PUPPYGRAPH_DEMO.MODERN.PERSON ADD COLUMN tenant_id STRING;
UPDATE PUPPYGRAPH_DEMO.MODERN.PERSON SET tenant_id = CASE WHEN id IN ('v1', 'v2') THEN 't-42' ELSE 't-1' END;
ALTER TABLE PUPPYGRAPH_DEMO.MODERN.SOFTWARE ADD COLUMN tenant_id STRING;
UPDATE PUPPYGRAPH_DEMO.MODERN.SOFTWARE SET tenant_id = CASE WHEN id = 'v5' THEN 't-42' ELSE 't-1' END;
ALTER TABLE PUPPYGRAPH_DEMO.MODERN.KNOWS ADD COLUMN tenant_id STRING;
UPDATE PUPPYGRAPH_DEMO.MODERN.KNOWS SET tenant_id = 't-42';
ALTER TABLE PUPPYGRAPH_DEMO.MODERN.CREATED ADD COLUMN tenant_id STRING;
UPDATE PUPPYGRAPH_DEMO.MODERN.CREATED SET tenant_id = CASE WHEN id = 'e9' THEN 't-42' ELSE 't-1' END;

-- One policy shape serves every table: the row belongs to the session's tenant.
CREATE OR REPLACE ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_TENANT
AS (tenant_id STRING)
RETURNS BOOLEAN -> tenant_id = GETVARIABLE('TENANT_ID');

ALTER TABLE PUPPYGRAPH_DEMO.MODERN.PERSON ADD ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_TENANT ON (tenant_id);
ALTER TABLE PUPPYGRAPH_DEMO.MODERN.SOFTWARE ADD ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_TENANT ON (tenant_id);
ALTER TABLE PUPPYGRAPH_DEMO.MODERN.KNOWS ADD ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_TENANT ON (tenant_id);
ALTER TABLE PUPPYGRAPH_DEMO.MODERN.CREATED ADD ROW ACCESS POLICY PUPPYGRAPH_DEMO.MODERN.RAP_TENANT ON (tenant_id);

GETVARIABLE returns NULL when the variable is not set, so the policy returns no rows to a connection on which the claim-set call did not run. Relationship queries and the graph algorithm above keep working because every mapped table now carries the same tenant policy. Do not leave a table on a PG_PROXY_USER policy: the claim-set procedure clears only the claim variables, so a pooled connection may still hold the proxy variable from an earlier session and such a table could answer from it. The procedure rethrows every error: one that swallowed a failed UNSET could report success while a previous user's variable is still set on the pooled connection. Grant the service account USAGE on the new procedure (Snowflake's procedure privilege; EXECUTE is not one), plus USAGE on PUPPYGRAPH_SECURITY and its UTIL schema if the role does not have them yet:

GRANT USAGE ON DATABASE PUPPYGRAPH_SECURITY TO ROLE <service_account_role>;
GRANT USAGE ON SCHEMA PUPPYGRAPH_SECURITY.UTIL TO ROLE <service_account_role>;
GRANT USAGE ON PROCEDURE PUPPYGRAPH_SECURITY.UTIL.SET_SESSION_CLAIMS(VARCHAR) TO ROLE <service_account_role>;

Point the catalog at the claims

Sign in again as the local administrator (puppygraph), as in Upload the schema as the local administrator: the schema's catalog section needs CATALOG:write, which the SSO users' Analyst role lacks. Then replace the catalog's identityPropagation block and upload the schema again:

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

Each claim becomes the session variable UPPER(claim). A user whose token lacks a claim marked required gets error IDP-02, naming the claim, before any Snowflake query runs; there is no setting that lets such a query proceed.

Obtain a token for alice and run the query with it:

TOKEN=$(curl -s -X POST http://localhost:18080/realms/puppygraph/protocol/openid-connect/token \
  -d grant_type=password -d client_id=puppygraph-api -d scope=openid \
  -d username=alice -d password=alice | jq -r .access_token)

curl -s -X POST http://localhost:8081/submitCypher \
  -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' \
  -d '{"query": "MATCH (p:Person) RETURN p.name AS name ORDER BY name"}'

The result names marko and vadas; the same call with a token for bob names josh and peter. A token without tenant_id gets error IDP-02 naming the claim instead of rows. Both modes need a PuppyGraph release whose DataAccess understands the request context; PuppyGraph rejects a sessionClaims catalog with a message naming the missing support wherever it does not.

Cleanup

Stop the local containers:

docker compose down -v

Remove the Snowflake demo database and security database when they are no longer needed:

USE ROLE ACCOUNTADMIN;
DROP DATABASE IF EXISTS PUPPYGRAPH_DEMO;
DROP DATABASE IF EXISTS PUPPYGRAPH_SECURITY;