This patch introduces a mechanism to address the security issue of stale,
authorized connections persisting beyond their validity period. .
Currently, once a session is established, postgres does not automatically
re-validate credentials. If a password expires (rolvaliduntil) the session
remains active indefinitely. Same applies to centralized authentication
systems (like Kerberos or OAuth).
This patch depends on the "GoAway" protocol message proposal currently
under review here:
https://www.postgresql.org/message-id/DDPQ1RV5FE9U.I2WW34NGRD8Z%40jeltef.nl
Please apply this patch on top of the GoAway patch.
The Solution: To handle this authorization gap gracefully, this patch
leverages the pending GoAway protocol message to notify clients.
Please find below summary of the solution
New GUC: auth_expiration_check_interval (integer, minutes). Controls the
frequency of checking a session's authorization status. Setting it to 0
(default) disables the check.
Periodic Idle Check: When a backend process is idle (waiting for the next
command) and the timeout is reached, the server calls a placeholder
function check_external_auth_status_expired().
Graceful Disconnect: If authorization is revoked/expired, the server sends
the GoAway message. This allows the client to finish any current
processing and reconnect cleanly.
Thanks & Best Regards,
Ajit Awekar
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 7a59e65f2d3..e05fa76f108 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -115,6 +115,8 @@ int client_connection_check_interval = 0;
/* flags for non-system relation kinds to restrict use */
int restrict_nonsystem_relation_kind;
+int auth_expiration_check_interval = 0;
+
/* ----------------
* private typedefs etc
* ----------------
@@ -4213,6 +4215,45 @@ PostgresSingleUserMain(int argc, char *argv[],
PostgresMain(dbname, username);
}
+/*
+ * check_auth_status_expired
+ *
+ * This function is a placeholder for the actual logic to determine if the
+ * user's authorization has expired or been revoked since the connection
+ * was established.
+ */
+static bool
+check_auth_status_expired(UserAuth auth_method)
+{
+ return false; /* Placeholder: Always returns false until implemented */
+}
+
+/*
+ * CheckSessionAuthorizationExpired
+ *
+ * Performs a periodic check to see if the session's external authorization
+ * has expired. If it has, the backend process is terminated with a FATAL error.
+ */
+static void
+CheckSessionAuthorizationExpired()
+{
+ static time_t last_check_time = 0;
+ time_t current_time = time(NULL);
+
+ if (current_time - last_check_time < auth_expiration_check_interval * 60)
+ return;
+
+ last_check_time = current_time;
+
+ if (check_auth_status_expired(MyClientConnectionInfo.auth_method))
+ {
+ StringInfoData buf;
+
+ pq_beginmessage(&buf, PqMsg_GoAway);
+ pq_endmessage(&buf);
+ pq_flush();
+ }
+}
/* ----------------------------------------------------------------
* PostgresMain
@@ -4651,6 +4692,13 @@ PostgresMain(const char *dbname, const char *username)
if (notifyInterruptPending)
ProcessNotifyInterrupt(false);
+ /*
+ * If the backend is about to wait for the next command (i.e., it's idle),
+ * this is the safest time to perform the external authorization check.
+ */
+ if (auth_expiration_check_interval > 0)
+ CheckSessionAuthorizationExpired();
+
/*
* Check if we need to report stats. If pgstat_report_stat()
* decides it's too soon to flush out pending stats / lock
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 3b9d8349078..467c7687f28 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -113,6 +113,17 @@
boot_val => 'true',
},
+
+{ name => 'auth_expiration_check_interval', type => 'int', context => 'PGC_SIGHUP', group => 'CONN_AUTH_AUTH',
+ short_desc => 'Sets the periodic interval for checking a session external authorization status.',
+ long_desc => '0 disables the interval.',
+ flags => 'GUC_UNIT_MIN',
+ variable => 'auth_expiration_check_interval',
+ boot_val => '0',
+ min => '0',
+ max => 'INT_MAX',
+},
+
{ name => 'authentication_timeout', type => 'int', context => 'PGC_SIGHUP', group => 'CONN_AUTH_AUTH',
short_desc => 'Sets the maximum allowed time to complete client authentication.',
flags => 'GUC_UNIT_S',
diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h
index c6f5ebceefd..a2c18c3b501 100644
--- a/src/include/storage/proc.h
+++ b/src/include/storage/proc.h
@@ -477,6 +477,7 @@ extern PGDLLIMPORT int IdleInTransactionSessionTimeout;
extern PGDLLIMPORT int TransactionTimeout;
extern PGDLLIMPORT int IdleSessionTimeout;
extern PGDLLIMPORT bool log_lock_waits;
+extern PGDLLIMPORT int auth_expiration_check_interval;
#ifdef EXEC_BACKEND
extern PGDLLIMPORT slock_t *ProcStructLock;
