Message-ID: <658c4eab.050a0220.731ff.011e@mx.google.com>
Sender: Michael Banck <mbanck@gmail.com>
Date: Wed, 27 Dec 2023 17:19:54 +0100
From: Michael Banck <mbanck@gmx.net>
To: pgsql-hackers@lists.postgresql.org
Cc: =?utf-8?B?5oiQ5LmL54SV?= <zhcheng@ceresdata.com>
Subject: [PATCH] Exponential backoff for auth_delay
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="ZPt4rx8FFjLCG7dd"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/658c4eab.050a0220.731ff.011e%40mx.google.com>
Precedence: bulk


--ZPt4rx8FFjLCG7dd
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

Hi,

we had a conversation with a customer about security compliance a while
ago and one thing they were concerned about was avoiding brute-force
attemps for remote password guessing. This is should not be a big
concern if reasonably secure passwords are used and increasing SCRAM
iteration count can also help, but generally auth_delay is recommended
for this if there are concerns.

This patch adds exponential backoff so that one can choose a small
initial value which gets doubled for each failed authentication attempt
until a maximum wait time (which is 10s by default, but can be disabled
if so desired).

Currently, this patch tracks remote hosts but not users, the idea being
that a remote attacker likely tries several users from a particular
host, but this could in theory be extended to users if there are
concerns.

The patch is partly based on an earlier, more ambitious attempt at
extending auth_delay by 成之焕 from a year ago:
https://postgr.es/m/AHwAxACqIwIVOEhs5YejpqoG.1.1668569845751.Hmail.zhcheng@ceresdata.com


Michael

--ZPt4rx8FFjLCG7dd
Content-Type: text/x-diff; charset=utf-8
Content-Disposition: attachment;
 filename="v1-0001-Add-optional-exponential-backoff-to-auth_delay-co.patch"
Content-Transfer-Encoding: 8bit

From 4c964c866010bbdbeee9f0b2a755d97c91c5c091 Mon Sep 17 00:00:00 2001
From: Michael Banck <michael.banck@credativ.de>
Date: Wed, 27 Dec 2023 15:55:39 +0100
Subject: [PATCH v1] Add optional exponential backoff to auth_delay contrib
 module.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This adds two new GUCs for auth_delay, exp_backoff and max_seconds. The former
controls whether exponential backoff should be used or not, the latter sets an
maximum delay (default is 10s) in case exponential backoff is active.

The exponential backoff is tracked per remote host and doubled for every failed
login attempt (i.e., wrong password, not just missing pg_hba line or database)
and reset to auth_delay.milliseconds after a successful authentication from
that host.

This patch is partly based on a larger (but ultimately rejected) patch by
成之焕.

Authors: Michael Banck, 成之焕
Discussion: https://postgr.es/m/AHwAxACqIwIVOEhs5YejpqoG.1.1668569845751.Hmail.zhcheng@ceresdata.com
---
 contrib/auth_delay/auth_delay.c  | 202 ++++++++++++++++++++++++++++++-
 doc/src/sgml/auth-delay.sgml     |  41 +++++++
 src/tools/pgindent/typedefs.list |   1 +
 3 files changed, 243 insertions(+), 1 deletion(-)

diff --git a/contrib/auth_delay/auth_delay.c b/contrib/auth_delay/auth_delay.c
index 8d6e4d2778..95e56db6ec 100644
--- a/contrib/auth_delay/auth_delay.c
+++ b/contrib/auth_delay/auth_delay.c
@@ -14,24 +14,50 @@
 #include <limits.h>
 
 #include "libpq/auth.h"
+#include "miscadmin.h"
 #include "port.h"
+#include "storage/ipc.h"
+#include "storage/shmem.h"
 #include "utils/guc.h"
 #include "utils/timestamp.h"
 
 PG_MODULE_MAGIC;
 
+#define MAX_CONN_RECORDS 50
+
 /* GUC Variables */
 static int	auth_delay_milliseconds = 0;
+static bool auth_delay_exp_backoff = false;
+static int	auth_delay_max_seconds = 0;
 
 /* Original Hook */
 static ClientAuthentication_hook_type original_client_auth_hook = NULL;
 
+typedef struct AuthConnRecord
+{
+	char		remote_host[NI_MAXHOST];
+	bool		used;
+	double		sleep_time;		/* in milliseconds */
+} AuthConnRecord;
+
+static shmem_startup_hook_type shmem_startup_next = NULL;
+static shmem_request_hook_type shmem_request_next = NULL;
+static AuthConnRecord *acr_array = NULL;
+
+static AuthConnRecord *find_conn_record(char *remote_host, int *free_index);
+static double record_failed_conn_auth(Port *port);
+static double find_conn_max_delay(void);
+static void record_conn_failure(AuthConnRecord *acr);
+static void cleanup_conn_record(Port *port);
+
 /*
  * Check authentication
  */
 static void
 auth_delay_checks(Port *port, int status)
 {
+	double		delay;
+
 	/*
 	 * Any other plugins which use ClientAuthentication_hook.
 	 */
@@ -43,8 +69,150 @@ auth_delay_checks(Port *port, int status)
 	 */
 	if (status != STATUS_OK)
 	{
-		pg_usleep(1000L * auth_delay_milliseconds);
+		if (auth_delay_exp_backoff)
+		{
+			/*
+			 * Exponential backoff per remote host.
+			 */
+			delay = record_failed_conn_auth(port);
+			if (auth_delay_max_seconds > 0)
+				delay = Min(delay, 1000L * auth_delay_max_seconds);
+		}
+		else
+			delay = auth_delay_milliseconds;
+		if (delay > 0)
+		{
+			elog(DEBUG1, "Authentication delayed for %g seconds", delay / 1000.0);
+			pg_usleep(1000L * (long) delay);
+		}
+	}
+	else
+	{
+		cleanup_conn_record(port);
+	}
+}
+
+static double
+record_failed_conn_auth(Port *port)
+{
+	AuthConnRecord *acr = NULL;
+	int			j = -1;
+
+	acr = find_conn_record(port->remote_host, &j);
+
+	if (!acr)
+	{
+		if (j == -1)
+
+			/*
+			 * No free space, MAX_CONN_RECORDS reached. Wait as long as the
+			 * largest delay for any remote host.
+			 */
+			return find_conn_max_delay();
+		acr = &acr_array[j];
+		strcpy(acr->remote_host, port->remote_host);
+		acr->used = true;
+		elog(DEBUG1, "new connection: %s, index: %d", acr->remote_host, j);
+	}
+
+	record_conn_failure(acr);
+	return acr->sleep_time;
+}
+
+static AuthConnRecord *
+find_conn_record(char *remote_host, int *free_index)
+{
+	int			i;
+
+	*free_index = -1;
+	for (i = 0; i < MAX_CONN_RECORDS; i++)
+	{
+		if (!acr_array[i].used)
+		{
+			if (*free_index == -1)
+				/* record unused element */
+				*free_index = i;
+			continue;
+		}
+		if (strcmp(acr_array[i].remote_host, remote_host) == 0)
+			return &acr_array[i];
+	}
+
+	return NULL;
+}
+
+static double
+find_conn_max_delay(void)
+{
+	int			i;
+	double		max_delay = 0.0;
+
+
+	for (i = 0; i < MAX_CONN_RECORDS; i++)
+	{
+		if (acr_array[i].used && acr_array[i].sleep_time > max_delay)
+			max_delay = acr_array[i].sleep_time;
 	}
+
+	return max_delay;
+}
+
+static void
+record_conn_failure(AuthConnRecord *acr)
+{
+	if (acr->sleep_time == 0)
+		acr->sleep_time = (double) auth_delay_milliseconds;
+	else
+		acr->sleep_time *= 2;
+}
+
+static void
+cleanup_conn_record(Port *port)
+{
+	int			free_index;
+	AuthConnRecord *acr = NULL;
+
+	acr = find_conn_record(port->remote_host, &free_index);
+	if (acr == NULL)
+		return;
+
+	acr->used = false;
+	acr->sleep_time = 0.0;
+}
+
+/*
+ * Set up shared memory
+ */
+
+static void
+auth_delay_shmem_request(void)
+{
+	Size		required;
+
+	if (shmem_request_next)
+		shmem_request_next();
+
+	required = sizeof(AuthConnRecord) * MAX_CONN_RECORDS;
+	required += sizeof(int);
+	RequestAddinShmemSpace(required);
+}
+
+static void
+auth_delay_shmem_startup(void)
+{
+	Size		required;
+	bool		found;
+
+	if (shmem_startup_next)
+		shmem_startup_next();
+
+	required = sizeof(AuthConnRecord) * MAX_CONN_RECORDS;
+	acr_array = ShmemInitStruct("Array of AuthConnRecord", required, &found);
+	if (found)
+		/* this should not happen ? */
+		elog(DEBUG1, "variable acr_array already exists");
+	/* all fileds are set to 0 */
+	memset(acr_array, 0, required);
 }
 
 /*
@@ -53,6 +221,11 @@ auth_delay_checks(Port *port, int status)
 void
 _PG_init(void)
 {
+	if (!process_shared_preload_libraries_in_progress)
+		ereport(ERROR,
+				(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+				 errmsg("auth_delay must be loaded via shared_preload_libraries")));
+
 	/* Define custom GUC variables */
 	DefineCustomIntVariable("auth_delay.milliseconds",
 							"Milliseconds to delay before reporting authentication failure",
@@ -66,9 +239,36 @@ _PG_init(void)
 							NULL,
 							NULL);
 
+	DefineCustomBoolVariable("auth_delay.exp_backoff",
+							 "Exponential backoff for failed connections, per remote host",
+							 NULL,
+							 &auth_delay_exp_backoff,
+							 false,
+							 PGC_SIGHUP,
+							 0,
+							 NULL,
+							 NULL,
+							 NULL);
+
+	DefineCustomIntVariable("auth_delay.max_seconds",
+							"Maximum seconds to wait when login fails during exponential backoff",
+							NULL,
+							&auth_delay_max_seconds,
+							10,
+							0, INT_MAX,
+							PGC_SIGHUP,
+							GUC_UNIT_S,
+							NULL, NULL, NULL);
+
 	MarkGUCPrefixReserved("auth_delay");
 
 	/* Install Hooks */
 	original_client_auth_hook = ClientAuthentication_hook;
 	ClientAuthentication_hook = auth_delay_checks;
+
+	/* Set up shared memory */
+	shmem_request_next = shmem_request_hook;
+	shmem_request_hook = auth_delay_shmem_request;
+	shmem_startup_next = shmem_startup_hook;
+	shmem_startup_hook = auth_delay_shmem_startup;
 }
diff --git a/doc/src/sgml/auth-delay.sgml b/doc/src/sgml/auth-delay.sgml
index 0571f2a99d..2a6efad851 100644
--- a/doc/src/sgml/auth-delay.sgml
+++ b/doc/src/sgml/auth-delay.sgml
@@ -16,6 +16,17 @@
   connection slots.
  </para>
 
+ <para>
+  It is optionally possible to let <filename>auth_delay</filename> wait longer
+  for each successive authentication failure from a particular remote host, if
+  the configuration parameter <varname>auth_delay.exp_backoff</varname> is
+  active.  Once an authentication succeeded from a remote host, the
+  authentication delay is reset to the value of
+  <varname>auth_delay.milliseconds</varname> for this host.  The parmaeter
+  <primary><varname>auth_delay.max_seconds</varname> sets an upper bound for
+  the delay in this case.
+ </para>
+
  <para>
   In order to function, this module must be loaded via
   <xref linkend="guc-shared-preload-libraries"/> in <filename>postgresql.conf</filename>.
@@ -39,6 +50,34 @@
      </para>
     </listitem>
    </varlistentry>
+   <varlistentry>
+    <term>
+     <varname>auth_delay.exp_backoff</varname> (<type>bool</type>)
+     <indexterm>
+      <primary><varname>auth_delay.exp_backoff</varname> configuration parameter</primary>
+     </indexterm>
+    </term>
+    <listitem>
+     <para>
+      Whether to use exponential backoff per remote host on authentication
+      failure.  The default is off.
+     </para>
+    </listitem>
+   </varlistentry>
+   <varlistentry>
+    <term>
+     <varname>auth_delay.max_seconds</varname> (<type>integer</type>)
+     <indexterm>
+      <primary><varname>auth_delay.max_seconds</varname> configuration parameter</primary>
+     </indexterm>
+    </term>
+    <listitem>
+     <para>
+      How many seconds to wait at most if exponential backoff is active.
+      Setting this parameter to 0 disables it.  The default is 10 seconds.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
 
   <para>
@@ -51,6 +90,8 @@
 shared_preload_libraries = 'auth_delay'
 
 auth_delay.milliseconds = '500'
+auth_delay.exp_backoff = 'on'
+auth_delay.max_seconds = '20'
 </programlisting>
  </sect2>
 
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e37ef9aa76..9b62945f28 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -164,6 +164,7 @@ AttrMap
 AttrMissing
 AttrNumber
 AttributeOpts
+AuthConnRecord
 AuthRequest
 AuthToken
 AutoPrewarmSharedState
-- 
2.39.2


--ZPt4rx8FFjLCG7dd--