Snowflake

# To generate an unencrypted private key
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

# Or to generate an encrypted private key (recommended)
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -v2 aes-256-cbc -out rsa_key.p8

# To generate the public key from the private key
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

-- set variables (these need to be uppercase)
set export_role = 'STIGG_ROLE';
set export_warehouse = 'STIGG_WAREHOUSE';
set export_database = 'STIGG_DATABASE';
set export_schema = 'STIGG_SCHEMA';

-- set credentials
set export_username = 'STIGG_USER';
set export_public_key = '<PASTE YOUR PUBLIC KEY HERE>'; -- base64 content only, no header/footer (skip if using password auth)

begin;

-- create role
use role securityadmin;
create role if not exists identifier ($export_role);
grant role identifier ($export_role) to role SYSADMIN;

-- create user
create user if not exists identifier ($export_username)
    default_role = $export_role
    default_warehouse = $export_warehouse;
    rsa_public_key = $export_public_key;

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

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

-- create warehouse
create warehouse if not exists identifier ($export_warehouse)
    warehouse_size = xsmall warehouse_type = standard
auto_suspend = 60
auto_resume = true
initially_suspended = true;

-- create database
create database if not exists identifier ($export_database);

-- grant warehouse access
grant USAGE
    on warehouse identifier ($export_warehouse)
    to role identifier ($export_role);

-- grant database access
grant OWNERSHIP
    on database identifier ($export_database)
    to role identifier ($export_role);

commit;

begin;

USE DATABASE identifier ($export_database);

-- create schema
CREATE SCHEMA IF NOT EXISTS identifier ($export_schema);

commit;

begin;

-- grant schema access
grant OWNERSHIP
    on schema identifier ($export_schema)
    to role identifier ($export_role);

commit;

SHOW NETWORK POLICIES;

CREATE NETWORK POLICY $EXPORT_POLICY_NAME ALLOWED_IP_LIST = (
-- Depending on your data residency location, add the following IP addresses to your allowlist:
-- United States:
  '34.106.109.131/32',
  '34.106.196.165/32',
  '34.106.60.246/32',
  '34.106.229.69/32',
  '34.106.127.139/32',
  '34.106.218.58/32',
  '34.106.115.240/32',
  '34.106.225.141/32',
-- European Union:
  '13.37.4.46/32',
  '13.37.142.60/32',
  '35.181.124.238/32'
);

-- this script updates the existing user created with password-based authentication
set export_username = 'STIGG_USER';

-- set the public key generated earlier
alter user identifier ($export_username) set rsa_public_key = '<BASE64_PUBLIC_KEY>';

-- optional: enforce key-pair only by removing the password
alter user identifier ($export_username) unset password;