SCRAM-SHA-256 documentation

[roaldle]

Introduction

There is almost no information on the access method scram-sha-256. It took me some time to get it working and I would like to share what I did.

Problem

The users password is created by using the ALTER ROLE command with the help of the psql cli tool. In my case, I wanted to change the password to an already hashed value. This requires some extra escaping or you will end up with a different value.
The query below is changed after every $ character.

  root@dokken:/# psql --echo-queries -c "ALTER ROLE user1 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:27klCUc487uwvJVGKI5YNA==$6K2Y+S3YBlpfRNrLROoO2ulWmnrQoRlGI1GqpNRq0T0=:y4esBVjK/hMtxDB5aWN4ynS1SnQcT1TFTqV0J/snls4='"
  ALTER ROLE user1 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256096:27klCUc487uwvJVGKI5YNA==K2Y+S3YBlpfRNrLROoO2ulWmnrQoRlGI1GqpNRq0T0=:y4esBVjK/hMtxDB5aWN4ynS1SnQcT1TFTqV0J/snls4='
  ALTER ROLE

Possible Solution

I replaced the $ character with \$ and then the authentication will work with the correct password.

  postgresql_user user1 do
    password 'SCRAM-SHA-256$4096:27klCUc487uwvJVGKI5YNA==$6K2Y+S3YBlpfRNrLROoO2ulWmnrQoRlGI1GqpNRq0T0=:y4esBVjK/hMtxDB5aWN4ynS1SnQcT1TFTqV0J/snls4='.gsub('$', '\$')
    action [:create, :update]
  end

Additional context

The company I work for has it's own control panel where users can create databases and provide the password they would like to use. The password is hashed on the fly and then a chef job is scheduled to create the required database and user. Chef only gets the hashed password never the plain text one.
I created the hashed password with the help off the code in: https://github.com/supercaracal/scram-sha-256.