AWS Glue: permission denied for schema accounts

The error you're encountering suggests that the AWS Glue job doesn't have sufficient permissions to access the "accounts" schema in your PostgreSQL RDS instance. While you can connect and see the tables using the same credentials, AWS Glue might require additional privileges to perform its operations.

To resolve this issue, you should ensure that the user account used by AWS Glue has the necessary permissions on the PostgreSQL database. Here are some steps you can take:

1. Verify that the user has SELECT privileges on all tables in the "accounts" schema. You may need to grant these permissions explicitly.

2. Ensure that the user has USAGE permission on the "accounts" schema.

3. Check if the user has been granted the necessary role to access the schema and its objects.

4. Make sure the RDS security group allows inbound traffic from the AWS Glue VPC on the appropriate database port (typically 5432 for PostgreSQL).

5. Confirm that the connection details (endpoint, database name, username, and password) in the AWS Glue connection are correct.

6. If you're using a VPC for your Glue job, ensure proper network connectivity between the Glue VPC and the RDS instance. This may involve setting up VPC peering, routes, or VPC endpoints.

7. Review the IAM permissions for the AWS Glue service role to ensure it has the necessary permissions to access RDS and other required AWS resources.

If the issue persists after verifying these points, you may want to temporarily grant more extensive permissions to the user account (such as read-only access to the entire database) for testing purposes. Once you confirm that Glue can connect and perform its operations, you can then gradually restrict the permissions to the minimum required for your specific use case.

Remember to follow the principle of least privilege when setting up permissions for your AWS Glue jobs to maintain security best practices.
Sources
Issue to connect to Postgres RDS using AWS Glue | AWS re:Post
Resolve connection error in AWS Glue | AWS re:Post