From 6934d0f185ea7e30afc447547e21d876cae8e58e Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@otacoo.com>
Date: Mon, 14 Mar 2016 23:49:23 +0100
Subject: [PATCH 3/9] Add pg_auth_verifiers_sanitize

This function is aimed at being used by pg_upgrade and system administers
to filter out password verifiers that are based on protocols not defined
on the system per the list given by password_protocols.
---
 doc/src/sgml/func.sgml        | 34 +++++++++++++++++++++
 src/backend/commands/user.c   | 71 +++++++++++++++++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.h |  4 +++
 src/include/commands/user.h   |  2 ++
 4 files changed, 111 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 000489d..3129da6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19050,6 +19050,40 @@ SELECT (pg_stat_file('filename')).modification;
 
   </sect2>
 
+  <sect2 id="functions-password">
+   <title>Password Functions</title>
+
+   <para>
+    The functions shown in <xref linkend="functions-password-table"> manage
+    system passwords.
+   </para>
+
+   <table id="functions-password-table">
+    <title>Password Functions</title>
+    <tgroup cols="3">
+     <thead>
+      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>
+      </row>
+     </thead>
+     <tbody>
+      <row>
+       <entry>
+        <literal><function>pg_auth_verifiers_sanitize()</function></literal>
+       </entry>
+       <entry><type>integer</type></entry>
+       <entry>remove password verifier entries in
+        <link linkend="catalog-pg-auth-verifiers"></> for password protocols
+        not listed in <xref linkend="guc-password-protocols">. This function
+        is limited to superusers. The return result is the number of entries
+        removed from the table.
+       </entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  </sect2>
+
   </sect1>
 
   <sect1 id="functions-trigger">
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 2b3a33c..d77e379 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -1761,3 +1761,74 @@ DeletePasswordVerifiers(Oid roleid)
 	/* keep lock until the end of transaction */
 	heap_close(pg_auth_verifiers_rel, NoLock);
 }
+
+/*
+ * pg_auth_sanitize
+ *
+ * Scan through pg_auth_verifiers and remove all the password verifiers
+ * that are not part of the list of supported protocols as defined by
+ * password_protocols. Returns to caller the number of entries removed.
+ */
+Datum
+pg_auth_verifiers_sanitize(PG_FUNCTION_ARGS)
+{
+	Relation		rel;
+	HeapScanDesc	scan;
+	HeapTuple		tup;
+	char		   *rawstring;
+	List		   *elemlist;
+	int				res = 0;
+
+	if (!superuser())
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 (errmsg("must be superuser to sanitize \"pg_auth_verifiers\""))));
+
+	rawstring = pstrdup(password_protocols);
+
+	if (!SplitIdentifierString(rawstring, ',', &elemlist))
+		Assert(false); /* should not happen */
+
+	rel = heap_open(AuthVerifRelationId, AccessShareLock);
+	scan = heap_beginscan_catalog(rel, 0, NULL);
+	while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL)
+	{
+		Form_pg_auth_verifiers	authform =
+			(Form_pg_auth_verifiers) GETSTRUCT(tup);
+		ListCell	   *l;
+		bool			remove_entry = true;
+
+		foreach(l, elemlist)
+		{
+			char   *meth_name = lfirst(l);
+
+			/* Check for protocol matches */
+			if (authform->verimet == AUTH_VERIFIER_MD5 &&
+				strcmp(meth_name, "md5") == 0)
+			{
+				remove_entry = false;
+				break;
+			}
+			else if (authform->verimet == AUTH_VERIFIER_PLAIN &&
+					 strcmp(meth_name, "plain") == 0)
+			{
+				remove_entry = false;
+				break;
+			}
+		}
+
+		if (remove_entry)
+		{
+			simple_heap_delete(rel, &tup->t_self);
+			res++;
+		}
+	}
+
+	heap_endscan(scan);
+	heap_close(rel, NoLock);
+
+	pfree(rawstring);
+	list_free(elemlist);
+
+	PG_RETURN_INT32(res);
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 5c71bce..dd99480 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5196,6 +5196,10 @@ DESCR("for use by pg_upgrade");
 DATA(insert OID = 3591 ( binary_upgrade_create_empty_extension PGNSP PGUID	12 1 0 0 0 f f f f f f v r 7 0 2278 "25 25 16 25 1028 1009 1009" _null_ _null_ _null_ _null_ _null_ binary_upgrade_create_empty_extension _null_ _null_ _null_ ));
 DESCR("for use by pg_upgrade");
 
+/* commands/user.h */
+DATA(insert OID = 3339 ( pg_auth_verifiers_sanitize PGNSP PGUID 12 1 0 0 0 f f f f t f v u 0 0 23 "" _null_ _null_ _null_ _null_ _null_ pg_auth_verifiers_sanitize _null_ _null_ _null_ ));
+DESCR("sanitize entries of pg_auth_verifiers using password_protocols");
+
 /* replication/origin.h */
 DATA(insert OID = 6003 ( pg_replication_origin_create PGNSP PGUID 12 1 0 0 0 f f f f t f v u 1 0 26 "25" _null_ _null_ _null_ _null_ _null_ pg_replication_origin_create _null_ _null_ _null_ ));
 DESCR("create a replication origin");
diff --git a/src/include/commands/user.h b/src/include/commands/user.h
index 7a73bc5..4277d5f 100644
--- a/src/include/commands/user.h
+++ b/src/include/commands/user.h
@@ -35,4 +35,6 @@ extern void DropOwnedObjects(DropOwnedStmt *stmt);
 extern void ReassignOwnedObjects(ReassignOwnedStmt *stmt);
 extern List *roleSpecsToIds(List *memberNames);
 
+extern Datum pg_auth_verifiers_sanitize(PG_FUNCTION_ARGS);
+
 #endif   /* USER_H */
-- 
2.7.3