Feature request: Add support for SCRAM-SHA-256 password authentication

[tinproject]

Since PostgreSQL password authentication with SCRAM-SHA-256 method is available: https://www.postgresql.org/docs/10/auth-methods.html.

Currently this library fails to connect when trying to authenticate to a server that enforces scram-sha-256 method with: unknown authentication response: 10

Note: I'm currently having this problem with a project that vendors this library, but code at https://github.com/lib/pq/blob/master/conn.go#L1135 also lacks this authentication method.

[suppadeliux]

Hello,

I am also working with SCRAM-SHA-256, and i would love to be able to use it in my project.

it gives the error pq: unknown authentication response: 10

My project is developped with go and angular, and i am using Buffalo framework and Postgresql.

At the moment i am using md5, but seems to be deprecated.

I am hoping to fix this issue soon.

Thanks for the help.

[raz-varren]

Submitted a pull request for this: #833

[Neustradamus]

Any news about it?

• Feature request: Add support for SCRAM-SHA-256 password authentication #817
• Use a safe implementation of SCRAM. #914
• Support scram password generation #941
• Add support for SCRAM-SHA-256 authentication. #608
• Inform user of unsupported scram auth method #621
• Support for SCRAM-SHA-256 authentication #788
• Add SCRAM-SHA-256 authentication to this library #833

Linked to:

• State of Play scram-sasl/info#1

[ptman]

has now been added?

[raz-varren]

Yes: #833

[raz-varren]

There is nothing special you need to do on the client side to make a connection using scram-sha-256.

import(
	"database/sql"
	_ "github.com/lib/pq"
)

func main() {
	connstr := "dbname=mydb host=localhost port=5432 user=myuser password='mypassword'"
	pgdb, err := sql.Open("postgres", connstr)
	if err != nil {
		panic(err)
	}
	defer pgdb.Close()

	err = pgdb.Ping()
	if err != nil {
		panic(err)
	}
}

However your postgres database server will need to support scram-sha-256. I think it's only supported in version 10 and up. To enable scram-sha-256 connections, your pg_hba.config file needs to look something like this:

local    all    postgres                trust
host     all    all        0.0.0.0/0    scram-sha-256

[adubkov]

@raz-varren Thanks for reply.

But I still getting an error FATAL: password authentication failed for user "XXX"

This is section of my pg_hba.conf

local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
local    all    postgres                trust
host all all all md5
host all all 0.0.0.0/0 scram-sha-256

This is how I create new user:

CREATE USER myuser WITH
  LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  INHERIT
  NOREPLICATION
  CONNECTION LIMIT -1
  ENCRYPTED
  PASSWORD 'scram-sha-256XXXXXXXXXXXX';

I can normally login with psql -U myuser -W but not with go or pgAdmin4...

[adubkov]

All right, apparently I create user in wrong way and the reason it worked with psql is because I run it locally.

I did this way and it solve the problem:

SET password_encryption = 'scram-sha-256';
ALTER ROLE myuser SET password_encryption = 'scram-sha-256';
ALTER ROLE myuser WITH PASSWORD 'mypassword';

[mnencia]

Why this issue is still open?

[joeycumines]

I believe I ran into the same thing as @adubkov, on upgrade from postgres 11 -> 14 by way of pg_dumpall. I'm using the default config bundled with the official docker image. My existing users had md5 password hashes. Once restored, I found that only things using this library were unable to connect.

The client side failed with the same error as mentioned above, while the server side logged this:

        Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-12-29 04:23:31.871 UTC [74] FATAL:  password authentication failed for user "postgres"
2021-12-29 04:23:31.871 UTC [74] DETAIL:  User "postgres" does not have a valid SCRAM secret.

Yay maintenance-only project 😞

[Neustradamus]

@knz: Can you close this issue?

It has been merged here: #833.

Thanks in advance.

[jordanlewis]

Hi @mjibson, could you please close this? Thank you!

[jordanlewis]

cc @rafiss @otan? Sorry, I don't know who has permissions on this repo!

[jordanlewis]

Thanks Matt!