CODEWITHSUNDEEP
postgresqlamazon-rdsamazon-vpcamazon-quicksight
Peter Toth
Peter Toth

Reputation: 770

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 :)

Upvotes: 2

Views: 3340

Answers (2)

M.Vanderlee
M.Vanderlee

Reputation: 2996

Another option is to set the password to an md5 hash. PostgreSQL will store it as is. This allows you to create an AWS QuickSight user without having to modify any server settings.

To generate a md5 hash for PostgreSQL concatenate the password with the username, get the hash, then prefix it with md5. Here is a oneliner:

echo -n 'mypasswordUSER' | md5sum | awk '{print "md5"$1}'

If the presented password string is already in MD5-encrypted or SCRAM-encrypted format, then it is stored as-is regardless of password_encryption

For an MD5 encrypted password, rolpassword column will begin with the string md5 followed by a 32-character hexadecimal MD5 hash. The MD5 hash will be of the user's password concatenated to their user name. For example, if user joe has password xyzzy, PostgreSQL will store the md5 hash of xyzzyjoe.

Upvotes: 2

Peter Toth
Peter Toth

Reputation: 770

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

Upvotes: 6

Related Questions