How do i connect quicksight to postgres hosted on aws lightsail instance?

neelay · June 6, 2024, 5:52pm

Hi there,

I am trying to connect Quicksight to a database hosted on AWS Lightsail instance.
I have VPC peering enabled on lightsail side too.

Thanks

duncan · June 7, 2024, 3:17pm

Hello @neelay !

My first recommendation would be to double check that you are in the correct region on Quicksight:

Did you receive any kind of errors about the connection or were you not able to validate the postgres connection?

Neeraj · June 7, 2024, 3:35pm

Hi @neelay ,
While doing VPC peering, hoping you have ‘Enable VPC peering’ is checked for the region where your QuickSight is deployed. Then you need to have security group for QuickSight to allow traffic from the Lightsail instance.

neelay · June 10, 2024, 6:43pm

Hi @Neeraj

Thanks for the response.
I have VPC peering enabled, everything is in the same region.
I also have a firewall rule on Lightsail server to allow traffic from private IP from the VPC.

I have created a VPC connection on Quicksight from https://ap-south-1.quicksight.aws.amazon.com/sn/console/vpc-connections/new and connected it to VPC.

But when I try to create data source, and click on Postgres, I don’t see an option to select that VPC for connection purposes.

Sanjeeb2022 · June 11, 2024, 3:14am

Hi @neelay - Since the database is configured in a server, I do not think you need to provide details on the VPC. Can you try to provide the details of postgres and try to connect ( hope the incoming traffic / ips of quicksights are white listed in the security group of lightsail).

Regards - Sanjeeb

neelay · June 11, 2024, 3:49am

@Sanjeeb2022
not sure if I understood. Can you share specifics?

My QS and lightsail, both are launched in Mumbai region.

I believe quicksight outbound can only be controlled by assigning vpc. I don’t think there is a way to know which IP range QS will make outbound request. The other way is to make server accept connection from 0.0.0.0

Sanjeeb2022 · June 11, 2024, 4:17am

Hi @neelay - Please see the below URL for reference - AWS Regions, websites, IP address ranges, and endpoints - Amazon QuickSight

I have not worked in lightsail but i am thinking there is no difference in configuring a database in an ec2 instance vs lightsail.

Regards - Sanjeeb

neelay · June 11, 2024, 6:21am

@Sanjeeb2022 ec2 and lighsail are not the same.

Sanjeeb2022 · June 11, 2024, 6:34am

Hi @neelay - Ok but from a database configuration prospective it should behave same as ec2. Did you try to whitelist the quicksight IPs and test the connection.

Regards - Sanjeeb

neelay · June 11, 2024, 6:48am

@sajeeb that’s my last option, as the request has to come through the public internet. I want to do it through the aws private network.

neelay · June 11, 2024, 9:23am

@Neeraj @Kristin
Besides network security, There is also a problem connecting to postgresql >= 14

ref: PostgreSQL 14+ support
Is there any update on that?

Currently, I have below in my pg_hba.conf (if scram-sha-256 is still not supported on postgres, )

host    all             quicksight           52.66.193.64/27    md5

server configuration

ssl = on and ssl_min_protocol_version=TLSv1.2

I have tried with md5 and scram-sha-256; In both case I am getting this error on quicksight.

region: ap-south-1
timestamp: 1718097082394
requestId: bcbd106e-6bd3-4f46-bbf9-9e8e93c0bf65
sourceErrorCode: SSL_CERT_VALIDATION_FAILURE
sourceErrorMessage:  
`SSL error`: 
    PKIX path building failed: `sun.security.provider.certpath.SunCertPathBuilderException`: unable to find valid certification path to requested target

And on the Postgresql server side, I am getting the below error

LOG:  could not accept SSL connection: sslv3 alert certificate unknown
LOG:  could not accept SSL connection: wrong version number
HINT:  This may indicate that the client does not support any SSL protocol version between TLSv1.2 and TLSv1.3.

Any suggestion?

thanks
Neelay

Kristin · June 11, 2024, 4:45pm

Hey @neelay – I pinged one of our SAs who is off the rest of the day but should be back tomorrow – to take a look at this. Thx!

Xclipse · June 28, 2024, 3:39am

Thank you for letting us know. I would recommend filing a case with AWS Support where we can dive into the details so that we can help you further. Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf. Hope this helps!