agora inbox for [email protected]  

help / color / mirror / Atom feed

[PATCH 4/4] demote: add various tests related to demote and promote actions
7+ messages / 2 participants
[nested] [flat]

* [PATCH 4/4] demote: add various tests related to demote and promote actions
@ 2020-07-10 00:00 Jehan-Guillaume de Rorthais <[email protected]>
  0 siblings, 0 replies; 7+ messages in thread

From: Jehan-Guillaume de Rorthais @ 2020-07-10 00:00 UTC (permalink / raw)

* demote/promote with a standby replicating from the node
* make sure 2PC survive a demote/promote cycle
* commit 2PC and check the result
* swap roles between primary and standby
* make sure wal sender enters cascade mode
* commit a 2PC on the new primary
* confirm behavior of backends during smart/fast demote
---
 src/test/perl/PostgresNode.pm             |  25 ++
 src/test/recovery/t/021_promote-demote.pl | 287 ++++++++++++++++++++++
 2 files changed, 312 insertions(+)
 create mode 100644 src/test/recovery/t/021_promote-demote.pl

diff --git a/src/test/perl/PostgresNode.pm b/src/test/perl/PostgresNode.pm
index 8c1b77376f..4488365ffc 100644
--- a/src/test/perl/PostgresNode.pm
+++ b/src/test/perl/PostgresNode.pm
@@ -906,6 +906,31 @@ sub promote
 
 =pod
 
+=item $node->demote()
+
+Wrapper for pg_ctl demote
+
+=cut
+
+sub demote
+{
+	my ($self, $mode) = @_;
+	my $port    = $self->port;
+	my $pgdata  = $self->data_dir;
+	my $logfile = $self->logfile;
+	my $name    = $self->name;
+
+	$mode = 'fast' unless defined $mode;
+
+	print "### Demoting node \"$name\" using mode $mode\n";
+
+	TestLib::system_or_bail('pg_ctl', '-D', $pgdata, '-l', $logfile,
+		'-m', $mode, 'demote');
+	return;
+}
+
+=pod
+
 =item $node->logrotate()
 
 Wrapper for pg_ctl logrotate
diff --git a/src/test/recovery/t/021_promote-demote.pl b/src/test/recovery/t/021_promote-demote.pl
new file mode 100644
index 0000000000..245acfb211
--- /dev/null
+++ b/src/test/recovery/t/021_promote-demote.pl
@@ -0,0 +1,287 @@
+# Test demote/promote actions in various scenarios using three
+# nodes alpha, beta and gamma. We check proper actions results,
+# correct data replication and cascade across multiple
+# demote/promote, manual switchover, smart and fast demote.
+
+use strict;
+use warnings;
+use PostgresNode;
+use TestLib;
+use Test::More tests => 24;
+
+$ENV{PGDATABASE} = 'postgres';
+
+# Initialize node alpha
+my $node_alpha = get_new_node('alpha');
+$node_alpha->init(allows_streaming => 1);
+$node_alpha->append_conf(
+	'postgresql.conf', qq(
+	max_prepared_transactions = 10
+));
+
+# Take backup
+my $backup_name = 'alpha_backup';
+$node_alpha->start;
+$node_alpha->backup($backup_name);
+
+# Create node beta from backup
+my $node_beta = get_new_node('beta');
+$node_beta->init_from_backup($node_alpha, $backup_name);
+$node_beta->enable_streaming($node_alpha);
+$node_beta->start;
+
+# Create node gamma from backup
+my $node_gamma = get_new_node('gamma');
+$node_gamma->init_from_backup($node_alpha, $backup_name);
+$node_gamma->enable_streaming($node_alpha);
+$node_gamma->start;
+
+# Create some 2PC on alpha for future tests
+$node_alpha->safe_psql('postgres', q{
+CREATE TABLE ins AS SELECT 1 AS i;
+BEGIN;
+CREATE TABLE new AS SELECT generate_series(1,5) AS i;
+PREPARE TRANSACTION 'pxact1';
+BEGIN;
+INSERT INTO ins VALUES (2);
+PREPARE TRANSACTION 'pxact2';
+});
+
+# create an in idle in xact session
+my ($sess1_in, $sess1_out, $sess1_err) = ('', '', '');
+my $sess1 = IPC::Run::start(
+	[
+		'psql', '-X', '-qAt', '-v', 'ON_ERROR_STOP=1', '-f', '-', '-d',
+		$node_alpha->connstr('postgres')
+	],
+	'<', \$sess1_in,
+	'>', \$sess1_out,
+	'2>', \$sess1_err);
+
+$sess1_in = q{
+BEGIN;
+CREATE TABLE public.test_aborted (i int);
+SELECT pg_backend_pid();
+};
+$sess1->pump until $sess1_out =~ qr/[[:digit:]]+[\r\n]$/m;
+my $sess1_pid = $sess1_out;
+chomp $sess1_pid;
+
+# create an in idle session
+my ($sess2_in, $sess2_out, $sess2_err) = ('', '', '');
+my $sess2 = IPC::Run::start(
+	[
+		'psql', '-X', '-qAt', '-v', 'ON_ERROR_STOP=1', '-f', '-', '-d',
+		$node_alpha->connstr('postgres')
+	],
+	'<', \$sess2_in,
+	'>', \$sess2_out,
+	'2>', \$sess2_err);
+$sess2_in = q{
+SELECT pg_backend_pid();
+};
+$sess2->pump until $sess2_out =~ qr/\d+\s*$/m;
+my $sess2_pid = $sess2_out;
+chomp $sess2_pid;
+
+$sess2_in = q{
+SELECT pg_is_in_recovery();
+};
+$sess2->pump until $sess2_out =~ qr/(t|f)\s*$/m;
+
+# idle session is not in recovery
+is( $1, 'f', 'idle session is not in recovery' );
+
+# Fast demote alpha.
+# Secondaries beta and gamma should keep streaming from it as cascaded standbys.
+# Idle in xact session should be terminate, idle session should stay alive.
+$node_alpha->demote('fast');
+
+is( $node_alpha->safe_psql( 'postgres', 'SELECT pg_is_in_recovery()'),
+	't', 'node alpha demoted to standby' );
+
+is( $node_alpha->safe_psql(
+		'postgres',
+		'SELECT array_agg(application_name ORDER BY application_name ASC) FROM pg_stat_replication'),
+	'{beta,gamma}', 'standbys keep replicating with alpha after demote' );
+
+# the idle in xact session should not survive the demote
+is( $node_alpha->safe_psql(
+		'postgres',
+		qq{SELECT count(*)
+		   FROM pg_catalog.pg_stat_activity
+		   WHERE pid = $sess1_pid}),
+	'0', 'previous idle in transaction session should be terminated' );
+
+# table "test_aborted" has been rollbacked
+is( $node_alpha->safe_psql(
+		'postgres',
+		q{SELECT count(*) FROM pg_catalog.pg_class
+		  WHERE relname='test_aborted'
+		    AND relnamespace = (SELECT oid FROM pg_namespace
+		                        WHERE nspname='public')}),
+	'0', 'the tansaction bas been aborted during fast demote' );
+
+# the idle session should survive the demote
+is( $node_alpha->safe_psql(
+		'postgres',
+		qq{SELECT count(*)
+		   FROM pg_catalog.pg_stat_activity
+		   WHERE pid = $sess2_pid}),
+	'1', "the idle session should survive the demote: $sess2_pid" );
+
+# the idle session should report in recovery
+$sess2_out = '';
+$sess2_in = q{
+SELECT pg_is_in_recovery();
+};
+$sess2->pump until $sess2_out =~ qr/(t|f)\s*$/m;
+
+# idle session is not in recovery
+is( $1, 't', 'the idle session reports in recovery' );
+
+# close both sessions
+$sess1_out = $sess2_out = $sess1_in = $sess2_in = '';
+$sess1->finish;
+$sess2->finish;
+
+# Promote alpha back in production.
+$node_alpha->promote;
+
+is( $node_alpha->safe_psql( 'postgres', 'SELECT pg_is_in_recovery()'),
+	'f', "node alpha promoted" );
+
+# Check all 2PC xact have been restored
+is( $node_alpha->safe_psql(
+		'postgres',
+		"SELECT string_agg(gid, ',' order by gid asc) FROM pg_prepared_xacts"),
+	'pxact1,pxact2', "prepared transactions 'pxact1' and 'pxact2' exists" );
+
+# Commit one 2PC and check it on alpha and beta
+$node_alpha->safe_psql( 'postgres', "commit prepared 'pxact1'");
+
+is( $node_alpha->safe_psql(
+		'postgres', "SELECT array_agg(i::text ORDER BY i ASC) FROM new"),
+	'{1,2,3,4,5}', "prepared transaction 'pxact1' commited" );
+
+$node_alpha->wait_for_catchup($node_beta);
+$node_alpha->wait_for_catchup($node_gamma);
+
+is( $node_beta->safe_psql(
+		'postgres', "SELECT array_agg(i::text ORDER BY i ASC) FROM new"),
+	'{1,2,3,4,5}', "prepared transaction 'pxact1' replicated to beta" );
+
+is( $node_gamma->safe_psql(
+		'postgres', "SELECT array_agg(i::text ORDER BY i ASC) FROM new"),
+	'{1,2,3,4,5}', "prepared transaction 'pxact1' replicated to gamma" );
+
+# create another idle in xact session
+$sess1_in = q{
+BEGIN;
+CREATE TABLE public.test_succeed (i int);
+SELECT pg_backend_pid();
+};
+$sess1->pump until $sess1_out =~ qr/\d+\s*$/m;
+$sess1_pid = $sess1_out;
+chomp $sess1_pid;
+
+# swap roles between alpha and beta
+
+# Demote alpha in smart mode.
+# Don't wait for demote to complete here so we can use sess1
+# to keep doing some more write activity before commit and demote.
+is( $node_alpha->safe_psql( 'postgres', 'SELECT pg_demote(false, false)'),
+	't', "demote signal sent to node alpha" );
+
+# wait for the demote to begin and wait for active xact.
+my $fh;
+while (1) {
+	my $status;
+	open my $fh, '<', $node_alpha->data_dir . '/postmaster.pid';
+	$status = $_ while <$fh>;
+	close $fh;
+	chomp($status);
+	last if $status eq 'demoting';
+	sleep 1;
+}
+
+# make sure the demote waits for running xacts
+sleep 2;
+
+# test no new session possible during demote
+$sess2_in = q{
+SELECT 1;
+};
+$sess2->start;
+$sess2->finish;
+ok( $sess2_err =~ /FATAL:  the database system is demoting\s$/, 'session rejected during demote process');
+
+# add some write activity on demote-blocking session sess1
+$sess1_out = '';
+$sess1_in = q{
+INSERT INTO public.test_succeed VALUES (1) RETURNING i;
+COMMIT;
+};
+$sess1->pump until $sess1_out =~ qr/\d+\s*$/m;
+$sess1->finish;
+
+chomp($sess1_out);
+is($sess1_out, '1', 'session in active xact able to write the smart demote signal');
+
+$node_alpha->poll_query_until('postgres', 'SELECT pg_is_in_recovery()', 't');
+
+is( $node_alpha->safe_psql( 'postgres', 'SELECT pg_is_in_recovery()'),
+	't', "node alpha demoted" );
+
+# fetch the last REDO location from alpha and chek beta received everyting
+my ($stdout, $stderr) = run_command([ 'pg_controldata', $node_alpha->data_dir ]);
+$stdout =~ m{REDO location:\s+([0-9A-F]+/[0-9A-F]+)$}mg;
+my $redo_loc = $1;
+
+is( $node_beta->safe_psql(
+		'postgres',
+		"SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), '$redo_loc') > 0 "),
+	't', "node beta received the demote checkpoint from alpha" );
+
+# promote beta and check it
+$node_beta->promote;
+is( $node_beta->safe_psql( 'postgres', 'SELECT pg_is_in_recovery()'),
+	'f', "node beta promoted" );
+
+# Setup alpha to replicate from beta
+$node_alpha->enable_streaming($node_beta);
+$node_alpha->reload;
+
+# check alpha is replicating from it
+$node_beta->wait_for_catchup($node_alpha);
+
+is( $node_beta->safe_psql(
+		'postgres', 'SELECT application_name FROM pg_stat_replication'),
+	$node_alpha->name, 'alpha is replicating from beta' );
+
+# check gamma is still replicating from from alpha
+$node_alpha->wait_for_catchup($node_gamma, 'write', $node_alpha->lsn('receive'));
+
+is( $node_alpha->safe_psql(
+		'postgres', 'SELECT application_name FROM pg_stat_replication'),
+	$node_gamma->name, 'gamma is replicating from beta' );
+
+# make sure the second 2PC is still available on beta
+is( $node_beta->safe_psql(
+		'postgres', 'SELECT gid FROM pg_prepared_xacts'),
+	'pxact2', "prepared transactions pxact2' exists" );
+
+# commit the second 2PC and check its result on alpha and beta nodes
+$node_beta->safe_psql( 'postgres', "commit prepared 'pxact2'");
+
+is( $node_beta->safe_psql( 'postgres', 'SELECT 1 FROM ins WHERE i=2'),
+	'1', "prepared transaction 'pxact2' commited" );
+
+$node_beta->wait_for_catchup($node_alpha);
+is( $node_alpha->safe_psql( 'postgres', 'SELECT 1 FROM ins WHERE i=2'),
+	'1', "prepared transaction 'pxact2' streamed to alpha" );
+
+# check the 2PC has been cascaded to gamma
+$node_alpha->wait_for_catchup($node_gamma, 'write', $node_alpha->lsn('receive'));
+is( $node_gamma->safe_psql( 'postgres', 'SELECT 1 FROM ins WHERE i=2'),
+	'1', "prepared transaction 'pxact2' streamed to gamma" );
-- 
2.20.1


--MP_/Mp45B_GpB5m14pibp/TRwlo--





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* [PATCH v5] Add optional exponential backoff to auth_delay contrib module.
@ 2023-12-27 14:55 Michael Banck <[email protected]>
  0 siblings, 0 replies; 7+ messages in thread

From: Michael Banck @ 2023-12-27 14:55 UTC (permalink / raw)

This adds the new auth_delay.max_milliseconds GUC. If set (its default is 0),
auth_delay adds exponential backoff with this GUC's value as maximum delay.

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 or when
no authentication attempts have been made for 5*max_milliseconds from that
host.

Authors: Michael Banck, based on an earlier patch by 成之焕
Reviewed-by: Abhijit Menon-Sen, Tomas Vondra
Discussion: https://postgr.es/m/AHwAxACqIwIVOEhs5YejpqoG.1.1668569845751.Hmail.zhcheng@ceresdata.com
---
 contrib/auth_delay/auth_delay.c  | 216 ++++++++++++++++++++++++++++++-
 doc/src/sgml/auth-delay.sgml     |  31 ++++-
 src/tools/pgindent/typedefs.list |   1 +
 3 files changed, 244 insertions(+), 4 deletions(-)

diff --git a/contrib/auth_delay/auth_delay.c b/contrib/auth_delay/auth_delay.c
index ff0e1fd461..5fb123d133 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/dsm_registry.h"
+#include "storage/ipc.h"
+#include "storage/lwlock.h"
+#include "storage/shmem.h"
 #include "utils/guc.h"
 #include "utils/timestamp.h"
 
 PG_MODULE_MAGIC;
 
+#define MAX_CONN_RECORDS 100
+
 /* GUC Variables */
 static int	auth_delay_milliseconds = 0;
+static int	auth_delay_max_milliseconds = 0;
 
 /* Original Hook */
 static ClientAuthentication_hook_type original_client_auth_hook = NULL;
 
+typedef struct AuthConnRecord
+{
+	char		remote_host[NI_MAXHOST];
+	double		sleep_time;		/* in milliseconds */
+	TimestampTz last_failed_auth;
+} AuthConnRecord;
+
+static shmem_startup_hook_type shmem_startup_next = NULL;
+static AuthConnRecord *acr_array = NULL;
+
+static AuthConnRecord *auth_delay_find_acr_for_host(char *remote_host);
+static AuthConnRecord *auth_delay_find_free_acr(void);
+static double auth_delay_increase_delay_after_failed_conn_auth(Port *port);
+static void auth_delay_cleanup_conn_record(Port *port);
+static void auth_delay_expire_conn_records(Port *port);
+
 /*
  * Check authentication
  */
 static void
 auth_delay_checks(Port *port, int status)
 {
+	double		delay = auth_delay_milliseconds;
+
 	/*
 	 * Any other plugins which use ClientAuthentication_hook.
 	 */
@@ -39,20 +65,190 @@ auth_delay_checks(Port *port, int status)
 		original_client_auth_hook(port, status);
 
 	/*
-	 * Inject a short delay if authentication failed.
+	 * We handle both STATUS_ERROR and STATUS_OK - the third option
+	 * (STATUS_EOF) is disregarded.
+	 *
+	 * In case of STATUS_ERROR we inject a short delay, optionally with
+	 * exponential backoff.
+	 */
+	if (status == STATUS_ERROR)
+	{
+		if (auth_delay_max_milliseconds > 0)
+		{
+			/*
+			 * Delay by 2^n seconds after each authentication failure from a
+			 * particular host, where n is the number of consecutive
+			 * authentication failures.
+			 */
+			delay = auth_delay_increase_delay_after_failed_conn_auth(port);
+
+			/*
+			 * Clamp delay to a maximum of auth_delay_max_milliseconds.
+			 */
+			delay = Min(delay, auth_delay_max_milliseconds);
+		}
+
+		if (delay > 0)
+		{
+			elog(DEBUG1, "Authentication delayed for %g seconds due to auth_delay", delay / 1000.0);
+			pg_usleep(1000L * (long) delay);
+		}
+
+		/*
+		 * Expire delays from other hosts after auth_delay_max_milliseconds *
+		 * 5.
+		 */
+		auth_delay_expire_conn_records(port);
+	}
+
+	/*
+	 * Remove host-specific delay if authentication succeeded.
+	 */
+	if (status == STATUS_OK)
+		auth_delay_cleanup_conn_record(port);
+}
+
+static double
+auth_delay_increase_delay_after_failed_conn_auth(Port *port)
+{
+	AuthConnRecord *acr = NULL;
+
+	acr = auth_delay_find_acr_for_host(port->remote_host);
+
+	if (!acr)
+	{
+		acr = auth_delay_find_free_acr();
+
+		if (!acr)
+		{
+			/*
+			 * No free space, MAX_CONN_RECORDS reached. Wait for the
+			 * configured maximum amount.
+			 */
+			elog(LOG, "auth_delay: host connection list full, waiting maximum amount");
+			return auth_delay_max_milliseconds;
+		}
+		strcpy(acr->remote_host, port->remote_host);
+	}
+	if (acr->sleep_time == 0)
+		acr->sleep_time = (double) auth_delay_milliseconds;
+	else
+		acr->sleep_time *= 2;
+
+	/*
+	 * Set current timestamp for later expiry.
 	 */
-	if (status != STATUS_OK)
+	acr->last_failed_auth = GetCurrentTimestamp();
+
+	return acr->sleep_time;
+}
+
+static AuthConnRecord *
+auth_delay_find_acr_for_host(char *remote_host)
+{
+	int			i;
+
+	for (i = 0; i < MAX_CONN_RECORDS; i++)
+	{
+		if (strcmp(acr_array[i].remote_host, remote_host) == 0)
+			return &acr_array[i];
+	}
+
+	return NULL;
+}
+
+static AuthConnRecord *
+auth_delay_find_free_acr(void)
+{
+	int			i;
+
+	for (i = 0; i < MAX_CONN_RECORDS; i++)
+	{
+		if (!acr_array[i].remote_host[0])
+			return &acr_array[i];
+	}
+
+	return 0;
+}
+
+static void
+auth_delay_cleanup_conn_record(Port *port)
+{
+	AuthConnRecord *acr = NULL;
+
+	acr = auth_delay_find_acr_for_host(port->remote_host);
+	if (acr == NULL)
+		return;
+
+	port->remote_host[0] = '\0';
+
+	acr->sleep_time = 0.0;
+	acr->last_failed_auth = 0.0;
+}
+
+static void
+auth_delay_expire_conn_records(Port *port)
+{
+	int			i;
+	TimestampTz now = GetCurrentTimestamp();
+
+	for (i = 0; i < MAX_CONN_RECORDS; i++)
 	{
-		pg_usleep(1000L * auth_delay_milliseconds);
+		/*
+		 * Do not expire the host from which the current authentication
+		 * failure originated.
+		 */
+		if (strcmp(acr_array[i].remote_host, port->remote_host) == 0)
+			continue;
+
+		if (acr_array[i].last_failed_auth > 0 && (long) ((now - acr_array[i].last_failed_auth) / 1000) > 5 * auth_delay_max_milliseconds)
+		{
+			acr_array[i].remote_host[0] = '\0';
+			acr_array[i].sleep_time = 0.0;
+			acr_array[i].last_failed_auth = 0.0;
+		}
 	}
 }
 
+/*
+ * Set up shared memory
+ */
+
+static void
+auth_delay_init_state(void *ptr)
+{
+	Size		shm_size;
+	AuthConnRecord *array = (AuthConnRecord *) ptr;
+
+	shm_size = sizeof(AuthConnRecord) * MAX_CONN_RECORDS;
+
+	memset(array, 0, shm_size);
+}
+
+static void
+auth_delay_shmem_startup(void)
+{
+	bool		found;
+	Size		shm_size;
+
+	if (shmem_startup_next)
+		shmem_startup_next();
+
+	shm_size = sizeof(AuthConnRecord) * MAX_CONN_RECORDS;
+	acr_array = GetNamedDSMSegment("auth_delay", shm_size, auth_delay_init_state, &found);
+}
+
 /*
  * Module Load Callback
  */
 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 +262,23 @@ _PG_init(void)
 							NULL,
 							NULL);
 
+	DefineCustomIntVariable("auth_delay.max_milliseconds",
+							"Maximum delay for exponential backoff",
+							NULL,
+							&auth_delay_max_milliseconds,
+							0,
+							0, INT_MAX / 1000,
+							PGC_SIGHUP,
+							GUC_UNIT_MS,
+							NULL, NULL, NULL);
+
 	MarkGUCPrefixReserved("auth_delay");
 
 	/* Install Hooks */
 	original_client_auth_hook = ClientAuthentication_hook;
 	ClientAuthentication_hook = auth_delay_checks;
+
+	/* Set up shared memory */
+	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..e3c182cd45 100644
--- a/doc/src/sgml/auth-delay.sgml
+++ b/doc/src/sgml/auth-delay.sgml
@@ -16,6 +16,19 @@
   connection slots.
  </para>
 
+ <para>
+  It is optionally possible to let <filename>auth_delay</filename> wait longer
+  on each successive authentication failure if the configuration parameter
+  <varname>auth_delay.max_milliseconds</varname> is set.  In this case,
+  <filename>auth_delay</filename> will start with a delay of
+  <varname>auth_delay.milliseconds</varname> and double the delay after each
+  consecutive authentication failure from a particular host, up to the given
+  <varname>auth_delay.max_milliseconds</varname>. If the host authenticates
+  successfully or after a timeout of five times
+  <varname>auth_delay.max_milliseconds</varname>, the delay is reset to
+  <varname>auth_delay.milliseconds</varname>.
+ </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 +52,21 @@
      </para>
     </listitem>
    </varlistentry>
+   <varlistentry>
+    <term>
+     <varname>auth_delay.max_milliseconds</varname> (<type>integer</type>)
+     <indexterm>
+      <primary><varname>auth_delay.max_milliseconds</varname> configuration parameter</primary>
+     </indexterm>
+    </term>
+    <listitem>
+     <para>
+      The maximum delay in milliseconds, implying exponential backoff between
+      each successive failed attempt.  The default is 0, meaning exponential
+      backoff is not active.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
 
   <para>
@@ -50,7 +78,8 @@
 # postgresql.conf
 shared_preload_libraries = 'auth_delay'
 
-auth_delay.milliseconds = '500'
+auth_delay.milliseconds = '125'
+auth_delay.max_milliseconds = '20000'
 </programlisting>
  </sect2>
 
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 95ae7845d8..dd9fb9e530 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -166,6 +166,7 @@ AttrMap
 AttrMissing
 AttrNumber
 AttributeOpts
+AuthConnRecord
 AuthRequest
 AuthToken
 AutoPrewarmSharedState
-- 
2.39.2


--hoZxPH4CaxYzWscb--





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* [PATCH v3] Add optional exponential backoff to auth_delay contrib module.
@ 2023-12-27 14:55 Michael Banck <[email protected]>
  0 siblings, 0 replies; 7+ messages in thread

From: Michael Banck @ 2023-12-27 14:55 UTC (permalink / raw)

This adds two new GUCs for auth_delay, exponential_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, 成之焕
Reviewed-by: Abhijit Menon-Sen
Discussion: https://postgr.es/m/AHwAxACqIwIVOEhs5YejpqoG.1.1668569845751.Hmail.zhcheng@ceresdata.com
---
 contrib/auth_delay/auth_delay.c  | 197 ++++++++++++++++++++++++++++++-
 doc/src/sgml/auth-delay.sgml     |  48 +++++++-
 src/tools/pgindent/typedefs.list |   1 +
 3 files changed, 243 insertions(+), 3 deletions(-)

diff --git a/contrib/auth_delay/auth_delay.c b/contrib/auth_delay/auth_delay.c
index ff0e1fd461..06d2f5f280 100644
--- a/contrib/auth_delay/auth_delay.c
+++ b/contrib/auth_delay/auth_delay.c
@@ -14,24 +14,49 @@
 #include <limits.h>
 
 #include "libpq/auth.h"
+#include "miscadmin.h"
 #include "port.h"
+#include "storage/ipc.h"
+#include "storage/lwlock.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];
+	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_acr_for_host(char *remote_host);
+static AuthConnRecord *find_free_acr(void);
+static double increase_delay_after_failed_conn_auth(Port *port);
+static void cleanup_conn_record(Port *port);
+
 /*
  * Check authentication
  */
 static void
 auth_delay_checks(Port *port, int status)
 {
+	double		delay = auth_delay_milliseconds;
+
 	/*
 	 * Any other plugins which use ClientAuthentication_hook.
 	 */
@@ -41,10 +66,146 @@ auth_delay_checks(Port *port, int status)
 	/*
 	 * Inject a short delay if authentication failed.
 	 */
-	if (status != STATUS_OK)
+	if (status == STATUS_ERROR)
 	{
-		pg_usleep(1000L * auth_delay_milliseconds);
+		if (auth_delay_exp_backoff)
+		{
+			/*
+			 * Delay by 2^n seconds after each authentication failure from a
+			 * particular host, where n is the number of consecutive
+			 * authentication failures.
+			 */
+			delay = increase_delay_after_failed_conn_auth(port);
+
+			/*
+			 * Clamp delay to a maximum of auth_delay_max_seconds.
+			 */
+			if (auth_delay_max_seconds > 0) {
+				delay = Min(delay, 1000L * auth_delay_max_seconds);
+			}
+		}
+
+		if (delay > 0)
+		{
+			elog(DEBUG1, "Authentication delayed for %g seconds due to auth_delay", delay / 1000.0);
+			pg_usleep(1000L * (long) delay);
+		}
 	}
+
+	/*
+	 * Remove host-specific delay if authentication succeeded.
+	 */
+	if (status == STATUS_OK)
+		cleanup_conn_record(port);
+}
+
+static double
+increase_delay_after_failed_conn_auth(Port *port)
+{
+	AuthConnRecord *acr = NULL;
+
+	acr = find_acr_for_host(port->remote_host);
+
+	if (!acr)
+	{
+		acr = find_free_acr();
+
+		if (!acr)
+		{
+			/*
+			 * No free space, MAX_CONN_RECORDS reached. Wait for the
+			 * configured maximum amount.
+			 */
+			return 1000L * auth_delay_max_seconds;
+		}
+		strcpy(acr->remote_host, port->remote_host);
+	}
+	if (acr->sleep_time == 0)
+		acr->sleep_time = (double) auth_delay_milliseconds;
+	else
+		acr->sleep_time *= 2;
+
+	return acr->sleep_time;
+}
+
+static AuthConnRecord *
+find_acr_for_host(char *remote_host)
+{
+	int			i;
+
+	for (i = 0; i < MAX_CONN_RECORDS; i++)
+	{
+		if (strcmp(acr_array[i].remote_host, remote_host) == 0)
+			return &acr_array[i];
+	}
+
+	return NULL;
+}
+
+static AuthConnRecord *
+find_free_acr(void)
+{
+	int			i;
+
+	for (i = 0; i < MAX_CONN_RECORDS; i++)
+	{
+		if (!acr_array[i].remote_host[0])
+			return &acr_array[i];
+	}
+
+	return 0;
+}
+
+static void
+cleanup_conn_record(Port *port)
+{
+	AuthConnRecord *acr = NULL;
+
+	acr = find_acr_for_host(port->remote_host);
+	if (acr == NULL)
+		return;
+
+	port->remote_host[0] = '\0';
+
+	acr->sleep_time = 0.0;
+}
+
+/*
+ * Set up shared memory
+ */
+
+static void
+auth_delay_shmem_request(void)
+{
+	Size		shm_size;
+
+	if (shmem_request_next)
+		shmem_request_next();
+
+	shm_size = sizeof(AuthConnRecord) * MAX_CONN_RECORDS;
+	shm_size += sizeof(int);
+	RequestAddinShmemSpace(shm_size);
+}
+
+static void
+auth_delay_shmem_startup(void)
+{
+	bool		found;
+	Size		shm_size;
+
+	if (shmem_startup_next)
+		shmem_startup_next();
+
+	shm_size = sizeof(AuthConnRecord) * MAX_CONN_RECORDS;
+
+	LWLockAcquire(AddinShmemInitLock, LW_EXCLUSIVE);
+	acr_array = ShmemInitStruct("Array of AuthConnRecord", shm_size, &found);
+	if (!found)
+	{
+		/* First time through ... */
+		memset(acr_array, 0, shm_size);
+	}
+	LWLockRelease(AddinShmemInitLock);
 }
 
 /*
@@ -53,6 +214,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 +232,36 @@ _PG_init(void)
 							NULL,
 							NULL);
 
+	DefineCustomBoolVariable("auth_delay.exponential_backoff",
+							 "Double the delay after each authentication failure from a particular host",
+							 NULL,
+							 &auth_delay_exp_backoff,
+							 false,
+							 PGC_SIGHUP,
+							 0,
+							 NULL,
+							 NULL,
+							 NULL);
+
+	DefineCustomIntVariable("auth_delay.max_seconds",
+							"Maximum delay when exponential backoff is enabled",
+							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..89585ea3c8 100644
--- a/doc/src/sgml/auth-delay.sgml
+++ b/doc/src/sgml/auth-delay.sgml
@@ -16,6 +16,22 @@
   connection slots.
  </para>
 
+ <para>
+  It is optionally possible to let <filename>auth_delay</filename> wait longer
+  on each successive authentication failure if the configuration parameter
+  <varname>auth_delay.exponential_backoff</varname> is active.  If enabled,
+  <filename>auth_delay</filename> will double the delay after each consecutive
+  authentication failure from a particular host, up to the given
+  <varname>auth_delay.max_seconds</varname> (default: 10s). If the host
+  authenticates successfully, the delay is reset.
+ </para>
+
+ <para>
+  In this case, it might be desirable to decrease the configuration parameter
+  <varname>auth_delay.milliseconds</varname> from the default of 1 second in
+  order to not delay as long for single accidental authentication failures.
+ </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 +55,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>
+      The maximum delay, in seconds, when exponential backoff is
+      enabled.  The default is 10 seconds.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
 
   <para>
@@ -50,7 +94,9 @@
 # postgresql.conf
 shared_preload_libraries = 'auth_delay'
 
-auth_delay.milliseconds = '500'
+auth_delay.milliseconds = '125'
+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 29fd1cae64..ee30969f0c 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -165,6 +165,7 @@ AttrMap
 AttrMissing
 AttrNumber
 AttributeOpts
+AuthConnRecord
 AuthRequest
 AuthToken
 AutoPrewarmSharedState
-- 
2.39.2


--KFztAG8eRSV9hGtP--





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* [PATCH v2] Add optional exponential backoff to auth_delay contrib module.
@ 2023-12-27 14:55 Michael Banck <[email protected]>
  0 siblings, 0 replies; 7+ messages in thread

From: Michael Banck @ 2023-12-27 14:55 UTC (permalink / raw)

This adds two new GUCs for auth_delay, exponential_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, 成之焕
Reviewed-by: Abhijit Menon-Sen
Discussion: https://postgr.es/m/AHwAxACqIwIVOEhs5YejpqoG.1.1668569845751.Hmail.zhcheng@ceresdata.com
---
 contrib/auth_delay/auth_delay.c  | 197 ++++++++++++++++++++++++++++++-
 doc/src/sgml/auth-delay.sgml     |  48 +++++++-
 src/tools/pgindent/typedefs.list |   1 +
 3 files changed, 243 insertions(+), 3 deletions(-)

diff --git a/contrib/auth_delay/auth_delay.c b/contrib/auth_delay/auth_delay.c
index ff0e1fd461..06d2f5f280 100644
--- a/contrib/auth_delay/auth_delay.c
+++ b/contrib/auth_delay/auth_delay.c
@@ -14,24 +14,49 @@
 #include <limits.h>
 
 #include "libpq/auth.h"
+#include "miscadmin.h"
 #include "port.h"
+#include "storage/ipc.h"
+#include "storage/lwlock.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];
+	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_acr_for_host(char *remote_host);
+static AuthConnRecord *find_free_acr(void);
+static double increase_delay_after_failed_conn_auth(Port *port);
+static void cleanup_conn_record(Port *port);
+
 /*
  * Check authentication
  */
 static void
 auth_delay_checks(Port *port, int status)
 {
+	double		delay = auth_delay_milliseconds;
+
 	/*
 	 * Any other plugins which use ClientAuthentication_hook.
 	 */
@@ -41,10 +66,146 @@ auth_delay_checks(Port *port, int status)
 	/*
 	 * Inject a short delay if authentication failed.
 	 */
-	if (status != STATUS_OK)
+	if (status == STATUS_ERROR)
 	{
-		pg_usleep(1000L * auth_delay_milliseconds);
+		if (auth_delay_exp_backoff)
+		{
+			/*
+			 * Delay by 2^n seconds after each authentication failure from a
+			 * particular host, where n is the number of consecutive
+			 * authentication failures.
+			 */
+			delay = increase_delay_after_failed_conn_auth(port);
+
+			/*
+			 * Clamp delay to a maximum of auth_delay_max_seconds.
+			 */
+			if (auth_delay_max_seconds > 0) {
+				delay = Min(delay, 1000L * auth_delay_max_seconds);
+			}
+		}
+
+		if (delay > 0)
+		{
+			elog(DEBUG1, "Authentication delayed for %g seconds due to auth_delay", delay / 1000.0);
+			pg_usleep(1000L * (long) delay);
+		}
 	}
+
+	/*
+	 * Remove host-specific delay if authentication succeeded.
+	 */
+	if (status == STATUS_OK)
+		cleanup_conn_record(port);
+}
+
+static double
+increase_delay_after_failed_conn_auth(Port *port)
+{
+	AuthConnRecord *acr = NULL;
+
+	acr = find_acr_for_host(port->remote_host);
+
+	if (!acr)
+	{
+		acr = find_free_acr();
+
+		if (!acr)
+		{
+			/*
+			 * No free space, MAX_CONN_RECORDS reached. Wait for the
+			 * configured maximum amount.
+			 */
+			return 1000L * auth_delay_max_seconds;
+		}
+		strcpy(acr->remote_host, port->remote_host);
+	}
+	if (acr->sleep_time == 0)
+		acr->sleep_time = (double) auth_delay_milliseconds;
+	else
+		acr->sleep_time *= 2;
+
+	return acr->sleep_time;
+}
+
+static AuthConnRecord *
+find_acr_for_host(char *remote_host)
+{
+	int			i;
+
+	for (i = 0; i < MAX_CONN_RECORDS; i++)
+	{
+		if (strcmp(acr_array[i].remote_host, remote_host) == 0)
+			return &acr_array[i];
+	}
+
+	return NULL;
+}
+
+static AuthConnRecord *
+find_free_acr(void)
+{
+	int			i;
+
+	for (i = 0; i < MAX_CONN_RECORDS; i++)
+	{
+		if (!acr_array[i].remote_host[0])
+			return &acr_array[i];
+	}
+
+	return 0;
+}
+
+static void
+cleanup_conn_record(Port *port)
+{
+	AuthConnRecord *acr = NULL;
+
+	acr = find_acr_for_host(port->remote_host);
+	if (acr == NULL)
+		return;
+
+	port->remote_host[0] = '\0';
+
+	acr->sleep_time = 0.0;
+}
+
+/*
+ * Set up shared memory
+ */
+
+static void
+auth_delay_shmem_request(void)
+{
+	Size		shm_size;
+
+	if (shmem_request_next)
+		shmem_request_next();
+
+	shm_size = sizeof(AuthConnRecord) * MAX_CONN_RECORDS;
+	shm_size += sizeof(int);
+	RequestAddinShmemSpace(shm_size);
+}
+
+static void
+auth_delay_shmem_startup(void)
+{
+	bool		found;
+	Size		shm_size;
+
+	if (shmem_startup_next)
+		shmem_startup_next();
+
+	shm_size = sizeof(AuthConnRecord) * MAX_CONN_RECORDS;
+
+	LWLockAcquire(AddinShmemInitLock, LW_EXCLUSIVE);
+	acr_array = ShmemInitStruct("Array of AuthConnRecord", shm_size, &found);
+	if (!found)
+	{
+		/* First time through ... */
+		memset(acr_array, 0, shm_size);
+	}
+	LWLockRelease(AddinShmemInitLock);
 }
 
 /*
@@ -53,6 +214,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 +232,36 @@ _PG_init(void)
 							NULL,
 							NULL);
 
+	DefineCustomBoolVariable("auth_delay.exponential_backoff",
+							 "Double the delay after each authentication failure from a particular host",
+							 NULL,
+							 &auth_delay_exp_backoff,
+							 false,
+							 PGC_SIGHUP,
+							 0,
+							 NULL,
+							 NULL,
+							 NULL);
+
+	DefineCustomIntVariable("auth_delay.max_seconds",
+							"Maximum delay when exponential backoff is enabled",
+							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..89585ea3c8 100644
--- a/doc/src/sgml/auth-delay.sgml
+++ b/doc/src/sgml/auth-delay.sgml
@@ -16,6 +16,22 @@
   connection slots.
  </para>
 
+ <para>
+  It is optionally possible to let <filename>auth_delay</filename> wait longer
+  on each successive authentication failure if the configuration parameter
+  <varname>auth_delay.exponential_backoff</varname> is active.  If enabled,
+  <filename>auth_delay</filename> will double the delay after each consecutive
+  authentication failure from a particular host, up to the given
+  <varname>auth_delay.max_seconds</varname> (default: 10s). If the host
+  authenticates successfully, the delay is reset.
+ </para>
+
+ <para>
+  In this case, it might be desirable to decrease the configuration parameter
+  <varname>auth_delay.milliseconds</varname> from the default of 1 second in
+  order to not delay as long for single accidental authentication failures.
+ </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 +55,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>
+      The maximum delay, in seconds, when exponential backoff is
+      enabled.  The default is 10 seconds.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
 
   <para>
@@ -50,7 +94,9 @@
 # postgresql.conf
 shared_preload_libraries = 'auth_delay'
 
-auth_delay.milliseconds = '500'
+auth_delay.milliseconds = '125'
+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 29fd1cae64..ee30969f0c 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -165,6 +165,7 @@ AttrMap
 AttrMissing
 AttrNumber
 AttributeOpts
+AuthConnRecord
 AuthRequest
 AuthToken
 AutoPrewarmSharedState
-- 
2.39.2


--ikeVEW9yuYc//A+q--





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* [PATCH v4] Add optional exponential backoff to auth_delay contrib module.
@ 2023-12-27 14:55 Michael Banck <[email protected]>
  0 siblings, 0 replies; 7+ messages in thread

From: Michael Banck @ 2023-12-27 14:55 UTC (permalink / raw)

This adds two new GUCs for auth_delay, exponential_backoff and
max_milliseconds. 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 or when
no authentication attempts have been made for 5*max_milliseconds from that
host.

Authors: Michael Banck, based on an earlier patch by 成之焕
Reviewed-by: Abhijit Menon-Sen, Tomas Vondra
Discussion: https://postgr.es/m/AHwAxACqIwIVOEhs5YejpqoG.1.1668569845751.Hmail.zhcheng@ceresdata.com
---
 contrib/auth_delay/auth_delay.c  | 231 ++++++++++++++++++++++++++++++-
 doc/src/sgml/auth-delay.sgml     |  45 +++++-
 src/tools/pgindent/typedefs.list |   1 +
 3 files changed, 273 insertions(+), 4 deletions(-)

diff --git a/contrib/auth_delay/auth_delay.c b/contrib/auth_delay/auth_delay.c
index ff0e1fd461..647aa4e1cd 100644
--- a/contrib/auth_delay/auth_delay.c
+++ b/contrib/auth_delay/auth_delay.c
@@ -14,24 +14,51 @@
 #include <limits.h>
 
 #include "libpq/auth.h"
+#include "miscadmin.h"
 #include "port.h"
+#include "storage/dsm_registry.h"
+#include "storage/ipc.h"
+#include "storage/lwlock.h"
+#include "storage/shmem.h"
 #include "utils/guc.h"
 #include "utils/timestamp.h"
 
 PG_MODULE_MAGIC;
 
+#define MAX_CONN_RECORDS 100
+
 /* GUC Variables */
 static int	auth_delay_milliseconds = 0;
+static bool auth_delay_exp_backoff = false;
+static int	auth_delay_max_milliseconds = 0;
 
 /* Original Hook */
 static ClientAuthentication_hook_type original_client_auth_hook = NULL;
 
+typedef struct AuthConnRecord
+{
+	char		remote_host[NI_MAXHOST];
+	double		sleep_time;		/* in milliseconds */
+	TimestampTz last_failed_auth;
+} AuthConnRecord;
+
+static shmem_startup_hook_type shmem_startup_next = NULL;
+static AuthConnRecord *acr_array = NULL;
+
+static AuthConnRecord *auth_delay_find_acr_for_host(char *remote_host);
+static AuthConnRecord *auth_delay_find_free_acr(void);
+static double auth_delay_increase_delay_after_failed_conn_auth(Port *port);
+static void auth_delay_cleanup_conn_record(Port *port);
+static void auth_delay_expire_conn_records(Port *port);
+
 /*
  * Check authentication
  */
 static void
 auth_delay_checks(Port *port, int status)
 {
+	double		delay = auth_delay_milliseconds;
+
 	/*
 	 * Any other plugins which use ClientAuthentication_hook.
 	 */
@@ -39,20 +66,193 @@ auth_delay_checks(Port *port, int status)
 		original_client_auth_hook(port, status);
 
 	/*
-	 * Inject a short delay if authentication failed.
+	 * We handle both STATUS_ERROR and STATUS_OK - the third option
+	 * (STATUS_EOF) is disregarded.
+	 *
+	 * In case of STATUS_ERROR we inject a short delay, optionally with
+	 * exponential backoff.
+	 */
+	if (status == STATUS_ERROR)
+	{
+		if (auth_delay_exp_backoff)
+		{
+			/*
+			 * Delay by 2^n seconds after each authentication failure from a
+			 * particular host, where n is the number of consecutive
+			 * authentication failures.
+			 */
+			delay = auth_delay_increase_delay_after_failed_conn_auth(port);
+
+			/*
+			 * Clamp delay to a maximum of auth_delay_max_milliseconds.
+			 */
+			if (auth_delay_max_milliseconds > 0)
+			{
+				delay = Min(delay, auth_delay_max_milliseconds);
+			}
+		}
+
+		if (delay > 0)
+		{
+			elog(DEBUG1, "Authentication delayed for %g seconds due to auth_delay", delay / 1000.0);
+			pg_usleep(1000L * (long) delay);
+		}
+
+		/*
+		 * Expire delays from other hosts after auth_delay_max_milliseconds *
+		 * 5.
+		 */
+		auth_delay_expire_conn_records(port);
+	}
+
+	/*
+	 * Remove host-specific delay if authentication succeeded.
+	 */
+	if (status == STATUS_OK)
+		auth_delay_cleanup_conn_record(port);
+}
+
+static double
+auth_delay_increase_delay_after_failed_conn_auth(Port *port)
+{
+	AuthConnRecord *acr = NULL;
+
+	acr = auth_delay_find_acr_for_host(port->remote_host);
+
+	if (!acr)
+	{
+		acr = auth_delay_find_free_acr();
+
+		if (!acr)
+		{
+			/*
+			 * No free space, MAX_CONN_RECORDS reached. Wait for the
+			 * configured maximum amount.
+			 */
+			elog(LOG, "auth_delay: host connection list full, waiting maximum amount");
+			return auth_delay_max_milliseconds;
+		}
+		strcpy(acr->remote_host, port->remote_host);
+	}
+	if (acr->sleep_time == 0)
+		acr->sleep_time = (double) auth_delay_milliseconds;
+	else
+		acr->sleep_time *= 2;
+
+	/*
+	 * Set current timestamp for later expiry.
 	 */
-	if (status != STATUS_OK)
+	acr->last_failed_auth = GetCurrentTimestamp();
+
+	return acr->sleep_time;
+}
+
+static AuthConnRecord *
+auth_delay_find_acr_for_host(char *remote_host)
+{
+	int			i;
+
+	for (i = 0; i < MAX_CONN_RECORDS; i++)
+	{
+		if (strcmp(acr_array[i].remote_host, remote_host) == 0)
+			return &acr_array[i];
+	}
+
+	return NULL;
+}
+
+static AuthConnRecord *
+auth_delay_find_free_acr(void)
+{
+	int			i;
+
+	for (i = 0; i < MAX_CONN_RECORDS; i++)
+	{
+		if (!acr_array[i].remote_host[0])
+			return &acr_array[i];
+	}
+
+	return 0;
+}
+
+static void
+auth_delay_cleanup_conn_record(Port *port)
+{
+	AuthConnRecord *acr = NULL;
+
+	acr = auth_delay_find_acr_for_host(port->remote_host);
+	if (acr == NULL)
+		return;
+
+	port->remote_host[0] = '\0';
+
+	acr->sleep_time = 0.0;
+	acr->last_failed_auth = 0.0;
+}
+
+static void
+auth_delay_expire_conn_records(Port *port)
+{
+	int			i;
+	TimestampTz now = GetCurrentTimestamp();
+
+	for (i = 0; i < MAX_CONN_RECORDS; i++)
 	{
-		pg_usleep(1000L * auth_delay_milliseconds);
+		/*
+		 * Do not expire the host from which the current authentication
+		 * failure originated.
+		 */
+		if (strcmp(acr_array[i].remote_host, port->remote_host) == 0)
+			continue;
+
+		if (acr_array[i].last_failed_auth > 0 && (long) ((now - acr_array[i].last_failed_auth) / 1000) > 5 * auth_delay_max_milliseconds)
+		{
+			acr_array[i].remote_host[0] = '\0';
+			acr_array[i].sleep_time = 0.0;
+			acr_array[i].last_failed_auth = 0.0;
+		}
 	}
 }
 
+/*
+ * Set up shared memory
+ */
+
+static void
+auth_delay_init_state(void *ptr)
+{
+	Size		shm_size;
+	AuthConnRecord *array = (AuthConnRecord *) ptr;
+
+	shm_size = sizeof(AuthConnRecord) * MAX_CONN_RECORDS;
+
+	memset(array, 0, shm_size);
+}
+
+static void
+auth_delay_shmem_startup(void)
+{
+	bool		found;
+	Size		shm_size;
+
+	if (shmem_startup_next)
+		shmem_startup_next();
+
+	shm_size = sizeof(AuthConnRecord) * MAX_CONN_RECORDS;
+	acr_array = GetNamedDSMSegment("auth_delay", shm_size, auth_delay_init_state, &found);
+}
+
 /*
  * Module Load Callback
  */
 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 +266,34 @@ _PG_init(void)
 							NULL,
 							NULL);
 
+	DefineCustomBoolVariable("auth_delay.exponential_backoff",
+							 "Double the delay after each authentication failure from a particular host",
+							 NULL,
+							 &auth_delay_exp_backoff,
+							 false,
+							 PGC_SIGHUP,
+							 0,
+							 NULL,
+							 NULL,
+							 NULL);
+
+	DefineCustomIntVariable("auth_delay.max_milliseconds",
+							"Maximum delay when exponential backoff is enabled",
+							NULL,
+							&auth_delay_max_milliseconds,
+							10000,
+							0, INT_MAX / 1000,
+							PGC_SIGHUP,
+							GUC_UNIT_MS,
+							NULL, NULL, NULL);
+
 	MarkGUCPrefixReserved("auth_delay");
 
 	/* Install Hooks */
 	original_client_auth_hook = ClientAuthentication_hook;
 	ClientAuthentication_hook = auth_delay_checks;
+
+	/* Set up shared memory */
+	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..b02b6b0b30 100644
--- a/doc/src/sgml/auth-delay.sgml
+++ b/doc/src/sgml/auth-delay.sgml
@@ -16,6 +16,18 @@
   connection slots.
  </para>
 
+ <para>
+  It is optionally possible to let <filename>auth_delay</filename> wait longer
+  on each successive authentication failure if the configuration parameter
+  <varname>auth_delay.exponential_backoff</varname> is active.  If enabled,
+  <filename>auth_delay</filename> will start with a delay of
+  <varname>auth_delay.milliseconds</varname> and double the delay after each
+  consecutive authentication failure from a particular host, up to the given
+  <varname>auth_delay.max_milliseconds</varname> (default: 10s). If the host
+  authenticates successfully or after a timeout of five times
+  <varname>auth_delay.max_milliseconds</varname>, the delay is reset.
+ </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 +51,35 @@
      </para>
     </listitem>
    </varlistentry>
+   <varlistentry>
+    <term>
+     <varname>auth_delay.exponential_backoff</varname> (<type>bool</type>)
+     <indexterm>
+      <primary><varname>auth_delay.exponential_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_milliseconds</varname> (<type>integer</type>)
+     <indexterm>
+      <primary><varname>auth_delay.max_milliseconds</varname> configuration parameter</primary>
+     </indexterm>
+    </term>
+    <listitem>
+     <para>
+      The maximum delay, in milliseconds, when exponential backoff is
+      enabled.  The default is 10 seconds.  If set to 0, authentication delays
+      will increase without limit.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
 
   <para>
@@ -50,7 +91,9 @@
 # postgresql.conf
 shared_preload_libraries = 'auth_delay'
 
-auth_delay.milliseconds = '500'
+auth_delay.milliseconds = '125'
+auth_delay.exponential_backoff = 'on'
+auth_delay.max_milliseconds = '20000'
 </programlisting>
  </sect2>
 
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 95ae7845d8..dd9fb9e530 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -166,6 +166,7 @@ AttrMap
 AttrMissing
 AttrNumber
 AttributeOpts
+AuthConnRecord
 AuthRequest
 AuthToken
 AutoPrewarmSharedState
-- 
2.39.2


--iFRdW5/EC4oqxDHL--





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* [PATCH v1] Add optional exponential backoff to auth_delay contrib module.
@ 2023-12-27 14:55 Michael Banck <[email protected]>
  0 siblings, 0 replies; 7+ messages in thread

From: Michael Banck @ 2023-12-27 14:55 UTC (permalink / raw)

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--





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* [PATCH v2] Add optional exponential backoff to auth_delay contrib module.
@ 2023-12-27 14:55 Michael Banck <[email protected]>
  0 siblings, 0 replies; 7+ messages in thread

From: Michael Banck @ 2023-12-27 14:55 UTC (permalink / raw)

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..2ca9528011 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 parameter
+  <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


--uQr8t48UFsdbeI+V--





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

end of thread, other threads:[~2023-12-27 14:55 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-10 00:00 [PATCH 4/4] demote: add various tests related to demote and promote actions Jehan-Guillaume de Rorthais <[email protected]>
2023-12-27 14:55 [PATCH v5] Add optional exponential backoff to auth_delay contrib module. Michael Banck <[email protected]>
2023-12-27 14:55 [PATCH v3] Add optional exponential backoff to auth_delay contrib module. Michael Banck <[email protected]>
2023-12-27 14:55 [PATCH v2] Add optional exponential backoff to auth_delay contrib module. Michael Banck <[email protected]>
2023-12-27 14:55 [PATCH v4] Add optional exponential backoff to auth_delay contrib module. Michael Banck <[email protected]>
2023-12-27 14:55 [PATCH v1] Add optional exponential backoff to auth_delay contrib module. Michael Banck <[email protected]>
2023-12-27 14:55 [PATCH v2] Add optional exponential backoff to auth_delay contrib module. Michael Banck <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox