Since MD5 passwords are slated to be marked as deprecated in v18, I figured
it might be a good idea to add a check for roles with MD5 passwords to
pg_upgrade. I'm tempted to suggest that we apply this to v18, but I'm
content to leave it for v19 if nobody feels too strongly about it.
The one thing I don't like about this check is that it's probably not great
from a security standpoint to effectively announce which roles have MD5
passwords. However, pg_upgrade must be run as the bootstrap superuser, and
we'll need to start failing for MD5 passwords at some point, so I'm not
sure how worried to be about that.
One other thing I noticed is that checks that only emit warnings, like
check_for_unicode_update(), require using --retain in order to see the
generated report file. Otherwise, pg_upgrade deletes the files after
successful completion. I don't know how worried to be about this, either,
but I did run into it while testing the attached patch, so it seemed worth
bringing up.
--
nathan
>From 011fe65c172d8788b1b6f2447b658717b84db6f0 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nat...@postgresql.org>
Date: Mon, 2 Jun 2025 10:10:03 -0500
Subject: [PATCH v1 1/1] pg_upgrade: Warn about roles with MD5 passwords.
---
src/bin/pg_upgrade/check.c | 66 ++++++++++++++++++++++++++++++++++++++
1 file changed, 66 insertions(+)
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 940fc77fc2e..9da2b5977e1 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -31,6 +31,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_for_md5_passwords(ClusterInfo *cluster);
/*
* DataTypesUsageChecks - definitions of data type checks for the old cluster
@@ -685,6 +686,12 @@ check_and_dump_old_cluster(void)
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 905)
check_for_pg_role_prefix(&old_cluster);
+ /*
+ * MD5 password support is deprecated. Warn if any roles have MD5
+ * passwords.
+ */
+ check_for_md5_passwords(&old_cluster);
+
/*
* While not a check option, we do this now because this is the only
time
* the old server is running.
@@ -2272,3 +2279,62 @@ check_old_cluster_subscription_state(void)
else
check_ok();
}
+
+/*
+ * check_for_md5_passwords()
+ *
+ * As of v18, MD5 password support is marked as deprecated and to-be-removed in
+ * a future major release.
+ */
+static void
+check_for_md5_passwords(ClusterInfo *cluster)
+{
+ PGresult *res;
+ PGconn *conn = connectToServer(cluster, "template1");
+ int ntups;
+ int i_roloid;
+ int i_rolname;
+ FILE *script = NULL;
+ char output_path[MAXPGPATH];
+
+ prep_status("Checking for roles with MD5 passwords");
+
+ snprintf(output_path, sizeof(output_path), "%s/%s",
+ log_opts.basedir,
+ "roles_with_md5_passwords.txt");
+
+ res = executeQueryOrDie(conn,
+ "SELECT oid AS roloid,
rolname "
+ "FROM
pg_catalog.pg_authid "
+ "WHERE rolpassword ~
'^md5'");
+
+ ntups = PQntuples(res);
+ i_roloid = PQfnumber(res, "roloid");
+ i_rolname = PQfnumber(res, "rolname");
+ for (int rowno = 0; rowno < ntups; rowno++)
+ {
+ if (script == NULL && (script = fopen_priv(output_path, "w"))
== NULL)
+ pg_fatal("could not open file \"%s\": %m", output_path);
+ fprintf(script, "%s (oid=%s)\n",
+ PQgetvalue(res, rowno, i_rolname),
+ PQgetvalue(res, rowno, i_roloid));
+ }
+
+ PQclear(res);
+
+ PQfinish(conn);
+
+ if (script)
+ {
+ fclose(script);
+ report_status(PG_WARNING, "warning");
+ pg_log(PG_WARNING,
+ "Your installation contains roles with MD5
passwords.\n"
+ "Support for MD5-encrypted passwords is deprecated
and will be\n"
+ "removed in a future release of PostgreSQL. A list
of roles\n"
+ "with MD5 passwords is in the file:\n"
+ " %s", output_path);
+ }
+ else
+ check_ok();
+}
--
2.39.5 (Apple Git-154)
