On Tue, 2008-10-21 at 21:05 +0300, Martin Pihlak wrote:
> Hannu Krosing wrote:
>
> > In my brief reading of SQL-MED spec I could only find info on defining
> > FOREIGN SERVER and FOREIGN-DATA WRAPPER and nowhere in these could one
> > define connection parameters like username and password.
>
> 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 ?
> >From http://www.wiscorp.com/sql_2003_standard.zip "4.4 User mappings":
>
> "A user mapping is an SQL-environment element, pairing an ...
> ... The mapping is specified by generic options defined by the
> foreign-data wrapper."
>
> and "13.3 <user mapping definition>":
>
> <user mapping definition> ::=
> CREATE USER MAPPING FOR <specific or generic authorization identifier>
> SERVER <foreign server name> [ <generic options> ]
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> [ TYPE <server type> ] [ VERSION <server version> ] FOREIGN DATA WRAPPER
<foreign-datawrapper name> OPTIONS (HOST "host.ip", PORT "port_nr", DBNAME "databasename")
;
probably with a default / dummy "FOREIGN DATA WRAPPER" called "DEFAULT"
and
CREATE USER MAPPING FOR <specific or generic authorization identifier> SERVER <foreign server name> OPTIONS
(USER"username", PASSWORD "pwd")
;
plus a possibility to GRANT USAGE on those and also the function
ConnectServer(<foreign server name>) to actually make the connection.
I guess we can skip the FOREIGN DATA WRAPPER stuff until we actually
need it.
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>) .
----------------
Hannu