Redshift Materialised View Permissions on Zero-ETL base data

Question

I am creating a table in Redshift Serverless using Zero_ETL from DynamoDB and then creating a materialised view for use by a BI Dashboard, with the intention that the connection can only access the MV.

```
CREATE MATERIALIZED VIEW messages AUTO REFRESH YES AS
    SELECT  
        value.sender."S"::VARCHAR(10) AS sender, 
        value.source."S"::VARCHAR(10) AS source, 
        value.message."S"::VARCHAR(10) AS message, 
        timestamp 'epoch' + timestamp * interval '1 second' AS datetime 
    FROM "ddb_message"."public"."ddb-message";
    
grant select on table messages to bi_user;
```

According to https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-query.html "other users don't need to have the SELECT permission on the underlying base tables".

However when bi_user queries the MV they consistently get

```
SQL Error [XX000]: ERROR: Relation ddb-message does not exist in the database.
```

unless I also run

```
grant select on table "ddb_message"."public"."ddb-message" to bi_user;
```

Can anyone advise what is wrong here and how the necessity for granting select can be eliminated?

Thanks.

re:Post Agent · Answer

The issue you're experiencing is related to the unique characteristics of Zero-ETL integration between DynamoDB and Redshift. While it's generally true that users don't need SELECT permission on base tables to query materialized views, Zero-ETL integration tables have some specific limitations.

When using Zero-ETL integration, the replicated data from DynamoDB is stored in a special type of read-only table in Redshift. These tables have certain restrictions on how they can be accessed and used.

In your case, the materialized view is trying to access the Zero-ETL integrated table "ddb-message". Even though you've created a materialized view, the underlying Zero-ETL table still requires specific permissions to be accessed, which is why granting SELECT on the base table resolves the issue.

To eliminate the need for granting SELECT on the base Zero-ETL table, you could try the following approaches:

1. Instead of creating a materialized view, create a regular view. Regular views in Redshift don't store data and simply pass through the query to the underlying table. This might allow you to avoid the permission issue.

2. Create an intermediate table that copies data from the Zero-ETL table, and then create your materialized view based on this intermediate table. This would allow you to control permissions more granularly.

3. If possible, adjust your Zero-ETL integration setup to include the necessary permissions for the bi_user to access the replicated data.

4. Consider using Redshift Spectrum or external tables if you need more flexibility in accessing DynamoDB data without the limitations of Zero-ETL integration.

Remember that Zero-ETL integration is designed for seamless read access to operational data, but it comes with certain limitations. For more complex scenarios, you might need to combine Zero-ETL with other data integration strategies to meet your specific requirements while maintaining the desired access control.  
**Sources**  
[Usage of Zero-ETL Integration Tables (from Aurora-Postgres => Redshift Serverless) | AWS re:Post](https://repost.aws/questions/QUAgzgRRwCSLyHjQwyBmzTrA/usage-of-zero-etl-integration-tables-from-aurora-postgres-redshift-serverless)  
[Usage notes - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/r_GRANT-usage-notes.html)

Redshift Materialised View Permissions on Zero-ETL base data

Add your answer

Relevant content