Re: How to debug authentication issues in Postgres

Adrian Klaver <adrian.klaver@xxxxxxxxxxx> · Fri, 27 Nov 2020 08:53:19 -0800

On 11/27/20 7:15 AM, Hemil Ruparel wrote:
The database has been upgraded

Just to be clear the postgresql.conf file has:

password_encryption = scram-sha-256

set correct?

On Fri, Nov 27, 2020 at 8:41 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx 
<mailto:adrian.klaver@xxxxxxxxxxx>> wrote:

    On 11/27/20 7:01 AM, Hemil Ruparel wrote:
     > I don't quite get what you mean by upgrading to scram-sha256. I
     > installed postgres 13. I haven't upgraded anything yet.

    In postgresql.conf see what password_encryption has been set to. If it
    is 'scram-sha-256` then it has been upgraded.

     >
     > On Fri, Nov 27, 2020 at 8:06 PM Zwettler Markus (OIZ)
     > <Markus.Zwettler@xxxxxxxxxx <mailto:Markus.Zwettler@xxxxxxxxxx>
    <mailto:Markus.Zwettler@xxxxxxxxxx
    <mailto:Markus.Zwettler@xxxxxxxxxx>>> wrote:
     >
     >     Did you correctly upgrade your whole environment to
    scram-sha-256?____
     >
     >     __ __
     >
     >     __ __
     >
     >     <quote>
     >     To upgrade an existing installation from |md5|to |scram-sha-256|,
     >     after having ensured that all client libraries in use are new
    enough
     >     to support SCRAM, set |password_encryption = 'scram-sha-256'|in
     >     |postgresql.conf|, make all users set new passwords, and
    change the
     >     authentication method specifications in |pg_hba.conf|to
     >     |scram-sha-256|.____
     >
     >     </quote>____
     >
     >     __ __
     >
     >     __ __
     >
     >     -Markus____
     >
     >     __ __
     >
     >     __ __
     >
     >     __ __
     >
     >     *Von:*Hemil Ruparel <hemilruparel2002@xxxxxxxxx
    <mailto:hemilruparel2002@xxxxxxxxx>
     >     <mailto:hemilruparel2002@xxxxxxxxx
    <mailto:hemilruparel2002@xxxxxxxxx>>>
     >     *Gesendet:* Freitag, 27. November 2020 09:38
     >     *An:* Laurenz Albe <laurenz.albe@xxxxxxxxxxx
    <mailto:laurenz.albe@xxxxxxxxxxx>
     >     <mailto:laurenz.albe@xxxxxxxxxxx
    <mailto:laurenz.albe@xxxxxxxxxxx>>>
     >     *Cc:* pgsql-generallists.postgresql.org
    <http://pgsql-generallists.postgresql.org>
     >     <http://pgsql-generallists.postgresql.org
    <http://pgsql-generallists.postgresql.org>>
     >     <pgsql-general@xxxxxxxxxxxxxxxxxxxx
    <mailto:pgsql-general@xxxxxxxxxxxxxxxxxxxx>
     >     <mailto:pgsql-general@xxxxxxxxxxxxxxxxxxxx
    <mailto:pgsql-general@xxxxxxxxxxxxxxxxxxxx>>>
     >     *Betreff:* Re: How to debug authentication issues in Postgres____
     >
     >     __ __
     >
     >     The log says:____
     >
     >      > FATAL:  password authentication failed for user "centos"
     >      > DETAIL:  Connection matched pg_hba.conf line 88: "host   
    user
     >          password 0.0.0.0/0 <http://0.0.0.0/0> <http://0.0.0.0/0
    <http://0.0.0.0/0>>
     >     scram-sha-256"____
     >
     >     __ __
     >
     >     I can't understand where is the problem as both psql and pgadmin
     >     connect without problems using the same password____
     >
     >     __ __
     >
     >     On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel
     >     <hemilruparel2002@xxxxxxxxx
    <mailto:hemilruparel2002@xxxxxxxxx>
    <mailto:hemilruparel2002@xxxxxxxxx <mailto:hemilruparel2002@xxxxxxxxx>>>
     >     wrote:____
     >
     >         Sorry. This was the replication section:____
     >
     >         local   replication     all                              
           peer
     >         host    replication     all 127.0.0.1/32
    <http://127.0.0.1/32> <http://127.0.0.1/32 <http://127.0.0.1/32>>
     >                   scram-sha-256
     >         host    replication     all             ::1/128
     >         scram-sha-256____
     >
     >         __ __
     >
     >         On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe
     >         <laurenz.albe@xxxxxxxxxxx
    <mailto:laurenz.albe@xxxxxxxxxxx> <mailto:laurenz.albe@xxxxxxxxxxx
    <mailto:laurenz.albe@xxxxxxxxxxx>>>
     >         wrote:____
     >
     >             On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:
     >              > I have restarted postgres quite a few times to try
    making
     >             configuration changes and it
     >              >  is always back up. I don't know how. Feels weird
    to me.
     >             I didn't add the line
     >              >  "local replication all". It was there by default
     >
     >             I don't believe that.
     >
     >             This is how it looks by default:
     >
     >             # Allow replication connections from localhost, by a user
     >             with the
     >             # replication privilege.
     >             local   replication     all
     >                 trust
     >             host    replication     all 127.0.0.1/32
    <http://127.0.0.1/32>
     >             <http://127.0.0.1/32 <http://127.0.0.1/32>>         
       trust
     >             host    replication     all             ::1/128
     >                 trust
     >
     >             Yours,
     >             Laurenz Albe
     >             --
     >             Cybertec | https://www.cybertec-postgresql.com
    <https://www.cybertec-postgresql.com>
     >             <https://www.cybertec-postgresql.com
    <https://www.cybertec-postgresql.com>>____
     >

    -- 
    Adrian Klaver
    adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx