AWS Glue Permissions required for Amazon Redshift Spectrum Table Creation

In this Amazon Redshift Spectrum tutorial, I want to show which AWS Glue permissions are required for the IAM role used during external schema creation on Redshift database. For a successfull SQL table creation using external table on Amazon Redshift database, a few AWS Glue permissions should be granted to the IAM role by attaching a custom policy. I want to share the error message in case the IAM role is missing these permissions and how to create and attach a suitable AWS Glue policy for the IAM role so that SQL users and administrators can create an external table which will be used to query parquet or csv formatted data files stored on Amazon S3 bucket folders.

While I try to create external table in an external schema on Amazon Redshift database, I got an error message saying "not authorized to perform: glue:CreateTable on resource"
Here is the sample SQL code that I execute on Redshift database in order to read and query data stored in Amazon S3 buckets in parquet format using the Redshift Spectrum feature

create external table spectrumdb.sampletable
(
 id nvarchar(256),
 evtdatetime nvarchar(256),
 device_type nvarchar(256),
 device_category nvarchar(256),
 country nvarchar(256)
)
stored as parquet
location 's3://mys3awsbucket/analytics-data/iot/parquetdata/';

Error message is as follows

An error occurred when executing the SQL command:
create external table spectrumdb.sampletable
(
 id nvarchar(256),
 evtdatetime nvarchar(256),
 device_type nvarchar(256),
 device_category nvarchar(256),
 country nvar...

[Amazon](500310) Invalid operation: User: arn:aws:sts::123456789012:assumed-role/Redshift_S3_ReadOnlyAccess_All/RedshiftIamRoleSession is not authorized to perform: glue:CreateTable on resource: arn:aws:glue:eu-central-1:462037219736:catalog; [SQL State=XX000, DB Errorcode=500310]
1 statement failed.

This is because the role is during external schema creation is missing some specific permissions on target data resources.
Here in this case the permission glue:CreateTable is missing on resource arn:aws:glue:eu-central-1:123456789012:catalog

If you are not the Amazon Redshift database administrator or SQL developer who created the external schema, you may not know the IAM role used or causing authorization error.
Then you can simply run following SQL query on system view SVV_EXTERNAL_SCHEMAS to get detailed information about the external schemas in Redshift database.

Following SQL execution output shows the IAM role in esoptions column

Once you identified the IAM role, AWS users can attach AWSGlueConsoleFullAccess policy to the target IAM role

Once the Amazon Redshift developer wants to drop the external table, the following Amazon Glue permission is also required glue:DeleteTable

Of course, in order to execute SQL SELECT queries on Amazon S3 bucket folders, AWS users should also grant the glue:GetTable permission to the IAM role.

When the Redshift SQL developer uses a SQL Database Management tool and connect to Redshift database to view these external tables featuring Redshift Spectrum, glue:GetTables permission is also required.

It is possible to limit the permissions by creating a custom policy and attaching the IAM policy to the IAM role used in external schema creation on Redshift database.

Following policy is a good alternative to full access prebuild AWS IAM policy AWSGlueConsoleFullAccess

Below is a screenshot from Policy Editor showing the necessary AWS IAM policy configuration for Amazon Redshift Spectrum with Glue actions on Glue resources

For more tutorials on Amazon Redshift Spectrum, SQL developers building applications on AWS Cloud can refer to Create External Table in Amazon Athena Database to Query Amazon S3 Text Files and Amazon Redshift Data Warehouse