Redshift Spectrum Query Fails with S3 Table 405 Method Not Allowed

Question

#### **Issue Description:**  
I am trying to query an **S3 table (`ams_dev_schema.sp_traffic`) registered in AWS Glue** from **Amazon Redshift**. However, when running:

```sql
SELECT * FROM ams_dev_schema.sp_traffic;
```
I encounter the following error:

```
ERROR: S3ServiceException: The specified method is not allowed against this resource., Status 405, Error MethodNotAllowed
Rid: 7NJ4XZH7E3TB19EY
ExtRid: kfECDdCdJIsgKfoIcpBadQdsZonUVcTULitnUbPZ7KNxDOszxy2/yePGrHJF9xsqotuABoXdpss=
CanRetry: 1
code: 8001
context: Listing bucket=0b43bd30*******7g6p551sjrpwbrirdj79dx4kuse1b--table-s3
location: scan_range_manager.cpp:938
process: padbmaster
```

#### **Setup Details:**  
- **Redshift Cluster:** Standard cluster with AWS Glue Data Catalog as the external schema.  
- **Table Format:** S3-based table registered in AWS Glue.  
- **IAM Role Permissions for Redshift:**  
  - `AmazonS3ReadOnlyAccess`  
  - `AWSGlueFullAccess`  
  - `AmazonAthenaFullAccess`

#### **Questions:**  
- Why is Redshift getting a `405 Method Not Allowed` error when querying the S3 table?  
- Is there an additional IAM permission or Redshift configuration required for querying S3 tables?

re:Post Agent · Answer

The "405 Method Not Allowed" error you're encountering when querying your S3 table through Redshift Spectrum suggests there might be an issue with the IAM permissions or the S3 bucket configuration. Here are a few potential reasons and solutions:

1. IAM Role Permissions:
   While you've mentioned that your IAM role has AmazonS3ReadOnlyAccess, this might not be sufficient. Redshift Spectrum often requires more granular permissions to access S3 buckets. You may need to add specific S3 actions like s3:ListBucket, s3:GetBucketLocation, and s3:GetObject for the specific S3 bucket and objects you're trying to access.

2. S3 Bucket Policy:
   Check if the S3 bucket has a bucket policy that might be restricting access. Ensure that the bucket policy allows the necessary actions (GET, LIST) for the Redshift IAM role.

3. VPC Configuration:
   If your Redshift cluster is in a VPC, make sure it can access S3. You might need to set up a VPC endpoint for S3 or ensure that your cluster has internet access through a NAT gateway.

4. Cross-Region Access:
   Verify that your Redshift cluster and the S3 bucket are in the same AWS region. Cross-region Redshift Spectrum queries are not supported.

5. S3 Access Point:
   If you're using S3 Access Points, ensure they're configured correctly. Redshift Spectrum supports S3 access point aliases, but not VPC with S3 access point aliases.

6. Temporary Table Permissions:
   Ensure that the database user has permission to create temporary tables in the database. This is required for running Redshift Spectrum queries.

To resolve this issue:

1. Review and update your IAM role permissions to include specific S3 actions for your bucket.
2. Check your S3 bucket policy and VPC configuration.
3. Verify that your Redshift cluster and S3 bucket are in the same region.
4. Grant temporary table creation permissions to your database user if not already done.

If the issue persists after these checks, you may need to investigate further by looking at Redshift system tables and logs for more detailed error information.  
**Sources**  
[Query troubleshooting in Amazon Redshift Spectrum - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/c-spectrum-troubleshooting.html)  
[Amazon Redshift Spectrum limitations - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/c-spectrum-considerations.html)

Redshift Spectrum Query Fails with S3 Table 405 Method Not Allowed

Issue Description:

Setup Details:

Questions:

Relevant content