I'm attempting to integrate PostgreSQL into my ASP.NET Core project using Entity Framework. However, whenever I try to interact with the database, I encounter the following error: 'Only authentication clear text password and authentication md5 password supported for now. Received 10.'" as error message suggests that there's an issue with the authentication method being used. 

It seems that PostgreSQL is expecting either a clear text password or an MD5 hashed password, but it received something else  to resolve this issue, we need to ensure that the authentication method being used in our connection string matches the requirements of PostgreSQL. 

We should check our PostgreSQL server configuration and the connection settings in our ASP.NET Core project to ensure that we're using either clear text or MD5 password authentication. 

If we're already using the correct authentication method, we might need to investigate further to determine why PostgreSQL is receiving an unexpected value during authentication and thta can  involve checking for any misconfigurations or compatibility issues between our ASP.NET Core application and PostgreSQL.

Solution for SCRAM-SHA-256 Hashed Password Issue

So, we encountered an issue where the password in our database is SCRAM-SHA-256 hashed. We confirmed this by querying pg_authid:

SELECT rolpassword FROM pg_authid
WHERE rolname = 'youruser';

This happened because the parameter password_encryption is set to scram-sha-256. Now, even though it says MD5 in pg_hba.conf, PostgreSQL uses scram-sha-256 authentication when there's a SCRAM-hashed password. This is a compatibility feature intended to ease the transition to SCRAM.

To make MD5 authentication work, we need to change the parameter password_encryption in postgresql.conf to md5, reload the database, and reset the user's password. This will give us an MD5 hashed password, enabling MD5 authentication.

However, the better solution would be to upgrade our client software. Running old software is never ideal.

Sending a hashed password won't help here; it's about the authentication method requested by the server. Our .NET provider is too old to understand the requested scram-sha-256 authentication method introduced in PostgreSQL v10.

We need to upgrade to a more recent version of our .NET provider. This will likely resolve the problem. 

Additionally, we must upgrade the PostgreSQL client software used by our Rust driver to a later version supporting the scram-sha-256 authentication method introduced in PostgreSQL v10.

Specifically, we need to upgrade to Npgsql latest, which supports the scram-sha-256 authentication method available since PostgreSQL v10.

It's possible that while the database server has been upgraded, the client library used by our C# code to connect to the server hasn't. The old client library doesn't understand the new authentication method. We should try updating the Npgsql library to resolve this issue.


So, we decided to downgrade to PostgreSQL 12, and it actually helped resolve our issue. However, we should note that downgrading password_encryption in PostgreSQL to md5, changing all the passwords, and using the md5 authentication method is a possible solution, but it's not a recommended one.

This alternative requires more effort, and in the end, we get worse security and end up with old, potentially buggy software. It's generally not a good idea to compromise security and stability for the a quick fix.

It's important to consider the long-term implications and prioritize maintaining a secure and up-to-date environment. Downgrading should be a last option and only considered if absolutely necessary.