0001-Add-pg_hosts_file_rules-to-inspect-the-hosts-configu.patch

From 5db9a6bb286db74f85c6869d04d9db12f9836cce Mon Sep 17 00:00:00 2001
From: Zsolt Parragi <zsolt.parragi@percona.com>
Date: Wed, 17 Jun 2026 12:23:50 +0000
Subject: [PATCH] Add pg_hosts_file_rules() to inspect the hosts configuration
 file

---
 doc/src/sgml/system-views.sgml       | 148 +++++++++++++++++++++++++
 src/backend/catalog/system_views.sql |   5 +
 src/backend/libpq/be-secure-common.c |  14 ++-
 src/backend/utils/adt/hbafuncs.c     | 158 +++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat      |   7 ++
 src/include/libpq/hba.h              |   1 +
 src/test/regress/expected/rules.out  |  11 ++
 src/test/ssl/t/004_sni.pl            |  18 +++
 8 files changed, 359 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 2ebec6928d5..e71c2e5edb5 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -101,6 +101,11 @@
       <entry>summary of client authentication configuration file contents</entry>
      </row>
 
+     <row>
+      <entry><link linkend="view-pg-hosts-file-rules"><structname>pg_hosts_file_rules</structname></link></entry>
+      <entry>summary of hosts configuration file contents</entry>
+     </row>
+
      <row>
       <entry><link linkend="view-pg-ident-file-mappings"><structname>pg_ident_file_mappings</structname></link></entry>
       <entry>summary of client user name mapping configuration file contents</entry>
@@ -1558,6 +1563,149 @@ AND c1.path[c2.level] = c2.path[c2.level];
   </para>
  </sect1>
 
+ <sect1 id="view-pg-hosts-file-rules">
+  <title><structname>pg_hosts_file_rules</structname></title>
+
+  <indexterm zone="view-pg-hosts-file-rules">
+   <primary>pg_hosts_file_rules</primary>
+  </indexterm>
+
+  <para>
+   The view <structname>pg_hosts_file_rules</structname> provides a summary of
+   the contents of the hosts configuration file, which maps incoming TLS
+   server name indication (SNI) host names to SSL certificate configuration.
+   A row appears in this view for each entry in the file, with annotations
+   indicating whether the entry could be parsed successfully.
+  </para>
+
+  <para>
+   This view can be helpful for checking whether planned changes in the
+   hosts configuration file will work, or for diagnosing a previous failure.
+   Note that this view reports on the <emphasis>current</emphasis> contents
+   of the file, not on what was last loaded by the server.
+  </para>
+
+  <para>
+   By default, the <structname>pg_hosts_file_rules</structname> view can be
+   read only by superusers.
+  </para>
+
+  <table>
+   <title><structname>pg_hosts_file_rules</structname> Columns</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>rule_number</structfield> <type>int4</type>
+      </para>
+      <para>
+       Number of this rule, if valid, otherwise <literal>NULL</literal>
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>file_name</structfield> <type>text</type>
+      </para>
+      <para>
+       Name of the file containing this rule
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>line_number</structfield> <type>int4</type>
+      </para>
+      <para>
+       Line number of this rule in <literal>file_name</literal>
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>hostnames</structfield> <type>text[]</type>
+      </para>
+      <para>
+       List of host name(s) to which this rule applies
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>ssl_certificate</structfield> <type>text</type>
+      </para>
+      <para>
+       Path to the SSL certificate file used for these host names
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>ssl_key</structfield> <type>text</type>
+      </para>
+      <para>
+       Path to the SSL private key file used for these host names
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>ssl_ca</structfield> <type>text</type>
+      </para>
+      <para>
+       Path to the SSL certificate authority file, or null if not specified
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>passphrase_command</structfield> <type>text</type>
+      </para>
+      <para>
+       Command used to obtain the private key passphrase, or null if not
+       specified
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>passphrase_command_reload</structfield> <type>bool</type>
+      </para>
+      <para>
+       Whether the passphrase command is run on configuration reload
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>error</structfield> <type>text</type>
+      </para>
+      <para>
+       If not null, an error message indicating why this
+       entry could not be processed
+      </para></entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+
+  <para>
+   Usually, a row reflecting an incorrect entry will have values for only
+   the <structfield>line_number</structfield> and <structfield>error</structfield> fields.
+  </para>
+ </sect1>
+
  <sect1 id="view-pg-ident-file-mappings">
   <title><structname>pg_ident_file_mappings</structname></title>
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 8f129baec90..983a2537516 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -667,6 +667,11 @@ CREATE VIEW pg_hba_file_rules AS
 
 REVOKE ALL ON pg_hba_file_rules FROM PUBLIC;
 
+CREATE VIEW pg_hosts_file_rules AS
+   SELECT * FROM pg_hosts_file_rules() AS A;
+
+REVOKE ALL ON pg_hosts_file_rules FROM PUBLIC;
+
 CREATE VIEW pg_ident_file_mappings AS
    SELECT * FROM pg_ident_file_mappings() AS A;
 
diff --git a/src/backend/libpq/be-secure-common.c b/src/backend/libpq/be-secure-common.c
index 6ec887b8a47..9de816c0d9e 100644
--- a/src/backend/libpq/be-secure-common.c
+++ b/src/backend/libpq/be-secure-common.c
@@ -29,8 +29,6 @@
 #include "utils/builtins.h"
 #include "utils/guc.h"
 
-static HostsLine *parse_hosts_line(TokenizedAuthLine *tok_line, int elevel);
-
 /*
  * Run ssl_passphrase_command
  *
@@ -191,10 +189,11 @@ check_ssl_key_file_permissions(const char *ssl_key_file, bool isServerStart)
  * the TLS backend. Validation of the parsed values is left for the TLS backend
  * to implement.
  */
-static HostsLine *
+HostsLine *
 parse_hosts_line(TokenizedAuthLine *tok_line, int elevel)
 {
 	HostsLine  *parsedline;
+	char	  **err_msg = &tok_line->err_msg;
 	List	   *tokens;
 	ListCell   *field;
 	AuthToken  *token;
@@ -217,6 +216,7 @@ parse_hosts_line(TokenizedAuthLine *tok_line, int elevel)
 		if ((tokens->length > 1) &&
 			(strcmp(hostname->string, "*") == 0 || strcmp(hostname->string, "/no_sni/") == 0))
 		{
+			*err_msg = "default and non-SNI entries cannot be mixed with other entries";
 			ereport(elevel,
 					errcode(ERRCODE_CONFIG_FILE_ERROR),
 					errmsg("default and non-SNI entries cannot be mixed with other entries"),
@@ -232,6 +232,7 @@ parse_hosts_line(TokenizedAuthLine *tok_line, int elevel)
 	field = lnext(tok_line->fields, field);
 	if (!field)
 	{
+		*err_msg = "missing entry at end of line";
 		ereport(elevel,
 				errcode(ERRCODE_CONFIG_FILE_ERROR),
 				errmsg("missing entry at end of line"),
@@ -242,6 +243,7 @@ parse_hosts_line(TokenizedAuthLine *tok_line, int elevel)
 	tokens = lfirst(field);
 	if (tokens->length > 1)
 	{
+		*err_msg = "multiple values specified for SSL certificate";
 		ereport(elevel,
 				errcode(ERRCODE_CONFIG_FILE_ERROR),
 				errmsg("multiple values specified for SSL certificate"),
@@ -256,6 +258,7 @@ parse_hosts_line(TokenizedAuthLine *tok_line, int elevel)
 	field = lnext(tok_line->fields, field);
 	if (!field)
 	{
+		*err_msg = "missing entry at end of line";
 		ereport(elevel,
 				errcode(ERRCODE_CONFIG_FILE_ERROR),
 				errmsg("missing entry at end of line"),
@@ -266,6 +269,7 @@ parse_hosts_line(TokenizedAuthLine *tok_line, int elevel)
 	tokens = lfirst(field);
 	if (tokens->length > 1)
 	{
+		*err_msg = "multiple values specified for SSL key";
 		ereport(elevel,
 				errcode(ERRCODE_CONFIG_FILE_ERROR),
 				errmsg("multiple values specified for SSL key"),
@@ -283,6 +287,7 @@ parse_hosts_line(TokenizedAuthLine *tok_line, int elevel)
 	tokens = lfirst(field);
 	if (tokens->length > 1)
 	{
+		*err_msg = "multiple values specified for SSL CA";
 		ereport(elevel,
 				errcode(ERRCODE_CONFIG_FILE_ERROR),
 				errmsg("multiple values specified for SSL CA"),
@@ -300,6 +305,7 @@ parse_hosts_line(TokenizedAuthLine *tok_line, int elevel)
 		tokens = lfirst(field);
 		if (tokens->length > 1)
 		{
+			*err_msg = "multiple values specified for SSL passphrase command";
 			ereport(elevel,
 					errcode(ERRCODE_CONFIG_FILE_ERROR),
 					errmsg("multiple values specified for SSL passphrase command"),
@@ -328,6 +334,7 @@ parse_hosts_line(TokenizedAuthLine *tok_line, int elevel)
 			 */
 			if (lnext(tok_line->fields, field))
 			{
+				*err_msg = "extra fields at end of line";
 				ereport(elevel,
 						errcode(ERRCODE_CONFIG_FILE_ERROR),
 						errmsg("extra fields at end of line"),
@@ -338,6 +345,7 @@ parse_hosts_line(TokenizedAuthLine *tok_line, int elevel)
 
 			if (tokens->length > 1 || !parse_bool(token->string, &parsedline->ssl_passphrase_reload))
 			{
+				*err_msg = "incorrect syntax for boolean value SSL_passphrase_cmd_reload";
 				ereport(elevel,
 						errcode(ERRCODE_CONFIG_FILE_ERROR),
 						errmsg("incorrect syntax for boolean value SSL_passphrase_cmd_reload"),
diff --git a/src/backend/utils/adt/hbafuncs.c b/src/backend/utils/adt/hbafuncs.c
index bd23eda3f79..b17d2b5bc12 100644
--- a/src/backend/utils/adt/hbafuncs.c
+++ b/src/backend/utils/adt/hbafuncs.c
@@ -30,6 +30,10 @@ static void fill_hba_line(Tuplestorestate *tuple_store, TupleDesc tupdesc,
 						  int rule_number, char *filename, int lineno,
 						  HbaLine *hba, const char *err_msg);
 static void fill_hba_view(Tuplestorestate *tuple_store, TupleDesc tupdesc);
+static void fill_hosts_line(Tuplestorestate *tuple_store, TupleDesc tupdesc,
+							int rule_number, const char *filename, int lineno,
+							HostsLine *host, const char *err_msg);
+static void fill_hosts_view(Tuplestorestate *tuple_store, TupleDesc tupdesc);
 static void fill_ident_line(Tuplestorestate *tuple_store, TupleDesc tupdesc,
 							int map_number, char *filename, int lineno,
 							IdentLine *ident, const char *err_msg);
@@ -448,6 +452,160 @@ pg_hba_file_rules(PG_FUNCTION_ARGS)
 	PG_RETURN_NULL();
 }
 
+/* Number of columns in pg_hosts_file_rules view */
+#define NUM_PG_HOSTS_FILE_RULES_ATTS	 10
+
+/*
+ * fill_hosts_line
+ *		Build one row of pg_hosts_file_rules view, add it to tuplestore.
+ *
+ * rule_number: unique identifier among valid rules (ignored when err_msg set)
+ * filename, lineno: always valid
+ * host: parsed entry (NULL when err_msg is set)
+ * err_msg: error message (NULL if none)
+ */
+static void
+fill_hosts_line(Tuplestorestate *tuple_store, TupleDesc tupdesc,
+				int rule_number, const char *filename, int lineno,
+				HostsLine *host, const char *err_msg)
+{
+	Datum		values[NUM_PG_HOSTS_FILE_RULES_ATTS];
+	bool		nulls[NUM_PG_HOSTS_FILE_RULES_ATTS];
+	HeapTuple	tuple;
+	int			index;
+
+	Assert(tupdesc->natts == NUM_PG_HOSTS_FILE_RULES_ATTS);
+
+	memset(values, 0, sizeof(values));
+	memset(nulls, 0, sizeof(nulls));
+	index = 0;
+
+	/* rule_number, nothing on error */
+	if (err_msg)
+		nulls[index++] = true;
+	else
+		values[index++] = Int32GetDatum(rule_number);
+
+	/* file_name */
+	values[index++] = CStringGetTextDatum(filename);
+
+	/* line_number */
+	values[index++] = Int32GetDatum(lineno);
+
+	if (host != NULL)
+	{
+		/* hostnames text[] */
+		if (host->hostnames)
+			values[index++] = PointerGetDatum(strlist_to_textarray(host->hostnames));
+		else
+			nulls[index++] = true;
+
+		/* ssl_certificate */
+		if (host->ssl_cert)
+			values[index++] = CStringGetTextDatum(host->ssl_cert);
+		else
+			nulls[index++] = true;
+
+		/* ssl_key */
+		if (host->ssl_key)
+			values[index++] = CStringGetTextDatum(host->ssl_key);
+		else
+			nulls[index++] = true;
+
+		/* ssl_ca */
+		if (host->ssl_ca)
+			values[index++] = CStringGetTextDatum(host->ssl_ca);
+		else
+			nulls[index++] = true;
+
+		/* passphrase_command */
+		if (host->ssl_passphrase_cmd)
+			values[index++] = CStringGetTextDatum(host->ssl_passphrase_cmd);
+		else
+			nulls[index++] = true;
+
+		/* passphrase_command_reload */
+		values[index++] = BoolGetDatum(host->ssl_passphrase_reload);
+	}
+	else
+	{
+		/* no parse result: null the per-host columns (indexes 3..8) */
+		memset(&nulls[3], true, (NUM_PG_HOSTS_FILE_RULES_ATTS - 4) * sizeof(bool));
+	}
+
+	/* error */
+	if (err_msg)
+		values[NUM_PG_HOSTS_FILE_RULES_ATTS - 1] = CStringGetTextDatum(err_msg);
+	else
+		nulls[NUM_PG_HOSTS_FILE_RULES_ATTS - 1] = true;
+
+	tuple = heap_form_tuple(tupdesc, values, nulls);
+	tuplestore_puttuple(tuple_store, tuple);
+}
+
+/*
+ * fill_hosts_view
+ *		Read the pg_hosts file and fill the tuplestore with view records.
+ */
+static void
+fill_hosts_view(Tuplestorestate *tuple_store, TupleDesc tupdesc)
+{
+	FILE	   *file;
+	List	   *hosts_lines = NIL;
+	ListCell   *line;
+	int			rule_number = 0;
+	MemoryContext hostscxt;
+	MemoryContext oldcxt;
+
+	file = open_auth_file(HostsFileName, DEBUG3, 0, NULL);
+	if (file == NULL)
+		return;
+
+	tokenize_auth_file(HostsFileName, file, &hosts_lines, DEBUG3, 0);
+
+	hostscxt = AllocSetContextCreate(CurrentMemoryContext,
+									 "hosts parser context",
+									 ALLOCSET_SMALL_SIZES);
+	oldcxt = MemoryContextSwitchTo(hostscxt);
+	foreach(line, hosts_lines)
+	{
+		TokenizedAuthLine *tok_line = (TokenizedAuthLine *) lfirst(line);
+		HostsLine  *hostsline = NULL;
+
+		if (tok_line->err_msg == NULL)
+			hostsline = parse_hosts_line(tok_line, DEBUG3);
+
+		if (tok_line->err_msg == NULL)
+			rule_number++;
+
+		fill_hosts_line(tuple_store, tupdesc, rule_number,
+						tok_line->file_name, tok_line->line_num, hostsline,
+						tok_line->err_msg);
+	}
+
+	free_auth_file(file, 0);
+	MemoryContextSwitchTo(oldcxt);
+	MemoryContextDelete(hostscxt);
+}
+
+/*
+ * pg_hosts_file_rules
+ *
+ * SQL-accessible SRF returning all entries in the pg_hosts configuration file.
+ */
+Datum
+pg_hosts_file_rules(PG_FUNCTION_ARGS)
+{
+	ReturnSetInfo *rsi;
+
+	InitMaterializedSRF(fcinfo, 0);
+
+	rsi = (ReturnSetInfo *) fcinfo->resultinfo;
+	fill_hosts_view(rsi->setResult, rsi->setDesc);
+
+	PG_RETURN_NULL();
+}
+
 /* Number of columns in pg_ident_file_mappings view */
 #define NUM_PG_IDENT_FILE_MAPPINGS_ATTS	 7
 
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index be157a5fbe9..37c9020e92d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6605,6 +6605,13 @@
   proargmodes => '{o,o,o,o,o,o,o,o,o,o,o}',
   proargnames => '{rule_number,file_name,line_number,type,database,user_name,address,netmask,auth_method,options,error}',
   prosrc => 'pg_hba_file_rules', proacl => '{POSTGRES=X}' },
+{ oid => '8801', descr => 'show pg_hosts configuration file rules',
+  proname => 'pg_hosts_file_rules', prorows => '1000', proretset => 't',
+  provolatile => 'v', prorettype => 'record', proargtypes => '',
+  proallargtypes => '{int4,text,int4,_text,text,text,text,text,bool,text}',
+  proargmodes => '{o,o,o,o,o,o,o,o,o,o}',
+  proargnames => '{rule_number,file_name,line_number,hostnames,ssl_certificate,ssl_key,ssl_ca,passphrase_command,passphrase_command_reload,error}',
+  prosrc => 'pg_hosts_file_rules', proacl => '{POSTGRES=X}' },
 { oid => '6250', descr => 'show pg_ident.conf mappings',
   proname => 'pg_ident_file_mappings', prorows => '1000', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => '',
diff --git a/src/include/libpq/hba.h b/src/include/libpq/hba.h
index 4aa6258a345..c9fa8d8f1dc 100644
--- a/src/include/libpq/hba.h
+++ b/src/include/libpq/hba.h
@@ -194,6 +194,7 @@ extern int	check_usermap(const char *usermap_name,
 						  const char *pg_user, const char *system_user,
 						  bool case_insensitive);
 extern HbaLine *parse_hba_line(TokenizedAuthLine *tok_line, int elevel);
+extern HostsLine *parse_hosts_line(TokenizedAuthLine *tok_line, int elevel);
 extern IdentLine *parse_ident_line(TokenizedAuthLine *tok_line, int elevel);
 extern FILE *open_auth_file(const char *filename, int elevel, int depth,
 							char **err_msg);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index a65a5bf0c4f..0a392f8ec1f 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1373,6 +1373,17 @@ pg_hba_file_rules| SELECT rule_number,
     options,
     error
    FROM pg_hba_file_rules() a(rule_number, file_name, line_number, type, database, user_name, address, netmask, auth_method, options, error);
+pg_hosts_file_rules| SELECT rule_number,
+    file_name,
+    line_number,
+    hostnames,
+    ssl_certificate,
+    ssl_key,
+    ssl_ca,
+    passphrase_command,
+    passphrase_command_reload,
+    error
+   FROM pg_hosts_file_rules() a(rule_number, file_name, line_number, hostnames, ssl_certificate, ssl_key, ssl_ca, passphrase_command, passphrase_command_reload, error);
 pg_ident_file_mappings| SELECT map_number,
     file_name,
     line_number,
diff --git a/src/test/ssl/t/004_sni.pl b/src/test/ssl/t/004_sni.pl
index f1a135bb3c8..e39dd4951c6 100644
--- a/src/test/ssl/t/004_sni.pl
+++ b/src/test/ssl/t/004_sni.pl
@@ -454,4 +454,22 @@ $result = $node->restart(fail_ok => 1);
 is($result, 0,
 	'pg_hosts.conf: restart fails with non-boolean value in boolean field');
 
+# pg_hosts_file_rules() reflects the parsed conf file.  Write a known-good
+# configuration so the SRF reports at least one valid entry.
+$node->append_conf('pg_hba.conf', "local all all trust");
+ok(unlink($node->data_dir . '/pg_hosts.conf'));
+$node->append_conf('pg_hosts.conf',
+	'example.org server-cn-only.crt server-cn-only.key root+client_ca.crt');
+$node->restart;
+my $rules = $node->safe_psql('postgres',
+	"SELECT count(*) FROM pg_hosts_file_rules() WHERE error IS NULL");
+ok($rules >= 1, "pg_hosts_file_rules: valid conf entries are reported");
+
+# A malformed entry shows up as an error row, not a thrown error.
+$node->append_conf('pg_hosts.conf', "this line has too many fields a b c d e f");
+$node->reload;
+my $errs = $node->safe_psql('postgres',
+	"SELECT count(*) FROM pg_hosts_file_rules() WHERE error IS NOT NULL");
+ok($errs >= 1, "pg_hosts_file_rules: malformed conf entry becomes an error row");
+
 done_testing();
-- 
2.43.0