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
Date: Thu, 31 Oct 2019 19:54:41 -0400
Message-ID: <f941b95e-27ad-cb5c-2495-13c44f90b1bc@2ndQuadrant.com> (raw)
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
Attachments:
[text/x-patch] postgres_fdw_sslparms-1.patch (4.5K, ../f941b95e-27ad-cb5c-2495-13c44f90b1bc@2ndQuadrant.com/2-postgres_fdw_sslparms-1.patch)
download | inline diff:diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.outindex 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.cindex 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.sqlindex 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.sgmlindex 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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: andrew.dunstan@2ndquadrant.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Allow 'sslkey' and 'sslcert' in postgres_fdw user mappings
In-Reply-To: <f941b95e-27ad-cb5c-2495-13c44f90b1bc@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox