2

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?

Shaounak Nasikkar
  • 194
  • 1
  • 11

2 Answers2

1

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)
Bill Weiner
  • 8,835
  • 2
  • 7
  • 18
1

I know this is an old question, but there is an answer now.

You can grant a user the ability to truncate other user tables by using the Redshift Role-based Access Control.

  1. Create a ROLE
  2. Grant TRUNCATE TABLE to the new role
  3. Grant the ROLE to a USER

This user should now be able to truncate other user's tables in the cluster.

Redshift System Permission RBAC

Redshift GRANT syntax

ArcticShrike
  • 29
  • 2
  • Unfortunately, this gives capability to truncate any table in the cluster, not just specific tables. – Gabe Mar 30 '23 at 13:33
  • @Gabe I agree, but it does answer the original question. In other databases, I have granted individual user access to a function that allows them to truncate specific tables. I don't believe Redshift has this capability. – ArcticShrike Apr 03 '23 at 13:37