When using Amazon Redshift to analyze behavioral data captured by Snowplow, managing access permissions for BI tools like Tableau and Metabase is a key part of operationalizing your data. But even seasoned engineers can run into an error like:

ERROR: permission denied for schema <schema_name>

This post unpacks what causes this error — even when SELECT privileges seem to be correctly granted — and how to fix it.

Q: I granted SELECT on all tables, so why is access still denied?

This issue commonly trips up Redshift users because table-level privileges are not enough on their own. In Redshift (as with PostgreSQL), access to objects within a schema requires explicit usage privileges on the schema itself.

What does that mean?

Even if a user has SELECT permission on a table, they also need USAGE permission on the schema that contains that table in order to:

• List tables in BI tools like Tableau or Metabase
  
  
• Query the table via SQL clients
  
  
• Resolve object names like schema.table
  
  

Q: How do I grant the correct permissions in Redshift?

You need two levels of access:

1. USAGE on the schema

This allows the user to access the schema’s namespace and object references.

GRANT USAGE ON SCHEMA your_schema TO read_only_user;

2. SELECT on all tables in the schema

This allows the user to query the tables themselves.

GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO read_only_user;

Optional (recommended): If you're adding tables over time, you should also grant future access:

ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema 

GRANT SELECT ON TABLES TO read_only_user;

💡 Snowplow Pro Tip: When deploying Snowplow models in Redshift via dbt or other modeling tools, include schema-level permissions in your orchestration scripts to ensure BI access is automatically maintained.

Q: How do I verify permissions?

You can inspect privileges in Redshift with this query:

SELECT has_schema_privilege('read_only_user', 'your_schema', 'USAGE'),

       has_table_privilege('read_only_user', 'your_schema.your_table', 'SELECT');

Both should return true. If the USAGE check returns false, that’s your issue.

Q: Why does Tableau or Metabase still complain even when SELECT is true?

Because the tools typically attempt to list or auto-complete schema/table names, which depends on USAGE privilege on the schema — not just the table.

If you're seeing errors during schema or table discovery, it's usually a USAGE problem. Once resolved, your BI tool should load metadata as expected.

Q: How can Snowplow users handle Redshift access at scale?

If you’re running Snowplow pipelines into Amazon Redshift, you may be creating multiple schemas for raw data, enriched events, or derived models. To make access management easier:

✅ Best Practices for Access Management

• Use database roles (e.g., read_only_role) rather than granting privileges to individual users
  
  
• Automate permission grants via Terraform, Liquibase, or dbt hooks
  
  
• Regularly audit permissions using the pg_namespace and information_schema.role_table_grants tables
  
  
• Ensure consistent naming conventions and access patterns across Snowplow-modeled data
  
  

Final Thoughts

Redshift’s permission model is powerful but layered. If you're seeing “permission denied” errors despite granting table-level access, check that you've also granted USAGE on the schema. Snowplow users leveraging Redshift for event-level analytics can avoid BI friction by incorporating access controls directly into their data modeling workflow.

Snowplow pipelines make it easy to deliver rich, well-structured event data to Redshift — just be sure your consumers can access it!