Let people set host(no)ssl settings from initdb

Started by David Fetterabout 6 years ago24 messages

David Fetter

david@fetter.org

about 6 years ago

1 attachment(s)

Folks,

I've found myself writing a lot of boilerplate pg_hba.conf entries
along the lines of

hostnossl all all 0.0.0.0/0 reject
hostssl all all 0.0.0.0/0 md5

so I thought I'd make it easier to do that from initdb.

What say?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Attachments:

v1-0001-Enable-setting-pg_hba.conf-permissions-from-initd.patchtext/x-diff; charset=us-asciiDownload

From 5750bafe6169a239f6084d8d2e12b40da422fced Mon Sep 17 00:00:00 2001
From: David Fetter <david.fetter@onelogin.com>
Date: Wed, 11 Dec 2019 18:55:03 -0800
Subject: [PATCH v1] Enable setting pg_hba.conf permissions from initdb
To: hackers
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="------------2.23.0"

This is a multi-part message in MIME format.
--------------2.23.0
Content-Type: text/plain; charset=UTF-8; format=fixed
Content-Transfer-Encoding: 8bit


The new optional arguments are --auth-hostssl and --auth-hostnossl

diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgml
index da5c8f5307..03b76732d1 100644
--- a/doc/src/sgml/ref/initdb.sgml
+++ b/doc/src/sgml/ref/initdb.sgml
@@ -154,6 +154,28 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--auth-hostssl=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        TLS connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostssl</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>--auth-hostnossl=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        non-TLS connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostnossl</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--auth-local=<replaceable class="parameter">authmethod</replaceable></option></term>
       <listitem>
diff --git a/src/backend/libpq/pg_hba.conf.sample b/src/backend/libpq/pg_hba.conf.sample
index c853e36232..48ef465cca 100644
--- a/src/backend/libpq/pg_hba.conf.sample
+++ b/src/backend/libpq/pg_hba.conf.sample
@@ -87,3 +87,9 @@ host    all             all             ::1/128                 @authmethodhost@
 @remove-line-for-nolocal@local   replication     all                                     @authmethodlocal@
 host    replication     all             127.0.0.1/32            @authmethodhost@
 host    replication     all             ::1/128                 @authmethodhost@
+
+# hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@
+# hostnossl all           all             ::/0                    @authmethodhostnossl@
+
+# hostssl all             all             0.0.0.0/0               @authmethodhostssl@
+# hostssl all             all             ::/0                    @authmethodhostssl@
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 1f6d8939be..392f52d21f 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -136,6 +136,8 @@ static char *pwfilename = NULL;
 static char *superuser_password = NULL;
 static const char *authmethodhost = NULL;
 static const char *authmethodlocal = NULL;
+static const char *authmethodhostssl = NULL;
+static const char *authmethodhostnossl = NULL;
 static bool debug = false;
 static bool noclean = false;
 static bool do_sync = true;
@@ -438,7 +440,6 @@ replace_token(char **lines, const char *token, const char *replacement)
  *
  * a sort of poor man's grep -v
  */
-#ifndef HAVE_UNIX_SOCKETS
 static char **
 filter_lines_with_token(char **lines, const char *token)
 {
@@ -461,7 +462,6 @@ filter_lines_with_token(char **lines, const char *token)
 
 	return result;
 }
-#endif
 
 /*
  * get the lines from a text file
@@ -1326,6 +1326,40 @@ setup_config(void)
 							  "@authcomment@",
 							  (strcmp(authmethodlocal, "trust") == 0 || strcmp(authmethodhost, "trust") == 0) ? AUTHTRUST_WARNING : "");
 
+	if (authmethodhostssl != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostssl all             all             0.0.0.0/0               @authmethodhostssl@",
+								  "hostssl all             all             0.0.0.0/0               @authmethodhostssl@");
+		conflines = replace_token(conflines,
+								  "# hostssl all             all             ::/0                    @authmethodhostssl@",
+								  "hostssl all             all             ::/0                    @authmethodhostssl@");
+		conflines = replace_token(conflines,
+								  "@authmethodhostssl@",
+								  authmethodhostssl);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostssl@");
+	}
+
+	if (authmethodhostnossl != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@",
+								  "hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@");
+		conflines = replace_token(conflines,
+								  "# hostnossl all           all             ::/0                    @authmethodhostnossl@",
+								  "hostnossl all           all             ::/0                    @authmethodhostnossl@");
+		conflines = replace_token(conflines,
+								  "@authmethodhostnossl@",
+								  authmethodhostnossl);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostnossl@");
+	}
+
 	snprintf(path, sizeof(path), "%s/pg_hba.conf", pg_data);
 
 	writefile(path, conflines);
@@ -2327,36 +2361,38 @@ usage(const char *progname)
 	printf(_("Usage:\n"));
 	printf(_("  %s [OPTION]... [DATADIR]\n"), progname);
 	printf(_("\nOptions:\n"));
-	printf(_("  -A, --auth=METHOD         default authentication method for local connections\n"));
-	printf(_("      --auth-host=METHOD    default authentication method for local TCP/IP connections\n"));
-	printf(_("      --auth-local=METHOD   default authentication method for local-socket connections\n"));
-	printf(_(" [-D, --pgdata=]DATADIR     location for this database cluster\n"));
-	printf(_("  -E, --encoding=ENCODING   set default encoding for new databases\n"));
-	printf(_("  -g, --allow-group-access  allow group read/execute on data directory\n"));
-	printf(_("      --locale=LOCALE       set default locale for new databases\n"));
+	printf(_("  -A, --auth=METHOD           default authentication method for local connections\n"));
+	printf(_("      --auth-host=METHOD      default authentication method for local TCP/IP connections\n"));
+	printf(_("      --auth-local=METHOD     default authentication method for local-socket connections\n"));
+	printf(_("      --auth-hostssl=METHOD   default authentication method for TLS connections\n"));
+	printf(_("      --auth-hostnossl=METHOD default authentication method for non-TLS connections\n"));
+	printf(_(" [-D, --pgdata=]DATADIR       location for this database cluster\n"));
+	printf(_("  -E, --encoding=ENCODING     set default encoding for new databases\n"));
+	printf(_("  -g, --allow-group-access    allow group read/execute on data directory\n"));
+	printf(_("      --locale=LOCALE         set default locale for new databases\n"));
 	printf(_("      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE\n"
 			 "      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE\n"
-			 "                            set default locale in the respective category for\n"
-			 "                            new databases (default taken from environment)\n"));
-	printf(_("      --no-locale           equivalent to --locale=C\n"));
-	printf(_("      --pwfile=FILE         read password for the new superuser from file\n"));
+			 "                              set default locale in the respective category for\n"
+			 "                              new databases (default taken from environment)\n"));
+	printf(_("      --no-locale             equivalent to --locale=C\n"));
+	printf(_("      --pwfile=FILE           read password for the new superuser from file\n"));
 	printf(_("  -T, --text-search-config=CFG\n"
-			 "                            default text search configuration\n"));
-	printf(_("  -U, --username=NAME       database superuser name\n"));
-	printf(_("  -W, --pwprompt            prompt for a password for the new superuser\n"));
-	printf(_("  -X, --waldir=WALDIR       location for the write-ahead log directory\n"));
-	printf(_("      --wal-segsize=SIZE    size of WAL segments, in megabytes\n"));
+			 "                              default text search configuration\n"));
+	printf(_("  -U, --username=NAME         database superuser name\n"));
+	printf(_("  -W, --pwprompt              prompt for a password for the new superuser\n"));
+	printf(_("  -X, --waldir=WALDIR         location for the write-ahead log directory\n"));
+	printf(_("      --wal-segsize=SIZE      size of WAL segments, in megabytes\n"));
 	printf(_("\nLess commonly used options:\n"));
-	printf(_("  -d, --debug               generate lots of debugging output\n"));
-	printf(_("  -k, --data-checksums      use data page checksums\n"));
-	printf(_("  -L DIRECTORY              where to find the input files\n"));
-	printf(_("  -n, --no-clean            do not clean up after errors\n"));
-	printf(_("  -N, --no-sync             do not wait for changes to be written safely to disk\n"));
-	printf(_("  -s, --show                show internal settings\n"));
-	printf(_("  -S, --sync-only           only sync data directory\n"));
+	printf(_("  -d, --debug                 generate lots of debugging output\n"));
+	printf(_("  -k, --data-checksums        use data page checksums\n"));
+	printf(_("  -L DIRECTORY                where to find the input files\n"));
+	printf(_("  -n, --no-clean              do not clean up after errors\n"));
+	printf(_("  -N, --no-sync               do not wait for changes to be written safely to disk\n"));
+	printf(_("  -s, --show                  show internal settings\n"));
+	printf(_("  -S, --sync-only             only sync data directory\n"));
 	printf(_("\nOther options:\n"));
-	printf(_("  -V, --version             output version information, then exit\n"));
-	printf(_("  -?, --help                show this help, then exit\n"));
+	printf(_("  -V, --version               output version information, then exit\n"));
+	printf(_("  -?, --help                  show this help, then exit\n"));
 	printf(_("\nIf the data directory is not specified, the environment variable PGDATA\n"
 			 "is used.\n"));
 	printf(_("\nReport bugs to <pgsql-bugs@lists.postgresql.org>.\n"));
@@ -3010,6 +3046,8 @@ main(int argc, char *argv[])
 		{"auth", required_argument, NULL, 'A'},
 		{"auth-local", required_argument, NULL, 10},
 		{"auth-host", required_argument, NULL, 11},
+		{"auth-hostssl", required_argument, NULL, 13},
+		{"auth-hostnossl", required_argument, NULL, 14},
 		{"pwprompt", no_argument, NULL, 'W'},
 		{"pwfile", required_argument, NULL, 9},
 		{"username", required_argument, NULL, 'U'},
@@ -3090,6 +3128,12 @@ main(int argc, char *argv[])
 			case 11:
 				authmethodhost = pg_strdup(optarg);
 				break;
+			case 13:
+				authmethodhostssl = pg_strdup(optarg);
+				break;
+			case 14:
+				authmethodhostnossl = pg_strdup(optarg);
+				break;
 			case 'D':
 				pg_data = pg_strdup(optarg);
 				break;
@@ -3224,6 +3268,10 @@ main(int argc, char *argv[])
 
 	check_authmethod_valid(authmethodlocal, auth_methods_local, "local");
 	check_authmethod_valid(authmethodhost, auth_methods_host, "host");
+	if (authmethodhostssl != NULL)
+		check_authmethod_valid(authmethodhostssl, auth_methods_host, "hostssl");
+	if (authmethodhostnossl != NULL)
+		check_authmethod_valid(authmethodhostnossl, auth_methods_host, "hostnossl");
 
 	check_need_password(authmethodlocal, authmethodhost);
 

--------------2.23.0--

Tom Lane

tgl@sss.pgh.pa.us

about 6 years ago

In reply to: David Fetter (#1)

Re: Let people set host(no)ssl settings from initdb

David Fetter <david@fetter.org> writes:

I've found myself writing a lot of boilerplate pg_hba.conf entries
along the lines of
hostnossl all all 0.0.0.0/0 reject
hostssl all all 0.0.0.0/0 md5
so I thought I'd make it easier to do that from initdb.
What say?

I'm pretty suspicious of loading down initdb with random configuration
options, because I think most people nowadays use PG via vendor packages
that script their calls to initdb. So an option like this doesn't help
unless you can persuade all those vendors to pass the option through.

That problem exists even before you get to the question of whether
this specific option is useful or well-designed ... a question I'm
not opining about here, but it would certainly require thought.

regards, tom lane

David Fetter

david@fetter.org

about 6 years ago

In reply to: Tom Lane (#2)

1 attachment(s)

Re: Let people set host(no)ssl settings from initdb

On Thu, Dec 12, 2019 at 12:23:42AM -0500, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

I've found myself writing a lot of boilerplate pg_hba.conf entries
along the lines of
hostnossl all all 0.0.0.0/0 reject
hostssl all all 0.0.0.0/0 md5
so I thought I'd make it easier to do that from initdb.
What say?

I'm pretty suspicious of loading down initdb with random configuration
options, because I think most people nowadays use PG via vendor packages
that script their calls to initdb. So an option like this doesn't help
unless you can persuade all those vendors to pass the option through.

Would the official PGDG .deb and .rpm packages suffice?

That problem exists even before you get to the question of whether
this specific option is useful or well-designed ... a question I'm
not opining about here, but it would certainly require thought.

I think it was a reasonable extension. We cover lines that start with
local and host, but they can also start with hostssl and hostnossl.

Meanwhile, please find attached a fix for an oversight around IPv6.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Attachments:

v2-0001-Enable-setting-pg_hba.conf-permissions-from-initd.patchtext/x-diff; charset=us-asciiDownload

From cd32f9d27dbb0f7c24a5f8141267b0943320f546 Mon Sep 17 00:00:00 2001
From: David Fetter <david.fetter@onelogin.com>
Date: Wed, 11 Dec 2019 18:55:03 -0800
Subject: [PATCH v2] Enable setting pg_hba.conf permissions from initdb
To: hackers
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="------------2.23.0"

This is a multi-part message in MIME format.
--------------2.23.0
Content-Type: text/plain; charset=UTF-8; format=fixed
Content-Transfer-Encoding: 8bit


The new optional arguments are --auth-hostssl and --auth-hostnossl

diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgml
index da5c8f5307..03b76732d1 100644
--- a/doc/src/sgml/ref/initdb.sgml
+++ b/doc/src/sgml/ref/initdb.sgml
@@ -154,6 +154,28 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--auth-hostssl=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        TLS connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostssl</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>--auth-hostnossl=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        non-TLS connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostnossl</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--auth-local=<replaceable class="parameter">authmethod</replaceable></option></term>
       <listitem>
diff --git a/src/backend/libpq/pg_hba.conf.sample b/src/backend/libpq/pg_hba.conf.sample
index c853e36232..48ef465cca 100644
--- a/src/backend/libpq/pg_hba.conf.sample
+++ b/src/backend/libpq/pg_hba.conf.sample
@@ -87,3 +87,9 @@ host    all             all             ::1/128                 @authmethodhost@
 @remove-line-for-nolocal@local   replication     all                                     @authmethodlocal@
 host    replication     all             127.0.0.1/32            @authmethodhost@
 host    replication     all             ::1/128                 @authmethodhost@
+
+# hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@
+# hostnossl all           all             ::/0                    @authmethodhostnossl@
+
+# hostssl all             all             0.0.0.0/0               @authmethodhostssl@
+# hostssl all             all             ::/0                    @authmethodhostssl@
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 1f6d8939be..09bf49aa23 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -136,6 +136,8 @@ static char *pwfilename = NULL;
 static char *superuser_password = NULL;
 static const char *authmethodhost = NULL;
 static const char *authmethodlocal = NULL;
+static const char *authmethodhostssl = NULL;
+static const char *authmethodhostnossl = NULL;
 static bool debug = false;
 static bool noclean = false;
 static bool do_sync = true;
@@ -438,7 +440,6 @@ replace_token(char **lines, const char *token, const char *replacement)
  *
  * a sort of poor man's grep -v
  */
-#ifndef HAVE_UNIX_SOCKETS
 static char **
 filter_lines_with_token(char **lines, const char *token)
 {
@@ -461,7 +462,6 @@ filter_lines_with_token(char **lines, const char *token)
 
 	return result;
 }
-#endif
 
 /*
  * get the lines from a text file
@@ -1326,6 +1326,44 @@ setup_config(void)
 							  "@authcomment@",
 							  (strcmp(authmethodlocal, "trust") == 0 || strcmp(authmethodhost, "trust") == 0) ? AUTHTRUST_WARNING : "");
 
+	if (authmethodhostssl != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostssl all             all             0.0.0.0/0               @authmethodhostssl@",
+								  "hostssl all             all             0.0.0.0/0               @authmethodhostssl@");
+#ifdef HAVE_IPV6
+		conflines = replace_token(conflines,
+								  "# hostssl all             all             ::/0                    @authmethodhostssl@",
+								  "hostssl all             all             ::/0                    @authmethodhostssl@");
+#endif
+		conflines = replace_token(conflines,
+								  "@authmethodhostssl@",
+								  authmethodhostssl);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostssl@");
+	}
+
+	if (authmethodhostnossl != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@",
+								  "hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@");
+#ifdef HAVE_IPV6
+		conflines = replace_token(conflines,
+								  "# hostnossl all           all             ::/0                    @authmethodhostnossl@",
+								  "hostnossl all           all             ::/0                    @authmethodhostnossl@");
+#endif
+		conflines = replace_token(conflines,
+								  "@authmethodhostnossl@",
+								  authmethodhostnossl);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostnossl@");
+	}
+
 	snprintf(path, sizeof(path), "%s/pg_hba.conf", pg_data);
 
 	writefile(path, conflines);
@@ -2327,36 +2365,38 @@ usage(const char *progname)
 	printf(_("Usage:\n"));
 	printf(_("  %s [OPTION]... [DATADIR]\n"), progname);
 	printf(_("\nOptions:\n"));
-	printf(_("  -A, --auth=METHOD         default authentication method for local connections\n"));
-	printf(_("      --auth-host=METHOD    default authentication method for local TCP/IP connections\n"));
-	printf(_("      --auth-local=METHOD   default authentication method for local-socket connections\n"));
-	printf(_(" [-D, --pgdata=]DATADIR     location for this database cluster\n"));
-	printf(_("  -E, --encoding=ENCODING   set default encoding for new databases\n"));
-	printf(_("  -g, --allow-group-access  allow group read/execute on data directory\n"));
-	printf(_("      --locale=LOCALE       set default locale for new databases\n"));
+	printf(_("  -A, --auth=METHOD           default authentication method for local connections\n"));
+	printf(_("      --auth-host=METHOD      default authentication method for local TCP/IP connections\n"));
+	printf(_("      --auth-local=METHOD     default authentication method for local-socket connections\n"));
+	printf(_("      --auth-hostssl=METHOD   default authentication method for TLS connections\n"));
+	printf(_("      --auth-hostnossl=METHOD default authentication method for non-TLS connections\n"));
+	printf(_(" [-D, --pgdata=]DATADIR       location for this database cluster\n"));
+	printf(_("  -E, --encoding=ENCODING     set default encoding for new databases\n"));
+	printf(_("  -g, --allow-group-access    allow group read/execute on data directory\n"));
+	printf(_("      --locale=LOCALE         set default locale for new databases\n"));
 	printf(_("      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE\n"
 			 "      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE\n"
-			 "                            set default locale in the respective category for\n"
-			 "                            new databases (default taken from environment)\n"));
-	printf(_("      --no-locale           equivalent to --locale=C\n"));
-	printf(_("      --pwfile=FILE         read password for the new superuser from file\n"));
+			 "                              set default locale in the respective category for\n"
+			 "                              new databases (default taken from environment)\n"));
+	printf(_("      --no-locale             equivalent to --locale=C\n"));
+	printf(_("      --pwfile=FILE           read password for the new superuser from file\n"));
 	printf(_("  -T, --text-search-config=CFG\n"
-			 "                            default text search configuration\n"));
-	printf(_("  -U, --username=NAME       database superuser name\n"));
-	printf(_("  -W, --pwprompt            prompt for a password for the new superuser\n"));
-	printf(_("  -X, --waldir=WALDIR       location for the write-ahead log directory\n"));
-	printf(_("      --wal-segsize=SIZE    size of WAL segments, in megabytes\n"));
+			 "                              default text search configuration\n"));
+	printf(_("  -U, --username=NAME         database superuser name\n"));
+	printf(_("  -W, --pwprompt              prompt for a password for the new superuser\n"));
+	printf(_("  -X, --waldir=WALDIR         location for the write-ahead log directory\n"));
+	printf(_("      --wal-segsize=SIZE      size of WAL segments, in megabytes\n"));
 	printf(_("\nLess commonly used options:\n"));
-	printf(_("  -d, --debug               generate lots of debugging output\n"));
-	printf(_("  -k, --data-checksums      use data page checksums\n"));
-	printf(_("  -L DIRECTORY              where to find the input files\n"));
-	printf(_("  -n, --no-clean            do not clean up after errors\n"));
-	printf(_("  -N, --no-sync             do not wait for changes to be written safely to disk\n"));
-	printf(_("  -s, --show                show internal settings\n"));
-	printf(_("  -S, --sync-only           only sync data directory\n"));
+	printf(_("  -d, --debug                 generate lots of debugging output\n"));
+	printf(_("  -k, --data-checksums        use data page checksums\n"));
+	printf(_("  -L DIRECTORY                where to find the input files\n"));
+	printf(_("  -n, --no-clean              do not clean up after errors\n"));
+	printf(_("  -N, --no-sync               do not wait for changes to be written safely to disk\n"));
+	printf(_("  -s, --show                  show internal settings\n"));
+	printf(_("  -S, --sync-only             only sync data directory\n"));
 	printf(_("\nOther options:\n"));
-	printf(_("  -V, --version             output version information, then exit\n"));
-	printf(_("  -?, --help                show this help, then exit\n"));
+	printf(_("  -V, --version               output version information, then exit\n"));
+	printf(_("  -?, --help                  show this help, then exit\n"));
 	printf(_("\nIf the data directory is not specified, the environment variable PGDATA\n"
 			 "is used.\n"));
 	printf(_("\nReport bugs to <pgsql-bugs@lists.postgresql.org>.\n"));
@@ -3010,6 +3050,8 @@ main(int argc, char *argv[])
 		{"auth", required_argument, NULL, 'A'},
 		{"auth-local", required_argument, NULL, 10},
 		{"auth-host", required_argument, NULL, 11},
+		{"auth-hostssl", required_argument, NULL, 13},
+		{"auth-hostnossl", required_argument, NULL, 14},
 		{"pwprompt", no_argument, NULL, 'W'},
 		{"pwfile", required_argument, NULL, 9},
 		{"username", required_argument, NULL, 'U'},
@@ -3090,6 +3132,12 @@ main(int argc, char *argv[])
 			case 11:
 				authmethodhost = pg_strdup(optarg);
 				break;
+			case 13:
+				authmethodhostssl = pg_strdup(optarg);
+				break;
+			case 14:
+				authmethodhostnossl = pg_strdup(optarg);
+				break;
 			case 'D':
 				pg_data = pg_strdup(optarg);
 				break;
@@ -3224,6 +3272,10 @@ main(int argc, char *argv[])
 
 	check_authmethod_valid(authmethodlocal, auth_methods_local, "local");
 	check_authmethod_valid(authmethodhost, auth_methods_host, "host");
+	if (authmethodhostssl != NULL)
+		check_authmethod_valid(authmethodhostssl, auth_methods_host, "hostssl");
+	if (authmethodhostnossl != NULL)
+		check_authmethod_valid(authmethodhostnossl, auth_methods_host, "hostnossl");
 
 	check_need_password(authmethodlocal, authmethodhost);
 

--------------2.23.0--

Peter Eisentraut

peter.eisentraut@2ndquadrant.com

about 6 years ago

In reply to: David Fetter (#3)

Re: Let people set host(no)ssl settings from initdb

On 2019-12-12 07:24, David Fetter wrote:

That problem exists even before you get to the question of whether
this specific option is useful or well-designed ... a question I'm
not opining about here, but it would certainly require thought.

I think it was a reasonable extension. We cover lines that start with
local and host, but they can also start with hostssl and hostnossl.

I suspect the real purpose here is to easily reject non-SSL connections
altogether. This is currently quite cumbersome and requires careful
ongoing maintenance of pg_hba.conf. But I see two problems with the
proposed approach: (1) initdb doesn't support setting up SSL, so the
only thing you can achieve here is to reject all TCP/IP connections,
until you have set up SSL. (2) The default pg_hba.conf only covers
localhost connections. The value of enforcing SSL connections to
localhost is probably quite low. You still need ongoing careful
pg_hba.conf maintenance as you add more host entries.

Maybe we just need something like libpq's sslmode on the server side.
Probably not quite the same, perhaps just ssl = require.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

David Fetter

david@fetter.org

about 6 years ago

In reply to: Peter Eisentraut (#4)

Re: Let people set host(no)ssl settings from initdb

On Thu, Dec 12, 2019 at 10:47:52AM +0100, Peter Eisentraut wrote:

On 2019-12-12 07:24, David Fetter wrote:

That problem exists even before you get to the question of whether
this specific option is useful or well-designed ... a question I'm
not opining about here, but it would certainly require thought.

I think it was a reasonable extension. We cover lines that start with
local and host, but they can also start with hostssl and hostnossl.

I suspect the real purpose here is to easily reject non-SSL connections
altogether. This is currently quite cumbersome and requires careful ongoing
maintenance of pg_hba.conf.

Yes, and kinda. It's certainly possible to put lines high up in
pg_hba.conf that read:

hostnossl all all 0.0.0.0/0 reject
hostnossl all all ::/0 reject

and then the only ongoing maintenance is not to put lines above them
that contradict it.

But I see two problems with the proposed approach: (1) initdb
doesn't support setting up SSL, so the only thing you can achieve
here is to reject all TCP/IP connections, until you have set up SSL.

I don't believe any special setup is needed to require TLS for the
connection, which is what this patch handles in a straightforward way.

Setting up cert-based auth is the hassle you describe.

(2) The default pg_hba.conf only covers localhost connections.

As of this patch, it can be asked to cover all connections.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

tsunakawa.takay@fujitsu.com

about 6 years ago

In reply to: David Fetter (#5)

RE: Let people set host(no)ssl settings from initdb

From: David Fetter <david@fetter.org>

But I see two problems with the proposed approach: (1) initdb
doesn't support setting up SSL, so the only thing you can achieve
here is to reject all TCP/IP connections, until you have set up SSL.

I don't believe any special setup is needed to require TLS for the
connection, which is what this patch handles in a straightforward way.

I think this feature can be useful because it's common to reject remote non-TLS connections. Eliminating the need to script for pg_hba.conf is welcome. Setting GUC parameters just after initdb is relatively easy, because we can simply add lines at the end of postgresql.conf. But pg_hba.conf is not because the first matching entry is effective.

In terms of rejecting non-secure remote connections, should hostgssenc/hostnogssenc also be handled similarly?

(2) The default pg_hba.conf only covers localhost connections.

As of this patch, it can be asked to cover all connections.

+      <term><option>--auth-hostssl=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
fg
+        TLS connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostssl</literal> lines).
+       </para>
	+      </listitem>

The relationship between --auth/--auth-local/--auth-host and --auth-hostssl/--auth-hostnossl is confusing. The former is for local connections, and the latter is for remote ones. Can we just add "remote" in the above documentation?

Plus, you're adding the first option to initdb that handles remote connections. As the following execution shows, it doesn't warn about using "trust" for remote connections.

$ initdb --auth=md5 --pwprompt --auth-hostssl=trust --auth-hostnossl=trust
...
syncing data to disk ... ok

Success. You can now start the database server using:

pg_ctl -D /tuna/pg2 -l logfile start

I think we should emit a warning message like the following existing one:

--------------------------------------------------
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
-
initdb: warning: enabling "trust" authentication

Regards
Takayuki Tsunakawa

David Fetter

david@fetter.org

almost 6 years ago

In reply to: tsunakawa.takay@fujitsu.com (#6)

Re: Let people set host(no)ssl settings from initdb

On Wed, Jan 08, 2020 at 02:53:47AM +0000, tsunakawa.takay@fujitsu.com wrote:

From: David Fetter <david@fetter.org>

But I see two problems with the proposed approach: (1) initdb
doesn't support setting up SSL, so the only thing you can achieve
here is to reject all TCP/IP connections, until you have set up SSL.

I don't believe any special setup is needed to require TLS for the
connection, which is what this patch handles in a straightforward way.

I think this feature can be useful because it's common to reject remote non-TLS connections. Eliminating the need to script for pg_hba.conf is welcome. Setting GUC parameters just after initdb is relatively easy, because we can simply add lines at the end of postgresql.conf. But pg_hba.conf is not because the first matching entry is effective.

In terms of rejecting non-secure remote connections, should hostgssenc/hostnogssenc also be handled similarly?

Yes, and they are in the enclosed patch.

(2) The default pg_hba.conf only covers localhost connections.

As of this patch, it can be asked to cover all connections.
+      <term><option>--auth-hostssl=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
fg
+        TLS connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostssl</literal> lines).
+       </para>
+      </listitem>
The relationship between --auth/--auth-local/--auth-host and --auth-hostssl/--auth-hostnossl is confusing. The former is for local connections, and the latter is for remote ones. Can we just add "remote" in the above documentation?

Done.

Plus, you're adding the first option to initdb that handles remote connections. As the following execution shows, it doesn't warn about using "trust" for remote connections.

$ initdb --auth=md5 --pwprompt --auth-hostssl=trust --auth-hostnossl=trust
...
syncing data to disk ... ok

Success. You can now start the database server using:

pg_ctl -D /tuna/pg2 -l logfile start

I think we should emit a warning message like the following existing one:

--------------------------------------------------
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
-
initdb: warning: enabling "trust" authentication

Done.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter

david@fetter.org

almost 6 years ago

In reply to: David Fetter (#7)

1 attachment(s)

Re: Let people set host(no)ssl settings from initdb

On Fri, Jan 17, 2020 at 08:47:49PM +0100, David Fetter wrote:

On Wed, Jan 08, 2020 at 02:53:47AM +0000, tsunakawa.takay@fujitsu.com wrote:

From: David Fetter <david@fetter.org>

But I see two problems with the proposed approach: (1) initdb
doesn't support setting up SSL, so the only thing you can achieve
here is to reject all TCP/IP connections, until you have set up SSL.

I don't believe any special setup is needed to require TLS for the
connection, which is what this patch handles in a straightforward way.

I think this feature can be useful because it's common to reject remote non-TLS connections. Eliminating the need to script for pg_hba.conf is welcome. Setting GUC parameters just after initdb is relatively easy, because we can simply add lines at the end of postgresql.conf. But pg_hba.conf is not because the first matching entry is effective.

In terms of rejecting non-secure remote connections, should hostgssenc/hostnogssenc also be handled similarly?

Yes, and they are in the enclosed patch.
(2) The default pg_hba.conf only covers localhost connections.

As of this patch, it can be asked to cover all connections.
+      <term><option>--auth-hostssl=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
fg
+        TLS connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostssl</literal> lines).
+       </para>
+      </listitem>
The relationship between --auth/--auth-local/--auth-host and --auth-hostssl/--auth-hostnossl is confusing. The former is for local connections, and the latter is for remote ones. Can we just add "remote" in the above documentation?
Done.

Plus, you're adding the first option to initdb that handles remote connections. As the following execution shows, it doesn't warn about using "trust" for remote connections.

$ initdb --auth=md5 --pwprompt --auth-hostssl=trust --auth-hostnossl=trust
...
syncing data to disk ... ok

Success. You can now start the database server using:

pg_ctl -D /tuna/pg2 -l logfile start

I think we should emit a warning message like the following existing one:

--------------------------------------------------
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
-
initdb: warning: enabling "trust" authentication

Done.

Best,
David.

This time, with the patch attached.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Attachments:

v3-0001-Enable-setting-pg_hba.conf-permissions-from-initd.patchtext/x-diff; charset=us-asciiDownload

From 9dbe5b8a0e4a452367d3e86792111c2950c7b379 Mon Sep 17 00:00:00 2001
From: David Fetter <david.fetter@onelogin.com>
Date: Wed, 11 Dec 2019 18:55:03 -0800
Subject: [PATCH v3] Enable setting pg_hba.conf permissions from initdb
To: hackers
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="------------2.24.1"

This is a multi-part message in MIME format.
--------------2.24.1
Content-Type: text/plain; charset=UTF-8; format=fixed
Content-Transfer-Encoding: 8bit


The new optional arguments are --auth-hostssl, --auth-hostnossl,
--auth-hostgssenc, and --auth-hostnogssenc.

diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgml
index da5c8f5307..4126880bcb 100644
--- a/doc/src/sgml/ref/initdb.sgml
+++ b/doc/src/sgml/ref/initdb.sgml
@@ -154,6 +154,50 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--auth-hostssl=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        TLS remote connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostssl</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>--auth-hostnossl=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        non-TLS remote connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostnossl</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>--auth-hostgssenc=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        encrypted GSSAPI remote connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostgssenc</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>--auth-hostnogssenc=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        remote connections not using encrypted GSSAPI in <filename>pg_hba.conf</filename>
+        (<literal>hostnogssenc</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--auth-local=<replaceable class="parameter">authmethod</replaceable></option></term>
       <listitem>
diff --git a/src/backend/libpq/pg_hba.conf.sample b/src/backend/libpq/pg_hba.conf.sample
index c853e36232..7d5189dd8f 100644
--- a/src/backend/libpq/pg_hba.conf.sample
+++ b/src/backend/libpq/pg_hba.conf.sample
@@ -87,3 +87,15 @@ host    all             all             ::1/128                 @authmethodhost@
 @remove-line-for-nolocal@local   replication     all                                     @authmethodlocal@
 host    replication     all             127.0.0.1/32            @authmethodhost@
 host    replication     all             ::1/128                 @authmethodhost@
+
+# hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@
+# hostnossl all           all             ::/0                    @authmethodhostnossl@
+
+# hostssl all             all             0.0.0.0/0               @authmethodhostssl@
+# hostssl all             all             ::/0                    @authmethodhostssl@
+
+# hostnogssenc all           all             0.0.0.0/0               @authmethodhostnogssenc@
+# hostnogssenc all           all             ::/0                    @authmethodhostnogssenc@
+
+# hostgssenc all             all             0.0.0.0/0               @authmethodhostgssenc@
+# hostgssenc all             all             ::/0                    @authmethodhostgssenc@
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index ec6d0bdf8e..982b1ba0b8 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -136,6 +136,10 @@ static char *pwfilename = NULL;
 static char *superuser_password = NULL;
 static const char *authmethodhost = NULL;
 static const char *authmethodlocal = NULL;
+static const char *authmethodhostssl = NULL;
+static const char *authmethodhostnossl = NULL;
+static const char *authmethodhostgssenc = NULL;
+static const char *authmethodhostnogssenc = NULL;
 static bool debug = false;
 static bool noclean = false;
 static bool do_sync = true;
@@ -181,10 +185,12 @@ static const char *default_timezone = NULL;
  * Warning messages for authentication methods
  */
 #define AUTHTRUST_WARNING \
-"# CAUTION: Configuring the system for local \"trust\" authentication\n" \
-"# allows any local user to connect as any PostgreSQL user, including\n" \
-"# the database superuser.  If you do not trust all your local users,\n" \
-"# use another authentication method.\n"
+"# CAUTION: Configuring the system for \"trust\" authentication\n" \
+"# allows any user who can reach the databse on the route specified\n" \
+"# to connect as any PostgreSQL user, including the database\n" \
+"# superuser.  If you do not trust all the users who could\n" \
+"# reach the database on the route specified, use a more restrictive\n" \
+"# authentication method.\n"
 static bool authwarning = false;
 
 /*
@@ -438,7 +444,6 @@ replace_token(char **lines, const char *token, const char *replacement)
  *
  * a sort of poor man's grep -v
  */
-#ifndef HAVE_UNIX_SOCKETS
 static char **
 filter_lines_with_token(char **lines, const char *token)
 {
@@ -461,7 +466,6 @@ filter_lines_with_token(char **lines, const char *token)
 
 	return result;
 }
-#endif
 
 /*
  * get the lines from a text file
@@ -1322,9 +1326,97 @@ setup_config(void)
 							  "@authmethodlocal@",
 							  authmethodlocal);
 
+	authwarning = (
+			strcmp(authmethodlocal, "trust") == 0 ||
+			strcmp(authmethodhost, "trust") == 0 ||
+			(authmethodhostssl != NULL &&
+			 strcmp(authmethodhostssl, "trust") == 0) ||
+			(authmethodhostnossl != NULL &&
+			 strcmp(authmethodhostnossl, "trust") == 0) ||
+			(authmethodhostgssenc != NULL &&
+			 strcmp(authmethodhostgssenc, "trust") == 0) ||
+			(authmethodhostnogssenc != NULL &&
+			 strcmp(authmethodhostnogssenc, "trust")) == 0);
+
 	conflines = replace_token(conflines,
 							  "@authcomment@",
-							  (strcmp(authmethodlocal, "trust") == 0 || strcmp(authmethodhost, "trust") == 0) ? AUTHTRUST_WARNING : "");
+							   authwarning ? AUTHTRUST_WARNING : "");
+
+	if (authmethodhostssl != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostssl all             all             0.0.0.0/0               @authmethodhostssl@",
+								  "hostssl all             all             0.0.0.0/0               @authmethodhostssl@");
+#ifdef HAVE_IPV6
+		conflines = replace_token(conflines,
+								  "# hostssl all             all             ::/0                    @authmethodhostssl@",
+								  "hostssl all             all             ::/0                    @authmethodhostssl@");
+#endif
+		conflines = replace_token(conflines,
+								  "@authmethodhostssl@",
+								  authmethodhostssl);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostssl@");
+	}
+
+	if (authmethodhostnossl != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@",
+								  "hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@");
+#ifdef HAVE_IPV6
+		conflines = replace_token(conflines,
+								  "# hostnossl all           all             ::/0                    @authmethodhostnossl@",
+								  "hostnossl all           all             ::/0                    @authmethodhostnossl@");
+#endif
+		conflines = replace_token(conflines,
+								  "@authmethodhostnossl@",
+								  authmethodhostnossl);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostnossl@");
+	}
+
+	if (authmethodhostgssenc != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostgssenc all             all             0.0.0.0/0               @authmethodhostgssenc@",
+								  "hostgssenc all             all             0.0.0.0/0               @authmethodhostgssenc@");
+#ifdef HAVE_IPV6
+		conflines = replace_token(conflines,
+								  "# hostgssenc all             all             ::/0                    @authmethodhostgssenc@",
+								  "hostgssenc all             all             ::/0                    @authmethodhostgssenc@");
+#endif
+		conflines = replace_token(conflines,
+								  "@authmethodhostgssenc@",
+								  authmethodhostgssenc);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostgssenc@");
+	}
+
+	if (authmethodhostnogssenc != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostnogssenc all           all             0.0.0.0/0               @authmethodhostnogssenc@",
+								  "hostnogssenc all           all             0.0.0.0/0               @authmethodhostnogssenc@");
+#ifdef HAVE_IPV6
+		conflines = replace_token(conflines,
+								  "# hostnogssenc all           all             ::/0                    @authmethodhostnogssenc@",
+								  "hostnogssenc all           all             ::/0                    @authmethodhostnogssenc@");
+#endif
+		conflines = replace_token(conflines,
+								  "@authmethodhostnogssenc@",
+								  authmethodhostnogssenc);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostnogssenc@");
+	}
 
 	snprintf(path, sizeof(path), "%s/pg_hba.conf", pg_data);
 
@@ -2327,36 +2419,40 @@ usage(const char *progname)
 	printf(_("Usage:\n"));
 	printf(_("  %s [OPTION]... [DATADIR]\n"), progname);
 	printf(_("\nOptions:\n"));
-	printf(_("  -A, --auth=METHOD         default authentication method for local connections\n"));
-	printf(_("      --auth-host=METHOD    default authentication method for local TCP/IP connections\n"));
-	printf(_("      --auth-local=METHOD   default authentication method for local-socket connections\n"));
-	printf(_(" [-D, --pgdata=]DATADIR     location for this database cluster\n"));
-	printf(_("  -E, --encoding=ENCODING   set default encoding for new databases\n"));
-	printf(_("  -g, --allow-group-access  allow group read/execute on data directory\n"));
-	printf(_("      --locale=LOCALE       set default locale for new databases\n"));
+	printf(_("  -A, --auth=METHOD              default authentication method for local connections\n"));
+	printf(_("      --auth-host=METHOD         default authentication method for local TCP/IP connections\n"));
+	printf(_("      --auth-local=METHOD        default authentication method for local-socket connections\n"));
+	printf(_("      --auth-hostssl=METHOD      default authentication method for TLS connections\n"));
+	printf(_("      --auth-hostnossl=METHOD    default authentication method for non-TLS connections\n"));
+	printf(_("      --auth-hostgssenc=METHOD   default authentication method for encrypted GSSAPI connections\n"));
+	printf(_("      --auth-hostnogssenc=METHOD default authentication method for connections not using encrypted GSSAPI\n"));
+	printf(_(" [-D, --pgdata=]DATADIR          location for this database cluster\n"));
+	printf(_("  -E, --encoding=ENCODING        set default encoding for new databases\n"));
+	printf(_("  -g, --allow-group-access       allow group read/execute on data directory\n"));
+	printf(_("      --locale=LOCALE            set default locale for new databases\n"));
 	printf(_("      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE\n"
 			 "      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE\n"
-			 "                            set default locale in the respective category for\n"
-			 "                            new databases (default taken from environment)\n"));
-	printf(_("      --no-locale           equivalent to --locale=C\n"));
-	printf(_("      --pwfile=FILE         read password for the new superuser from file\n"));
+			 "                                 set default locale in the respective category for\n"
+			 "                                 new databases (default taken from environment)\n"));
+	printf(_("      --no-locale                equivalent to --locale=C\n"));
+	printf(_("      --pwfile=FILE              read password for the new superuser from file\n"));
 	printf(_("  -T, --text-search-config=CFG\n"
-			 "                            default text search configuration\n"));
-	printf(_("  -U, --username=NAME       database superuser name\n"));
-	printf(_("  -W, --pwprompt            prompt for a password for the new superuser\n"));
-	printf(_("  -X, --waldir=WALDIR       location for the write-ahead log directory\n"));
-	printf(_("      --wal-segsize=SIZE    size of WAL segments, in megabytes\n"));
+			 "                                 default text search configuration\n"));
+	printf(_("  -U, --username=NAME            database superuser name\n"));
+	printf(_("  -W, --pwprompt                 prompt for a password for the new superuser\n"));
+	printf(_("  -X, --waldir=WALDIR            location for the write-ahead log directory\n"));
+	printf(_("      --wal-segsize=SIZE         size of WAL segments, in megabytes\n"));
 	printf(_("\nLess commonly used options:\n"));
-	printf(_("  -d, --debug               generate lots of debugging output\n"));
-	printf(_("  -k, --data-checksums      use data page checksums\n"));
-	printf(_("  -L DIRECTORY              where to find the input files\n"));
-	printf(_("  -n, --no-clean            do not clean up after errors\n"));
-	printf(_("  -N, --no-sync             do not wait for changes to be written safely to disk\n"));
-	printf(_("  -s, --show                show internal settings\n"));
-	printf(_("  -S, --sync-only           only sync data directory\n"));
+	printf(_("  -d, --debug                    generate lots of debugging output\n"));
+	printf(_("  -k, --data-checksums           use data page checksums\n"));
+	printf(_("  -L DIRECTORY                   where to find the input files\n"));
+	printf(_("  -n, --no-clean                 do not clean up after errors\n"));
+	printf(_("  -N, --no-sync                  do not wait for changes to be written safely to disk\n"));
+	printf(_("  -s, --show                     show internal settings\n"));
+	printf(_("  -S, --sync-only                only sync data directory\n"));
 	printf(_("\nOther options:\n"));
-	printf(_("  -V, --version             output version information, then exit\n"));
-	printf(_("  -?, --help                show this help, then exit\n"));
+	printf(_("  -V, --version                  output version information, then exit\n"));
+	printf(_("  -?, --help                     show this help, then exit\n"));
 	printf(_("\nIf the data directory is not specified, the environment variable PGDATA\n"
 			 "is used.\n"));
 	printf(_("\nReport bugs to <pgsql-bugs@lists.postgresql.org>.\n"));
@@ -3010,6 +3106,10 @@ main(int argc, char *argv[])
 		{"auth", required_argument, NULL, 'A'},
 		{"auth-local", required_argument, NULL, 10},
 		{"auth-host", required_argument, NULL, 11},
+		{"auth-hostssl", required_argument, NULL, 13},
+		{"auth-hostnossl", required_argument, NULL, 14},
+		{"auth-hostgssenc", required_argument, NULL, 15},
+		{"auth-hostnogssenc", required_argument, NULL, 16},
 		{"pwprompt", no_argument, NULL, 'W'},
 		{"pwfile", required_argument, NULL, 9},
 		{"username", required_argument, NULL, 'U'},
@@ -3090,6 +3190,18 @@ main(int argc, char *argv[])
 			case 11:
 				authmethodhost = pg_strdup(optarg);
 				break;
+			case 13:
+				authmethodhostssl = pg_strdup(optarg);
+				break;
+			case 14:
+				authmethodhostnossl = pg_strdup(optarg);
+				break;
+			case 15:
+				authmethodhostgssenc = pg_strdup(optarg);
+				break;
+			case 16:
+				authmethodhostnogssenc = pg_strdup(optarg);
+				break;
 			case 'D':
 				pg_data = pg_strdup(optarg);
 				break;
@@ -3224,6 +3336,14 @@ main(int argc, char *argv[])
 
 	check_authmethod_valid(authmethodlocal, auth_methods_local, "local");
 	check_authmethod_valid(authmethodhost, auth_methods_host, "host");
+	if (authmethodhostssl != NULL)
+		check_authmethod_valid(authmethodhostssl, auth_methods_host, "hostssl");
+	if (authmethodhostnossl != NULL)
+		check_authmethod_valid(authmethodhostnossl, auth_methods_host, "hostnossl");
+	if (authmethodhostgssenc != NULL)
+		check_authmethod_valid(authmethodhostgssenc, auth_methods_host, "hostgssenc");
+	if (authmethodhostnogssenc != NULL)
+		check_authmethod_valid(authmethodhostnogssenc, auth_methods_host, "hostnogssenc");
 
 	check_need_password(authmethodlocal, authmethodhost);
 
@@ -3306,9 +3426,9 @@ main(int argc, char *argv[])
 	if (authwarning)
 	{
 		printf("\n");
-		pg_log_warning("enabling \"trust\" authentication for local connections");
+		pg_log_warning("enabling \"trust\" authentication");
 		fprintf(stderr, _("You can change this by editing pg_hba.conf or using the option -A, or\n"
-						  "--auth-local and --auth-host, the next time you run initdb.\n"));
+						  "the various options that start with --auth, the next time you run initdb.\n"));
 	}
 
 	/*

--------------2.24.1--

Cary Huang

cary.huang@highgo.ca

almost 6 years ago

In reply to: David Fetter (#8)

Re: Let people set host(no)ssl settings from initdb

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: tested, passed

Hi
I applied the patch "v3-0001-Enable-setting-pg_hba.conf-permissions-from-initd.patch" and did some verification with it. The intended feature works overall and I think it is quite useful to support default auth methods for ssl and gss host types. I have also found some minor things in the patch and would like to share as below:

+"# CAUTION: Configuring the system for \"trust\" authentication\n" \
+"# allows any user who can reach the databse on the route specified\n" \
+"# to connect as any PostgreSQL user, including the database\n" \
+"# superuser.  If you do not trust all the users who could\n" \
+"# reach the database on the route specified, use a more restrictive\n" \
+"# authentication method.\n"

Found a typo: should be 'database' instead of 'databse'

* a sort of poor man's grep -v
*/
-#ifndef HAVE_UNIX_SOCKETS
static char **
filter_lines_with_token(char **lines, const char *token)
{
@@ -461,7 +466,6 @@ filter_lines_with_token(char **lines, const char *token)

return result;
}
-#endif

I see that you have removed "#ifndef HAVE_UNIX_SOCKETS" around the filter_lines_with_token() function definition so it would be always available, which is used to remove the @tokens@ in case user does not specify a default auth method for the new hostssl, hostgss options. I think you should also remove the "#ifndef HAVE_UNIX_SOCKETS" around its declaration as well so both function definition and declaration would make sense.

#ifndef HAVE_UNIX_SOCKETS
static char **filter_lines_with_token(char **lines, const char *token);
#endif

Cary Huang
-------------
HighGo Software Inc. (Canada)
cary.huang@highgo.ca
www.highgo.ca

#10

David Fetter

david@fetter.org

almost 6 years ago

In reply to: Cary Huang (#9)

1 attachment(s)

Re: Let people set host(no)ssl settings from initdb

On Mon, Apr 06, 2020 at 10:12:16PM +0000, Cary Huang wrote:

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: tested, passed

Hi
I applied the patch "v3-0001-Enable-setting-pg_hba.conf-permissions-from-initd.patch" and did some verification with it. The intended feature works overall and I think it is quite useful to support default auth methods for ssl and gss host types. I have also found some minor things in the patch and would like to share as below:
+"# CAUTION: Configuring the system for \"trust\" authentication\n" \
+"# allows any user who can reach the databse on the route specified\n" \
+"# to connect as any PostgreSQL user, including the database\n" \
+"# superuser.  If you do not trust all the users who could\n" \
+"# reach the database on the route specified, use a more restrictive\n" \
+"# authentication method.\n"
Found a typo: should be 'database' instead of 'databse'

Fixed.

* a sort of poor man's grep -v
*/
-#ifndef HAVE_UNIX_SOCKETS
static char **
filter_lines_with_token(char **lines, const char *token)
{
@@ -461,7 +466,6 @@ filter_lines_with_token(char **lines, const char *token)

return result;
}
-#endif

I see that you have removed "#ifndef HAVE_UNIX_SOCKETS" around the
filter_lines_with_token() function definition so it would be always
available, which is used to remove the @tokens@ in case user does
not specify a default auth method for the new hostssl, hostgss
options. I think you should also remove the "#ifndef
HAVE_UNIX_SOCKETS" around its declaration as well so both function
definition and declaration would make sense.

Fixed.

Thanks very much for the review!

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Attachments:

v4-0001-Enable-setting-pg_hba.conf-permissions-from-initd.patchtext/x-diff; charset=us-asciiDownload

From 3b49afec7f0b708285eadc10a097b4dc23d23927 Mon Sep 17 00:00:00 2001
From: David Fetter <david.fetter@onelogin.com>
Date: Wed, 11 Dec 2019 18:55:03 -0800
Subject: [PATCH v4] Enable setting pg_hba.conf permissions from initdb
To: hackers
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="------------2.25.2"

This is a multi-part message in MIME format.
--------------2.25.2
Content-Type: text/plain; charset=UTF-8; format=fixed
Content-Transfer-Encoding: 8bit


The new optional arguments are --auth-hostssl, --auth-hostnossl,
--auth-hostgssenc, and --auth-hostnogssenc.

diff --git doc/src/sgml/ref/initdb.sgml doc/src/sgml/ref/initdb.sgml
index a04a180165..ee65cf925d 100644
--- doc/src/sgml/ref/initdb.sgml
+++ doc/src/sgml/ref/initdb.sgml
@@ -154,6 +154,50 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--auth-hostssl=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        TLS remote connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostssl</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>--auth-hostnossl=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        non-TLS remote connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostnossl</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>--auth-hostgssenc=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        encrypted GSSAPI remote connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostgssenc</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>--auth-hostnogssenc=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        remote connections not using encrypted GSSAPI in <filename>pg_hba.conf</filename>
+        (<literal>hostnogssenc</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--auth-local=<replaceable class="parameter">authmethod</replaceable></option></term>
       <listitem>
diff --git src/backend/libpq/pg_hba.conf.sample src/backend/libpq/pg_hba.conf.sample
index c853e36232..7d5189dd8f 100644
--- src/backend/libpq/pg_hba.conf.sample
+++ src/backend/libpq/pg_hba.conf.sample
@@ -87,3 +87,15 @@ host    all             all             ::1/128                 @authmethodhost@
 @remove-line-for-nolocal@local   replication     all                                     @authmethodlocal@
 host    replication     all             127.0.0.1/32            @authmethodhost@
 host    replication     all             ::1/128                 @authmethodhost@
+
+# hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@
+# hostnossl all           all             ::/0                    @authmethodhostnossl@
+
+# hostssl all             all             0.0.0.0/0               @authmethodhostssl@
+# hostssl all             all             ::/0                    @authmethodhostssl@
+
+# hostnogssenc all           all             0.0.0.0/0               @authmethodhostnogssenc@
+# hostnogssenc all           all             ::/0                    @authmethodhostnogssenc@
+
+# hostgssenc all             all             0.0.0.0/0               @authmethodhostgssenc@
+# hostgssenc all             all             ::/0                    @authmethodhostgssenc@
diff --git src/bin/initdb/initdb.c src/bin/initdb/initdb.c
index a6577486ce..ee2776711d 100644
--- src/bin/initdb/initdb.c
+++ src/bin/initdb/initdb.c
@@ -136,6 +136,10 @@ static char *pwfilename = NULL;
 static char *superuser_password = NULL;
 static const char *authmethodhost = NULL;
 static const char *authmethodlocal = NULL;
+static const char *authmethodhostssl = NULL;
+static const char *authmethodhostnossl = NULL;
+static const char *authmethodhostgssenc = NULL;
+static const char *authmethodhostnogssenc = NULL;
 static bool debug = false;
 static bool noclean = false;
 static bool do_sync = true;
@@ -179,10 +183,12 @@ static const char *default_timezone = NULL;
  * Warning messages for authentication methods
  */
 #define AUTHTRUST_WARNING \
-"# CAUTION: Configuring the system for local \"trust\" authentication\n" \
-"# allows any local user to connect as any PostgreSQL user, including\n" \
-"# the database superuser.  If you do not trust all your local users,\n" \
-"# use another authentication method.\n"
+"# CAUTION: Configuring the system for \"trust\" authentication\n" \
+"# allows any user who can reach the database on the route specified\n" \
+"# to connect as any PostgreSQL user, including the database\n" \
+"# superuser.  If you do not trust all the users who could\n" \
+"# reach the database on the route specified, use a more restrictive\n" \
+"# authentication method.\n"
 static bool authwarning = false;
 
 /*
@@ -231,9 +237,7 @@ static char backend_exec[MAXPGPATH];
 static char **replace_token(char **lines,
 							const char *token, const char *replacement);
 
-#ifndef HAVE_UNIX_SOCKETS
 static char **filter_lines_with_token(char **lines, const char *token);
-#endif
 static char **readfile(const char *path);
 static void writefile(char *path, char **lines);
 static FILE *popen_check(const char *command, const char *mode);
@@ -436,7 +440,6 @@ replace_token(char **lines, const char *token, const char *replacement)
  *
  * a sort of poor man's grep -v
  */
-#ifndef HAVE_UNIX_SOCKETS
 static char **
 filter_lines_with_token(char **lines, const char *token)
 {
@@ -459,7 +462,6 @@ filter_lines_with_token(char **lines, const char *token)
 
 	return result;
 }
-#endif
 
 /*
  * get the lines from a text file
@@ -1320,9 +1322,97 @@ setup_config(void)
 							  "@authmethodlocal@",
 							  authmethodlocal);
 
+	authwarning = (
+			strcmp(authmethodlocal, "trust") == 0 ||
+			strcmp(authmethodhost, "trust") == 0 ||
+			(authmethodhostssl != NULL &&
+			 strcmp(authmethodhostssl, "trust") == 0) ||
+			(authmethodhostnossl != NULL &&
+			 strcmp(authmethodhostnossl, "trust") == 0) ||
+			(authmethodhostgssenc != NULL &&
+			 strcmp(authmethodhostgssenc, "trust") == 0) ||
+			(authmethodhostnogssenc != NULL &&
+			 strcmp(authmethodhostnogssenc, "trust")) == 0);
+
 	conflines = replace_token(conflines,
 							  "@authcomment@",
-							  (strcmp(authmethodlocal, "trust") == 0 || strcmp(authmethodhost, "trust") == 0) ? AUTHTRUST_WARNING : "");
+							   authwarning ? AUTHTRUST_WARNING : "");
+
+	if (authmethodhostssl != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostssl all             all             0.0.0.0/0               @authmethodhostssl@",
+								  "hostssl all             all             0.0.0.0/0               @authmethodhostssl@");
+#ifdef HAVE_IPV6
+		conflines = replace_token(conflines,
+								  "# hostssl all             all             ::/0                    @authmethodhostssl@",
+								  "hostssl all             all             ::/0                    @authmethodhostssl@");
+#endif
+		conflines = replace_token(conflines,
+								  "@authmethodhostssl@",
+								  authmethodhostssl);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostssl@");
+	}
+
+	if (authmethodhostnossl != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@",
+								  "hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@");
+#ifdef HAVE_IPV6
+		conflines = replace_token(conflines,
+								  "# hostnossl all           all             ::/0                    @authmethodhostnossl@",
+								  "hostnossl all           all             ::/0                    @authmethodhostnossl@");
+#endif
+		conflines = replace_token(conflines,
+								  "@authmethodhostnossl@",
+								  authmethodhostnossl);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostnossl@");
+	}
+
+	if (authmethodhostgssenc != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostgssenc all             all             0.0.0.0/0               @authmethodhostgssenc@",
+								  "hostgssenc all             all             0.0.0.0/0               @authmethodhostgssenc@");
+#ifdef HAVE_IPV6
+		conflines = replace_token(conflines,
+								  "# hostgssenc all             all             ::/0                    @authmethodhostgssenc@",
+								  "hostgssenc all             all             ::/0                    @authmethodhostgssenc@");
+#endif
+		conflines = replace_token(conflines,
+								  "@authmethodhostgssenc@",
+								  authmethodhostgssenc);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostgssenc@");
+	}
+
+	if (authmethodhostnogssenc != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostnogssenc all           all             0.0.0.0/0               @authmethodhostnogssenc@",
+								  "hostnogssenc all           all             0.0.0.0/0               @authmethodhostnogssenc@");
+#ifdef HAVE_IPV6
+		conflines = replace_token(conflines,
+								  "# hostnogssenc all           all             ::/0                    @authmethodhostnogssenc@",
+								  "hostnogssenc all           all             ::/0                    @authmethodhostnogssenc@");
+#endif
+		conflines = replace_token(conflines,
+								  "@authmethodhostnogssenc@",
+								  authmethodhostnogssenc);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostnogssenc@");
+	}
 
 	snprintf(path, sizeof(path), "%s/pg_hba.conf", pg_data);
 
@@ -2290,36 +2380,40 @@ usage(const char *progname)
 	printf(_("Usage:\n"));
 	printf(_("  %s [OPTION]... [DATADIR]\n"), progname);
 	printf(_("\nOptions:\n"));
-	printf(_("  -A, --auth=METHOD         default authentication method for local connections\n"));
-	printf(_("      --auth-host=METHOD    default authentication method for local TCP/IP connections\n"));
-	printf(_("      --auth-local=METHOD   default authentication method for local-socket connections\n"));
-	printf(_(" [-D, --pgdata=]DATADIR     location for this database cluster\n"));
-	printf(_("  -E, --encoding=ENCODING   set default encoding for new databases\n"));
-	printf(_("  -g, --allow-group-access  allow group read/execute on data directory\n"));
-	printf(_("      --locale=LOCALE       set default locale for new databases\n"));
+	printf(_("  -A, --auth=METHOD              default authentication method for local connections\n"));
+	printf(_("      --auth-host=METHOD         default authentication method for local TCP/IP connections\n"));
+	printf(_("      --auth-local=METHOD        default authentication method for local-socket connections\n"));
+	printf(_("      --auth-hostssl=METHOD      default authentication method for TLS connections\n"));
+	printf(_("      --auth-hostnossl=METHOD    default authentication method for non-TLS connections\n"));
+	printf(_("      --auth-hostgssenc=METHOD   default authentication method for encrypted GSSAPI connections\n"));
+	printf(_("      --auth-hostnogssenc=METHOD default authentication method for connections not using encrypted GSSAPI\n"));
+	printf(_(" [-D, --pgdata=]DATADIR          location for this database cluster\n"));
+	printf(_("  -E, --encoding=ENCODING        set default encoding for new databases\n"));
+	printf(_("  -g, --allow-group-access       allow group read/execute on data directory\n"));
+	printf(_("      --locale=LOCALE            set default locale for new databases\n"));
 	printf(_("      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE\n"
 			 "      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE\n"
-			 "                            set default locale in the respective category for\n"
-			 "                            new databases (default taken from environment)\n"));
-	printf(_("      --no-locale           equivalent to --locale=C\n"));
-	printf(_("      --pwfile=FILE         read password for the new superuser from file\n"));
+			 "                                 set default locale in the respective category for\n"
+			 "                                 new databases (default taken from environment)\n"));
+	printf(_("      --no-locale                equivalent to --locale=C\n"));
+	printf(_("      --pwfile=FILE              read password for the new superuser from file\n"));
 	printf(_("  -T, --text-search-config=CFG\n"
-			 "                            default text search configuration\n"));
-	printf(_("  -U, --username=NAME       database superuser name\n"));
-	printf(_("  -W, --pwprompt            prompt for a password for the new superuser\n"));
-	printf(_("  -X, --waldir=WALDIR       location for the write-ahead log directory\n"));
-	printf(_("      --wal-segsize=SIZE    size of WAL segments, in megabytes\n"));
+			 "                                 default text search configuration\n"));
+	printf(_("  -U, --username=NAME            database superuser name\n"));
+	printf(_("  -W, --pwprompt                 prompt for a password for the new superuser\n"));
+	printf(_("  -X, --waldir=WALDIR            location for the write-ahead log directory\n"));
+	printf(_("      --wal-segsize=SIZE         size of WAL segments, in megabytes\n"));
 	printf(_("\nLess commonly used options:\n"));
-	printf(_("  -d, --debug               generate lots of debugging output\n"));
-	printf(_("  -k, --data-checksums      use data page checksums\n"));
-	printf(_("  -L DIRECTORY              where to find the input files\n"));
-	printf(_("  -n, --no-clean            do not clean up after errors\n"));
-	printf(_("  -N, --no-sync             do not wait for changes to be written safely to disk\n"));
-	printf(_("  -s, --show                show internal settings\n"));
-	printf(_("  -S, --sync-only           only sync data directory\n"));
+	printf(_("  -d, --debug                    generate lots of debugging output\n"));
+	printf(_("  -k, --data-checksums           use data page checksums\n"));
+	printf(_("  -L DIRECTORY                   where to find the input files\n"));
+	printf(_("  -n, --no-clean                 do not clean up after errors\n"));
+	printf(_("  -N, --no-sync                  do not wait for changes to be written safely to disk\n"));
+	printf(_("  -s, --show                     show internal settings\n"));
+	printf(_("  -S, --sync-only                only sync data directory\n"));
 	printf(_("\nOther options:\n"));
-	printf(_("  -V, --version             output version information, then exit\n"));
-	printf(_("  -?, --help                show this help, then exit\n"));
+	printf(_("  -V, --version                  output version information, then exit\n"));
+	printf(_("  -?, --help                     show this help, then exit\n"));
 	printf(_("\nIf the data directory is not specified, the environment variable PGDATA\n"
 			 "is used.\n"));
 	printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
@@ -2968,6 +3062,10 @@ main(int argc, char *argv[])
 		{"auth", required_argument, NULL, 'A'},
 		{"auth-local", required_argument, NULL, 10},
 		{"auth-host", required_argument, NULL, 11},
+		{"auth-hostssl", required_argument, NULL, 13},
+		{"auth-hostnossl", required_argument, NULL, 14},
+		{"auth-hostgssenc", required_argument, NULL, 15},
+		{"auth-hostnogssenc", required_argument, NULL, 16},
 		{"pwprompt", no_argument, NULL, 'W'},
 		{"pwfile", required_argument, NULL, 9},
 		{"username", required_argument, NULL, 'U'},
@@ -3048,6 +3146,18 @@ main(int argc, char *argv[])
 			case 11:
 				authmethodhost = pg_strdup(optarg);
 				break;
+			case 13:
+				authmethodhostssl = pg_strdup(optarg);
+				break;
+			case 14:
+				authmethodhostnossl = pg_strdup(optarg);
+				break;
+			case 15:
+				authmethodhostgssenc = pg_strdup(optarg);
+				break;
+			case 16:
+				authmethodhostnogssenc = pg_strdup(optarg);
+				break;
 			case 'D':
 				pg_data = pg_strdup(optarg);
 				break;
@@ -3182,6 +3292,14 @@ main(int argc, char *argv[])
 
 	check_authmethod_valid(authmethodlocal, auth_methods_local, "local");
 	check_authmethod_valid(authmethodhost, auth_methods_host, "host");
+	if (authmethodhostssl != NULL)
+		check_authmethod_valid(authmethodhostssl, auth_methods_host, "hostssl");
+	if (authmethodhostnossl != NULL)
+		check_authmethod_valid(authmethodhostnossl, auth_methods_host, "hostnossl");
+	if (authmethodhostgssenc != NULL)
+		check_authmethod_valid(authmethodhostgssenc, auth_methods_host, "hostgssenc");
+	if (authmethodhostnogssenc != NULL)
+		check_authmethod_valid(authmethodhostnogssenc, auth_methods_host, "hostnogssenc");
 
 	check_need_password(authmethodlocal, authmethodhost);
 
@@ -3264,9 +3382,9 @@ main(int argc, char *argv[])
 	if (authwarning)
 	{
 		printf("\n");
-		pg_log_warning("enabling \"trust\" authentication for local connections");
+		pg_log_warning("enabling \"trust\" authentication");
 		fprintf(stderr, _("You can change this by editing pg_hba.conf or using the option -A, or\n"
-						  "--auth-local and --auth-host, the next time you run initdb.\n"));
+						  "the various options that start with --auth, the next time you run initdb.\n"));
 	}
 
 	/*

--------------2.25.2--

#11

Daniel Gustafsson

daniel@yesql.se

over 5 years ago

In reply to: David Fetter (#10)

Re: Let people set host(no)ssl settings from initdb

The CF Patch Tester consider this patch to be malformed and is unable to apply
and test it. Can you please submit a rebased version?

cheers ./daniel

#12

Michael Paquier

michael@paquier.xyz

over 5 years ago

In reply to: Daniel Gustafsson (#11)

Re: Let people set host(no)ssl settings from initdb

On Thu, Jul 02, 2020 at 04:02:21PM +0200, Daniel Gustafsson wrote:

The CF Patch Tester consider this patch to be malformed and is unable to apply
and test it. Can you please submit a rebased version?

I have looked at the patch of this thread, and I doubt that it is a
good idea to put more burden into initdb for that. I agree that
being able to reject easily non-SSL connections in pg_hba.conf is a
bit of a hassle now, but putting more logic into initdb does not seem
the right course to me. Perhaps we could consider an idea like
Peter's to have a sslmode=require on the server side and ease the
generation of HBA rules..

The patch has stalled for two months now without a rebase provided, so
I am marking it as returned with feedback.
--
Michael

#13

David Fetter

david@fetter.org

about 5 years ago

In reply to: Michael Paquier (#12)

Re: Let people set host(no)ssl settings from initdb

On Mon, Sep 07, 2020 at 11:57:58AM +0900, Michael Paquier wrote:

On Thu, Jul 02, 2020 at 04:02:21PM +0200, Daniel Gustafsson wrote:

The CF Patch Tester consider this patch to be malformed and is unable to apply
and test it. Can you please submit a rebased version?

I have looked at the patch of this thread, and I doubt that it is a
good idea to put more burden into initdb for that. I agree that
being able to reject easily non-SSL connections in pg_hba.conf is a
bit of a hassle now, but putting more logic into initdb does not seem
the right course to me. Perhaps we could consider an idea like
Peter's to have a sslmode=require on the server side and ease the
generation of HBA rules..

The patch has stalled for two months now without a rebase provided, so
I am marking it as returned with feedback.

Please find attached the rebased patch.

Peter's suggestion seems a little more subtle to me than requiring TLS
on the server side in that what people generally want to do is
disallow clear text connections entirely. In those scenarios, people
would also want to set (and be able to change at runtime) some kind of
cryptographic policy, as SSH and TLS do. While I see this as a worthy
goal, it's a much bigger lift than an optional argument or two to
initdb, and requires a lot more discussion than it's had to date.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

#14

David Fetter

david@fetter.org

about 5 years ago

In reply to: David Fetter (#13)

1 attachment(s)

Re: Let people set host(no)ssl settings from initdb

On Wed, Dec 30, 2020 at 08:24:06PM +0100, David Fetter wrote:

On Mon, Sep 07, 2020 at 11:57:58AM +0900, Michael Paquier wrote:

On Thu, Jul 02, 2020 at 04:02:21PM +0200, Daniel Gustafsson wrote:

The CF Patch Tester consider this patch to be malformed and is unable to apply
and test it. Can you please submit a rebased version?

I have looked at the patch of this thread, and I doubt that it is a
good idea to put more burden into initdb for that. I agree that
being able to reject easily non-SSL connections in pg_hba.conf is a
bit of a hassle now, but putting more logic into initdb does not seem
the right course to me. Perhaps we could consider an idea like
Peter's to have a sslmode=require on the server side and ease the
generation of HBA rules..

The patch has stalled for two months now without a rebase provided, so
I am marking it as returned with feedback.

Please find attached the rebased patch.

Peter's suggestion seems a little more subtle to me than requiring TLS
on the server side in that what people generally want to do is
disallow clear text connections entirely. In those scenarios, people
would also want to set (and be able to change at runtime) some kind of
cryptographic policy, as SSH and TLS do. While I see this as a worthy
goal, it's a much bigger lift than an optional argument or two to
initdb, and requires a lot more discussion than it's had to date.

*sigh*

This time with patch actually attached.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Attachments:

v5-0001-Enable-setting-pg_hba.conf-permissions-from-initd.patchtext/x-diff; charset=us-asciiDownload

From 537e58136890d17d33699e9965d0518d4c8a1822 Mon Sep 17 00:00:00 2001
From: David Fetter <david.fetter@onelogin.com>
Date: Wed, 11 Dec 2019 18:55:03 -0800
Subject: [PATCH v5] Enable setting pg_hba.conf permissions from initdb
To: hackers
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="------------2.29.2"

This is a multi-part message in MIME format.
--------------2.29.2
Content-Type: text/plain; charset=UTF-8; format=fixed
Content-Transfer-Encoding: 8bit


The new optional arguments are --auth-hostssl, --auth-hostnossl,
--auth-hostgssenc, and --auth-hostnogssenc.

diff --git doc/src/sgml/ref/initdb.sgml doc/src/sgml/ref/initdb.sgml
index 385ac25150..c63e2c316e 100644
--- doc/src/sgml/ref/initdb.sgml
+++ doc/src/sgml/ref/initdb.sgml
@@ -152,6 +152,50 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>--auth-hostssl=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        TLS remote connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostssl</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>--auth-hostnossl=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        non-TLS remote connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostnossl</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>--auth-hostgssenc=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        encrypted GSSAPI remote connections used in <filename>pg_hba.conf</filename>
+        (<literal>hostgssenc</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>--auth-hostnogssenc=<replaceable class="parameter">authmethod</replaceable></option></term>
+      <listitem>
+       <para>
+        This option specifies the authentication method for users via
+        remote connections not using encrypted GSSAPI in <filename>pg_hba.conf</filename>
+        (<literal>hostnogssenc</literal> lines).
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--auth-local=<replaceable class="parameter">authmethod</replaceable></option></term>
       <listitem>
diff --git src/backend/libpq/pg_hba.conf.sample src/backend/libpq/pg_hba.conf.sample
index b6de12b298..e132cf4db3 100644
--- src/backend/libpq/pg_hba.conf.sample
+++ src/backend/libpq/pg_hba.conf.sample
@@ -91,3 +91,15 @@ host    all             all             ::1/128                 @authmethodhost@
 @remove-line-for-nolocal@local   replication     all                                     @authmethodlocal@
 host    replication     all             127.0.0.1/32            @authmethodhost@
 host    replication     all             ::1/128                 @authmethodhost@
+
+# hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@
+# hostnossl all           all             ::/0                    @authmethodhostnossl@
+
+# hostssl all             all             0.0.0.0/0               @authmethodhostssl@
+# hostssl all             all             ::/0                    @authmethodhostssl@
+
+# hostnogssenc all           all             0.0.0.0/0               @authmethodhostnogssenc@
+# hostnogssenc all           all             ::/0                    @authmethodhostnogssenc@
+
+# hostgssenc all             all             0.0.0.0/0               @authmethodhostgssenc@
+# hostgssenc all             all             ::/0                    @authmethodhostgssenc@
diff --git src/bin/initdb/initdb.c src/bin/initdb/initdb.c
index 0865f73ee0..ddb5cc5c9c 100644
--- src/bin/initdb/initdb.c
+++ src/bin/initdb/initdb.c
@@ -137,6 +137,10 @@ static char *pwfilename = NULL;
 static char *superuser_password = NULL;
 static const char *authmethodhost = NULL;
 static const char *authmethodlocal = NULL;
+static const char *authmethodhostssl = NULL;
+static const char *authmethodhostnossl = NULL;
+static const char *authmethodhostgssenc = NULL;
+static const char *authmethodhostnogssenc = NULL;
 static bool debug = false;
 static bool noclean = false;
 static bool do_sync = true;
@@ -180,10 +184,12 @@ static const char *default_timezone = NULL;
  * Warning messages for authentication methods
  */
 #define AUTHTRUST_WARNING \
-"# CAUTION: Configuring the system for local \"trust\" authentication\n" \
-"# allows any local user to connect as any PostgreSQL user, including\n" \
-"# the database superuser.  If you do not trust all your local users,\n" \
-"# use another authentication method.\n"
+"# CAUTION: Configuring the system for \"trust\" authentication\n" \
+"# allows any user who can reach the database on the route specified\n" \
+"# to connect as any PostgreSQL user, including the database\n" \
+"# superuser.  If you do not trust all the users who could\n" \
+"# reach the database on the route specified, use a more restrictive\n" \
+"# authentication method.\n"
 static bool authwarning = false;
 
 /*
@@ -232,9 +238,7 @@ static char backend_exec[MAXPGPATH];
 static char **replace_token(char **lines,
 							const char *token, const char *replacement);
 
-#ifndef HAVE_UNIX_SOCKETS
 static char **filter_lines_with_token(char **lines, const char *token);
-#endif
 static char **readfile(const char *path);
 static void writefile(char *path, char **lines);
 static FILE *popen_check(const char *command, const char *mode);
@@ -417,7 +421,6 @@ replace_token(char **lines, const char *token, const char *replacement)
  *
  * a sort of poor man's grep -v
  */
-#ifndef HAVE_UNIX_SOCKETS
 static char **
 filter_lines_with_token(char **lines, const char *token)
 {
@@ -440,7 +443,6 @@ filter_lines_with_token(char **lines, const char *token)
 
 	return result;
 }
-#endif
 
 /*
  * get the lines from a text file
@@ -1299,9 +1301,97 @@ setup_config(void)
 							  "@authmethodlocal@",
 							  authmethodlocal);
 
+	authwarning = (
+			strcmp(authmethodlocal, "trust") == 0 ||
+			strcmp(authmethodhost, "trust") == 0 ||
+			(authmethodhostssl != NULL &&
+			 strcmp(authmethodhostssl, "trust") == 0) ||
+			(authmethodhostnossl != NULL &&
+			 strcmp(authmethodhostnossl, "trust") == 0) ||
+			(authmethodhostgssenc != NULL &&
+			 strcmp(authmethodhostgssenc, "trust") == 0) ||
+			(authmethodhostnogssenc != NULL &&
+			 strcmp(authmethodhostnogssenc, "trust")) == 0);
+
 	conflines = replace_token(conflines,
 							  "@authcomment@",
-							  (strcmp(authmethodlocal, "trust") == 0 || strcmp(authmethodhost, "trust") == 0) ? AUTHTRUST_WARNING : "");
+							   authwarning ? AUTHTRUST_WARNING : "");
+
+	if (authmethodhostssl != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostssl all             all             0.0.0.0/0               @authmethodhostssl@",
+								  "hostssl all             all             0.0.0.0/0               @authmethodhostssl@");
+#ifdef HAVE_IPV6
+		conflines = replace_token(conflines,
+								  "# hostssl all             all             ::/0                    @authmethodhostssl@",
+								  "hostssl all             all             ::/0                    @authmethodhostssl@");
+#endif
+		conflines = replace_token(conflines,
+								  "@authmethodhostssl@",
+								  authmethodhostssl);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostssl@");
+	}
+
+	if (authmethodhostnossl != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@",
+								  "hostnossl all           all             0.0.0.0/0               @authmethodhostnossl@");
+#ifdef HAVE_IPV6
+		conflines = replace_token(conflines,
+								  "# hostnossl all           all             ::/0                    @authmethodhostnossl@",
+								  "hostnossl all           all             ::/0                    @authmethodhostnossl@");
+#endif
+		conflines = replace_token(conflines,
+								  "@authmethodhostnossl@",
+								  authmethodhostnossl);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostnossl@");
+	}
+
+	if (authmethodhostgssenc != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostgssenc all             all             0.0.0.0/0               @authmethodhostgssenc@",
+								  "hostgssenc all             all             0.0.0.0/0               @authmethodhostgssenc@");
+#ifdef HAVE_IPV6
+		conflines = replace_token(conflines,
+								  "# hostgssenc all             all             ::/0                    @authmethodhostgssenc@",
+								  "hostgssenc all             all             ::/0                    @authmethodhostgssenc@");
+#endif
+		conflines = replace_token(conflines,
+								  "@authmethodhostgssenc@",
+								  authmethodhostgssenc);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostgssenc@");
+	}
+
+	if (authmethodhostnogssenc != NULL)
+	{
+		conflines = replace_token(conflines,
+								  "# hostnogssenc all           all             0.0.0.0/0               @authmethodhostnogssenc@",
+								  "hostnogssenc all           all             0.0.0.0/0               @authmethodhostnogssenc@");
+#ifdef HAVE_IPV6
+		conflines = replace_token(conflines,
+								  "# hostnogssenc all           all             ::/0                    @authmethodhostnogssenc@",
+								  "hostnogssenc all           all             ::/0                    @authmethodhostnogssenc@");
+#endif
+		conflines = replace_token(conflines,
+								  "@authmethodhostnogssenc@",
+								  authmethodhostnogssenc);
+	}
+	else
+	{
+		conflines = filter_lines_with_token(conflines, "@authmethodhostnogssenc@");
+	}
 
 	snprintf(path, sizeof(path), "%s/pg_hba.conf", pg_data);
 
@@ -2269,36 +2359,40 @@ usage(const char *progname)
 	printf(_("Usage:\n"));
 	printf(_("  %s [OPTION]... [DATADIR]\n"), progname);
 	printf(_("\nOptions:\n"));
-	printf(_("  -A, --auth=METHOD         default authentication method for local connections\n"));
-	printf(_("      --auth-host=METHOD    default authentication method for local TCP/IP connections\n"));
-	printf(_("      --auth-local=METHOD   default authentication method for local-socket connections\n"));
-	printf(_(" [-D, --pgdata=]DATADIR     location for this database cluster\n"));
-	printf(_("  -E, --encoding=ENCODING   set default encoding for new databases\n"));
-	printf(_("  -g, --allow-group-access  allow group read/execute on data directory\n"));
-	printf(_("      --locale=LOCALE       set default locale for new databases\n"));
+	printf(_("  -A, --auth=METHOD              default authentication method for local connections\n"));
+	printf(_("      --auth-host=METHOD         default authentication method for local TCP/IP connections\n"));
+	printf(_("      --auth-local=METHOD        default authentication method for local-socket connections\n"));
+	printf(_("      --auth-hostssl=METHOD      default authentication method for TLS connections\n"));
+	printf(_("      --auth-hostnossl=METHOD    default authentication method for non-TLS connections\n"));
+	printf(_("      --auth-hostgssenc=METHOD   default authentication method for encrypted GSSAPI connections\n"));
+	printf(_("      --auth-hostnogssenc=METHOD default authentication method for connections not using encrypted GSSAPI\n"));
+	printf(_(" [-D, --pgdata=]DATADIR          location for this database cluster\n"));
+	printf(_("  -E, --encoding=ENCODING        set default encoding for new databases\n"));
+	printf(_("  -g, --allow-group-access       allow group read/execute on data directory\n"));
+	printf(_("      --locale=LOCALE            set default locale for new databases\n"));
 	printf(_("      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE\n"
 			 "      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE\n"
-			 "                            set default locale in the respective category for\n"
-			 "                            new databases (default taken from environment)\n"));
-	printf(_("      --no-locale           equivalent to --locale=C\n"));
-	printf(_("      --pwfile=FILE         read password for the new superuser from file\n"));
+			 "                                 set default locale in the respective category for\n"
+			 "                                 new databases (default taken from environment)\n"));
+	printf(_("      --no-locale                equivalent to --locale=C\n"));
+	printf(_("      --pwfile=FILE              read password for the new superuser from file\n"));
 	printf(_("  -T, --text-search-config=CFG\n"
-			 "                            default text search configuration\n"));
-	printf(_("  -U, --username=NAME       database superuser name\n"));
-	printf(_("  -W, --pwprompt            prompt for a password for the new superuser\n"));
-	printf(_("  -X, --waldir=WALDIR       location for the write-ahead log directory\n"));
-	printf(_("      --wal-segsize=SIZE    size of WAL segments, in megabytes\n"));
+			 "                                 default text search configuration\n"));
+	printf(_("  -U, --username=NAME            database superuser name\n"));
+	printf(_("  -W, --pwprompt                 prompt for a password for the new superuser\n"));
+	printf(_("  -X, --waldir=WALDIR            location for the write-ahead log directory\n"));
+	printf(_("      --wal-segsize=SIZE         size of WAL segments, in megabytes\n"));
 	printf(_("\nLess commonly used options:\n"));
-	printf(_("  -d, --debug               generate lots of debugging output\n"));
-	printf(_("  -k, --data-checksums      use data page checksums\n"));
-	printf(_("  -L DIRECTORY              where to find the input files\n"));
-	printf(_("  -n, --no-clean            do not clean up after errors\n"));
-	printf(_("  -N, --no-sync             do not wait for changes to be written safely to disk\n"));
-	printf(_("  -s, --show                show internal settings\n"));
-	printf(_("  -S, --sync-only           only sync data directory\n"));
+	printf(_("  -d, --debug                    generate lots of debugging output\n"));
+	printf(_("  -k, --data-checksums           use data page checksums\n"));
+	printf(_("  -L DIRECTORY                   where to find the input files\n"));
+	printf(_("  -n, --no-clean                 do not clean up after errors\n"));
+	printf(_("  -N, --no-sync                  do not wait for changes to be written safely to disk\n"));
+	printf(_("  -s, --show                     show internal settings\n"));
+	printf(_("  -S, --sync-only                only sync data directory\n"));
 	printf(_("\nOther options:\n"));
-	printf(_("  -V, --version             output version information, then exit\n"));
-	printf(_("  -?, --help                show this help, then exit\n"));
+	printf(_("  -V, --version                  output version information, then exit\n"));
+	printf(_("  -?, --help                     show this help, then exit\n"));
 	printf(_("\nIf the data directory is not specified, the environment variable PGDATA\n"
 			 "is used.\n"));
 	printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
@@ -2944,6 +3038,10 @@ main(int argc, char *argv[])
 		{"auth", required_argument, NULL, 'A'},
 		{"auth-local", required_argument, NULL, 10},
 		{"auth-host", required_argument, NULL, 11},
+		{"auth-hostssl", required_argument, NULL, 13},
+		{"auth-hostnossl", required_argument, NULL, 14},
+		{"auth-hostgssenc", required_argument, NULL, 15},
+		{"auth-hostnogssenc", required_argument, NULL, 16},
 		{"pwprompt", no_argument, NULL, 'W'},
 		{"pwfile", required_argument, NULL, 9},
 		{"username", required_argument, NULL, 'U'},
@@ -3024,6 +3122,18 @@ main(int argc, char *argv[])
 			case 11:
 				authmethodhost = pg_strdup(optarg);
 				break;
+			case 13:
+				authmethodhostssl = pg_strdup(optarg);
+				break;
+			case 14:
+				authmethodhostnossl = pg_strdup(optarg);
+				break;
+			case 15:
+				authmethodhostgssenc = pg_strdup(optarg);
+				break;
+			case 16:
+				authmethodhostnogssenc = pg_strdup(optarg);
+				break;
 			case 'D':
 				pg_data = pg_strdup(optarg);
 				break;
@@ -3158,6 +3268,14 @@ main(int argc, char *argv[])
 
 	check_authmethod_valid(authmethodlocal, auth_methods_local, "local");
 	check_authmethod_valid(authmethodhost, auth_methods_host, "host");
+	if (authmethodhostssl != NULL)
+		check_authmethod_valid(authmethodhostssl, auth_methods_host, "hostssl");
+	if (authmethodhostnossl != NULL)
+		check_authmethod_valid(authmethodhostnossl, auth_methods_host, "hostnossl");
+	if (authmethodhostgssenc != NULL)
+		check_authmethod_valid(authmethodhostgssenc, auth_methods_host, "hostgssenc");
+	if (authmethodhostnogssenc != NULL)
+		check_authmethod_valid(authmethodhostnogssenc, auth_methods_host, "hostnogssenc");
 
 	check_need_password(authmethodlocal, authmethodhost);
 
@@ -3240,9 +3358,9 @@ main(int argc, char *argv[])
 	if (authwarning)
 	{
 		printf("\n");
-		pg_log_warning("enabling \"trust\" authentication for local connections");
+		pg_log_warning("enabling \"trust\" authentication");
 		fprintf(stderr, _("You can change this by editing pg_hba.conf or using the option -A, or\n"
-						  "--auth-local and --auth-host, the next time you run initdb.\n"));
+						  "the various options that start with --auth, the next time you run initdb.\n"));
 	}
 
 	/*

--------------2.29.2--

#15

Tom Lane

tgl@sss.pgh.pa.us

about 5 years ago

In reply to: David Fetter (#14)

Re: Let people set host(no)ssl settings from initdb

David Fetter <david@fetter.org> writes:

On Wed, Dec 30, 2020 at 08:24:06PM +0100, David Fetter wrote:

On Mon, Sep 07, 2020 at 11:57:58AM +0900, Michael Paquier wrote:

I have looked at the patch of this thread, and I doubt that it is a
good idea to put more burden into initdb for that. I agree that
being able to reject easily non-SSL connections in pg_hba.conf is a
bit of a hassle now, but putting more logic into initdb does not seem
the right course to me. Perhaps we could consider an idea like
Peter's to have a sslmode=require on the server side and ease the
generation of HBA rules..

Please find attached the rebased patch.

Peter's suggestion seems a little more subtle to me than requiring TLS
on the server side in that what people generally want to do is
disallow clear text connections entirely. In those scenarios, people
would also want to set (and be able to change at runtime) some kind of
cryptographic policy, as SSH and TLS do. While I see this as a worthy
goal, it's a much bigger lift than an optional argument or two to
initdb, and requires a lot more discussion than it's had to date.

FWIW, I still agree with what Michael says above. I do not think
that adding more options to initdb is a useful solution here.
In the first place, it's unlikely that we'll manage to cover many
people's exact requirements this way. In the second place, it's
very unclear where to stop adding options. In the third place,
I believe the vast majority of users don't invoke initdb "by hand"
anymore. The typical scenario is to go through a packager-provided
script, which almost certainly won't offer access to these additional
options. In the fourth place, many people won't know at initdb time
exactly what they should do, or they'll change their minds later.

The last two points suggest that what'd be more useful is some sort
of tool to modify an existing pg_hba.conf file. Or maybe even just
audit a file to see if it implements $desired-policy, such as
"no unencrypted network connections" or "no plaintext passwords".
(I kind of like the auditing-tool approach; it seems less scary
than something that actually rewrites the file.)

regards, tom lane

#16

Isaac Morland

isaac.morland@gmail.com

about 5 years ago

In reply to: Tom Lane (#15)

Re: Let people set host(no)ssl settings from initdb

On Wed, 30 Dec 2020 at 15:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:

In the third place,
I believe the vast majority of users don't invoke initdb "by hand"
anymore. The typical scenario is to go through a packager-provided
script, which almost certainly won't offer access to these additional
options.

I can't speak to other distributions, but on Ubuntu pg_createcluster allows
a -- followed by initdb options. So at least on Ubuntu any additional
options will indeed be available to everybody. I would hope that other
distributions have the same capability.

I for one would like to be able to tell initdb (pg_createcluster) what to
put in the first column of pb_hba.conf in the same way I can already use
--auth{,-host,-local}= to set the auth-method column. Ideally, for simple
situations (think testing scripts and the like, rather than long-term
installations) the pg_hba.conf could be created by initdb and not changed
after that.

#17

David Fetter

david@fetter.org

about 5 years ago

In reply to: Tom Lane (#15)

Re: Let people set host(no)ssl settings from initdb

On Wed, Dec 30, 2020 at 03:00:17PM -0500, Tom Lane wrote:

David Fetter <david@fetter.org> writes:

On Wed, Dec 30, 2020 at 08:24:06PM +0100, David Fetter wrote:

On Mon, Sep 07, 2020 at 11:57:58AM +0900, Michael Paquier wrote:

I have looked at the patch of this thread, and I doubt that it is a
good idea to put more burden into initdb for that. I agree that
being able to reject easily non-SSL connections in pg_hba.conf is a
bit of a hassle now, but putting more logic into initdb does not seem
the right course to me. Perhaps we could consider an idea like
Peter's to have a sslmode=require on the server side and ease the
generation of HBA rules..

Please find attached the rebased patch.

Peter's suggestion seems a little more subtle to me than requiring TLS
on the server side in that what people generally want to do is
disallow clear text connections entirely. In those scenarios, people
would also want to set (and be able to change at runtime) some kind of
cryptographic policy, as SSH and TLS do. While I see this as a worthy
goal, it's a much bigger lift than an optional argument or two to
initdb, and requires a lot more discussion than it's had to date.

FWIW, I still agree with what Michael says above. I do not think
that adding more options to initdb is a useful solution here.
In the first place, it's unlikely that we'll manage to cover many
people's exact requirements this way. In the second place, it's
very unclear where to stop adding options. In the third place,
I believe the vast majority of users don't invoke initdb "by hand"
anymore. The typical scenario is to go through a packager-provided
script, which almost certainly won't offer access to these additional
options. In the fourth place, many people won't know at initdb time
exactly what they should do, or they'll change their minds later.

To that last, I suspect that there are folks in regulated industries
who want to be able to show that they've deployed at some kind of
minimal level of protection. If there's not a window during which a
non-conforming pg_hba.conf is in play, that's easier to do.

The last two points suggest that what'd be more useful is some sort
of tool to modify an existing pg_hba.conf file. Or maybe even just
audit a file to see if it implements $desired-policy, such as
"no unencrypted network connections" or "no plaintext passwords".
(I kind of like the auditing-tool approach; it seems less scary
than something that actually rewrites the file.)

Am I understanding correctly that you're suggesting we write up a
formal specification of pg_hba.conf? That could be handy if we don't
choose to export the parser the backend uses for it, for example
because we want it to respond super quickly to HUPs, which might
conflict with making it usable by things that weren't the backend.

I agree that anything that does a write to pg_hba.conf needs to be
approached with a good deal of caution. Audit tools such as you
propose could spit out a suggestion that doesn't overwrite, although
it could get a little hairy if it's intended to patch something that
has an include directive in it.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

#18

Magnus Hagander

magnus@hagander.net

about 5 years ago

In reply to: Tom Lane (#15)

Re: Let people set host(no)ssl settings from initdb

On Wed, Dec 30, 2020 at 9:00 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Fetter <david@fetter.org> writes:

On Wed, Dec 30, 2020 at 08:24:06PM +0100, David Fetter wrote:

On Mon, Sep 07, 2020 at 11:57:58AM +0900, Michael Paquier wrote:

I have looked at the patch of this thread, and I doubt that it is a
good idea to put more burden into initdb for that. I agree that
being able to reject easily non-SSL connections in pg_hba.conf is a
bit of a hassle now, but putting more logic into initdb does not seem
the right course to me. Perhaps we could consider an idea like
Peter's to have a sslmode=require on the server side and ease the
generation of HBA rules..

Please find attached the rebased patch.

Peter's suggestion seems a little more subtle to me than requiring TLS
on the server side in that what people generally want to do is
disallow clear text connections entirely. In those scenarios, people
would also want to set (and be able to change at runtime) some kind of
cryptographic policy, as SSH and TLS do. While I see this as a worthy
goal, it's a much bigger lift than an optional argument or two to
initdb, and requires a lot more discussion than it's had to date.

FWIW, I still agree with what Michael says above. I do not think
that adding more options to initdb is a useful solution here.
In the first place, it's unlikely that we'll manage to cover many
people's exact requirements this way. In the second place, it's
very unclear where to stop adding options. In the third place,
I believe the vast majority of users don't invoke initdb "by hand"
anymore. The typical scenario is to go through a packager-provided
script, which almost certainly won't offer access to these additional
options. In the fourth place, many people won't know at initdb time
exactly what they should do, or they'll change their minds later.

AFAIK bot the debian/ubuntu script mentioned by Isaac downthread, and the
RedHat/Fedora ones do allow you to specify inidb options. That would cover
the majority I'd say...

That said, I agree with not adding it as an option to initdb. You'll
quickly get to the point where you specify the whole pg_hba file on the
commandline to initdb -- and most people today who actually care that much
about it would have their pg_hba.conf file under some sort of configuration
management anyway, whether it's ansible, chef, puppet or something else.

The last two points suggest that what'd be more useful is some sort

of tool to modify an existing pg_hba.conf file. Or maybe even just

I don't think we need, or indeed want, a tool to *modify* pg_hba.conf. For
people who want that, there are already plenty options out there in the
configuration management space, let's not invent our own.

audit a file to see if it implements $desired-policy, such as
"no unencrypted network connections" or "no plaintext passwords".
(I kind of like the auditing-tool approach; it seems less scary
than something that actually rewrites the file.)

Audiring, however, is a lot more interesting.

For people who actually care about most of this, it's not that important
what the initial one is, if it can trivially be changed to become insecure.
And unfortunately due to the complexity of pg_hba, that can easily happen.
Keeping it under configuration management helps with that, but doesn't
entirely solve the problem.

Another possible approach could be to add global gucs for
"allow_unencrypted_connections" and maybe
"available_authentication_methods". That would override pg_hba. At least in
doing so, there would be *one* spot where one could fairly strictly lock
things down. (Similar to Peters suggestion upthread)

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

#19

Peter Eisentraut

peter.eisentraut@enterprisedb.com

almost 5 years ago

In reply to: Magnus Hagander (#18)

Re: Let people set host(no)ssl settings from initdb

On 01.01.21 14:12, Magnus Hagander wrote:

That said, I agree with not adding it as an option to initdb. You'll
quickly get to the point where you specify the whole pg_hba file on the
commandline to initdb -- and most people today who actually care that
much about it would have their pg_hba.conf file under some sort of
configuration management anyway, whether it's ansible, chef, puppet or
something else.

I think there is enough sustained opposition to this patch that we can
mark this as rejected in the commitfest.

#20

David Steele

david@pgmasters.net

almost 5 years ago

In reply to: Peter Eisentraut (#19)

Re: Let people set host(no)ssl settings from initdb

On 3/3/21 9:07 AM, Peter Eisentraut wrote:

On 01.01.21 14:12, Magnus Hagander wrote:

That said, I agree with not adding it as an option to initdb. You'll
quickly get to the point where you specify the whole pg_hba file on
the commandline to initdb -- and most people today who actually care
that much about it would have their pg_hba.conf file under some sort
of configuration management anyway, whether it's ansible, chef, puppet
or something else.

I think there is enough sustained opposition to this patch that we can
mark this as rejected in the commitfest.

Agreed. I will do that on MAR 8 (to leave time for discussion) unless
somebody beats me to it.

Regards,
--
-David
david@pgmasters.net

#21

Michael Paquier

michael@paquier.xyz

almost 5 years ago

In reply to: Peter Eisentraut (#19)

Re: Let people set host(no)ssl settings from initdb

On Wed, Mar 03, 2021 at 03:07:30PM +0100, Peter Eisentraut wrote:

I think there is enough sustained opposition to this patch that we can mark
this as rejected in the commitfest.

+1.
--
Michael

#22

Ibrar Ahmed

ibrar.ahmad@gmail.com

almost 5 years ago

In reply to: Michael Paquier (#21)

Re: Let people set host(no)ssl settings from initdb

On Thu, Mar 4, 2021 at 7:25 AM Michael Paquier <michael@paquier.xyz> wrote:

On Wed, Mar 03, 2021 at 03:07:30PM +0100, Peter Eisentraut wrote:

I think there is enough sustained opposition to this patch that we can

mark

this as rejected in the commitfest.
+1.
--
Michael

The patch (v5-0001-Enable-setting-pg_hba.conf-permissions-from-initd.patch)
does not apply successfully.
There are two reasons first is it was not generated with proper "-p" which
confused cfbot. Second, after
fixing that issue you still need to rebase that.

http://cfbot.cputube.org/patch_32_2916.log

|+++ doc/src/sgml/ref/initdb.sgml
--------------------------
No file to patch. Skipping patch.
1 out of 1 hunk ignored
can't find file to patch at input line 77
Perhaps you used the wrong -p or --strip option?
The text leading up to this was:

Can we get a rebase?

I am marking the patch "Waiting on Author"

--
Ibrar Ahmed

#23

Andrew Dunstan

andrew@dunslane.net

almost 5 years ago

In reply to: Ibrar Ahmed (#22)

Re: Let people set host(no)ssl settings from initdb

On 3/8/21 11:23 AM, Ibrar Ahmed wrote:

On Thu, Mar 4, 2021 at 7:25 AM Michael Paquier <michael@paquier.xyz
<mailto:michael@paquier.xyz>> wrote:

On Wed, Mar 03, 2021 at 03:07:30PM +0100, Peter Eisentraut wrote:

I think there is enough sustained opposition to this patch that

we can mark

this as rejected in the commitfest.
+1.
--
Michael
The patch
(v5-0001-Enable-setting-pg_hba.conf-permissions-from-initd.patch) does
not apply successfully.
There are two reasons first is it was not generated with proper "-p"
which confused cfbot. Second, after
fixing that issue you still need to rebase that.

http://cfbot.cputube.org/patch_32_2916.log
<http://cfbot.cputube.org/patch_32_2916.log>

|+++ doc/src/sgml/ref/initdb.sgml
--------------------------
No file to patch. Skipping patch.
1 out of 1 hunk ignored
can't find file to patch at input line 77
Perhaps you used the wrong -p or --strip option?
The text leading up to this was:

Can we get a rebase?

I am marking the patch "Waiting on Author"

What is the point of doing that if we're going to reject the patch as
discussed upthread?

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

#24

Michael Paquier

michael@paquier.xyz

almost 5 years ago

In reply to: Andrew Dunstan (#23)

Re: Let people set host(no)ssl settings from initdb

On Mon, Mar 08, 2021 at 06:13:14PM -0500, Andrew Dunstan wrote:

What is the point of doing that if we're going to reject the patch as
discussed upthread?

I have read again this thread, and still understand that this is the
consensus that has been reached. The CF entry has been updated to
reflect that.
--
Michael