postgresqlamazon-rdsamazon-vpcamazon-quicksight

Connecting RDS to quicksight throws `GENERIC_SQL_EXCEPTION`

I have a RDS PSQL14 database on eu-central-1 and would like to connect this to Quicksight as a new data source.

However, I always get the following error:

sourceErrorCode:    GENERIC_SQL_EXCEPTION
sourceErrorMessage: 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 have no clue how could I edit the pg_hba.conf file. Though I know that the VPC in which the RDS is in would allow the connection.

Most likely this is something with sha256 vs md5 authentication or so I've read in a couple of posts, but I don't know for sure. Please someone educate me :)

Solution

  • This was a huge time waster.

    tldr: psql 13 and up uses scram-sha-256 but older version only support md5.

    The reason behind this is that AWS Quicksight is using PostgreSQL JDBC driver 42.2.1 If you try to connect this with any of the newer psql versions it will fail due to a change made to the password authentication method used in the more recent versions of PostgreSQL (scram-sha-256). However, the 42.2.x driver only supports connecting via md5 passwords.

    How to solve?

    1. Downgrade:

    If you downgrade to version 12.9 or below the problem should sort itself out.

    2. Change Auth to md5:

    !! it will affect all users and connections !!

    1. Create a new Parameter group.
    2. Once a new Parameter group is created > Search for password_encryption > Edit parameters
    3. Select md5 (change the "rds.accepted_password_auth_method" parameter to allow md5 + scram which would allow you to create a user with md5 for QuickSight to connect with).
    4. Save changes
    5. Then you can modify your database to use the DB parameter group created in step 1.
    6. Create a new user to be used in the QuickSight authentication with the RDS instance with the necessary permissions.

    3. New Quicksight user with session-level md5:

    1. Verify current password_encryption value:
      • show password_encryption;
    2. Set the session variable of the parameter to 'md5:
      • set password_encryption = 'md5';
    3. Create a user and assign it the necessary credentials
      • create user (username) with password '(password)';
      • grant connect on database (database) to (username);
    4. Use the user to connect from QuickSight and it should be able to connect successfully using the "md5" encryption and not "scram-sha-256".