Redshift Schema/Table privileges — ERROR: permission denied for relation (Community)

View the original community article here

Last tested: Oct 24, 2018

A weird quirk that happens specifically with Redshift is that when using a schema that isn't the Public one and tables are being updated with full table drops, every time a table is dropped and re-created it doesn't inherit the access permissions of the old table.

This can cause users to get errors when trying to access the relation like this: ```ERROR: permission denied for relation``` If they've GRANTED privileges on the table, they probably only granted them on that version of the table which was dropped at some point for an update.

There are (at least) 2 solutions for this problem. The simplest thing to do would be to make the looker DB user an owner of the new schema. That way any tables that are created will be fully accessible by the looker user and therefore Looker.

If you are hesitant to give ownership of the schema to the looker user, the other option is to log in as the super user and run this command:
 

ALTER DEFAULT PRIVILEGES

FOR USER schema_owning_user

IN SCHEMA my_schema_name

GRANT SELECT ON TABLES TO my_looker_user;

Note that we'd want to replace schema_owning_user, my_schema_name, and my_looker_user in this example.

This command is setting the default privileges for all tables created in that schema in the future and will solve this issue in Looker.