User ban is not happening after reaching the credcheck.max_auth_failure's value

[t-ushar]

Credcheck setting.

postgres=# \dx credcheck 
                            List of installed extensions
   Name    | Version | Schema |                     Description                      
-----------+---------+--------+------------------------------------------------------
 credcheck | 2.6.0   | public | credcheck - postgresql plain text credential checker
(1 row)

Baning setting.

postgres=# show credcheck.max_auth_failure;
 credcheck.max_auth_failure 
----------------------------
 5
(1 row)

postgres=# show credcheck.reset_superuser;
 credcheck.reset_superuser 
---------------------------
 on
(1 row)

postgres=# \q

Wrong password failure attempts:

In the below test case, the ban happened after 9 failures and banned the used at the 10th failure, however, it should have been banned on the 6th attempt.

below commands executed after around 5 seconds interval.

-0-postgres@postgrespoc:~ $ time psql -U bkptest -d postgres
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "bkptest"
password retrieved from file "/var/lib/pgsql/.pgpass"

-0-postgres@postgrespoc:~ $ time psql -U bkptest -d postgres
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "bkptest"
password retrieved from file "/var/lib/pgsql/.pgpass"

-0-postgres@postgrespoc:~ $ time psql -U bkptest -d postgres
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "bkptest"
password retrieved from file "/var/lib/pgsql/.pgpass"

-0-postgres@postgrespoc:~ $ time psql -U bkptest -d postgres
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "bkptest"
password retrieved from file "/var/lib/pgsql/.pgpass"

-0-postgres@postgrespoc:~ $ time psql -U bkptest -d postgres
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "bkptest"
password retrieved from file "/var/lib/pgsql/.pgpass"

-0-postgres@postgrespoc:~ $ time psql -U bkptest -d postgres
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "bkptest"
password retrieved from file "/var/lib/pgsql/.pgpass"

-0-postgres@postgrespoc:~ $ time psql -U bkptest -d postgres
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "bkptest"
password retrieved from file "/var/lib/pgsql/.pgpass"

-0-postgres@postgrespoc:~ $ time psql -U bkptest -d postgres
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "bkptest"
password retrieved from file "/var/lib/pgsql/.pgpass"

-0-postgres@postgrespoc:~ $ time psql -U bkptest -d postgres
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "bkptest"
password retrieved from file "/var/lib/pgsql/.pgpass"

-0-postgres@postgrespoc:~ $ time psql -U bkptest -d postgres
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  rejecting connection, user 'bkptest' has been banned

In DB it shows failure count 5 only, where at actual it's 10

postgres=# select * from pg_banned_role;
 roleid | failure_count |        banned_date        
--------+---------------+---------------------------
  28290 |             5 | 2024-06-05 15:31:51.69302
(1 row)

[darold]

What is your PostgreSQL version and what is the value returned by SHOW ssl;?

[t-ushar]

postgres=# SHOW ssl; ssl

on (1 row)

postgres=# select version(); version

PostgreSQL 15.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit (1 row)

Is this due to SSL=on? As in the code if EnableSSL has a value of 1 then increase the counter by 0.5.

    /* Create new entry, if not present */
    entry = (pgafEntry *) hash_search(pgaf_hash, &key, HASH_FIND, NULL);
    if (entry)
    {
        if (EnableSSL)
            fail_cnt = entry->failure_count + 0.5;

        else
            fail_cnt = entry->failure_count + 1;

        elog(DEBUG1, "Remove entry in auth failure hash table for user %s", username);
        hash_search(pgaf_hash, &entry->key, HASH_REMOVE, NULL);
    }