Thread: Locking out a user after several failed login attempts
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
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
On Wed, 29 Jun 2011 09:54:28 -0400, Mike Thomsen <mikerthomsen@gmail.com> 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.
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 <12ukwn@gmail.com> wrote: > On Wed, 29 Jun 2011 09:54:28 -0400, Mike Thomsen <mikerthomsen@gmail.com> > 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 >
On Wed, 29 Jun 2011 11:26:46 -0400, Mike Thomsen <mikerthomsen@gmail.com> 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.
Yes, we're logging all connections to the database. On Wed, Jun 29, 2011 at 11:42 AM, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote: > On Wed, 29 Jun 2011 11:26:46 -0400, Mike Thomsen <mikerthomsen@gmail.com> > 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. >
On Wed, 29 Jun 2011 11:43:00 -0400, Mike Thomsen <mikerthomsen@gmail.com>
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).
> -----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 > <mikerthomsen@gmail.com> > 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 areprobably going to wish the counter lived outside of your RDBMS.
On Thu, 30 Jun 2011 07:10:43 -0600, "mark" <dvlhntr@gmail.com> 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
"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
youare 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