Redshift: Truncating Table Created by another user

user_6186026 0
Viewed 2070

I'm trying to truncate a table in redshift but it's throwing the following error -

SQL Error [500310] [42501]: Amazon Invalid operation: must be owner of relation table;

I have already granted all the privileges on the table to the user. As checked through the online documentation for redshift, I can't grant the truncate table access explicitly like the way it's enabled now in PostgreSQL. Is there a way or a best practice to handle this scenario?

dml amazon-redshift sql-grant truncate privileges
1 Answers

As you say only the table owner or a superuser can truncate a table. There are several options.

  1. Change the table to be owned by the user that needs to truncate but this may not meet other constraints
  2. Alter table to current user, truncate, and alter it back to the previous user (requires DROP permission)
  3. Drop and recreate the table but this may break dependencies
  4. Make a table LIKE the original, perform an ALTER TABLE APPEND to this new table, and then drop the new table (some restrictions like no identity columns)
user_133506520
Related