Обсуждение: Failed Login Attempts parameter

Hi,

I've been searching the web and reviewing documentation, but I cannot find any reference to whether or not a parameter, for example, failed_login_attempts, exists in PostgreSQL that determines the number of attempts a user can make before being locked.  In addition, if such a parameter or similar setup exists, is there also some database object that provides a current count of the failed login attempts?

Thanks,
Frank


Frank Cavaliero

Database Administrator

IBM Infosphere Guardium

IBM Software Group, Information Management

978-899-3635 - Direct

For Technical Services Support Please Call 978-899-9195.

 

 

Hi,

A little suplement to previous answer You can also automate GRANT process by creating a table called e.g. locked_users containing role name and revoke timestamp then make a function that will check the time passed since revoking permissions for each user and if it will by equal or higher than time given e.g. 24 hours it will run GRANT LOGIN on the role.



--
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"

On 11/15/2012 04:40 PM, Lukasz Brodziak wrote:
> Hi,
>
> As far as I know there is no such parameter in PG. If you use PG as a
> database for your program you can implement the feature in the
> appplication by logging the failed logins in separate table(or the one
> holding application users and their passwords) and then disable login
> for the user if number of failures is bigger than specified value.
> Another way is to create a function in postgresql which will check
> failed login count and if the attempts go bigger than specified number
> it will run a REVOKE on the user role thus disabling login. Then admin
> will have to run GRANT for the user manually.

Another option would be to monitor syslog or the csvlog and lock the
user out by changing their password or revoking CONNECT rights if they
trip the threshold. It wouldn't be as responsive to high-rate brute
forcing attempts but your IDS should be handing those already.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

2012/11/15 Craig Ringer <craig@2ndquadrant.com>
> Another option would be to monitor syslog or the csvlog and lock the
> user out by changing their password or revoking CONNECT rights if they
> trip the threshold. It wouldn't be as responsive to high-rate brute
> forcing attempts but your IDS should be handing those already.
>
> --
>  Craig Ringer                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

I wouldn't go with password change approach, at least not
automatically this can be done 'on user's demand' at any point. I
admit that I wasn't specific in my solution with REVOKE as I didn't
say which rights should be revoked I of course mean revoke connect
command as Craig was kind to mention.

Regards
--
Łukasz Brodziak
"Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear"

* Craig James (cjames@emolecules.com) wrote:
> A far better approach is an escalating delay. Check the number of failed
> login attempts N and delay (for example) N^2 seconds before responding
> again.  Legitimate users are mildly inconvenienced, and hackers are
> severely hampered.

Sadly, in certain environments (US Federal organizations which are
required to follow FISMA), a lock-after-X-attempts control is required.

We dealt with this by utilizing the PAM authentication method with
pam_tally.  It's kind of ugly, but it can be made to work.  Other
alternatives are using Kerberos or Certificate-based authentication
where the user has to acquire initial credenials through some other
mechanism and then those have a limited time of usefulness (eg: Kerberos
tickets only last 10 hours).  By using those credentials instead of
having database-based password requirements, you can avoid the entire
problem (along with password ageing, etc..).

    Thanks,

        Stephen