
This assumes you are familiar with AWS Athena. If not, check out my article FreeKB - Amazon Web Services (AWS) - Getting Started with Athena.
Serverless Application Model (SAM) can be used to create a CloudFormation Stack that includes the following resources that will be used to for Athena to connect to Postgres.
- A Lambda Function
- An IAM Role
- An IAM Policy
The CloudFormation Stack will need an S3 "spill bucket" that Athena will use to "spill" data to so before using SAM to create the CloudFormation Stack, let's create the spill bucket. The aws s3api create-bucket command can be used to create an S3 Bucket.
aws s3api create-bucket --bucket my-athena-spill-bucket-abc123 --region us-east-1
The Lambda Function that will be used by Athena to connect to Postgres will need a Security Group that allows connections to Postgres. The aws ec2 create-security-group command can be used to create the Security Group.
aws ec2 create-security-group --group-name my-athena-postgres-security-group --description "Athena Postgres Security Group"
Later on, we are going to be configuring a Lambda Function with a JDBC Postgres connection string where you can either include the username and password to connect to Postgres in the connection string.
postgres://jdbc:postgresql://172.0.0.2:5432/db1?user=john.doe&password=itsasecret
Or you can retrieve the username and password from an AWS Secret. This is basically a Java JDBC. Check out my article FreeKB - Java - Connect to a SQL database via DriverManager for an example of how to connect to Postgres using Java.
postgres://jdbc:postgresql://172.0.0.2:5432/db1?secret=${my_secret}
If you are going to retrieve the username and password from a secret, which I strongly recommend, the aws secretsmanager create-secret command can be used to create the secret.
aws secretsmanager create-secret \
--name my-secret \
--description "postgres username and password" \
--secret-string "{\"username\":\"john.doe\",\"password\":\"itsasecret\"}"
Let's say you have a Postgres database running on an EC2 instance in a private subnet, meaning the EC2 instance has no internet access, no Internet Gateway.
The EC2 instance will be associated with a Security Group. Let's update the Security Group being used by the EC2 instance to allow connections from the Security Group we created a moment ago on whatever port is being used by Postgres, which by default is 5432. The aws ec2 authorize-security-group-ingress command can be used to allow connections from the Security Group you created to the Security Group being used by the EC2 instance.
aws ec2 authorize-security-group-ingress --group-id sg-abc123def456ghi789 --protocol tcp --port 5432 --source-group sg-987ihg654fed321cba
In the Athena console, let's select Data sources > Create data source.
And select the Postgres data source.
And then let's select Create Lambda function.
This should bring you to the Lambda Applications console. If you are going to retrieve the username and password from a secret, which I strongly recommend, the SecretNamePrefix will almost always be the name of your AWS Secret.
And DefaultConnectionString will be your Postgres JDBC URL.
SpillBucket will be the S3 spill bucket you created.
SecurityGroupIds will be the Security Group you created for Athena Postgres.
And SubnetIds will be the same subnet as being used by your EC2 instance running Postgres.
This will create an IAM Role and an IAM Policy.
And a Lambda Function.
The reason it was important to use the same Subnet in the same Virtual Private Cloud as the EC2 instance running Postgres is so that the Lambda Function is in the same Subnet in the same VPC as the EC2 instance.
This will also create a CloudFormation Stack.
If you are going to retrieve the username and password from a secret, which I strongly recommend, the IAM Role will probably need to be updated so that the role is allowed to retrieve the AWS Secret. The aws iam attach-role-policy command can be used to attach the SecretsManagerReadWrite Policy to the Role.
aws iam attach-role-policy --policy-arn arn:aws:iam::aws:policy/SecretsManagerReadWrite --role-name serverlessrepo-AthenaPostgreSQLConnect-FunctionRole-abc123
And if all goes according to plan, you should have a successful connection to your Postgres database in Athena, and you should have your lists of tables and columns, and be able to issue SQL queries. Nice!
Did you find this article helpful?
If so, consider buying me a coffee over at