RDS Data Source Validation timeout in Amazon QuickSight

0

UPDATE: After trying @AWS-Aaftab_A's suggestion, I am still having the same problem. Attaching relevant screenshots and security group configurations that I have tried below.

  • I followed the steps you have suggested, created a new parameter group, attached it to the db, changed the authentication to md5, created a new user (bi_test_2). Restarted multiple times and still getting a timeout :(.
    • After changing the encryption type and creating a new user
  • Tried different security group combinations and attached both of them to the RDS instance. Here are the inbound and outbound rules for each.
    • The inbound sg that the aws docs suggested(ireland is my region, hence the ireland CIDR is used): Enter image description here
    • The inbound/outbound sg that I believe makes more sense(taken from a different tutorial where the target is the sg of my rds instance)Enter image description here Enter image description here

Hello

I have an Amazon RDS PostgreSQL instance (v14.7) hosted inside our production VPC, which is not publicly available. I have been trying to connect to the RDS instance from Quicksight Enterprise but no matter what I have tried, I'm experiencing Source Validation Timeouts and I started to suspect that the official AWS Docs for RDS<->Quicksight connection is not valid for postgres 14.7 since I have seen issues online similar to the one that I am having.

I am in desperate need of help because I believe I am following all the steps as described in the official docs.

Here are the things that I have tried:

Some issues I found online that I think might be related to the issue I am having:

2 Answers
3
Accepted Answer

There is a known issue with QuickSight and PostgreSQL 14, whereby newer versions of PostgreSQL uses a newer password encryption namely "scram-sha-256" which the JDBC driver which QuickSight uses to connect does not support. The driver only supports "md5" encryption. If this applies to your case, you can verify this by creating a test user with "md5" encryption:

  1. Verify current password_encryption value:

show password_encryption;

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

set password_encryption = 'md5';

  1. Create a user and assign it the necessary credentials

create user (username) with password '(password)'; grant connect on (database) to (username); Note: add any additional permissions as required.

Or Modify the password for existing user - \password (username)

  1. EXTENSION rds_tools -

CREATE EXTENSION rds_tools;

  1. Query to verify the user's encryption_type.

select * from rds_tools.role_password_encryption_type();

The above applies to session level only.

However, the second workaround consist on creating a RDS custom parameter group with the parameter password_encryption set to 'md5' and attaching it to the cluster, while rebooting the cluster to have the new parameter group to take effect. After this has been done, any new password set will be encrypted using 'md5'. Any existing password will still be encrypted using 'scram-sha-256', so to update the password encryption, you will need to alter the target user password (you can use a Master user to set the same password for this user, having the encryption changed). As this option will have all the future passwords encrypted using 'md5', you may not want to use it as you would like to still have users not being used by QuickSight to be encrypted using 'scram-sha-256'.

Steps to change the authentication to md5

  1. Create a new Parameter group.
  2. Once a new Parameter group is created > Search for password_encryption > Edit parameters
  3. Select md5
  4. Save changes
  5. Then you can modify your database to use the DB parameter group created in step 1. Please note that you may need to reboot your instance for the changes to take place.
  6. After the change above is complete, create a new user to be used in the QuickSight authentication with the RDS instance.

I would kindly request that you then use the above user to try and connect from Quicksight to RDS and, let me know what the outcome is. If this is the case, the "password_encryption" parameter RDS PostgreSQL versions 13 or newer needs to be set to 'md5'.

Reference : https://community.amazonquicksight.com/t/my-quicksight-cannot-connect-to-rds-postgresql-db-via-vpc/4696/4?u=seattle

It's important to note, for any RDS cluster which is still connecting without the above workaround after the cluster upgraded to PostgreSQL 14 or above, it should mean that no password change for the user has happened since cluster upgrade, thus remaining with the 'md5' encryption.

AWS
answered 10 months ago
  • Thank you for the detailed response. Unfortunately, I am still having the same timeout error. I updated the question with screenshots while trying what you have suggested. I appreciate if you can give it a look :/

  • Eventually, what @AWS-Aaftab_A suggested worked. I also had a misconfigured security group that caused the same timeout, making it harder to detect the problem.

    It is really hard to believe that Amazon/Quicksight has not fixed this problem considering it has been a year since the release of postgres 14.x on Amazon

    Thanks!

  • Thank you for this answer AWS-Aaftab_A, please if AWS doesn't have plans to use a JDBC driver that supports scam-sha-256, at least make a documentation update, so people like us that were forced to upgrade from postgres10 to postgres14 due to the EOL, and have been using Quicksight for years, don't waste almost a month looking for a solution and clients getting angry because their dashboards stopped working.

  • @Erick_Gutierrez have you been able to continue using QuickSight? After this answer the initial connection was succesful but I started to see random timeouts as I wrote in https://repost.aws/questions/QUf7s1v2XiQ-KtSNQimgC1Jw/quicksight-postgresql-random-timeouts-when-creating-a-dataset

0

Also to confirm. Did you try restarting your instance after the config changes You made. Be sure that the port for your instance is at least reachable on the network and your pg_hba.conf file is in order. This works magically sometimes

answered 10 months ago
  • yep I have restarted the database multiple times. I have updated the question with screenshots that I have tried in the last couple of hours. I would appreciate if you can take a look.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions