Re : Credcheck extension

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 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!

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/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&gt;

--
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 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&gt;

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&gt;

     <https://github.com/HexaCluster/credcheck/issues/39

<https://github.com/HexaCluster/credcheck/issues/39&gt;&gt;

     --
     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寫道：