From: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Allow 'sslkey' and 'sslcert' in postgres_fdw user mappings
Openpgp: preference=signencrypt
Autocrypt: addr=andrew.dunstan@2ndQuadrant.com; prefer-encrypt=mutual;
 keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNL0FuZHJldyBEdW5zdGFuIDxhbmRyZXcuZHVuc3RhbkAybmRRdWFkcmFudC5jb20+wsCY
 BBMBAgBCAhsDBgsJCAcDAgYVCAIJCgsEFgIDAQIeAQIXgAUJDyV6hBYhBOQ+WEYd/Hy/RGkV
 pZn6f8tZ/DuBBQJcH/EVAhkBAAoJEJn6f8tZ/DuB7BEH/iDWZEbVft2m2MT6SPjARzTupt/l
 xLLZ/Cl6n2US2hov5+fBbnR5xvECk/YCOfG9ICvKNlpFjceqNLRKStd8cmndnI+kDw91uNM2
 hpL4aJMeW65XCHvqXlOLSwv5chBpXz9NG7FMaJ+q+59sWEWjcTNmjXgkEHn53y+1pc7nSLDe
 cr5ZI1aB3nGXRm1SoDY5PYZkUKMykW+YBZwgcs/Q5tRaE+siN7DKbz8E628+PerwAkmHPVXN
 MdJFD6gs4S0gDFA8IxcyIwZU2v4By+brzcJ9Se3FK5BYxBDHtaYZxgSrJAvOUOrLIZPQTQfL
 TtnVs3C3JTfrxCH1ceHtnA6dRuTOwE0ETspYWQEIANGc4zQULOxhbqO2dyD51YhqCNRmm9oK
 Waqf+wmW4tpDe/VVcxAnNizd4LWCHfzpb5cHAtGkOPePMfzWVf6nvdF7d3eglbtf59+zG7O7
 llV0xSSoFiieQBsrGvqDInXYX/4mRRXMtyhM353/tixC9RWLs1oofyYmCPPXXY7h9R7en3B8
 BoVrRFcdzlIY/NFNhFGW/9dkEiGjgna2Rk6e15kln4ZvFBWUg23p93w/pqXcxY6+k/8TEk+C
 4R+M6w7o2PLGOjdZ+kPiUcw5H85zf/yZJwQXzisXaNduwWB6Vads9YC9dj6kPR1c4VGRqAaY
 L++LAEOqrlvm2TvqQqZRtnEAEQEAAcLAZQQYAQIADwIbDAUCXA6fpAUJDyV6xAAKCRCZ+n/L
 Wfw7geU4B/9vFFsVmWxerDWgNjyiK80zIPmlw/iwdUlU6FIu0JWLMg4093kvxBs7RMZV9hBq
 n3ekBvD4IvFsVLJrw9qzSI2FH3UeYy5WXpEs01Ppia60NOlvRDqXbryzuvfe+iYo6bVP3Ikr
 TPTNYL/BePUTD2DPvSI82SbGTyzuA7SJv6NRqqQDMIy1fU6KyQFLmQJp/QJyhKWzm9jWh8ee
 w8jVaoMY0Kbq5N7RqisEx1Lu/Iw646UphdYzQNymf+kbiU5Kf4I6DhzV8w9sie1Bo1juNj3I
 c5/fFJsmxljbnaDbXPcnFsur1Oo9mIHFakgWgOBMuxoWfjONiLjjLh/9+ZIqSSLX
Message-ID: <f941b95e-27ad-cb5c-2495-13c44f90b1bc@2ndQuadrant.com>
Date: Thu, 31 Oct 2019 19:54:41 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.7.0
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------CF3C1E00C66900D4CA836AE9"
Content-Language: en-MW
Precedence: bulk

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


This patch achieves  $SUBJECT and also provides some testing of the
sslpassword setting.


cheers


andrew


-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--------------CF3C1E00C66900D4CA836AE9
Content-Type: text/x-patch;
 name="postgres_fdw_sslparms-1.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="postgres_fdw_sslparms-1.patch"

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 298f652afc..c2661320bc 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -174,6 +174,18 @@ WARNING:  extension "bar" is not installed
 ALTER SERVER testserver1 OPTIONS (DROP extensions);
 ALTER USER MAPPING FOR public SERVER testserver1
 	OPTIONS (DROP user, DROP password);
+-- Attempt to add a valid option that's not allowed in a user mapping
+ALTER USER MAPPING FOR public SERVER testserver1
+	OPTIONS (ADD sslmode 'require');
+ERROR:  invalid option "sslmode"
+HINT:  Valid options in this context are: user, password, sslpassword, password_required, sslcert, sslkey
+-- But we can add valid ones fine
+ALTER USER MAPPING FOR public SERVER testserver1
+	OPTIONS (ADD sslpassword 'dummy');
+-- Ensure valid options we haven't used in a user mapping yet are
+-- permitted to check validation.
+ALTER USER MAPPING FOR public SERVER testserver1
+	OPTIONS (ADD sslkey 'value', ADD sslcert 'value');
 ALTER FOREIGN TABLE ft1 OPTIONS (schema_name 'S 1', table_name 'T 1');
 ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1');
 ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 07fc7fa00a..0f35dfe54f 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -196,6 +196,16 @@ InitPgFdwOptions(void)
 		{"fetch_size", ForeignServerRelationId, false},
 		{"fetch_size", ForeignTableRelationId, false},
 		{"password_required", UserMappingRelationId, false},
+		/*
+		 * Extra room for the user mapping copies of sslcert and sslkey. These
+		 * are really libpq options but we repeat them here to allow them to
+		 * appear in both foreign server context (when we generate libpq
+		 * options) and user mapping context (from here). Bit of a hack
+		 * putting this in "non_libpq_options".
+		 */
+		{"sslcert", UserMappingRelationId, true},
+		{"sslkey", UserMappingRelationId, true},
+
 		{NULL, InvalidOid, false}
 	};
 
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 5f50c65566..bfdd5dd4d2 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -188,6 +188,19 @@ ALTER SERVER testserver1 OPTIONS (DROP extensions);
 ALTER USER MAPPING FOR public SERVER testserver1
 	OPTIONS (DROP user, DROP password);
 
+-- Attempt to add a valid option that's not allowed in a user mapping
+ALTER USER MAPPING FOR public SERVER testserver1
+	OPTIONS (ADD sslmode 'require');
+
+-- But we can add valid ones fine
+ALTER USER MAPPING FOR public SERVER testserver1
+	OPTIONS (ADD sslpassword 'dummy');
+
+-- Ensure valid options we haven't used in a user mapping yet are
+-- permitted to check validation.
+ALTER USER MAPPING FOR public SERVER testserver1
+	OPTIONS (ADD sslkey 'value', ADD sslcert 'value');
+
 ALTER FOREIGN TABLE ft1 OPTIONS (schema_name 'S 1', table_name 'T 1');
 ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1');
 ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index bdd6ff8ae4..ef203d225c 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -107,13 +107,13 @@
     A foreign server using the <filename>postgres_fdw</filename> foreign data wrapper
     can have the same options that <application>libpq</application> accepts in
     connection strings, as described in <xref linkend="libpq-paramkeywords"/>,
-    except that these options are not allowed:
+    except that these options are not allowed or have special handling:
 
     <itemizedlist spacing="compact">
      <listitem>
       <para>
        <literal>user</literal>, <literal>password</literal> and <literal>sslpassword</literal> (specify these
-       in a user mapping, instead)
+       in a user mapping, instead, or use a service file)
       </para>
      </listitem>
      <listitem>
@@ -128,6 +128,14 @@
        <literal>postgres_fdw</literal>)
       </para>
      </listitem>
+     <listitem>
+      <para>
+       <literal>sslkey</literal> and <literal>sslpassword</literal> - these may
+       appear in <emphasis>either or both</emphasis> a connection and a user
+       mapping. If both are present, the user mapping setting overrides the
+       connection setting.
+      </para>
+     </listitem>
     </itemizedlist>
    </para>
 

--------------CF3C1E00C66900D4CA836AE9--