Reputation: 11
created two schemas in redshift and one has all tables and other schema has views created from earlier schema tables. Users were granted select privileges on second schema views. When trying to query one particular view using select in redshift, it throws "Job::UserError: PG::InsufficientPrivilege: ERROR: permission denied for schema".
The error comes only when accessing that particular view, all others are absolutely fine.
Verified the privileges and users do have select permission on views and tables. Any direction would be helpful.
Upvotes: 1
Views: 8258
Reputation: 401
If you find that this is only affecting one particular view, it may be because the view was dropped and recreated after the privileges were assigned (and therefore the table has lost its inheritance of the schema permissions).
The solution may be to:
DROP
and then CREATE
the view, use the CREATE OR REPLACE VIEW your_view_name AS
commandUpvotes: 1
Reputation: 466
You must also grant the USAGE
privilege on the new schema:
GRANT USAGE ON SCHEMA <schema_name> TO <schema_user>
Upvotes: 2