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
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>