Redshift Spectrum 'alter table add partition' security

Question

What security do I need to hold in order to add a partition to an existing Redshift Spectrum table?

I tried to do:

alter table my_spectrum.my_outputs
add if not exists partition(client_num=101, env_tag='dev')
LOCATION 's3a://my-bucket/my-folder/my-outputs/'
;

This throws a SQLException:

[Amazon](500310) Invalid operation: must be owner of relation my_spectrum;

I am not the owner here... but if I have a few batch programs that might want to append partitions... what should I do? They can't call be the owner right?

Trying to grant myself access doesn't work either:

GRANT ALTER ON EXTERNAL SCHEMA my_spectrum to dedelstein;

Throws

Invalid operation: Grantee can only be an IAM Role or PUBLIC for grants on external resources

Or am I misunderstanding something fundamental about Spectrum? Will it automatically add partitions if my files follow a certain format? Does my file path need to be underneath the main s3 location that was setup when setting the original table location? And does each partition key need to be in the file path (ex 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02/').

Thanks for any help!

Answer 1

AWS Support replied to our ticket with the suggestion that we use 'Glue Crawler'.

1. Glue crawler can automatically add partitions if they are in a particular format and location
2. Glue crawler needs the partition keys to be in the path
3. IAM roles work with lake formation.