From 5d8cd1bcddc98ed179ecf529c7dfbc64d858fa98 Mon Sep 17 00:00:00 2001
From: Mahendra Singh Thalor <mahi6run@gmail.com>
Date: Wed, 2 Apr 2025 16:07:31 +0530
Subject: [PATCH] add handling to pg_upgrade to report alert for invalid 
 database, user and role names.

If database/role/user name has any newline or carraige return character
in name, then pg_upgrade will report ALERT for these.
---
 src/bin/pg_upgrade/check.c | 73 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 73 insertions(+)

diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 02d9146e5ed..7bb657dc130 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -29,6 +29,7 @@ static void check_new_cluster_logical_replication_slots(void);
 static void check_new_cluster_subscription_configuration(void);
 static void check_old_cluster_for_valid_slots(void);
 static void check_old_cluster_subscription_state(void);
+static void check_database_user_role_names_in_old_cluser(ClusterInfo *cluster);
 
 /*
  * DataTypesUsageChecks - definitions of data type checks for the old cluster
@@ -596,6 +597,9 @@ check_and_dump_old_cluster(void)
 	 */
 	check_for_connection_status(&old_cluster);
 
+	/* Validate database, user and role names from old cluser. */
+	check_database_user_role_names_in_old_cluser(&old_cluster);
+
 	/*
 	 * Extract a list of databases, tables, and logical replication slots from
 	 * the old cluster.
@@ -2084,3 +2088,72 @@ check_old_cluster_subscription_state(void)
 	else
 		check_ok();
 }
+
+/*
+ * check_database_user_role_names_in_old_cluser()
+ *
+ * If any database, user or role name has newline or carriage return character
+ * in name, then this will report those as these special characters are not
+ * allowed in these names from v18.
+ */
+static void
+check_database_user_role_names_in_old_cluser(ClusterInfo *cluster)
+{
+	int			i;
+	PGconn		*conn_template1;
+	PGresult	*res;
+	int			ntups;
+	FILE		*script = NULL;
+	char		output_path[MAXPGPATH];
+	int			count = 0;
+
+	prep_status("Checking names of databases/users/roles ");
+
+	snprintf(output_path, sizeof(output_path), "%s/%s",
+			log_opts.basedir,
+			"db_role_user_invalid_names.txt");
+
+	conn_template1 = connectToServer(cluster, "template1");
+
+	/*
+	 * Get database, user/role names from cluster.  Can't use
+	 * pg_authid because only superusers can view it.
+	 */
+	res = executeQueryOrDie(conn_template1,
+			"SELECT datname AS objname, 'database' AS objtype "
+			"FROM pg_catalog.pg_database UNION ALL "
+			"SELECT rolname AS objname, 'role' AS objtype "
+			"FROM pg_catalog.pg_roles ORDER BY 2 ");
+
+	ntups = PQntuples(res);
+	for (i = 0; i < ntups; i++)
+	{
+		char	*objname = PQgetvalue(res, i, 0);
+		char	*objtype = PQgetvalue(res, i, 1);
+
+		/* If name has \n or \r, then report it. */
+		if (strpbrk(objname, "\n\r"))
+		{
+			if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
+				pg_fatal("could not open file \"%s\": %m", output_path);
+
+			fprintf(script, "%d : %s name = \"%s\"\n", ++count, objtype, objname);
+		}
+	}
+
+	PQclear(res);
+	PQfinish(conn_template1);
+
+	if (script)
+	{
+		fclose(script);
+		pg_log(PG_REPORT, "fatal");
+		pg_fatal("All the database, role/user names should have only valid characters. A newline or \n"
+				"carriage return character is not allowed in these object names.  To fix this, please \n"
+				"rename these names with valid names. \n"
+				"To see all %d invalid object names, refer db_role_user_invalid_names.txt file. \n"
+				"    %s", count, output_path);
+	}
+	else
+		check_ok();
+}
-- 
2.39.3