Something seems to have happened when I used credcheck.max_auth_failure

[yinzhishu]

I have set credcheck.max_auth_failure to 3 But when I use a user connection error more than 3 times, the user is not banned from connecting, when I connect error 6 times is banned from connecting。banned_date is also inconsistent with the current database time。

Please let me know if my method is wrong。Thanks。

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

postgres=# \dx
                              List of installed extensions
   Name    | Version |   Schema   |                     Description                      
-----------+---------+------------+------------------------------------------------------
 credcheck | 2.0.0   | public     | credcheck - postgresql plain text credential checker

[pg@host227 ~]$ sed -i 's/trust/md5/g' pgdata/pg_hba.conf
[pg@host227 ~]$ pg_ctl restart -D pgdata
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-06-13 14:04:36.453 CST [89341] LOG:  redirecting log output to logging collector process
2023-06-13 14:04:36.453 CST [89341] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[pg@host227 ~]$ psql -d postgres -p 65432
Password for user pg: 
psql (13.9)
Type "help" for help.

postgres=# create user user_ban1 password 'pass'; 
CREATE ROLE
postgres=# SELECT * FROM pg_banned_role;
 roleid | failure_count | banned_date 
--------+---------------+-------------
(0 rows)

postgres=# \q
[pg@host227 ~]$ psql -d postgres -p 65432
Password for user pg: 
psql (13.9)
Type "help" for help.   
postgres=# alter system set credcheck.max_auth_failure=3;
ALTER SYSTEM
postgres=# \q
[pg@host227 ~]$ pg_ctl restart -D pgdata
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-06-13 14:05:48.440 CST [89447] LOG:  redirecting log output to logging collector process
2023-06-13 14:05:48.440 CST [89447] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[pg@host227 ~]$ psql -d postgres -p 65432
Password for user pg: 
psql: error: FATAL:  password authentication failed for user "pg"
[pg@host227 ~]$ psql -d postgres -p 65432
Password for user pg: 
psql (13.9)
Type "help" for help.

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

postgres=# 
postgres=#  SELECT * FROM pg_banned_role;
 roleid | failure_count | banned_date 
--------+---------------+-------------
(0 rows)

postgres=# \q
[pg@host227 ~]$ psql -d postgres -p 65432 -U user_ban1
Password for user user_ban1: 
psql: error: FATAL:  password authentication failed for user "user_ban1"
[pg@host227 ~]$ 
[pg@host227 ~]$ 
[pg@host227 ~]$ psql -d postgres -p 65432 -U user_ban1
Password for user user_ban1: 
psql: error: FATAL:  password authentication failed for user "user_ban1"
[pg@host227 ~]$ 
[pg@host227 ~]$ 
[pg@host227 ~]$ psql -d postgres -p 65432 -U user_ban1
Password for user user_ban1: 
psql: error: FATAL:  password authentication failed for user "user_ban1"
[pg@host227 ~]$ 
[pg@host227 ~]$ 
[pg@host227 ~]$ psql -d postgres -p 65432 -U user_ban1
Password for user user_ban1: 
psql: error: FATAL:  password authentication failed for user "user_ban1"
[pg@host227 ~]$ 
[pg@host227 ~]$ 
[pg@host227 ~]$ psql -d postgres -p 65432 -U user_ban1
Password for user user_ban1: 
psql: error: FATAL:  password authentication failed for user "user_ban1"
[pg@host227 ~]$ psql -d postgres -p 65432 -U user_ban1
Password for user user_ban1: 
psql: error: FATAL:  rejecting connection, user 'user_ban1' has been banned
[pg@host227 ~]$ 
[pg@host227 ~]$ psql -d postgres -p 65432
Password for user pg: 
psql (13.9)
Type "help" for help.

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

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

postgres=# \dx
                              List of installed extensions
   Name    | Version |   Schema   |                     Description                      
-----------+---------+------------+------------------------------------------------------
 credcheck | 2.0.0   | public     | credcheck - postgresql plain text credential checker
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

[yzs@adb01 ~]$ psql -d postgres
Password for user yzs: 
psql (13.3)
Type "help" for help.

postgres=#  SELECT * FROM pg_banned_role;
 roleid | failure_count |        banned_date         
--------+---------------+----------------------------
  16478 |             3 | 2023-06-13 03:26:11.944297
(1 row)

postgres=#  SELECT * FROM pg_banned_role;
 roleid | failure_count |        banned_date         
--------+---------------+----------------------------
  16478 |             3 | 2023-06-13 03:26:11.944297
(1 row)

postgres=# select now();
              now              
-------------------------------
 2023-06-13 14:42:12.357784+08
(1 row)

postgres=# \q
[yzs@adb01 ~]$ date
2023年 06月 13日 星期二 14:42:14 CST

[darold]

I'm not able to reproduce the problem using version 13.11 with pg_hba.conf authent method md5 or scram-sha-256.

Can you report the authent method used in your pg_hba.conf and does upgrading to PG v13.11 solves the issue?

[yinzhishu]

I still reproduce the problem using version pg13.11. Is there any other configuration that I need to pay attention to. my pg13.11 postgresql.conf

----vim  postgresql.conf
port=65432
listen_addresses = '*'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
wal_level=logical
shared_preload_libraries = 'credcheck'
credcheck.max_auth_failure=3

My configure parameters: ../postgresql-13.11/configure --prefix=$PWD/app --with-segsize=8 --with-wal-blocksize=64 --with-perl --with-python --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety --enable-cassert --enable-debug --enable-depend CFLAGS='-O0'

[pg@host227 ~]$ psql -d postgres -p 65432
psql (13.11)
Type "help" for help.

postgres=# 
postgres=# CREATE EXTENSION credcheck;
CREATE EXTENSION
[pg@host227 ~]$ psql -d postgres -p 65432
psql (13.11)
Type "help" for help.

postgres=# alter user pg password '123456';
ALTER ROLE
postgres=# create user user_ban1 password 'pass';   
CREATE ROLE
postgres=# SELECT * FROM pg_banned_role;
 roleid | failure_count | banned_date 
--------+---------------+-------------
(0 rows)

postgres=# \dx
                              List of installed extensions
   Name    | Version |   Schema   |                     Description                      
-----------+---------+------------+------------------------------------------------------
 credcheck | 2.0.0   | public     | credcheck - postgresql plain text credential checker
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 13.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=# \q
[pg@host227 ~]$ sed -i 's/trust/md5/g' pg1311/pg_hba.conf
[pg@host227 ~]$ pg_ctl restart -D pg1311
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-06-13 15:58:50.260 CST [148921] LOG:  redirecting log output to logging collector process
2023-06-13 15:58:50.260 CST [148921] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[pg@host227 ~]$ psql -d postgres -p 65432
Password for user pg: 
psql (13.11)
Type "help" for help.

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

postgres=# \q
[pg@host227 ~]$ psql -d postgres -U user_ban1 -p 65432
Password for user user_ban1: 
psql: error: FATAL:  password authentication failed for user "user_ban1"
[pg@host227 ~]$ psql -d postgres -U user_ban1 -p 65432
Password for user user_ban1: 
psql: error: FATAL:  password authentication failed for user "user_ban1"
[pg@host227 ~]$ psql -d postgres -U user_ban1 -p 65432
Password for user user_ban1: 
psql: error: FATAL:  password authentication failed for user "user_ban1"
[pg@host227 ~]$ psql -d postgres -U user_ban1 -p 65432
Password for user user_ban1: 
psql: error: FATAL:  password authentication failed for user "user_ban1"
[pg@host227 ~]$ psql -d postgres -U user_ban1 -p 65432
Password for user user_ban1: 
psql: error: FATAL:  password authentication failed for user "user_ban1"
[pg@host227 ~]$ psql -d postgres -U user_ban1 -p 65432
Password for user user_ban1: 
psql: error: FATAL:  rejecting connection, user 'user_ban1' has been banned
[pg@host227 ~]$ sed -i 's/trust/md5/g' pg1311/pg_hba.conf^C
[pg@host227 ~]$ vi pg1311/postgresql.conf 
[pg@host227 ~]$ 
[pg@host227 ~]$ psql -d postgres -p 65432
Password for user pg: 
psql (13.11)
Type "help" for help.

postgres=#  SELECT * FROM pg_banned_role;
 roleid | failure_count |        banned_date         
--------+---------------+----------------------------
  16401 |             3 | 2023-06-13 07:59:35.384972
(1 row)

postgres=# select now();
             now             
-----------------------------
 2023-06-13 16:03:13.5883+08
(1 row)

postgres=# \q
[pg@host227 ~]$ date
Tue Jun 13 16:03:19 CST 2023

[darold]

Got it, this problem looks to occurs with self compiled version of PostgreSQL. Does you PostgreSQL 13.9 version was also self compiled? With the PGDG binary version I can not reproduce on debian system, I will give it a try on a rpm based instance.

[Gabi201265]

I have the same issue on my server. I am also interested by the answer : )

[darold]

@Gabi201265 is this a self compiled version of PostgreSQL? What is the PostgreSQL version?

[yinzhishu]

@darold the param credcheck.Reset_superuser is also doesn't works.

[pg@host227 ~]$ psql -d postgres -p 65432
Password for user pg: 
psql (13.11)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 pg        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user_ban1 |                                                            | {}
 user_ban2 | Superuser                                                  | {}

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

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

postgres=# \q
[pg@host227 ~]$ psql -d postgres -p 65432 -U user_ban2
Password for user user_ban2: 
psql: error: FATAL:  password authentication failed for user "user_ban2"
[pg@host227 ~]$ psql -d postgres -p 65432 -U user_ban2
Password for user user_ban2: 
psql: error: FATAL:  rejecting connection, user 'user_ban2' has been banned

[darold]

Please report the result of the following query:

SELECT oid,rolname FROM pg_authid WHERE rolsuper;

[yinzhishu]

[pg@host227 ~]$ psql -d postgres -p 65432 psql (13.11) Type "help" for help.

postgres=# SELECT oid,rolname FROM pg_authid WHERE rolsuper; oid | rolname
-------+----------- 10 | pg 16402 | user_ban2 (2 rows)

postgres=# \q

[Gabi201265]

@darold All our versions of postgresql are compiled by us after going through the official repository. I'm currently on 14.8. I've just tested a version 13.7 and I still have the same problem.

[darold]

Ok good. Actually this reset only concern the postgres superuser with oid 10, here this is user pg in your installation. So you have to connect to PG using the pg user and reset the other superuser using SELECT pg_banned_role_reset('user_ban2');

Documentation says:

In case the postgres superuser was banned, he can not logged anymore. If there is no other superuser account that can be used to reset the record of the banned superuser, set the credcheck.reset_superuser configuration directive to true into postgresql.conf file and send the SIGHUP signal to the PostgreSQL process pid so that it will reread the configuration. Next time the superuser will try to connect, its authentication failure cache entry will be removed.

I will add a note to precise that only oid 10 is concerned.

@yinzhishu, next time please open a dedicated issue to not add confusion into an other unrelated issue. Thanks.

[darold]

@Gabi201265, actually the problem only concern self-compiled PostgreSQL, the problem doesn't occurs in binary version from PGDG. I have to figure out what is the difference between the build that lead to a different behavior.

[darold]

Got it, the count failure behavior change when ssl is set to on or off. Commit 842725c fix this issue.