DEFAULT ACL permissions after redshift table restore from snapshot


I recently encountered an issue where suddenly a group of users could not access a table anymore, due to lack of permission. Just before the issue occurred, we had done a table restore (from a snapshot) for the table in question. Even if the owner of the restored table was still the same as before, the default ACL permissions (which are defined for the table owner user) were not applied to the table, which leads me to think that the table restore happens under another DB username, and only after that the ownership is set to the original user (which does not trigger the ACL anymore)

Under which user does redshift service restore the tables from snapshot (so I can also define DEFAULT ACL for that user)?

asked a year ago263 views
1 Answer

In Amazon Redshift, when a table is restored from a snapshot, the restore process is executed by the AWS account's root user. The root user has full access to all resources in the account, including Redshift clusters and snapshots. However, after the restore is completed, the ownership of the restored table is set to the original user who executed the restore command, not the root user.

The issue you encountered with the default ACL permissions not being applied to the table after the restore could be due to the ownership change from the root user to the original user. Default ACLs are applied when a new object is created, and since the table is restored and not created from scratch, the default ACLs may not be triggered.

To overcome this issue, you can explicitly set the required permissions on the table after the restore is completed. Ensure that the necessary access control privileges are granted to the appropriate groups or users who need access to the table. You can also consider defining the required permissions in the default ACLs for the user who restores the table, as you mentioned, to ensure the permissions are applied correctly during the restore process.

I've listed a link below to documentation on altering privileges that should assist with this issue:

profile pictureAWS
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions