CODEWITHSUNDEEP
amazon-redshift
REN
REN

Reputation: 11

Getting privilege error when querying in redshift

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: 8274

Answers (2)

Sam Davey
Sam Davey

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:

  1. Reapply the privileges
  2. Next time you need to change the view, rather than DROP and then CREATE the view, use the CREATE OR REPLACE VIEW your_view_name AS command

Upvotes: 1

Jared Piedt
Jared Piedt

Reputation: 466

You must also grant the USAGE privilege on the new schema:

GRANT USAGE ON SCHEMA <schema_name> TO <schema_user>

Upvotes: 2

Related Questions