Snowflake Setup Guide

begin;

   -- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
   set role_name = 'PEERDB_ROLE';
   set user_name = 'PEERDB_USER';
   set user_password = 'PEERDB_5TR0NG_P455W0RD';
   set warehouse_name = 'PEERDB_WAREHOUSE';
   set database_name = 'YOUR_DATABASE';
   set internal_schema = '_PEERDB_INTERNAL'; -- do not change this value

   -- change role to securityadmin for user / role steps
   use role securityadmin;

   -- create role for peerdb
   create role if not exists identifier($role_name);

   -- create a user for peerdb
   create user if not exists identifier($user_name)
   password = $user_password
   default_role = $role_name
   default_warehouse = $warehouse_name;

   -- grant the role to the peerdb user
   grant role identifier($role_name) to user identifier($user_name);

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

   -- Only perform this step if you want to create a
   -- dedicated warehouse for peerdb (recommended)
   create warehouse if not exists identifier($warehouse_name)
   warehouse_size = xsmall
   warehouse_type = standard
   auto_suspend = 60
   auto_resume = true
   initially_suspended = true;

   -- change role to accountadmin to grant permissions
   use role ACCOUNTADMIN;

   -- grant peerdb role access to warehouse
   grant usage on warehouse identifier($warehouse_name)
   to role identifier($role_name);

   -- grant peerdb access to database
   grant usage on database identifier($database_name)
   to role identifier($role_name);

   use database identifier($database_name);

   -- create peerdb internal schema and give all permissions to peerdb role
   create schema if not exists identifier($internal_schema);
   grant all on schema identifier($internal_schema) to role identifier($role_name);
   grant create table on schema identifier($internal_schema) to role identifier($role_name);

   -- add a statement like this one for each schema you want to have accessed by peerdb
   -- EXAMPLE: grant usage on schema PUBLIC to role identifier($role_name);
   grant usage on schema <schema_name> to role identifier($role_name);

   -- add statements granting select permissions and create table permissions
   -- EXAMPLE: grant create table on schema PUBLIC to role identifier($role_name);
   grant create table on schema <schema_name> to role identifier($role_name);
 commit;