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.

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",
        "enableMetaCache": "false",
        "queryImpersonation": {
          "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"}]
    }
  ]
}

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 the graph schema page.
  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]

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;