My QuickSight cannot connect to RDS PostgreSQL DB via VPC

Question & Answer
1

Hello,

I am trying to connect from QuickSight to an AWS RDS PostgreSQL database within a VPC but the connection validation fails. I do have QuickSight Enterprise Edition so VPCs are supported and the PostgreSQL database has no public accessibility so it cannot be reached from the internet. I am receiving the following error:

GENERIC_SQL_EXCEPTION
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.

I followed pretty much the same steps that are described in this AWS article:

I also ran the Reachability Analyzer between the QuickSight ENI address and the RDS database IP address and it was successful, the status is shown as Reachable.

Any idea why the DB connection can fail?

Thanks a lot in advance,
Istvan

2

It looks like there is network access but access is being stopped by postgreSQL. To debug this, add this line in pg_hba.conf:

host    all             all           all            md5

and restart posgreSQL (or reload configuration).

Then check again to see if QuickSight is now allowed to access your instance. If it works you may want to look into hardening that pg_hba.conf line there to restrict access to just QuickSight.

3

I just found an article on QuickSight community talking about DB engine 14.2 incompatibility with QuickSight.

"For me, the problem was that the RDS Postgres instance was using Postgres version 14.2.
Although according to Quicksight documentation it should work, it just doesn’t.

I guess Quicksight uses some old postgres client behind the scenes that is not compatible with this setup. Connecting to other RDS Postgres with version 12.8 worked. This other DB is in same subnets and same security groups.

Confirmed this by downgrading the 14.2 version to 12.8 and now the connection works."

So the issue seems to be an incompatibility issue between PostgeSQL 14.2 engine which our database is using and QuickSight. Have you run into this issue elsewhere?

I will need to check with our DB guys if we can downgrade our DB engine version.

4

QuickSight is not working with default Postgres 14 configuration. Postgres 14 changed the default password encryption from MD5 to SCRAM (see PostgreSQL 14 and Recent SCRAM Authentication Changes - Should I Migrate to SCRAM?). This looks like requiring a driver upgrade to 42.2.0 (see java - Unable to connect to Postgres DB due to the authentication type 10 is not supported - Stack Overflow) and QS is using 42.0 (see Maven Central Repository Search).

The workaround is explicitly set the password_encryption parameter to “md5” in Postgres DB cluster parameter group. In order to quickly allow QuickSight to connect to the RDS PostgreSQL instance, you may consider creating a dedicated QuickSight user with the compatible password encryption. To do so, connect to the RDS PostgreSQL instance using as the master user and perform the following:

i. Verify current password_encryption value:

show password_encryption;

ii. Set the session variable of the parameter to 'md5:

set password_encryption = 'md5';

iii. Create a user and assign it the necessary credentials

create user (username) with password '(password)';
grant connect on (database) to (username);

iv. Add any additional permissions as required.

v. Use the new DB user to connect from QuickSight and it should be able to connect successfully using the “md5” encryption instead of “scram-sha-256”.
that.

1 Like
5

Thanks for your solution seattle, it works.

Just one thing to add, if you change the encryption in the main database don’t forget the

ALTER USER postgres WITH PASSWORD 'root';
8

@iszegedi @darcoli @seattle @sharker92

Amazon QuickSight now offers the latest PostgreSQL driver version for the connectors: RDS hosted PostgreSQL, Aurora PostgreSQL and Vanilla PostgreSQL thereby supporting SCRAM based authentication to PostgreSQL from QuickSight. No additional configuration is needed in Amazon QuickSight as long as you use the appropriate PostgreSQL engine version and have the correct configurations in PostgreSQL for SCRAM. If you still see any issues around establishing a SCRAM authentication to PostgreSQL from QuickSight, please create a support ticket.

– Amazon QuickSight Connectivity Team

1 Like