Skip to main content
Navigation

Snowflake: Read-Only Role

Create a secure, minimal-privilege Snowflake role for FreshGuard.

This guide walks you through creating a dedicated read-only Snowflake role and user for FreshGuard. The role has the minimum privileges needed to run monitoring queries.

Create the Role

Connect to Snowflake as ACCOUNTADMIN (or a role with CREATE ROLE and MANAGE GRANTS privileges) and run:

-- Create a dedicated role
CREATE ROLE FRESHGUARD_ROLE;


-- Grant usage on the warehouse FreshGuard will use
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE FRESHGUARD_ROLE;


-- Grant usage on the database and schema
GRANT USAGE ON DATABASE ANALYTICS TO ROLE FRESHGUARD_ROLE;
GRANT USAGE ON SCHEMA ANALYTICS.PUBLIC TO ROLE FRESHGUARD_ROLE;


-- Grant SELECT on all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS.PUBLIC TO ROLE FRESHGUARD_ROLE;


-- Grant SELECT on future tables (so new tables are automatically accessible)
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS.PUBLIC TO ROLE FRESHGUARD_ROLE;

Info

Replace COMPUTE_WH, ANALYTICS, and PUBLIC with your actual warehouse, database, and schema names.

Create the User

-- Create a user with a strong password
CREATE USER FRESHGUARD_USER
  PASSWORD = 'your-strong-password-here'
  DEFAULT_ROLE = FRESHGUARD_ROLE
  DEFAULT_WAREHOUSE = COMPUTE_WH
  MUST_CHANGE_PASSWORD = FALSE;


-- Assign the role to the user
GRANT ROLE FRESHGUARD_ROLE TO USER FRESHGUARD_USER;

Use a strong password

Generate a random password of at least 32 characters. Snowflake supports passwords up to 256 characters.

Multiple Schemas

If you want to monitor tables across multiple schemas or databases, repeat the grants:

-- Additional schema
GRANT USAGE ON SCHEMA ANALYTICS.STAGING TO ROLE FRESHGUARD_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS.STAGING TO ROLE FRESHGUARD_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS.STAGING TO ROLE FRESHGUARD_ROLE;


-- Additional database
GRANT USAGE ON DATABASE RAW_DATA TO ROLE FRESHGUARD_ROLE;
GRANT USAGE ON SCHEMA RAW_DATA.PUBLIC TO ROLE FRESHGUARD_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA RAW_DATA.PUBLIC TO ROLE FRESHGUARD_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA RAW_DATA.PUBLIC TO ROLE FRESHGUARD_ROLE;

Warehouse Sizing

FreshGuard runs lightweight queries (typically SELECT MAX(column) FROM table). An X-SMALL warehouse is more than sufficient. If you want to minimize costs, you can create a dedicated warehouse with aggressive auto-suspend:

CREATE WAREHOUSE FRESHGUARD_WH
  WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;


GRANT USAGE ON WAREHOUSE FRESHGUARD_WH TO ROLE FRESHGUARD_ROLE;

This warehouse will auto-suspend after 60 seconds of inactivity and resume automatically when FreshGuard runs a check.

Verify Permissions

Connect as FRESHGUARD_USER and confirm the role works:

USE ROLE FRESHGUARD_ROLE;
USE WAREHOUSE COMPUTE_WH;


-- This should work
SELECT COUNT(*) FROM ANALYTICS.PUBLIC.YOUR_TABLE;


-- This should fail with "insufficient privileges"
INSERT INTO ANALYTICS.PUBLIC.YOUR_TABLE (id) VALUES (0);

Tip

After creating the user, enter FRESHGUARD_USER, the password, and FRESHGUARD_ROLE when connecting Snowflake in FreshGuard.