Not Connecting the postgresql to QuickSight

[Ulises-Menem]

I am having trouble connecting the supabase postgres database into quicksight. It is very easy to connect with other services, like postico, pgAdmin but when trying to connect into quicksight there is an exception. This should not happen really. It should be easy to connect. i already connected Mysql and Sql DATABASES INTO QUICKSIGHT EASILY. Help please!!

[GaryAustin1]

Quicksight has issues with new Postgres versions it appears...
https://community.amazonquicksight.com/t/my-quicksight-cannot-connect-to-rds-postgresql-db-via-vpc/4696/3
Shows some sort of work around. I don't know if that work around will work without superuser privilege (which you don't have on Supabase) or not.

[Ulises-Menem]

Thank you, @GaryAustin1. I noticed that they change the password_encryption method and supabase does not provide superuser access anymore. So i am not being able to do so? Do you know a workaround to this?

[GaryAustin1]

Did you test and it does not work like your link shows?

set password_encryption = 'md5';
create user md5user with password 'XYZabc123!';
grant connect on database postgres to md5user;

Running that in the SQL editor at least creates a sign in user. I do not know if that user can connect and is really using md5 though as not going to set up to test that.

[Ulises-Menem]

Exactly, It does not change the password_encryption, for that i need to ALTER SYSTEM but for altering i need superuser privilege

[GaryAustin1]

OK. I thought though, that article implied you could set the password with md5 hash for just that user as it mentions just setting it in the session and not for the system, then while in that session setting the password. Then when you access the user it would be the correct format. But I don't know what QuickSight is up to.
Also Amazon RDS Postgres does not have superuser privilege either so would think it would be an issue there also.

[Ulises-Menem]

@GaryAustin1 At least how postgres in supabase works is that you could not encrypt passwords with different hash methods because it does not provide an automatic hashing system to understand in which method the password was hashed. So, the only way to do it is by setting the system hash and not for a user. I think it is something that cannot be done in other environment, there could be exceptions.

Thank you very much for the answers, you were a huge help. I will need to change the service provider and leave supabase.

[Ulises-Menem]

Okay something weird happen now, I managed to set the new password encryption

[Ulises-Menem]

With the exact same commands from 2 days ago