Re: SQL/MED compatible connection manager - Mailing list pgsql-hackers

Peter Eisentraut wrote:
> Martin Pihlak wrote:
>>> I would call it something like
>>>
>>> pg_postgresql_fdw_options_string(server, user) returns text
>>
>> Hmm, it is probably a good idea to avoid the fdw abbreviation -- the term
>> "foreign data wrapper" is already confusing enough. My suggestion:
>>
>> pg_foreign_server_conninfo(server)
>> pg_foreign_server_conninfo(server,user)
>>
>> If there are no objections, I'll whack those functions out, and bring
>> the dblink
>> patch up to date.
>
> Sure, propose some code.  (Note that you can use parameter default
> values now.)
>

Proposal attached. This adds two C functions:

List *GetForeignConnectionOptions(Oid serverid, Oid userid);
char *GetForeignConnectionString(Oid serverid, Oid userid);

One for obtaining all of the connection related options as a list, and
another for transforming these options into a libpq conninfo string.
The latter should be useful for dblink (although the userid and serverid
need to be obtained first).

On top of those there are two SQL accessible functions:

pg_foreign_connection_options(server name, user name = current_user,
    OUT option_class text, OUT option_name text, OUT option_value text);

pg_foreign_connection_string(server name, user  name = current_user);

These should initially be restricted from ordinary users -- grant explicitly
if the user should see the connect strings. Otherwise use from security definer
functions. The pg_foreign_connection_options() exposes all of the connection
options and can be used by clients such as DBI link to construct the connect
string or equivalent. pg_foreign_connection_string() can be used for instance
by plpythonu or plperlu functions to connect to remote postgres database.

Example:

select * from pg_foreign_connection_options('foo');

 option_class | option_name | option_value
--------------+-------------+--------------
 server       | host        | localhost
 server       | port        | 54321
 server       | dbname      | foo
 user mapping | user        | bob
 user mapping | password    | secret
(5 rows)

select * from pg_foreign_connection_string('foo');

                      pg_foreign_connection_string
-------------------------------------------------------------------------
 host='localhost' port='54321' dbname='foo' user='bob' password='secret'
(1 row)

Will add regression and tests if this is acceptable.

PS. I'm not sure if I nailed the "proargdefaults" syntax correctly in pg_proc.h,
for now I just copied it out from a sample function with similar arguments.

regards,
Martin

*** a/src/backend/foreign/foreign.c
--- b/src/backend/foreign/foreign.c
***************
*** 31,36 ****
--- 31,38 ----


  extern Datum pg_options_to_table(PG_FUNCTION_ARGS);
+ extern Datum pg_foreign_connection_string(PG_FUNCTION_ARGS);
+ extern Datum pg_foreign_connection_options(PG_FUNCTION_ARGS);


  /* list of currently loaded foreign-data wrapper interfaces */
***************
*** 321,338 **** GetUserMapping(Oid userid, Oid serverid)
  }


  /*
!  * deflist_to_tuplestore - Helper function to convert DefElem list to
   * tuplestore usable in SRF.
   */
  static void
! deflist_to_tuplestore(ReturnSetInfo *rsinfo, List *options)
  {
      ListCell   *cell;
      TupleDesc    tupdesc;
      Tuplestorestate *tupstore;
!     Datum        values[2];
!     bool        nulls[2] = { 0 };
      MemoryContext per_query_ctx;
      MemoryContext oldcontext;

--- 323,447 ----
  }


+ /*
+  * Helper for appending a ForeignConnectionOption node to a list.
+  */
+ static List *
+ append_option_list(List *options, GenericOptionFlags type, DefElem *def)
+ {
+     ForeignConnectionOption       *opt = makeNode(ForeignConnectionOption);
+
+     opt->opttype = type;
+     opt->option = def;
+     return lappend(options, opt);
+ }
+
+
+ /*
+  * GetForeignConnectionOptions - look up the options for foreign connection.
+  *
+  * Foreign connection is defined by the foreign data wrapper, server and
+  * user mapping triple. The options are simply merged together into a list
+  * of ForeignConnectionOption nodes.
+  *
+  * The role specified by userid must have a user mapping and USAGE privilege
+  * on the server.
+  */
+ List *
+ GetForeignConnectionOptions(Oid serverid, Oid userid)
+ {
+     ForeignServer       *server;
+     UserMapping           *um;
+     ForeignDataWrapper *fdw;
+     List               *result = NIL;
+     ListCell           *cell;
+     AclResult            aclresult;
+
+     server = GetForeignServer(serverid);
+     um = GetUserMapping(userid, serverid);
+     fdw    = GetForeignDataWrapper(server->fdwid);
+
+     /* Check permissions, user must have usage on the server. */
+     aclresult = pg_foreign_server_aclcheck(serverid, userid, ACL_USAGE);
+     if (aclresult != ACLCHECK_OK)
+         aclcheck_error(aclresult, ACL_KIND_FOREIGN_SERVER, server->servername);
+
+     /* Seems OK, prepare a list of all the options */
+     foreach (cell, fdw->options)
+         result = append_option_list(result, FdwOpt, lfirst(cell));
+     foreach (cell, server->options)
+         result = append_option_list(result, ServerOpt, lfirst(cell));
+     foreach (cell, um->options)
+         result = append_option_list(result, UserMappingOpt, lfirst(cell));
+
+     return result;
+ }
+
+
+ /*
+  * GetForeignConnectionString - return a libpq conninfo string for the
+  * foreign connection.
+  *
+  * Currently this just means transforming all of the fdw, server and user
+  * mapping options into a single string. No attempt is made to check if the
+  * resulting conninfo string is valid.
+  */
+ char *
+ GetForeignConnectionString(Oid serverid, Oid userid)
+ {
+     StringInfo        stringptr = makeStringInfo();
+     ListCell       *cell;
+
+     foreach (cell, GetForeignConnectionOptions(serverid, userid))
+     {
+         ForeignConnectionOption       *opt = lfirst(cell);
+
+         appendStringInfo(stringptr, "%s%s='%s'",
+                          (stringptr->len > 0) ? " " : "",
+                          opt->option->defname,
+                          strVal(opt->option->arg));
+     }
+
+     return stringptr->data;
+ }
+
+
+ /*
+  * Helper function for obtaining description of the option type.
+  */
+ static const char *
+ optclass_name(GenericOptionFlags opttype)
+ {
+     switch (opttype)
+     {
+         case FdwOpt:
+             return "foreign data wrapper";
+         case ServerOpt:
+             return "server";
+         case UserMappingOpt:
+             return "user mapping";
+         default:
+             return "unknown";
+     }
+
+     /* not reached */
+     return NULL;
+ }
+
  /*
!  * list_to_tuplestore - Helper function to convert a list to
   * tuplestore usable in SRF.
+  *
+  * All the list elements are assumed to nodes of type "type".
   */
  static void
! list_to_tuplestore(ReturnSetInfo *rsinfo, List *options, NodeTag type)
  {
      ListCell   *cell;
      TupleDesc    tupdesc;
      Tuplestorestate *tupstore;
!     Datum        values[3];
!     bool        nulls[3] = { 0 };
      MemoryContext per_query_ctx;
      MemoryContext oldcontext;

***************
*** 360,369 **** deflist_to_tuplestore(ReturnSetInfo *rsinfo, List *options)

      foreach (cell, options)
      {
!         DefElem       *def = lfirst(cell);

-         values[0] = CStringGetTextDatum(def->defname);
-         values[1] = CStringGetTextDatum(((Value *)def->arg)->val.str);
          tuplestore_putvalues(tupstore, tupdesc, values, nulls);
      }

--- 469,502 ----

      foreach (cell, options)
      {
!         NodeTag        nodeType = ((Node *)lfirst(cell))->type;
!
!         Assert(nodeType == type);
!
!         if (nodeType == T_DefElem)
!         {
!             /* Simple key/value list */
!             DefElem *def = lfirst(cell);
!
!             values[0] = CStringGetTextDatum(def->defname);
!             values[1] = CStringGetTextDatum(strVal(def->arg));
!         }
!         else if (nodeType == T_ForeignConnectionOption)
!         {
!             /* Foreign connection option list - class, key, value */
!             ForeignConnectionOption *opt = lfirst(cell);
!
!             values[0] = CStringGetTextDatum(optclass_name(opt->opttype));
!             values[1] = CStringGetTextDatum(opt->option->defname);
!             values[2] = CStringGetTextDatum(strVal(opt->option->arg));
!         }
!         else
!         {
!             ereport(ERROR,
!                     (errcode(ERRCODE_INTERNAL_ERROR),
!                      errmsg("unrecognized node type %d in list_to_tuplestore", type)));
!         }

          tuplestore_putvalues(tupstore, tupdesc, values, nulls);
      }

***************
*** 383,389 **** pg_options_to_table(PG_FUNCTION_ARGS)
  {
      Datum array = PG_GETARG_DATUM(0);

!     deflist_to_tuplestore((ReturnSetInfo *) fcinfo->resultinfo, untransformRelOptions(array));

      return (Datum) 0;
  }
--- 516,562 ----
  {
      Datum array = PG_GETARG_DATUM(0);

!     list_to_tuplestore((ReturnSetInfo *) fcinfo->resultinfo, untransformRelOptions(array), T_DefElem);
!
!     return (Datum) 0;
! }
!
!
! /*
!  * Return the libpq connection string for specified server and user.
!  */
! Datum
! pg_foreign_connection_string(PG_FUNCTION_ARGS)
! {
!     Name    servername = PG_GETARG_NAME(0);
!     Name    username = PG_GETARG_NAME(1);
!     Oid        serverid;
!     Oid        userid;
!
!     userid = get_roleid_checked(NameStr(*username));
!     serverid = GetForeignServerOidByName(NameStr(*servername), false);
!
!     return CStringGetTextDatum(GetForeignConnectionString(serverid, userid));
! }
!
!
! /*
!  * Return the connection options for specified server and user.
!  */
! Datum
! pg_foreign_connection_options(PG_FUNCTION_ARGS)
! {
!     Name    servername = PG_GETARG_NAME(0);
!     Name    username = PG_GETARG_NAME(1);
!     Oid        serverid;
!     Oid        userid;
!     List   *options;
!
!     userid = get_roleid_checked(NameStr(*username));
!     serverid = GetForeignServerOidByName(NameStr(*servername), false);
!     options = GetForeignConnectionOptions(serverid, userid);
!
!     list_to_tuplestore((ReturnSetInfo *) fcinfo->resultinfo, options, T_ForeignConnectionOption);

      return (Datum) 0;
  }
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2319,2324 **** DESCR("list of SQL keywords");
--- 2319,2330 ----
  DATA(insert OID = 2289 (  pg_options_to_table        PGNSP PGUID 12 1 3 0 f f f t t s 1 0 2249 "1009" "{1009,25,25}"
"{i,o,o}""{options_array,option_name,option_value}" _null_ pg_options_to_table _null_ _null_ _null_ )); 
  DESCR("convert generic options array to name/value table");

+ DATA(insert OID = 2316 (  pg_foreign_connection_string        PGNSP PGUID 12 1 0 0 f f f t f s 2 1 25 "19 19" _null_
_null_"{servername,username}" "({FUNCEXPR :funcid 745 :funcresulttype 19 :funcretset false :funcformat 0 :args <>
:location33})" pg_foreign_connection_string _null_ _null_ _null_ )); 
+ DESCR("returns the libpq conninfo string for the server and username");
+
+ DATA(insert OID = 2319 (  pg_foreign_connection_options        PGNSP PGUID 12 1 5 0 f f f t t s 2 1 2249 "19 19"
"{19,19,25,25,25}""{i,i,o,o,o}" "{servername,username,option_class,option_name,option_value}" "({FUNCEXPR :funcid 745
:funcresulttype19 :funcretset false :funcformat 0 :args <> :location 33})" pg_foreign_connection_options _null_ _null_
_null_)); 
+ DESCR("returns the foreign connection options for the server and username");
+
  DATA(insert OID = 1619 (  pg_typeof                PGNSP PGUID 12 1 0 0 f f f f f s 1 0 2206 "2276" _null_ _null_
_null__null_  pg_typeof _null_ _null_ _null_ )); 
  DESCR("returns the type of the argument");

*** a/src/include/foreign/foreign.h
--- b/src/include/foreign/foreign.h
***************
*** 65,70 **** typedef struct UserMapping
--- 65,78 ----
  } UserMapping;


+ typedef struct ForeignConnectionOption
+ {
+     NodeTag                type;        /* Node type */
+     GenericOptionFlags    opttype;    /* Option type, used as integer value, not flags */
+     DefElem               *option;        /* Option name and value */
+ } ForeignConnectionOption;
+
+
  /*
   * Foreign-data wrapper library function types.
   */
***************
*** 93,98 **** extern ForeignDataWrapper *GetForeignDataWrapperByName(const char *name,
--- 101,108 ----
                                                         bool missing_ok);
  extern Oid GetForeignDataWrapperOidByName(const char *name, bool missing_ok);
  extern ForeignDataWrapperLibrary *GetForeignDataWrapperLibrary(const char *libname);
+ extern List *GetForeignConnectionOptions(Oid serverid, Oid userid);
+ extern char *GetForeignConnectionString(Oid serverid, Oid userid);

  /* Foreign data wrapper interface functions */
  extern void _pg_validateOptionList(ForeignDataWrapper *fdw,
*** a/src/include/nodes/nodes.h
--- b/src/include/nodes/nodes.h
***************
*** 390,396 **** typedef enum NodeTag
      T_TriggerData = 950,        /* in commands/trigger.h */
      T_ReturnSetInfo,            /* in nodes/execnodes.h */
      T_WindowObjectData,            /* private in nodeWindowAgg.c */
!     T_TIDBitmap                    /* in nodes/tidbitmap.h */
  } NodeTag;

  /*
--- 390,397 ----
      T_TriggerData = 950,        /* in commands/trigger.h */
      T_ReturnSetInfo,            /* in nodes/execnodes.h */
      T_WindowObjectData,            /* private in nodeWindowAgg.c */
!     T_TIDBitmap,                /* in nodes/tidbitmap.h */
!     T_ForeignConnectionOption     /* private in foreign/foreign.h */
  } NodeTag;

  /*