Snowflake

begin;

-- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
set role_name = 'REFUEL_ROLE';
set user_name = 'REFUEL_USER';
set user_password = '<your-password>';
set warehouse_name = 'REFUEL_WAREHOUSE';
set database_name = 'REFUEL_DATABASE';

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

-- create role for refuel
create role if not exists identifier($role_name);
grant role identifier($role_name) to role SYSADMIN;

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

grant role identifier($role_name) to user identifier($user_name);

-- set binary_input_format to BASE64
alter user identifier($user_name) set binary_input_format = 'BASE64';

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

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

-- create database for refuel
create database if not exists identifier($database_name);

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

-- grant refuel access to database
grant create schema, monitor, usage
on database identifier($database_name)
to role identifier($role_name);

-- grant refuel read access to all tables and views in database
grant usage
on all schemas in database identifier($database_name)
to role identifier($role_name);
grant select
on all tables in database identifier($database_name)
to role identifier($role_name);
grant select
on all views in database identifier($database_name)
to role identifier($role_name);

-- if you want to limit access to specific schemas, you can use the following commands
-- grant USAGE on SCHEMA identifier($database_name).schema_name to role identifier($role_name);
-- grant SELECT on ALL TABLES in SCHEMA identifier($database_name).schema_name to role identifier($role_name);
-- grant SELECT on ALL VIEWS in SCHEMA identifier($database_name).schema_name to role identifier($role_name);

-- change role to ACCOUNTADMIN for STORAGE INTEGRATION support (only needed for Snowflake on GCP)
use role ACCOUNTADMIN;
grant CREATE INTEGRATION on account to role identifier($role_name);
use role sysadmin;    

commit;

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 AES256 -inform PEM -out rsa_key.p8