FAQ
Is it possible to lock a database user in 8.4 after several failed
login attempts? I looked through the docs, but couldn't find anything
about this.

Thanks,

Mike

Search Discussions

  • Ktm at Jun 29, 2011 at 1:58 pm

    On Wed, Jun 29, 2011 at 09:54:28AM -0400, Mike Thomsen wrote:
    Is it possible to lock a database user in 8.4 after several failed
    login attempts? I looked through the docs, but couldn't find anything
    about this.

    Thanks,

    Mike
    Hi Mike,

    Could you use something like fail2ban to watch the log for failed
    login attempts and then lock the user?

    Ken
  • Jean-Yves F. Barbier at Jun 29, 2011 at 2:17 pm

    On Wed, 29 Jun 2011 09:54:28 -0400, Mike Thomsen wrote:



    Is it possible to lock a database user in 8.4 after several failed
    login attempts? I looked through the docs, but couldn't find anything
    about this.
    I would say log attempts count in a table, reset count to 0 on good login =< 3
    (trigger) and another trigger that revokes the connect privilege from the
    baaad user if count reaches 3.

    --
    I can't drive 55.
  • Mike Thomsen at Jun 29, 2011 at 3:26 pm
    From the sounds of it, it's not a standard feature. Is that correct?
    On Wed, Jun 29, 2011 at 10:17 AM, Jean-Yves F. Barbier wrote:
    On Wed, 29 Jun 2011 09:54:28 -0400, Mike Thomsen wrote:


    Is it possible to lock a database user in 8.4 after several failed
    login attempts? I looked through the docs, but couldn't find anything
    about this.
    I would say log attempts count in a table, reset count to 0 on good login =< 3
    (trigger) and another trigger that revokes the connect privilege from the
    baaad user if count reaches 3.

    --
    I can't drive 55.

    --
    Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-novice
  • Jean-Yves F. Barbier at Jun 29, 2011 at 3:42 pm

    On Wed, 29 Jun 2011 11:26:46 -0400, Mike Thomsen wrote:



    From the sounds of it, it's not a standard feature. Is that correct?
    Not at all, but it depends on one thing: is there a possibility to retrieve
    unsuccessful login attempts with all wanted information?? (user, db @least).
    If so, it is trivial to set up.

    --
    May Allah blow sand in your Preparation H.
  • Mike Thomsen at Jun 29, 2011 at 3:43 pm
    Yes, we're logging all connections to the database.
    On Wed, Jun 29, 2011 at 11:42 AM, Jean-Yves F. Barbier wrote:
    On Wed, 29 Jun 2011 11:26:46 -0400, Mike Thomsen wrote:


    From the sounds of it, it's not a standard feature. Is that correct?
    Not at all, but it depends on one thing: is there a possibility to retrieve
    unsuccessful login attempts with all wanted information?? (user, db @least).
    If so, it is trivial to set up.

    --
    May Allah blow sand in your Preparation H.
  • Jean-Yves F. Barbier at Jun 29, 2011 at 4:00 pm

    On Wed, 29 Jun 2011 11:43:00 -0400, Mike Thomsen wrote:



    Yes, we're logging all connections to the database.
    I'm interested into that (you can it send to me as a PM.)

    So, you just have to add a counter to your login table:
    +------------------------------------+
    login attempt-----good----reset CTR to 1 |
    bad |
    CTR += 1 |
    CTR > 3?-------no-----------------------+
    yes
    revoke connect from bastarduser (until manually re-granted by dbA)
    trigger alarm in security dpt
    send 450kV into user's keyboard

    --
    Q: What do you call a half-dozen Indians with Asian flu?
    A: Six sick Sikhs (sic).
  • Mark at Jun 30, 2011 at 1:10 pm

    -----Original Message-----
    From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-
    owner@postgresql.org] On Behalf Of Jean-Yves F. Barbier
    Sent: Wednesday, June 29, 2011 10:01 AM
    To: Mike Thomsen
    Cc: pgsql-novice@postgresql.org
    Subject: Re: [NOVICE] Locking out a user after several failed login
    attempts

    On Wed, 29 Jun 2011 11:43:00 -0400, Mike Thomsen
    wrote:


    Yes, we're logging all connections to the database.
    I'm interested into that (you can it send to me as a PM.)

    So, you just have to add a counter to your login table:
    +------------------------------------+
    login attempt-----good----reset CTR to 1 |
    bad |
    CTR += 1 |
    CTR > 3?-------no-----------------------+
    yes


    That might be a ok on a small application with a limited number of users. A few thousand login attempts per min and you are probably going to wish the counter lived outside of your RDBMS.
  • Jean-Yves F. Barbier at Jun 30, 2011 at 3:44 pm

    On Thu, 30 Jun 2011 07:10:43 -0600, "mark" wrote:


    That might be a ok on a small application with a limited number of users. A
    few thousand login attempts per min and you are probably going to wish the
    counter lived outside of your RDBMS.
    As pg is able to sustain large loads, I don't see why. But you can
    externalize if you want, using dblink.

    --
    The weed of crime bears bitter fruit... but the leaves are good to smoke!
    -- The Shadow
  • Tom Lane at Jul 1, 2011 at 10:51 pm

    "mark" <dvlhntr@gmail.com> writes:
    From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-
    owner@postgresql.org] On Behalf Of Jean-Yves F. Barbier
    So, you just have to add a counter to your login table:
    That might be a ok on a small application with a limited number of users. A few thousand login attempts per min and you are probably going to wish the counter lived outside of your RDBMS.
    Usually, when somebody asks for this or related security-policy hacks,
    we suggest using PAM for authentication. There are already PAM modules
    for practically any reasonable password policy, so why reinvent the
    wheel ...

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 29, '11 at 1:54p
activeJul 1, '11 at 10:51p
posts10
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase