🐞 Quicksight can't connect to aws rds - postgres (v14 and above)

[LavMatt]

Describe the bug.

The issue is an encryption type mismatch between the postgres version (15) we have used for aws rds instance and the driver used by quicksight.

Postgres 14 and above encrypts passwords using SCRAM and before that (which is what quicksight expects) uses md5. This results in the following error when trying to connect to CP postgres from AP quicksight

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.

Suggested fixes relate to configuring postgres to encrypt passwords using md5 encryption

some useful links
https://community.amazonquicksight.com/t/my-quicksight-cannot-connect-to-rds-postgresql-db-via-vpc/4696/4
https://repost.aws/questions/QUH9WSQIV7Q026ODqYS7p2HQ/rds-data-source-validation-timeout-in-amazon-quicksight

To Reproduce

1. go to quicksight on the ap
2. click datasets>new dataset
3. click postgreSQL
4. enter db info and try to connect

Expected Behaviour

to be able to connect to postgres via quicksight

Additional context

No response

[jacobwoffenden]

Raised with AWS - 173149054800692

[jacobwoffenden]

IIRC this was solved after DC team made some changes to RDS, we should capture them in user guidance

[jacobwoffenden]

👋 @ministryofjustice/data-catalogue do you have list of things you did to make this work?

[MatMoore]

@jacobwoffenden we basically had to change to md5 for the user that connects to quicksight. The commands are in our runbook here https://runbooks.find-moj-data.service.justice.gov.uk/documentation/runbooks/015-dashboard.html#data-connectivity

Looks like you're already in the email chain with AWS but let me know if you want me to resend that as well.