Обсуждение: BUG #15035: scram-sha-256 blocks all logins

The following bug has been logged on the website:

Bug reference:      15035
Logged by:          Meirav Rath
Email address:      meirav.rath@imperva.com
PostgreSQL version: 10.0
Operating system:   windows server 2012 R2
Description:

Hi,

I'm trying to set up the new SCRAM-SHA-256 authentication method. The only
problem is that the moment I set this in both postgresql.conf and
pg_hba.conf I can no longer login with any user, and so I can't create new
users with passwords that are compatible. 

Here's what pg_hba.conf looks like:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
host    all        all        0.0.0.0/0        scram-sha-256
host    all        postgres        0.0.0.0/0        md5


And here's what postgresql.conf looks like:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'        # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost'; use '*' for all
                    # (change requires restart)
port = 5432                # (change requires restart)
max_connections = 100            # (change requires restart)
#superuser_reserved_connections = 3    # (change requires restart)
#unix_socket_directories = ''    # comma-separated list of directories
                    # (change requires restart)
#unix_socket_group = ''            # (change requires restart)
#unix_socket_permissions = 0777        # begin with 0 to use octal notation
                    # (change requires restart)
#bonjour = off                # advertise server via Bonjour
                    # (change requires restart)
#bonjour_name = ''            # defaults to the computer name
                    # (change requires restart)

# - Security and Authentication -

#authentication_timeout = 1min        # 1s-600s
#ssl = off
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_dh_params_file = ''
#ssl_cert_file = 'server.crt'
#ssl_key_file = 'server.key'
#ssl_ca_file = ''
#ssl_crl_file = ''
password_encryption = scram-sha-256
#db_user_namespace = off
#row_security = on

# GSSAPI using Kerberos
#krb_server_keyfile = ''
#krb_caseins_users = off

# - TCP Keepalives -
# see "man 7 tcp" for details

#tcp_keepalives_idle = 0        # TCP_KEEPIDLE, in seconds;
                    # 0 selects the system default
#tcp_keepalives_interval = 0        # TCP_KEEPINTVL, in seconds;
                    # 0 selects the system default
#tcp_keepalives_count = 0        # TCP_KEEPCNT;
                    # 0 selects the system default


Please advise.

This is not a bug - at least you haven't explained why the behavior
should be considered incorrect. This is a question and so belongs to
pgsql-general. Please, post it again to the right list.


On 01/29/2018 04:20 PM, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      15035
> Logged by:          Meirav Rath
> Email address:      meirav.rath@imperva.com
> PostgreSQL version: 10.0
> Operating system:   windows server 2012 R2
> Description:        
> 
> Hi,
> 
> I'm trying to set up the new SCRAM-SHA-256 authentication method. The only
> problem is that the moment I set this in both postgresql.conf and
> pg_hba.conf I can no longer login with any user, and so I can't create new
> users with passwords that are compatible. 
> 
> Here's what pg_hba.conf looks like:
> 
> ...
regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

I set up the password hashing method to scram and now no one can login to the server and that's not a bug??

Meirav Rath <meirav.rath@imperva.com> writes:
> I set up the password hashing method to scram and now no one can login to the server and that's not a bug??

You told it to lock out md5 passwords, and it did.

Try changing the passwords to scram type *first*.  Note where it
says in the fine manual:

    To ease transition from the md5 method to the newer SCRAM method, if
    md5 is specified as a method in pg_hba.conf but the user's password on
    the server is encrypted for SCRAM (see below), then SCRAM-based
    authentication will automatically be chosen instead.

            regards, tom lane

> On 30 Jan 2018, at 08:56, Meirav Rath <meirav.rath@imperva.com> wrote:
>
> Like this?

> host       all                          all                          0.0.0.0/0
scram-sha256

There is a typo in this authentication method, it should be scram-sha-256 (not
the second dash between "sha" and "256”).

> Because I can’t restart the postgresql service, it crashes when this is the setup….

Changing the above should allow the database to start up.

cheers ./daniel

On Tue, Jan 30, 2018 at 07:56:40AM +0000, Meirav Rath wrote:
> <snip>
> host all all 0.0.0.0/0 scram-sha256
> <snip>
>
> Because I can’t restart the postgresql service, it crashes when this
> is the setup.  User postgres is the superuser, by the way.

You need to make the difference between a crash and what seems to just
be an incorrect configuration which prevents your server to start.  If
you look at the line above which I have taken from the pg_hba.conf you
have written in your last email, it is incorrect.  "scram-sha256" is not
a correct HBA method.  You need to use "scram-sha-256" instead.

I would advise to look at the documentation correctly first:
https://www.postgresql.org/docs/devel/static/auth-pg-hba-conf.html
--
Michael

Yes, I was following a previous bug I found on your site  - pg_hba_file_rules: "scram-sha256" instead of
"scram-sha-256".

So I fixed it, now pg_hba.conf looks like this:

hostallall0.0.0.0/0scram-sha-256
hostallpostgres0.0.0.0/0trust

Postgresql service is running. However, I can't login with user postgres, with the following error message:
"The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the
client'sIP address or subnet, and that it is using an authentication scheme supported by the driver."
 
When trying DBeaver and with
" FATAL: password authentication failed for user "postgres""
When using PGadmin 4 version 2.1 flask version 0.12.2

-----Original Message-----
From: Daniel Gustafson [mailto:daniel.gustafson@gmail.com] On Behalf Of Daniel Gustafsson
Sent: Tuesday, January 30, 2018 10:03
To: Meirav Rath <meirav.rath@imperva.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>; Tomas Vondra <tomas.vondra@2ndquadrant.com>;
pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15035: scram-sha-256 blocks all logins

> On 30 Jan 2018, at 08:56, Meirav Rath <meirav.rath@imperva.com> wrote:
>
> Like this?

> host       all                          all                          0.0.0.0/0
scram-sha256

There is a typo in this authentication method, it should be scram-sha-256 (not the second dash between "sha" and
"256”).

> Because I can’t restart the postgresql service, it crashes when this is the setup….

Changing the above should allow the database to start up.

cheers ./daniel
-------------------------------------------
NOTICE:
This email and all attachments are confidential, may be proprietary, and may be privileged or otherwise protected from
disclosure.They are intended solely for the individual or entity to whom the email is addressed. However, mistakes
sometimeshappen in addressing emails. If you believe that you are not an intended recipient, please stop reading
immediately.Do not copy, forward, or rely on the contents in any way. Notify the sender and/or Imperva, Inc. by
telephoneat +1 (650) 832-6006 and then delete or destroy any copy of this email and its attachments. The sender
reservesand asserts all rights to confidentiality, as well as any privileges that may apply. Any disclosure, copying,
distributionor action taken or omitted to be taken by an unintended recipient in reliance on this message is prohibited
andmay be unlawful.
 
Please consider the environment before printing this email.

Yes, I was following a previous bug I found on your site  - pg_hba_file_rules: "scram-sha256" instead of "scram-sha-256".

So I fixed it, now pg_hba.conf looks like this:

hostallall0.0.0.0/0scram-sha-256
hostallpostgres0.0.0.0/0trust

If what you’re trying to say is “switch the order of the two lines in the file” then that is a very, very obscure way of doing it.

On 01/30/2018 03:37 PM, Meirav Rath wrote:
> Actually, my job is to test our products’ compatibility with 13 
> databases, PostgreSQL is one of them. I see a /lot /of documentation 
> and so far PostgreSQL has been relatively friendly.
> 
> However, this very roundabout way of saying things, so much so that 
> you need to ask a PostgreSQL person how to interpret the phrasing is 
> not user-friendly. After I’m done with PostgreSQL 10 new features’ 
> testing, I’m moving on to test other databases’ new versions and 
> their features.
> 

Great response to people who're helping you with your job for free.

Now consider that you've submitted something that is not a bug but an
issue with configuring pg_hba.conf properly. Generally, a bug report
requires some explanation of why the configuration is correct and what
the behavior should be.

We do see plenty of people posting such non-bug questions to psql-bugs
and we're somewhat annoyed by that. Most people however take the hint
and post the question to pgsql-general when asked to do so, instead of
attacking the people who are actually trying to help them.

I agree getting pg_hba.conf right is not simple, particularly if you do
it for the first time. But I don't see anything wrong with it, and you
don't explain what a better/simpler solution would look like. You'd have
to learn how any other solution works anyway - the principles would be
the same.

But complaining is simpler, I guess ...

What I find particularly strange is your response to David quoting the
documentation. Pointing you to the proper part of the docs is about the
best thing he could have done, the paragraph is technically accurate,
provides additional context about how pg_hba.conf works, and copying it
is much faster than typing the explanation by hand.

This is a mailing list and terse response are expected.

> If I was a DBA whose company considered moving to work with
> PostgreSQL, and I saw this obscure phrasing, and the clumsiness to
> this code, I’d tell my bosses to think twice about PostgreSQL and
> maybe try a sturdier database.
That's up to you. But perhaps your boss will see it differently.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

I suppose you can be terse all you like, this is the interwebz and any corner of it can quickly become 4chan when you
don'tdemand money for your product.
 

Funny how big grown-up companies with costumers that pay for their product are:
1. Successful and long-lasting
2. Widespread across the market and are used by many other big and prosperous companies
3. Have technical support representatives that don't get butthurt when they don't pick up on their users' mistake, even
whenthey're supposed to be knowledgeable about their own documentation.
 

Those big grown-up companies, they use Oracle, or SQL Server, or MySQL.

They don't use PostgreSQL.


-----Original Message-----
From: Tomas Vondra [mailto:tomas.vondra@2ndquadrant.com]
Sent: Tuesday, January 30, 2018 17:50
To: Meirav Rath <meirav.rath@imperva.com>; David G. Johnston <david.g.johnston@gmail.com>
Cc: Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15035: scram-sha-256 blocks all logins

On 01/30/2018 03:37 PM, Meirav Rath wrote:
> Actually, my job is to test our products’ compatibility with 13
> databases, PostgreSQL is one of them. I see a /lot /of documentation
> and so far PostgreSQL has been relatively friendly.
>
> However, this very roundabout way of saying things, so much so that
> you need to ask a PostgreSQL person how to interpret the phrasing is
> not user-friendly. After I’m done with PostgreSQL 10 new features’
> testing, I’m moving on to test other databases’ new versions and their
> features.
>

Great response to people who're helping you with your job for free.

Now consider that you've submitted something that is not a bug but an
issue with configuring pg_hba.conf properly. Generally, a bug report
requires some explanation of why the configuration is correct and what
the behavior should be.

We do see plenty of people posting such non-bug questions to psql-bugs
and we're somewhat annoyed by that. Most people however take the hint
and post the question to pgsql-general when asked to do so, instead of
attacking the people who are actually trying to help them.

I agree getting pg_hba.conf right is not simple, particularly if you do
it for the first time. But I don't see anything wrong with it, and you
don't explain what a better/simpler solution would look like. You'd have
to learn how any other solution works anyway - the principles would be
the same.

But complaining is simpler, I guess ...

What I find particularly strange is your response to David quoting the
documentation. Pointing you to the proper part of the docs is about the
best thing he could have done, the paragraph is technically accurate,
provides additional context about how pg_hba.conf works, and copying it
is much faster than typing the explanation by hand.

This is a mailing list and terse response are expected.

> If I was a DBA whose company considered moving to work with
> PostgreSQL, and I saw this obscure phrasing, and the clumsiness to
> this code, I’d tell my bosses to think twice about PostgreSQL and
> maybe try a sturdier database.
That's up to you. But perhaps your boss will see it differently.


regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-------------------------------------------
NOTICE:
This email and all attachments are confidential, may be proprietary, and may be privileged or otherwise protected from
disclosure.They are intended solely for the individual or entity to whom the email is addressed. However, mistakes
sometimeshappen in addressing emails. If you believe that you are not an intended recipient, please stop reading
immediately.Do not copy, forward, or rely on the contents in any way. Notify the sender and/or Imperva, Inc. by
telephoneat +1 (650) 832-6006 and then delete or destroy any copy of this email and its attachments. The sender
reservesand asserts all rights to confidentiality, as well as any privileges that may apply. Any disclosure, copying,
distributionor action taken or omitted to be taken by an unintended recipient in reliance on this message is prohibited
andmay be unlawful.
 
Please consider the environment before printing this email.

Seriously, though, it took all three of you a whole day to find out something from the depth of your documentation, and
whenI point out that the documentation is not user-friendly - which this ordeal obviously proved - you complain about
it??You complain about something true because an outsider pointed it out??
 

Our users notify us when (and if) they're about to switch to a different database, or start working with a new
database.You don't know if I'm in a position to consult with them whether or not to use PostgreSQL, you don't know how
manyusers we have who could hear from me to never, ever use PostgreSQL.
 

Grow up, quickly.

-----Original Message-----
From: Tomas Vondra [mailto:tomas.vondra@2ndquadrant.com]
Sent: Tuesday, January 30, 2018 17:50
To: Meirav Rath <meirav.rath@imperva.com>; David G. Johnston <david.g.johnston@gmail.com>
Cc: Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15035: scram-sha-256 blocks all logins

On 01/30/2018 03:37 PM, Meirav Rath wrote:
> Actually, my job is to test our products’ compatibility with 13
> databases, PostgreSQL is one of them. I see a /lot /of documentation
> and so far PostgreSQL has been relatively friendly.
>
> However, this very roundabout way of saying things, so much so that
> you need to ask a PostgreSQL person how to interpret the phrasing is
> not user-friendly. After I’m done with PostgreSQL 10 new features’
> testing, I’m moving on to test other databases’ new versions and their
> features.
>

Great response to people who're helping you with your job for free.

Now consider that you've submitted something that is not a bug but an
issue with configuring pg_hba.conf properly. Generally, a bug report
requires some explanation of why the configuration is correct and what
the behavior should be.

We do see plenty of people posting such non-bug questions to psql-bugs
and we're somewhat annoyed by that. Most people however take the hint
and post the question to pgsql-general when asked to do so, instead of
attacking the people who are actually trying to help them.

I agree getting pg_hba.conf right is not simple, particularly if you do
it for the first time. But I don't see anything wrong with it, and you
don't explain what a better/simpler solution would look like. You'd have
to learn how any other solution works anyway - the principles would be
the same.

But complaining is simpler, I guess ...

What I find particularly strange is your response to David quoting the
documentation. Pointing you to the proper part of the docs is about the
best thing he could have done, the paragraph is technically accurate,
provides additional context about how pg_hba.conf works, and copying it
is much faster than typing the explanation by hand.

This is a mailing list and terse response are expected.

> If I was a DBA whose company considered moving to work with
> PostgreSQL, and I saw this obscure phrasing, and the clumsiness to
> this code, I’d tell my bosses to think twice about PostgreSQL and
> maybe try a sturdier database.
That's up to you. But perhaps your boss will see it differently.


regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-------------------------------------------
NOTICE:
This email and all attachments are confidential, may be proprietary, and may be privileged or otherwise protected from
disclosure.They are intended solely for the individual or entity to whom the email is addressed. However, mistakes
sometimeshappen in addressing emails. If you believe that you are not an intended recipient, please stop reading
immediately.Do not copy, forward, or rely on the contents in any way. Notify the sender and/or Imperva, Inc. by
telephoneat +1 (650) 832-6006 and then delete or destroy any copy of this email and its attachments. The sender
reservesand asserts all rights to confidentiality, as well as any privileges that may apply. Any disclosure, copying,
distributionor action taken or omitted to be taken by an unintended recipient in reliance on this message is prohibited
andmay be unlawful.
 
Please consider the environment before printing this email.

On 01/30/2018 05:45 PM, Meirav Rath wrote:
> I suppose you can be terse all you like, this is the interwebz and 
> any corner of it can quickly become 4chan when you don't demand money
> for your product.
> 

Huh? Not sure what part of my response is 4chan-like ...

> Funny how big grown-up companies with costumers that pay for their
> product are:
> 1. Successful and long-lasting
> 2. Widespread across the market and are used by many other big and
> prosperous companies
> 3. Have technical support representatives that don't get butthurt 
> when they don't pick up on their users' mistake, even when they're
> supposed to be knowledgeable about their own documentation.
> 

The only one "butthurt" here is you, really. Other than that I don't
know what exactly is your point, considering that you're obviously not
paying anything for PostgreSQL (if you are, you would probably be asking
your vendor for help).

> Those big grown-up companies, they use Oracle, or SQL Server, or MySQL.
> 
> They don't use PostgreSQL.
> 

Good for them. My experience is rather different, though.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2018-01-30 16:45, Meirav Rath wrote:
> I suppose you can be terse all you like, this is the interwebz and any corner of it can quickly become 4chan when you
don'tdemand money for your product. 
>
> Funny how big grown-up companies with costumers that pay for their product are:
> 1. Successful and long-lasting
> 2. Widespread across the market and are used by many other big and prosperous companies
> 3. Have technical support representatives that don't get butthurt when they don't pick up on their users' mistake,
evenwhen they're supposed to be knowledgeable about their own documentation. 
>
> Those big grown-up companies, they use Oracle, or SQL Server, or MySQL.
>
> They don't use PostgreSQL.

This last bit isn’t true, but let’s try to be a bit more productive here
and keep sarcasm to a minimum. Text does a terrific job of conveying
tone of voice.

We believe the documentation to be quite clear on the format of the
pg_hba.conf file and the description of the behavior.

The comment in the pg_hba.conf itself does mention in the first sentence
to “refer to the "Client Authentication" section in the PostgreSQL
documentation for a complete description of this file.” The third
paragraph of the documentation [1] explains quite clearly to me that the
first match found is the last match checked.

If you found something confusing about it or had a different
expectation, we’re certainly willing to discuss it. We’ve made
documentation changes before, and we’re open to doing it again if it
will smooth out the learning curve.

As of now, though, it seems like you haven’t read anything and just
forged ahead, or did you read the documentation and
it lost you somewhere along the way?

[1]: https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

On 01/30/2018 05:55 PM, Meirav Rath wrote:
> Seriously, though, it took all three of you a whole day to find out 
> something from the depth of your documentation, and when I point out 
> that the documentation is not user-friendly - which this ordeal 
> obviously proved - you complain about it?? You complain about 
> something true because an outsider pointed it out??
> 

Looking up the paragraph in a docs is a matter of 5 seconds, when you
know what you're looking for - which David obviously does. So suggesting
we've spent "whole day" to look it up is somewhat ... well.

While I agree the documentation is not perfect, but it's quite unclear
what exactly you mean by "not user-friendly". Was it difficult to find
the pg_hba.conf docs? Was the phrasing unclear?

For me, it's the first entry when searching for "pg_hba.conf" on
postgresql.org, and I find the phrasing particularly clear.

I fail to see how this particular "ordeal" proved anything in this respect.

> Our users notify us when (and if) they're about to switch to a 
> different database, or start working with a new database. You don't 
> know if I'm in a position to consult with them whether or not to use 
> PostgreSQL, you don't know how many users we have who could hear
> from me to never, ever use PostgreSQL.
> 
> Grow up, quickly.
> 

meh


FWIW I have no intention to participate in this thread any further.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

>>>>> "Meirav" == Meirav Rath <meirav.rath@imperva.com> writes:

 Meirav> host all postgres 0.0.0.0/0 trust

Never do this. (If you need non-password access for the postgres user,
then use "local all postgres peer", or a certificate-based method, or at
the _very least_ limit it to trusted IP addresses.)

Someone who can connect as the postgres user can load code into the
database remotely and run it, in addition to being able to see or modify
all your data. People _do_ get exploited this way (we see instances of
it reported on the IRC channel every once in a while); they find
themselves running DDoS bots or cryptocurrency miners or whatever else.

-- 
Andrew (irc:RhodiumToad)

On Tue, Jan 30, 2018 at 04:55:49PM +0000, Meirav Rath wrote:
> Seriously, though, it took all three of you a whole day to find out
> something from the depth of your documentation, and when I point out
> that the documentation is not user-friendly - which this ordeal
> obviously proved - you complain about it?? You complain about
> something true because an outsider pointed it out??

Based solely on the contents of this thread, seven different people have
kindly replied to your problems and help you get through it, pointing
out your own mistakes and how to solve things.  And all those replies
happened within one day.

If you find yourself having a hard time to navigate through the
documentation, please note that you can search by keyword using the
index:
https://www.postgresql.org/docs/current/static/bookindex.html

Please note as well that it is possible to search for keywords by
yourself, in order to use this feature look for "Search Documentation"
at the top any documentation page on postgresql.org.  In your case,
looking for "pg_hba.conf" could have helped.

> Our users notify us when (and if) they're about to switch to a
> different database, or start working with a new database. You don't
> know if I'm in a position to consult with them whether or not to use
> PostgreSQL, you don't know how many users we have who could hear from
> me to never, ever use PostgreSQL.

Please note that this is a public mailing list, as an open source
project discussions are in an open field.  So say hi to yourself here,
this will be present for as long as postgresql.org exists:
https://www.postgresql.org/message-id/151723922191.1238.12656996435976596418%40wrigleys.postgresql.org
Are available on this public information your name as well as your
company which is the information you provided.

> Grow up, quickly.

Wishing you the same, and the best on top of that.
--
Michael