Snowflake

-- set variables (these need to be uppercase)
set export_role = 'STIGG_TRANSFER_ROLE';
set export_warehouse = 'STIGG_TRANSFER_WH';
set export_database = 'STIGG_DB';
set export_schema = 'STIGG_SCHEMA';
set export_username = 'STIGG_TRANSFER_USER';

begin;

-- create role
use role accountadmin;
create role if not exists identifier ($export_role);

-- grant the role to sysadmin so admins can query the synced tables
grant role identifier ($export_role) to role sysadmin;

-- create a service user, then set its attributes with ALTER rather than inline on CREATE.
-- if the user already exists (e.g. you're re-running this after the connect wizard
-- regenerated your key pair), inline attributes on CREATE ... IF NOT EXISTS are silently
-- skipped — the user keeps its old rsa_public_key and the connection fails with
-- JWT_TOKEN_INVALID_PUBLIC_KEY_FINGERPRINT_MISMATCH with no indication why. The ALTER
-- statements below always apply, so re-running this script is safe.
create user if not exists identifier ($export_username);
alter user identifier ($export_username) set type = service;
alter user identifier ($export_username) set default_role = $export_role;
alter user identifier ($export_username) set default_warehouse = $export_warehouse;
alter user identifier ($export_username) set rsa_public_key = '<STIGG FILLS IN YOUR GENERATED PUBLIC KEY HERE>';

grant role identifier ($export_role) to user identifier ($export_username);

-- change role to accountadmin for warehouse / database steps
use role accountadmin;

-- create warehouse
create warehouse if not exists identifier ($export_warehouse)
    warehouse_size = 'XSMALL'
    auto_suspend = 60
    auto_resume = true
    initially_suspended = true;

grant usage on warehouse identifier ($export_warehouse) to role identifier ($export_role);

-- create database and schema
create database if not exists identifier ($export_database);
create schema if not exists identifier ($export_database || '.' || $export_schema);

-- grant privileges
grant ownership on schema identifier ($export_database || '.' || $export_schema) to role identifier ($export_role);
grant usage on database identifier ($export_database) to role identifier ($export_role);

commit;

-- ============================================================
-- Optional: allow Stigg's transfer service to reach Snowflake from its static IP addresses.
-- Needed only if your Snowflake account restricts access by IP.
-- Run as ACCOUNTADMIN, after the setup script from the previous step (the user has to exist already).
-- ============================================================

set user_name      = 'STIGG_TRANSFER_USER';

use role accountadmin;

-- Read this result before you run the rest: everything below reuses a policy of this name if one already
-- exists, and replaces its allowlist. Empty means there is nothing to collide with. A row whose comment
-- names Stigg is ours, from an earlier run. A row that is neither is your own policy sharing the name —
-- rename it first. If it is owned by a role ACCOUNTADMIN cannot reach, the alter further down fails and
-- stops the script, which is the safe direction: nothing has been attached to your user at that point.
show network policies like 'STIGG_TRANSFER_NETWORK_POLICY';

create network policy if not exists STIGG_TRANSFER_NETWORK_POLICY
  allowed_ip_list = (
    -- Stigg data export (US):
    '35.192.85.117/32',
    -- Stigg data export (EU):
    '104.199.49.149/32'
  )
  comment = 'Stigg data export egress addresses. Managed by the Stigg connect wizard.';

-- The create above is skipped if the policy already exists, which would leave your user on that policy's
-- allowlist instead of this one, so set the addresses explicitly here.
alter network policy STIGG_TRANSFER_NETWORK_POLICY set allowed_ip_list = (
    -- Stigg data export (US):
    '35.192.85.117/32',
    -- Stigg data export (EU):
    '104.199.49.149/32'
);

-- A policy on the user overrides the one on the account, so this lets the transfer through without changing
-- your account-level policy. It does replace any network policy currently on this user and limits them to
-- the addresses above, so point it at a user dedicated to Stigg rather than one your own jobs log in as.
-- To undo: alter user identifier($user_name) unset network_policy;
alter user identifier($user_name) set network_policy = STIGG_TRANSFER_NETWORK_POLICY;