Hello, I am trying to use materialized views in Redshift, where I have a Zero-ETL integration configured from Aurora MySQL to Redshift. Since the destination Redshift database is completely read-only and I cannot create the views there, I have created a second database and have created external schemas in the new database that point to the schemas in the ETL destination database.
I have then created a local schema in the second database, since the external schemas are read only by default, and have defined my materialized view there. I can successfully query against this materialized view joined against external schema tables using my administrative AWS console user.
However, I cannot run the same query as a separate user I created for application access, and I cannot figure out what the correct way to grant permissions is. It seems like there are a bunch of hidden schemas and relations that are created, either as part of the Zero-ETL setup or the external schema setup.
The first issue I get is:
ProgrammingError: {'S': 'ERROR', 'C': 'XX000', 'M': 'Schema internal_padb_del_schema_119214 does not exist in the database.', 'F': '../src/external_catalog/external_catalog_api.cpp', 'L': '1521', 'R': 'localize_external_table'}
This goes away if I explicitly grant USAGE to the group the application user belongs to for this schema in the Zero-ETL destination database (since the hidden schemas are in there):
GRANT USAGE ON SCHEMA internal_padb_del_schema_119214 TO GROUP data_viewers;
After this, I get a new error, which I have not been able to resolve as I don't know where the relation is resolved in comment below:
ProgrammingError: {'S': 'ERROR', 'C': 'XX000', 'M': 'Relation padbdel#119246 does not exist in the database.', 'F': '../src/external_catalog/external_catalog_api.cpp', 'L': '1521', 'R': 'localize_external_table'}
What is the correct way to grant permissions without having to find all of these hidden schemas and grant permissions to them independently?
AWS OFFICIALUpdated 2 years ago
I have been able to get the query to run by running the generated SQL from this, however, this seems like a poor user workflow.
SELECT DISTINCT 'GRANT USAGE ON SCHEMA ' + schemaname + ' TO GROUP data_viewers; GRANT SELECT ON ALL TABLES IN SCHEMA ' + schemaname + ' TO GROUP data_viewers;' FROM pg_tables WHERE schemaname LIKE 'internal_padb%'