QuickSight Unable to Connect to RDS(postgres)

Question

I have QuickSight and an RDS(postgres) instance, both in the same region (US-West-2). I also have a valid VPC Connection (checked in the "Reachability Analyzer") between my QuickSight ENI and the RDS ENI.

My Goal is to connect QuickSight to the RDS DB, so I can use the DB as a QuickSight Data Source.

However, when I go to create a dataset in QuickSight, I am unable to validate the connection. The error I get back is a Postgres authentication error (copied below).

I've verified that:
- The VPC Connection is valid
- The DB username/password is correct
- Security Groups are configured as specified in AWS Documentation (gone over these multiple times) 
- I can interact with the DB otherwise (via application)

Error message after failed connection validation:

```
The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver.
```

Answer

Sorry guys but... no way to connect to Postgres 14.2 on Aurora yet?

Answer

It appears to be still occurring.

Answer

Hi,

You can try to update the cluster parameter group for parameter "Password encryption" to "md5". Create new dba user for your scenario and try connecting using that user on quicksight.

Thanks,
Shashi

Answer

I was able to find a workaround in the QuickSight Community without downgrading. It was simple fix by creating a new user with MD5 based password. I am running Postgresql 15 on Aurora Serverless V2.

Steps for new user:
set password_encryption = 'md5';
create user (quicksight_user) with password (PASSWORD_HERE);
grant connect on (database) to (quicksight_user);

If you already have a user created for QuickSight then simply Alter the user after setting MD5
Steps for Existing user:
set password_encryption = 'md5';
ALTER USER quicksight_user WITH PASSWORD '79kjrkay73334gF';

Once QuickSight supports the new password encryption then this will not be necessary.

Source: https://community.amazonquicksight.com/t/my-quicksight-cannot-connect-to-rds-postgresql-db-via-vpc/4696/4

Answer

Hello,
 
I understand that you are trying to connect your PostgresSQL DB instance to a QuickSight instance but are receiving the error: "The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver." when validating the connection.
 
I believe the issue is related the PostgreSQL version that your DB instance is running. Certain versions of PostgreSQL use the authentication type 10 which causes the aforementioned error.
 
The issue was replicated by using version 14.2 of PostgreSQL which received the same error. I solved it by using another DB instance running version 13.4 which lead to a successful validation.
 
It is currently not possible to downgrade the PostgreSQL version of a DB instance. I recommend using AWS Database Migration Service to transfer your data to another DB instance.
 
Step-by-Step AWS DMS Walkthroughs: https://docs.aws.amazon.com/dms/latest/sbs/dms-sbs-welcome.html

QuickSight Unable to Connect to RDS(postgres)

Contenus pertinents