How can I troubleshoot Lake Formation permission issues in Athena?

5 minute read
0

My Amazon Athena query failed due to “Insufficient Lake Formation permissions” or “COLUMN_NOT_FOUND” errors.

Resolution

Follow these troubleshooting steps for the error message that you received.

Error "Insufficient Lake Formation permissions on Amazon_S3_location"

You get this error when the following conditions are true for AWS Identity and Access Management (IAM) users or roles:

  • The IAM user or role doesn't have the appropriate Amazon Simple Storage Service (Amazon S3) data location permissions from Lake Formation.
  • The IAM user or role tries to create or alter a Data Catalog resource on an Amazon S3 bucket that's registered with AWS Lake Formation.

To resolve this error, you must grant data location permissions to the IAM user or role that you use to create the database or table. When you use Athena with Lake Formation, be sure to grant the required S3 permissions to the IAM user or role from Lake Formation. Also, be sure to grant the data access permissions required by the IAM user or role.

Data access permissions allow the IAM user or role to read and write data to the Amazon S3 location. However, data location permissions in Lake Formation allow an IAM user or role to create and alter Data Catalog resources. The Data Catalog resources point to the registered Amazon S3 location.

Verify that the data lake location is registered with Lake Formation

  1. Open the AWS Lake Formation console.
  2. In the navigation pane, under Administration, choose Data lake Locations.
  3. In Data lake locations, check that the S3 path pointed by the Data Catalog resources is registered with Lake Formation.

Grant data location permissions from the AWS Lake Formation console

  1. In the navigation pane, choose Data locations.
  2. Select Grant.
  3. In the Grant permissions dialog box, select My account.
  4. For IAM users and roles, select the IAM user or role that you want to grant permissions for.
  5. For Storage locations, select the S3 path that you're getting the error from.
  6. Choose Grant.

Note: Follow these steps only if the S3 path is within the same account. If the S3 path is in a different account, first be sure that all cross-account access prerequisites are met. Then, follow the instructions provided in Granting data location permissions (external account).

For more information, see Granting data location permissions (same account).

Error "Insufficient Lake Formation permissions on database_name"

Follow these steps to grant the IAM user or role permission to the database.

  1. Open the Lake Formation console.
  2. In the navigation pane, choose Databases.
  3. Choose your database name, choose Actions, and then choose Grant.
  4. Under Principals, choose IAM users and roles.
  5. For IAM users and roles, choose the IAM user or role that you want to grant permission to the database.
  6. Under LF-Tags or catalog resources, choose Named data catalog resources.
  7. Under Database permissions, choose Describe.
    Note:
    You can also add additional permissions based on your use case.
  8. Choose Grant.

Error "Insufficient Lake Formation permissions: Required Create Table on database_name"

Follow these steps to grant IAM permissions to the database.

  1. Open the Lake Formation console.
  2. In the navigation pane, under Permissions, choose Data lake permissions.
  3. Choose Grant.
  4. Under Principals, choose IAM users and roles.
  5. For IAM users and roles, choose the IAM user or role that you want to use for Athena.
  6. Under LF-Tags or catalog resources, choose Named data catalog resources.
  7. For Databases, choose the database that your IAM user or role writes to.
  8. Under Database permissions, choose Create table or Describe based on your use case.
    Note: The IAM user or role must have both Describe and Create Table permissions on the database to create the table.
  9. Choose Grant.

Error "COLUMN_NOT_FOUND: line 1:8: SELECT * not allowed from relation that has no columns"

Follow these steps to check the DESCRIBE permissions on the database. Then, check the DESCRIBE and SELECT permissions on the table.

Note: The Lake Formation administrator can see the database and table in the Athena console, but needs the required permissions to query data.

Use the named resource method to grant database permissions

  1. Open the Lake Formation console.
  2. In the navigation pane, under Permissions, choose Data lake permissions.
  3. Choose Grant.
  4. Under Principals, choose IAM users and roles.
  5. For IAM users and roles, choose the IAM user or role that you want to use with Athena.
  6. Under LF-Tags or catalog resources, choose Named data catalog resources.
  7. For Databases, choose the database for your table.
  8. Under Database permissions, choose Describe.
  9. Choose Grant.

For more information, see Granting database permissions using the named resource method.

Use the named resource method to grant table permissions

  1. Open the Lake Formation console.
  2. In the navigation pane, under Permissions, choose Data lake permissions.
  3. Choose Grant.
  4. Under Principals, choose IAM users and roles.
  5. For IAM users and roles, choose the IAM user or role that you want to use with Athena.
  6. Under LF-Tags or catalog resources, choose Named data catalog resources.
  7. For Databases, choose your database.
  8. For Tables, choose one or more tables, or All tables.
  9. Under Table permissions, choose Select or Describe to run a SELECT query, or choose the table permissions for your use case.
  10. Choose Grant.

Note: You can also grant permissions on a table through its resource link. To do so, on the Tables page, choose a resource link, choose Actions, and then choose Grant on target. For more information, see How resource links work in Lake Formation.

Related information

Granting data location permissions

AWS OFFICIAL
AWS OFFICIALUpdated a month ago