Thread: Re : Credcheck extension
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 completing 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 roleid does not correctly display the corresponding oid for the account with failed login attempts. I would like to ask if there is a solution for this issue. Thank you!
Hi, On Mon, Nov 18, 2024 at 5:03 PM 張宸瑋 <kenny020307@gmail.com> wrote: > > Hello! > I would like to inquire about the installation of the credcheck third-party package to support password complexity andexpiration date, etc., when setting up open-source PostgreSQL. I am using the credcheck--2.8.0.sql version from GitHub.After completing the setup, I encountered the following issue: when an account exceeds the configured number of incorrectlogin 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 roleid does not correctly display the corresponding oid for the account with failed login attempts. I would liketo ask if there is a solution for this issue. Thank you! I did a quick test locally and as far as I can see it seems to work as expected: $ psql -U bob postgres Password for user bob: psql: error: connection to server at "127.0.0.1", port 11035 failed: FATAL: password authentication failed for user "bob" $ psql -U postgres -c "SELECT roleid::regrole, * from pg_banned_role" roleid | roleid | failure_count | banned_date --------+--------+---------------+------------- bob | 750815 | 1 | <NULL> (1 row) If you don't get a similar behavior you should raise an issue to the author directly on the extension's github repo.
On 11/18/24 01:03, 張宸瑋 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 completing 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 roleid does not > correctly display the corresponding oid for the account with failed > login attempts. I would like to ask if there is a solution for this > issue. Thank you! Have you looked a?: https://github.com/HexaCluster/credcheck/issues/39 -- Adrian Klaver adrian.klaver@aklaver.com
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’t see any changes.
Adrian Klaver <adrian.klaver@aklaver.com>於 2024年11月18日 週一，下午11:15寫道：
On 11/18/24 01:03, 張宸瑋 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 completing 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 roleid does not
> correctly display the corresponding oid for the account with failed
> login attempts. I would like to ask if there is a solution for this
> issue. Thank you!
Have you looked a?:
https://github.com/HexaCluster/credcheck/issues/39
--
Adrian Klaver
adrian.klaver@aklaver.com
On 11/19/24 00:40, 張宸瑋 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’t 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=readme-ov-file#installation > > > Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>於 2024年11月18日 週一，下午11:15寫道： > > On 11/18/24 01:03, 張宸瑋 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 completing 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 roleid does not > > correctly display the corresponding oid for the account with failed > > login attempts. I would like to ask if there is a solution for this > > issue. Thank you! > > Have you looked a?: > > https://github.com/HexaCluster/credcheck/issues/39 > <https://github.com/HexaCluster/credcheck/issues/39> > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
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.
Adrian Klaver <adrian.klaver@aklaver.com>於 2024年11月20日 週三，上午12:25寫道：
On 11/19/24 00:40, 張宸瑋 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’t 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=readme-ov-file#installation
>
>
> Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>於 2024年11月18日 週一，下午11:15寫道：
>
> On 11/18/24 01:03, 張宸瑋 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 completing 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 roleid does not
> > correctly display the corresponding oid for the account with failed
> > login attempts. I would like to ask if there is a solution for this
> > issue. Thank you!
>
> Have you looked a?:
>
> https://github.com/HexaCluster/credcheck/issues/39
> <https://github.com/HexaCluster/credcheck/issues/39>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 11/19/24 19:01, 張宸瑋 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=readme-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>>於 2024年11月20日 週三，上午12:25寫道： > > On 11/19/24 00:40, 張宸瑋 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’t 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=readme-ov-file#installation <https://github.com/hexacluster/credcheck?tab=readme-ov-file#installation> > > > > > > > > Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>>於 2024年11月18日 週一，下午 > 11:15寫道： > > > > On 11/18/24 01:03, 張宸瑋 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 completing 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 roleid > does not > > > correctly display the corresponding oid for the account > with failed > > > login attempts. I would like to ask if there is a solution > for this > > > issue. Thank you! > > > > Have you looked a?: > > > > https://github.com/HexaCluster/credcheck/issues/39 > <https://github.com/HexaCluster/credcheck/issues/39> > > <https://github.com/HexaCluster/credcheck/issues/39 > <https://github.com/HexaCluster/credcheck/issues/39>> > > > > -- > > Adrian Klaver > > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
Thank you for your help.Problem solved!
Adrian Klaver <adrian.klaver@aklaver.com>於 2024年11月20日 週三，下午12:04寫道：
On 11/19/24 19:01, 張宸瑋 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=readme-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>>於 2024年11月20日 週三，上午12:25寫道：
>
> On 11/19/24 00:40, 張宸瑋 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’t 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=readme-ov-file#installation <https://github.com/hexacluster/credcheck?tab=readme-ov-file#installation>
>
>
> >
> >
> > Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>
> > <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>>於 2024年11月18日 週一，下午
> 11:15寫道：
> >
> > On 11/18/24 01:03, 張宸瑋 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 completing 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 roleid
> does not
> > > correctly display the corresponding oid for the account
> with failed
> > > login attempts. I would like to ask if there is a solution
> for this
> > > issue. Thank you!
> >
> > Have you looked a?:
> >
> > https://github.com/HexaCluster/credcheck/issues/39
> <https://github.com/HexaCluster/credcheck/issues/39>
> > <https://github.com/HexaCluster/credcheck/issues/39
> <https://github.com/HexaCluster/credcheck/issues/39>>
> >
> > --
> > Adrian Klaver
> > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
--
Adrian Klaver
adrian.klaver@aklaver.com