Redshift: Read-Only User

Create a secure, minimal-privilege Amazon Redshift user for FreshGuard.

This guide walks you through creating a dedicated read-only Redshift user for FreshGuard. Redshift uses PostgreSQL-compatible SQL with some differences in privilege management.

Create the User and Grant Access

Connect to your Redshift cluster as a superuser (e.g. admin), then run:

-- Create a dedicated user with a strong password
CREATE USER freshguard_readonly PASSWORD 'your-strong-password-here';

-- Grant usage on the schemas you want to monitor
GRANT USAGE ON SCHEMA public TO freshguard_readonly;

-- Grant SELECT on all existing tables in the schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO freshguard_readonly;

-- Grant SELECT on tables created in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO freshguard_readonly;

Use a strong password

Generate a random password of at least 32 characters. Redshift supports passwords up to 64 characters.

Multiple Schemas

If you need to monitor tables across multiple schemas, repeat the grants:

GRANT USAGE ON SCHEMA analytics TO freshguard_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO freshguard_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
  GRANT SELECT ON TABLES TO freshguard_readonly;

GRANT USAGE ON SCHEMA staging TO freshguard_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA staging TO freshguard_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA staging
  GRANT SELECT ON TABLES TO freshguard_readonly;

External Schemas (Redshift Spectrum)

If you use Redshift Spectrum to query data in S3, grant usage on the external schema:

GRANT USAGE ON SCHEMA spectrum_schema TO freshguard_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA spectrum_schema TO freshguard_readonly;

Verify Permissions

Connect as the new user and confirm it can read but not write:

-- This should work
SELECT COUNT(*) FROM public.your_table;

-- This should fail with "permission denied"
INSERT INTO public.your_table (id) VALUES (0);

Redshift Serverless

For Redshift Serverless, the same SQL commands apply. Connect to your workgroup endpoint and run the commands above using your admin credentials.

Tip

After creating the user, enter freshguard_readonly and the password you set when connecting Redshift in FreshGuard.