Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tDeRs-002IRF-V9 for pgsql-general@arkaria.postgresql.org; Wed, 20 Nov 2024 06:44:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tDeRr-002fYy-Bs for pgsql-general@arkaria.postgresql.org; Wed, 20 Nov 2024 06:44:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tDeRq-002fYq-Tz for pgsql-general@lists.postgresql.org; Wed, 20 Nov 2024 06:44:23 +0000 Received: from mail-il1-x12e.google.com ([2607:f8b0:4864:20::12e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tDeRo-002sUh-0r for pgsql-general@lists.postgresql.org; Wed, 20 Nov 2024 06:44:22 +0000 Received: by mail-il1-x12e.google.com with SMTP id e9e14a558f8ab-3a7675c5a94so1319575ab.2 for ; Tue, 19 Nov 2024 22:44:19 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732085058; x=1732689858; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=AwyV69M7w4ewnw9usWG2ZPbn0ShzHcLmvMIeeJBdgOI=; b=gyIICg0JWBemT09XM+uRxZi1KsS4eUxhHl7gHDOCSM1mNVkunEHpnLUI9TZSAqYbgD fswmHYtMhxtnUICXoVzIDR9u3gwRgfWOiWj56uDrDlR/35GrCBA5as81OLbQQX9wB+2h fpvnnVOsWBfMM0y3gfzqv9+i925bkVjP7om+4Bsaq9MUXftkFFLL2cKRtBLfro9UeUlU XnYhpcJawIXY67I3KuE+B4dHkGX3mo12hnYH9Pnw1r+JfVpEeEfaABLFQo+RnYPWarZe Z/4XeHyNDxFLu1wZv4CniUg/sTvQUIVz1nL8OVgHwGNKnh1V6XRCmHmmfHhP2adt7n19 OJqg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732085058; x=1732689858; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=AwyV69M7w4ewnw9usWG2ZPbn0ShzHcLmvMIeeJBdgOI=; b=RQQ+0WD/Xf09+RqNXrMQlcvyawgGRWPasKX/LZDdyD1naOFMK0FmuMzH5Y0MbCY3bM wySuX/QcV1xO4W2r2kewUvuR7ORU1ph6H75y/m2WpzyDxyoSkAif7p5Aq9dPH/XF0mof 1zTUI98ZUcwGV46pgNJSh9SBUabvxgTYiEUlj1fVdoTEfrIhhP/xJmDCcd6jFe+tS71G 6HD7aYXGUuVC7TjXyjqgRewQQJQqUurvq5VI1V7xvlsif83Gt9EWbCI0CXWte69tBUvJ z/QE+A1W/To05HlsuUvu9o0acxl2yYmUvKk/gB4XVxhOaST00Duwp1UPEcl4qmdvp429 ozLg== X-Gm-Message-State: AOJu0YyU6c6BWHdy7/qVKZo5urBIcp4aKK94eCo/UT6ODWPEdrW2XANz Uot2SL7AFCeVybkxhuXEENwgr8tylHqr/gSTPbzGj0kWU+QJsdo+ImiW5ereTiHjDSBAZwNhyAu /2IwLRWls+wHoJaDQHSmF9O5Raxw= X-Gm-Gg: ASbGncskLR+Z5ENpBxfRIqbEoz4VAvrNWumylYVysRWshkoVj268bZ2O0leT62MMafr 2miIPSqFjzf7nW0iB4wdwPhCPGEy8PUqeImrwQ85bhim6Yy8erIZIuTLaMGP5qDA= X-Google-Smtp-Source: AGHT+IEzWuYU8OaiTbY6G64511UxVzcdLi3mCMqQaU5MZHLLC/JzMObGBzBStfnhW49Rpc/XR/0yRkvCxmyw/bdGnGs= X-Received: by 2002:a05:6602:2cce:b0:83a:acc8:5faf with SMTP id ca18e2360f4ac-83eb612e606mr57138339f.5.1732085058080; Tue, 19 Nov 2024 22:44:18 -0800 (PST) MIME-Version: 1.0 References: <3b615ed5-1186-46f2-92bd-363b9b7769a6@aklaver.com> <7870eb0d-6092-44df-9c88-4ba498e14de2@aklaver.com> In-Reply-To: <7870eb0d-6092-44df-9c88-4ba498e14de2@aklaver.com> From: =?UTF-8?B?5by15a6455GL?= Date: Wed, 20 Nov 2024 14:44:04 +0800 Message-ID: Subject: Re: Re : Credcheck extension To: Adrian Klaver Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000009b99800627527a93" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009b99800627527a93 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you for your help.Problem solved! Adrian Klaver =E6=96=BC 2024=E5=B9=B411=E6=9C=88= 20=E6=97=A5 =E9=80=B1=E4=B8=89=EF=BC=8C=E4=B8=8B=E5=8D=8812:04=E5=AF=AB=E9= =81=93=EF=BC=9A > On 11/19/24 19:01, =E5=BC=B5=E5=AE=B8=E7=91=8B wrote: > > Thank you for your help!After applying the patch, the above issue has > > been resolved. > > > > I have another question: After identifying who is in the banned_role, > > the GitHub example uses the command SELECT pg_banned_role_reset(); to > > unlock everyone. I would like to know if there is a way to unlock a > > specific individual rather than unlocking everyone. > > It's in the docs: > > https://github.com/hexacluster/credcheck?tab=3Dreadme-ov-file#examples > > Authentication failure ban > > "A superuser can also reset the content of the banned user cache by > calling a function named public.pg_banned_role_reset(). If it is called > without an argument, all the banned cache will be cleared. To only > remove the record registered for a single user, just pass his name as > parameter. This function returns the number of records removed from the > cache. A restart of PostgreSQL also clear the cache." > > I would suggest reading the entire documentation. > > > > > Adrian Klaver > >=E6=96=BC 2024=E5=B9=B411=E6=9C=8820= =E6=97=A5 =E9=80=B1=E4=B8=89=EF=BC=8C=E4=B8=8A=E5=8D=8812:25=E5=AF=AB=E9=81= =93=EF=BC=9A > > > > On 11/19/24 00:40, =E5=BC=B5=E5=AE=B8=E7=91=8B wrote: > > > Sorry for the inconvenience, but I used make and make install to > > build > > > the credcheck--2.8.0.sql sources zip file. I would like to ask > > how I can > > > update and apply the changes to the system, as I modified the > > files in > > > credcheck/test/expected/06_reuse_interval.out and > > > credcheck/test/sql/06_reuse_interval.sql. However, after running > > make > > > and make install again, I don=E2=80=99t see any changes. > > > > Pretty sure you need to do: > > > > make clean > > > > first, then the rest of the install process. > > > > That process is shown here: > > > > > https://github.com/hexacluster/credcheck?tab=3Dreadme-ov-file#installatio= n < > https://github.com/hexacluster/credcheck?tab=3Dreadme-ov-file#installatio= n> > > > > > > > > > > > > > Adrian Klaver > > > > > >>=E6=96=BC 2024=E5=B9=B411=E6=9C= =8818=E6=97=A5 =E9=80=B1=E4=B8=80=EF=BC=8C=E4=B8=8B=E5=8D=88 > > 11:15=E5=AF=AB=E9=81=93=EF=BC=9A > > > > > > On 11/18/24 01:03, =E5=BC=B5=E5=AE=B8=E7=91=8B wrote: > > > > Hello! > > > > I would like to inquire about the installation of the > > credcheck > > > > third-party package to support password complexity and > > expiration > > > date, > > > > etc., when setting up open-source PostgreSQL. I am using > the > > > > credcheck--2.8.0.sql version from GitHub. After completin= g > the > > > setup, I > > > > encountered the following issue: when an account exceeds > the > > > configured > > > > number of incorrect login attempts, it gets locked. The > > command > > > SELECT * > > > > FROM pg_banned_role; should display the columns roleid, > > > failure_count, > > > > and banned_date, and the view is working properly and > > shows the > > > > information. However, according to the example, the rolei= d > > does not > > > > correctly display the corresponding oid for the account > > with failed > > > > login attempts. I would like to ask if there is a solutio= n > > for this > > > > issue. Thank you! > > > > > > Have you looked a?: > > > > > > https://github.com/HexaCluster/credcheck/issues/39 > > > > > > > > > > > > > -- > > > Adrian Klaver > > > adrian.klaver@aklaver.com > > >> > > > > > > > -- > > Adrian Klaver > > adrian.klaver@aklaver.com > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --0000000000009b99800627527a93 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you for your help.Problem solved!

Adrian Klaver= <adrian.klaver@aklaver.com= >=E6=96=BC 2024=E5=B9=B411=E6=9C=8820=E6=97=A5 =E9=80=B1=E4=B8=89=EF= =BC=8C=E4=B8=8B=E5=8D=8812:04=E5=AF=AB=E9=81=93=EF=BC=9A
On 11/19/24 19:01, =E5=BC=B5=E5=AE=B8=E7=91=8B wrote:
> Thank you for your help!After applying the patch, the above issue has =
> been resolved.
>
>=C2=A0 =C2=A0I have another question: After identifying who is in the b= anned_role,
> the GitHub example uses the command SELECT pg_banned_role_reset(); to =
> unlock everyone. I would like to know if there is a way to unlock a > specific individual rather than unlocking everyone.

It's in the docs:

https://github.com/hexacluster= /credcheck?tab=3Dreadme-ov-file#examples

Authentication failure ban

"A superuser can also reset the content of the banned user cache by calling a function named public.pg_banned_role_reset(). If it is called without an argument, all the banned cache will be cleared. To only
remove the record registered for a single user, just pass his name as
parameter. This function returns the number of records removed from the cache. A restart of PostgreSQL also clear the cache."

I would suggest reading the entire documentation.

>
> Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>=E6=96=BC 2024=E5=B9=B411=E6=9C=88= 20=E6=97=A5 =E9=80=B1=E4=B8=89=EF=BC=8C=E4=B8=8A=E5=8D=8812:25=E5=AF=AB=E9= =81=93=EF=BC=9A
>
>=C2=A0 =C2=A0 =C2=A0On 11/19/24 00:40, =E5=BC=B5=E5=AE=B8=E7=91=8B wrot= e:
>=C2=A0 =C2=A0 =C2=A0 > Sorry for the inconvenience, but I used make = and make install to
>=C2=A0 =C2=A0 =C2=A0build
>=C2=A0 =C2=A0 =C2=A0 > the credcheck--2.8.0.sql sources zip file. I = would like to ask
>=C2=A0 =C2=A0 =C2=A0how I can
>=C2=A0 =C2=A0 =C2=A0 > update and apply the changes to the system, a= s I modified the
>=C2=A0 =C2=A0 =C2=A0files in
>=C2=A0 =C2=A0 =C2=A0 > credcheck/test/expected/06_reuse_interval.out= and
>=C2=A0 =C2=A0 =C2=A0 > credcheck/test/sql/06_reuse_interval.sql. How= ever, after running
>=C2=A0 =C2=A0 =C2=A0make
>=C2=A0 =C2=A0 =C2=A0 > and make install again, I don=E2=80=99t see a= ny changes.
>
>=C2=A0 =C2=A0 =C2=A0Pretty sure you need to do:
>
>=C2=A0 =C2=A0 =C2=A0make clean
>
>=C2=A0 =C2=A0 =C2=A0first, then the rest of the install process.
>
>=C2=A0 =C2=A0 =C2=A0That process is shown here:
>
>=C2=A0 =C2=A0 =C2=A0ht= tps://github.com/hexacluster/credcheck?tab=3Dreadme-ov-file#installation <https://github.com/h= exacluster/credcheck?tab=3Dreadme-ov-file#installation>
>
>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Adrian Klaver <adrian.klaver@aklaver.com
>=C2=A0 =C2=A0 =C2=A0<mailto:adrian.klaver@aklaver.com>
>=C2=A0 =C2=A0 =C2=A0 > <mailto:adrian.klaver@aklaver.com
>=C2=A0 =C2=A0 =C2=A0<mailto:adrian.klaver@aklaver.com>>>=E6=96=BC 2= 024=E5=B9=B411=E6=9C=8818=E6=97=A5 =E9=80=B1=E4=B8=80=EF=BC=8C=E4=B8=8B=E5= =8D=88
>=C2=A0 =C2=A0 =C2=A011:15=E5=AF=AB=E9=81=93=EF=BC=9A
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0On 11/18/24 01:03, =E5=BC= =B5=E5=AE=B8=E7=91=8B wrote:
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > Hello!
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 I would= like to inquire about the installation of the
>=C2=A0 =C2=A0 =C2=A0credcheck
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > third-party package = to support password complexity and
>=C2=A0 =C2=A0 =C2=A0expiration
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0date,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > etc., when setting u= p open-source PostgreSQL. I am using the
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > credcheck--2.8.0.sql= version from GitHub. After completing the
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0setup, I
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > encountered the foll= owing issue: when an account exceeds the
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0configured
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > number of incorrect = login attempts, it gets locked. The
>=C2=A0 =C2=A0 =C2=A0command
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0SELECT *
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > FROM pg_banned_role;= should display the columns roleid,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0failure_count,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > and banned_date, and= the view is working properly and
>=C2=A0 =C2=A0 =C2=A0shows the
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > information. However= , according to the example, the roleid
>=C2=A0 =C2=A0 =C2=A0does not
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > correctly display th= e corresponding oid for the account
>=C2=A0 =C2=A0 =C2=A0with failed
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > login attempts. I wo= uld like to ask if there is a solution
>=C2=A0 =C2=A0 =C2=A0for this
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 > issue. Thank you! >=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0Have you looked a?:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > https://github.com/H= exaCluster/credcheck/issues/39
>=C2=A0 =C2=A0 =C2=A0<https://github.com/Hex= aCluster/credcheck/issues/39>
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0<https://github.com/HexaCluster/credcheck/issues/39
>=C2=A0 =C2=A0 =C2=A0<https://github.com/Hex= aCluster/credcheck/issues/39>>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0 > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>=C2=A0 =C2=A0 =C2=A0<mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.c= om>>
>=C2=A0 =C2=A0 =C2=A0 >
>
>=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><= br> >

--
Adrian Klaver
adrian.klave= r@aklaver.com

--0000000000009b99800627527a93--