From 10c09f7c57c1e79ccaafe5db1d06ac72e85baac4 Mon Sep 17 00:00:00 2001
From: Mahendra Singh Thalor <mahi6run@gmail.com>
Date: Fri, 28 Mar 2025 16:51:33 +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 | 99 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 99 insertions(+)

diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 02d9146e5ed..39e74ad48bb 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,98 @@ 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];
+
+	prep_status("Checking names of databases/users/roles ");
+
+	snprintf(output_path, sizeof(output_path), "%s/%s",
+			log_opts.basedir,
+			"db_role_invalid_names.txt");
+
+	conn_template1 = connectToServer(cluster, "template1");
+
+	/* get database names */
+	res = executeQueryOrDie(conn_template1,
+			"SELECT datname "
+			"FROM pg_catalog.pg_database");
+
+	ntups = PQntuples(res);
+	for (i = 0; i < ntups; i++)
+	{
+		char	*datname = PQgetvalue(res, i, 0);
+
+		/* If dbname has \n or \r, then report it. */
+		if (strpbrk(datname, "\n\r"))
+		{
+			if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
+				pg_fatal("could not open file \"%s\": %m", output_path);
+
+			fprintf(script, "database name = %s\n", datname);
+		}
+	}
+
+	PQclear(res);
+
+	if (script)
+	{
+		PQfinish(conn_template1);
+		fclose(script);
+		pg_log(PG_REPORT, "fatal");
+		pg_fatal("All the database names should have only valid characters.  A newline or \n"
+				"carriage return character is not allowed in database name.  To fix this, \n"
+				"please rename database names with valid names. \n"
+				"    %s", output_path);
+	}
+
+	/* Now check for users and roles. */
+	/* Can't use pg_authid because only superusers can view it. */
+	res = executeQueryOrDie(conn_template1,
+			"SELECT rolname "
+			"FROM pg_catalog.pg_roles ");
+
+	ntups = PQntuples(res);
+	for (i = 0; i < ntups; i++)
+	{
+		char	*rolname = PQgetvalue(res, i, 0);
+
+		/* If rolname has \n or \r, then report it. */
+		if (strpbrk(rolname, "\n\r"))
+		{
+			if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
+				pg_fatal("could not open file \"%s\": %m", output_path);
+
+			fprintf(script, "role name = %s\n", rolname);
+		}
+	}
+
+	PQclear(res);
+	PQfinish(conn_template1);
+
+	if (script)
+	{
+		fclose(script);
+		pg_log(PG_REPORT, "fatal");
+		pg_fatal("All the role/user names should have only valid characters.  A newline or \n"
+				"carriage return characters is not allowed in role/user name.  To fix this, \n"
+				"please rename role/user names with valid names. \n"
+				"    %s", output_path);
+	}
+	else
+		check_ok();
+}
-- 
2.39.3