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.