This is a quickie, but can be incredibly useful. For the background, in case you don't know, it's a very very bad idea to store passwords. The first change is storing the hash of a password. A hash is just a function that's given some bytes and deterministically produces some other bytes, but it's very hard to go the other way. That is, if you have password_hash = hash(password) (where password is just the user's plaintext password), you can't easily derive password from password_hash.

Because of this, you only need to store password hashes. A user sends a password, you hash it, and see if the hashes match. For a good hashing algorithm, the probability of two passwords producing the same hash is minuscule. If your database is broken into, attackers will only get the hashes, not the passwords.

This gave rise to "rainbow table attacks," in which you precompute hashes for a lot of common passwords for common hashing functions. If you get a dump of password hashes, you see if any of the hashes are present in the rainbow table. For any hashes that show up, now you've got their password.

The defense against this is called "salting." You generate a random string, called the salt, and you compute salted_hash = hash(password + salt) (plus being string/array concatenation). You then store salt and salted_hash, so you can repeat the calculation when a user goes to log in. It's fine if the salts are leaked, because again the only thing we're actually defending against here is rainbow tables: an attacker can't precompute the hash of common passwords concatenated with all possible salts.

Anyway, Postgres lets you provide a salted, hashed password when creating a user. The issue with providing a hashed password to someone else's system is that you need to follow exactly the same steps they do: this whole song and dance is about being able to reproduce the same hashes from the same passwords. Being a little bit wrong on the hash you provide will make logins fail.

I did way too much digging to figure out how to reproduce the Postgres password hashing algorithm (particularly digging through the Postgres and Npgsql sources), so hopefully this'll save someone from the same trouble:

let password_hash (password: string) =
    let normalized = System.Text.Encoding.UTF8.GetBytes(password.Normalize(System.Text.NormalizationForm.FormKC))
    let salt_len = 16
    let default_iterations = 4096

    let salt = System.Security.Cryptography.RandomNumberGenerator.GetBytes(salt_len)
    let mutable salt1 = Array.create (salt.Length + 4) 0uy

    let hmac = new System.Security.Cryptography.HMACSHA256(normalized)
    System.Buffer.BlockCopy(salt, 0, salt1, 0, salt.Length)
    salt1[salt1.Length - 1] <- 1uy

    let mutable hi = hmac.ComputeHash(salt1)
    let mutable u1 = hi

    for _ in 1 .. default_iterations - 1 do
        let u2 = hmac.ComputeHash(u1)
        for i in 0 .. hi.Length - 1 do
            hi[i] <- hi[i] ^^^ u2[i]
        u1 <- u2

    let client_key = (new System.Security.Cryptography.HMACSHA256(hi)).ComputeHash(System.Text.Encoding.UTF8.GetBytes("Client Key"))
    let stored_key = (System.Security.Cryptography.SHA256.Create()).ComputeHash(client_key)
    let server_key = (new System.Security.Cryptography.HMACSHA256(hi)).ComputeHash(System.Text.Encoding.UTF8.GetBytes("Server Key"))

    let builder = new System.Text.StringBuilder()
    builder.Append("'SCRAM-SHA-256$").Append(default_iterations.ToString()).Append(":").Append(System.Convert.ToBase64String(salt)).Append("$")
        .Append(System.Convert.ToBase64String(stored_key)).Append(":").Append(System.Convert.ToBase64String(server_key)).Append("'").ToString()

Then, within a CREATE ROLE query, you just use,

"ENCRYPTED PASSWORD " + password_hash password