Re: Withdraw PL/Proxy from commitfest

Hannu Krosing wrote:
>> It is cleverly hidden. The CREATE SERVER and CREATE USER MAPPING take
>> generic options (list of key/value pairs). These can be used for
>> defining the actual connection to the remote server.
>
> Are you sure this is how it is intended to be done ?
>
Yes.

> In pl/proxy context this would mean that in order to define connection
> info we would at least need (foreign) SERVER and USER MAPPING objects
>
> defined so
>
> CREATE SERVER <foreign server name>
[snip]
> CREATE USER MAPPING
[snip]
> plus a possibility to GRANT USAGE on those and also the function
> ConnectServer(<foreign server name>) to actually make the connection.
>

Yes, and with the generic options actually any connection can be described
(dbi, odbc, ...). In some situations the client module would probably need
to know the type of the connection - this is where we could use either
TYPE or FDW from the server definition.

> I guess we can skip the FOREIGN DATA WRAPPER stuff until we actually
> need it.
>

Actually a minimal FDW would be useful for validation the generic options
of SERVER and USER MAPPING. For instance this could be used to check that
server options are valid libpq conninfo parameters and that no password
is present in server options. The downside is that a wrapper would need to
be provided for all driver types (pgsql, dbi, ...). But I guess we could
start with just two - pgsql and "default". The latter would not validate
anything, thus enabling arbitrary options to be specified.

The FDW could also provide a function for obtaining the connection details
for the server and user mapping pair. For libpq connections it could merge
the server and user options into single connect string. Others probably
need username and password separately. This would take some of the complexity
off the client -- it needs not be concerned with what the actual option
keywords mean (there could be some that are not part of connect string).

For clients we could then provide a SQL accessible wrapper function that would
lookup server and user mapping. Then call the FDW to obtain connection details.
Something along the lines of:

# select * from pg_get_remote_connection_info('remotedb');

key value
----------- ----------------------------------------
conninfo dbname=remotedb host=remotehost user=...

Whereas dbi might also include a username and password:

key value
----------- ------------------------------------------------------------
conninfo dbi:mysql:database=sakila;host=localhost
username root
password salakala

What do you think, usable?

> there has to be some mechanism for prioritizing USER MAPPINGs in case
> you can use many. Maybe have an extra argument for
> ConnectServer(<foreign server name>, <specific or generic authorization
> identifier>) .
>

There is a restriction that we can have only one mapping per user/server.
And then there is PUBLIC if nothing else matches. In 13.3:

"The SQL-environment shall not include a user mapping descriptor whose
authorization identifier is U and whose foreign server name is equivalent
to FSN."

regards,
Martin