Federated catalog not showing all tables in PostgreSQL RDS Database

The issue you're experiencing with not all tables showing in your federated catalog after setting up AWS Lake Formation is likely related to permissions. When you set up a federated catalog using Lake Formation, it introduces an additional layer of access control that needs to be configured properly.

Here are a few steps to troubleshoot and potentially resolve the issue:

1. Check Lake Formation permissions: Make sure you have granted the necessary permissions in Lake Formation for all the tables you want to access. Lake Formation requires explicit permissions to be set for databases, tables, and columns.

2. Grant permissions to the correct principal: Ensure that you've granted the appropriate permissions to the IAM role or user that you're using to access the data. This includes permissions like "Describe" for the database and tables.

3. Verify catalog setup: Double-check that your federated catalog is set up correctly in Lake Formation. This includes ensuring that the connection to your RDS PostgreSQL database is properly configured.

4. Check for table-level permissions: In Lake Formation, you might need to grant permissions for each table individually. The fact that only 6 out of 126 tables are showing suggests that permissions might be set for those 6 tables but not for the others.

5. Refresh the catalog: Sometimes, changes in permissions or newly added tables might not appear immediately. Try refreshing the catalog or waiting a short while to see if the tables appear.

6. Review the IAM role: Ensure that the IAM role associated with your Athena queries has the necessary permissions to access Lake Formation and the underlying data source.

If you've verified all these points and are still experiencing issues, you may need to consult AWS support or dive deeper into the Lake Formation and Glue catalog configurations to identify any misconfigurations or permission issues.

Remember, Lake Formation is designed to provide fine-grained access control, which means it requires more detailed permission settings compared to direct Athena connectors. This additional security layer is likely the reason you're seeing a difference in behavior between the two methods of access.
Sources
Catalog and govern Amazon Athena federated queries with Amazon SageMaker Lakehouse | AWS Big Data Blog
Querying federated catalogs - AWS Lake Formation
Querying federated catalogs - AWS Lake Formation