Redshift "permission denied for schema": the GRANT USAGE fix

You granted SELECT on every table. Your BI tool still says "permission denied for schema".

The fix is usually two lines. Redshift needs USAGE on the schema as well as SELECT on the tables inside it:

GRANT USAGE ON SCHEMA your_schema TO read_only_user;
GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO read_only_user;

This guide explains why, how to check what a user can see, and how to keep access in place as new tables land.

Why does Redshift say "permission denied for schema"?

The full error looks like this:

ERROR: permission denied for schema your_schema

In Redshift, USAGE on a schema is what makes the objects in it accessible. Actions on those objects, such as SELECT on a table, are granted separately (AWS GRANT reference). A user with SELECT on every table but no USAGE on the schema still can't get in.

Without USAGE on the schema, a user can't:

• List the schema's tables in BI tools like Tableau or Metabase
• Query those tables from a SQL client
• Resolve names like your_schema.your_table

"Permission denied for relation" or "for table": what's different

This error usually means the opposite problem. The user can reach the schema, but doesn't have SELECT on the table or view they're querying. Grant SELECT on that table, or on every table in the schema:

GRANT SELECT ON your_schema.your_table TO read_only_user;
-- or
GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO read_only_user;

Grant access with a role

Granting to one user at a time gets hard to track. A role holds the permissions once, and you grant the role to each user who needs it:

CREATE ROLE read_only_role;
GRANT USAGE ON SCHEMA your_schema TO ROLE read_only_role;
GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO ROLE read_only_role;
GRANT ROLE read_only_role TO read_only_user;

To give a new BI user access, you only need the last line. See CREATE ROLE in the AWS docs.

Why the error comes back after new tables land

GRANT SELECT ON ALL TABLES IN SCHEMA covers the tables that exist when you run it. Tables created later aren't included, so the error returns for them. There are two ways to cover new tables.

Scoped permissions. A scoped permission applies to all current and future tables in a schema, whoever creates them (AWS scoped permissions). You can grant it to a user or a role:

GRANT USAGE ON SCHEMA your_schema TO ROLE read_only_role;
GRANT SELECT FOR TABLES IN SCHEMA your_schema TO ROLE read_only_role;

Default privileges. ALTER DEFAULT PRIVILEGES applies only to new objects, and only to objects created by one user. If you leave out FOR USER, that user is whoever runs the command (AWS ALTER DEFAULT PRIVILEGES). This is the usual reason the error comes back: an admin set default privileges for themselves, but a loader or dbt user creates the tables. Name that user instead:

ALTER DEFAULT PRIVILEGES FOR USER loader_user IN SCHEMA your_schema
GRANT SELECT ON TABLES TO ROLE read_only_role;

Only a superuser can set default privileges for another user. With IN SCHEMA, the user you name also needs CREATE on that schema.

This matters with Snowplow data. The Snowplow Redshift loader creates a table for each type of self-describing event and entity, and adjusts tables as new event types arrive. So new tables appear in your events schema over time, and a one-off grant won't cover them.

How to check a user's permissions

Run this as an admin:

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 is false, that's your problem. For a wider view, schema-level scoped permissions are in SVV_SCHEMA_PRIVILEGES, and default privileges are in PG_DEFAULT_ACL.

Why Tableau or Metabase still complains when SELECT is true

BI tools list schemas and tables and auto-complete names before they run a query. That discovery step depends on USAGE on the schema, not just SELECT on the table.

If the error appears while the tool is loading schemas or tables, it's almost always USAGE. Once it's granted, the tool should load the metadata as normal.

Managing Redshift access for Snowplow data

Snowplow data in Redshift usually spans more than one schema: the events schema the loader writes to, and the schemas your data models build. A few habits keep BI access working:

• Grant to roles, not users. One read-only role per audience is easier to audit than grants spread across users.
• Cover future tables. Use scoped permissions or default privileges for every schema the loader or your models write to.
• Keep grants in code. Put them in Terraform or dbt hooks next to the models, so a rebuild doesn't drop them. Snowplow's dbt packages support Redshift, so the grants can live in the same project.
• Check after changes. Run the permission check above after a new schema, a new model or a new event type.

More on working with Snowplow data in Redshift:

• Snowplow for Amazon Redshift
• How to generate Redshift DDL for existing tables
• De-duplicating events in Redshift
• Deleting customer data from Redshift for GDPR

Frequently asked questions

Why am I getting "permission denied for schema" in Redshift?

The user doesn't have USAGE on the schema. USAGE makes the objects in a schema accessible, and SELECT on the tables is granted separately, so a user with SELECT but no USAGE still gets the error. Run GRANT USAGE ON SCHEMA your_schema TO your_user.

How do I grant usage on a schema in Redshift?

Run GRANT USAGE ON SCHEMA your_schema TO your_user, or TO ROLE your_role to grant it to a role. Then grant SELECT on the tables, for example GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO your_user.

What does "permission denied for table" or "permission denied for relation" mean?

It usually means the user can reach the schema but doesn't have SELECT on the table or view they're querying. Grant SELECT on that table, or on all tables in the schema.

How do I grant SELECT on all tables in a Redshift schema, including future tables?

GRANT SELECT ON ALL TABLES IN SCHEMA covers the tables that exist now. For tables created later, use a scoped permission (GRANT SELECT FOR TABLES IN SCHEMA your_schema TO ROLE your_role), which covers current and future tables. Or use ALTER DEFAULT PRIVILEGES FOR USER, naming the user that creates the tables.

How do I check a user's schema permissions in Redshift?

Use has_schema_privilege('your_user', 'your_schema', 'USAGE') and has_table_privilege('your_user', 'your_schema.your_table', 'SELECT'). Both should return true. SVV_SCHEMA_PRIVILEGES shows schema-level scoped permissions, and PG_DEFAULT_ACL shows default privileges.