From 2dee57fd43ebe1cf1f43fb0161cdd5f072eeaa63 Mon Sep 17 00:00:00 2001
From: Nils Adermann <naderman@naderman.de>
Date: Fri, 10 Jun 2011 12:02:59 +0200
Subject: [ticket/9992] Adding a limit on login attempts per IP.

A new table was created to save all failed login attempts with
corresponding information on username, ip and useragent. By default
the limit is 50 login attempts within 6 hours per IP. The limit is
relatively high to avoid big problems on sites behind a reverse
proxy that don't receive the forwarded-for value as REMOTE_ADDR but
see all users as coming from the same IP address. But if these
users run into problems a special forwarded-for option is available
to limit logins by forwarded-for value instead of ip.

PHPBB3-9992
---
 phpBB/install/schemas/postgres_schema.sql | 24 +++++++++++++++++++++++-
 1 file changed, 23 insertions(+), 1 deletion(-)

(limited to 'phpBB/install/schemas/postgres_schema.sql')

diff --git a/phpBB/install/schemas/postgres_schema.sql b/phpBB/install/schemas/postgres_schema.sql
index a2d4dc3e0b..c4c2307c3a 100644
--- a/phpBB/install/schemas/postgres_schema.sql
+++ b/phpBB/install/schemas/postgres_schema.sql
@@ -1,6 +1,6 @@
 /*
 
- $Id$
+ $Id: $
 
 */
 
@@ -524,6 +524,28 @@ CREATE INDEX phpbb_log_topic_id ON phpbb_log (topic_id);
 CREATE INDEX phpbb_log_reportee_id ON phpbb_log (reportee_id);
 CREATE INDEX phpbb_log_user_id ON phpbb_log (user_id);
 
+/*
+	Table: 'phpbb_login_attempts'
+*/
+CREATE SEQUENCE phpbb_login_attempts_seq;
+
+CREATE TABLE phpbb_login_attempts (
+	attempt_id INT4 DEFAULT nextval('phpbb_login_attempts_seq'),
+	attempt_ip varchar(40) DEFAULT '' NOT NULL,
+	attempt_browser varchar(150) DEFAULT '' NOT NULL,
+	attempt_forwarded_for varchar(255) DEFAULT '' NOT NULL,
+	attempt_time INT4 DEFAULT '0' NOT NULL CHECK (attempt_time >= 0),
+	user_id INT4 DEFAULT '0' NOT NULL CHECK (user_id >= 0),
+	username varchar(255) DEFAULT '0' NOT NULL,
+	username_clean varchar_ci DEFAULT '0' NOT NULL,
+	PRIMARY KEY (attempt_id)
+);
+
+CREATE INDEX phpbb_login_attempts_attempt_ip ON phpbb_login_attempts (attempt_ip, attempt_time);
+CREATE INDEX phpbb_login_attempts_attempt_forwarded_for ON phpbb_login_attempts (attempt_forwarded_for, attempt_time);
+CREATE INDEX phpbb_login_attempts_attempt_time ON phpbb_login_attempts (attempt_time);
+CREATE INDEX phpbb_login_attempts_user_id ON phpbb_login_attempts (user_id);
+
 /*
 	Table: 'phpbb_moderator_cache'
 */
-- 
cgit v1.2.1

