SqlMedConnectionManager

Under construction ...

---

This design draft describes SQL/MED compatible connection manager for PostgreSQL. The initial approach is to provide only connection info management while leaving the actual connection handling to the client application.

Why do we need this?

• Built in repository for connection definitions - shareable between different modules.
• Access control and logging for remote connections.
• Improved control over who can actually create the connections.
• Possibility for connection invalidation -- really useful for pl/proxy.
• It is a prerequisite for further SQL/MED features.

Design notes

server

The server describes the connection to the remote database. All of the connection details (such as hostname, port database name etc.) are provided via generic options.

user mapping

Maps the local database user to remote, possible remote credentials are provided in the generic options (probably contains sensitive data).

foreign data wrapper

The FDW handles all aspects of foreign data access -- this includes connection management, datatype conversion etc. Every foreign server is associated with exactly one FDW, but the FDW can be used to access several foreign servers.

For the connection manager the FDW has mainly two responsibilites: one -- parsing and validating the generic options for server and user mapping, two -- providing connection information for the applications.

The FDW-s are implemented as shared libraries that conform to a specific API. Eventually there will be a FDW for every connection type - dbi, oci, odbc, etc. Minimally we need at least one FDW that operates in pass-through mode -- options are not validated and connection lookup returns the raw server and user mapping. This would also serve as a boilerplate for future FDW implementations.

SQL syntax

<foreign-data wrapper definition> ::=
    CREATE FOREIGN DATA WRAPPER <foreign-data wrapper name>
    [ LIBRARY <library name specification> ] <language clause> [ <generic options> ]
<alter foreign-data wrapper statement> ::=
    ALTER FOREIGN DATA WRAPPER <foreign-data wrapper name>
    [ <library name specification> ] [ <alter generic options> ]
<drop foreign-data wrapper statement> ::=
    DROP FOREIGN DATA WRAPPER <foreign-data wrapper name> <drop behavior>

<foreign server definition> ::=
    CREATE SERVER <foreign server name>
    [ TYPE <server type> ] [ VERSION <server version> ]
    FOREIGN DATA WRAPPER <foreign-data wrapper name> [ <generic options> ]
<alter foreign server statement> ::=
    ALTER SERVER <foreign server name> [ <new version> ] [ <alter generic options> ]
<drop foreign server statement> ::=
    DROP SERVER <foreign server name> <drop behavior>

<user mapping definition> ::=
    CREATE USER MAPPING FOR <authorization identifier | USER | CURRENT_USER | PUBLIC>
    SERVER <foreign server name> [ <generic options> ]
<alter user mapping statement> ::=
    ALTER USER MAPPING <specific or generic authorization identifier>
    SERVER <foreign server name> <alter generic options>
<drop user mapping statement> ::=
    DROP USER MAPPING FOR <specific or generic authorization identifier>
    SERVER <foreign server name>

CREATE FOREIGN DATA WRAPPER pgsql LIBRARY 'pgsql_fdw';
CREATE SERVER foo FOREIGN DATA WRAPPER pgsql OPTIONS (host 'remotehost', dbname 'remotedb');
CREATE USER MAPPING FOR PUBLIC SERVER foo OPTIONS (username 'bob', password 'secret');

• Add foreign data wrapper and foreign server to the list of objects that have USAGE privilege.
• Initially assume that only superusers can manipulate FDW's
• CREATE USER MAPPING leaves plaintext passwords in server log (but so does CREATE USER).
• Since SQL/MED is not very widely adopted we can probably loosen some of the syntax rules and drop irrelevant clauses.

Additional system catalogs

Introduce system catalogs for fdw, server and user mapping. These are only visible to superuser. Provide views or access functions for ordinary users.

Table "pg_catalog.pg_foreign_data_wrapper"
    Column    |   Type    | Modifiers 
--------------+-----------+-----------
 fdwname      | name      | not null
 fdwnamespace | oid       | not null
 fdwowner     | oid       | not null
 fdwlibrary   | text      | 
 fdwacl       | aclitem[] | 
 fdwoptions   | text[]    | 
Indexes:
    "pg_foreign_data_wrapper_name_nsp_index" UNIQUE, btree (fdwname, fdwnamespace)
    "pg_foreign_data_wrapper_oid_index" UNIQUE, btree (oid)

template1=# \d pg_foreign_server
 Table "pg_catalog.pg_foreign_server"
    Column    |   Type    | Modifiers 
--------------+-----------+-----------
 srvname      | name      | not null
 srvnamespace | oid       | not null
 srvowner     | oid       | not null
 srvfdw       | oid       | not null
 srvacl       | aclitem[] | 
 srvoptions   | text[]    | 
Indexes:
    "pg_foreign_server_name_nsp_index" UNIQUE, btree (srvname, srvnamespace)
    "pg_foreign_server_oid_index" UNIQUE, btree (oid)

template1=# \d pg_foreign_user_mapping
Table "pg_catalog.pg_foreign_user_mapping"
   Column   |  Type  | Modifiers 
------------+--------+-----------
 usesysid   | oid    | not null
 usesrv     | oid    | not null
 useoptions | text[] | 
Indexes:
    "pg_foreign_user_mapping_usesrvsysid_index" UNIQUE, btree (usesrv, usesysid)

• In pg_foreign_user_mapping InvalidOid signifies PUBLIC access.

Foreign data wrapper API

GetConnectionInfo(fdw, server, user)

Provide connection details for specified server and user mapping.

List *
GetConnectionInfo(ForeignDataWrapper *fdw, ForeignServer *server,
                    ForeignUserMapping *um)

validateOptionList(fdw, what, options)

Validate the generic options for the FDW, server or user mapping.

void
validateOptionList(ForeignDataWrapper *fdw, GenericOptionFlags flags,
                   List *options)

For the dummy FDW the functions are mostly nops -- validation always succeeds and connection lookup returns the raw list of options for server and user mapping. This allows applications to use the connection manager without providing their own FDW library. The downside is that the application must be able to construct the connect strings from the raw options -- not always so straightforward.

For more complicated FDW-s such as pgsql, the validation routine will check that the options provided to server are valid libpq conninfo parameters. Possibly also check that no user mapping related parameter appears in server options. Connection lookup will merge the server and user mapping options into single connect string.

Connection manager C API

Provide connection manager interface for stored C functions, procedural language handlers, etc.

GetForeignServer(serverid)

Look up the foreign server definition.

ForeignServer *GetForeignServer(Oid serverid);

GetForeignUserMapping(userid,serverid)

Look up remote mapping for the local user.

ForeignUserMapping *GetForeignUserMapping(Oid userid, Oid serverid);

GetForeignDataWrapper(fdwid)

Look up foreign data wrapper.

ForeignDataWrapper *GetForeignDataWrapper(Oid fdwid);

GetForeignDataWrapperInterface(fdw)

Fetch the FDW interface from cache. Load the external functions.

ForeignDataWrapperInterface *GetForeignDataWrapperInterface(ForeignDataWrapper *fdw);

GetRemoteConnectionInfo(serverid,userid)

Combines all of the above to obtain connection details.

List *GetRemoteConnectionInfo(Oid serverid, Oid userid);

SQL API

For clients we provide a SQL accessible function that looks up server and user mapping and then call the FDW to obtain connection details. Something along the lines of:

# select * from pg_get_remote_connection_info('remotedb');

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

Whereas other FDW-s, such as dbi might also include a separate tuples for username and password:

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

• Privilege checks and user mapping lookups are done with current effective user id. In some cases a session user or a role might be desired instead. For this purpouse we provide a 2 parameter version of the function, which also accepts a username. In this version the privilege are checked for the supplied user.
• The access to the function is initially limited to superusers only. The client modules that use it must also be owned by privileged user and defined as SECURITY DEFINER. Client module access can then be granted to ordinary users without leaking the connection details.

Privileges

	superuser	SERVER owner	FDW USAGE	SERVER USAGE
CREATE FDW	x
ALTER FDW	x
DROP FDW	x
GRANT USAGE ON FDW	x
CREATE SERVER	x		x
ALTER SERVER	x	x
DROP SERVER	x	x
GRANT USAGE ON SERVER	x	x
CREATE USER MAPPING	x	x
ALTER USER MAPPING	x	x
DROP USER MAPPING	x	x
GetRemoteConnectionInfo()	x	x		x
pg_get_remote_connection_info()	x

• For now only superuser can create a foreign data wrappers. Disallow FDW owner change.
• Grant USAGE on FDW essentially grants the right to create servers on that FDW.
• FDW USAGE is not required for using the SERVER.
• GetRemoteConnectionInfo() and pg_get_remote_connection_info() check the privileges of the supplied user.
• It should be safe to allow GetRemoteConnectionInfo() to be called by non-superusers - the calling function uses an untrusted language and hence can only be deployed by superuser.

psql support

• Need additional \dX commands for displaying remote connection information.
• Tab completion for the new syntax.
• Help

pg_dump support

• Need to be able to dump SERVER/USER MAPPING/FDW.
• Ordinary users cannot dump connection definitions.
• Be concerned about plaintext passwords in the dump.

Usage and statistics logging

Log access to remote connections.

Examples

pgsql

CREATE SERVER foodb FOREIGN DATA WRAPPER pgsql
    OPTIONS (dbname 'foodb', host 'bar');

CREATE USER MAPPING FOR public SERVER foodb
    OPTIONS (username 'bob', password 'salakala');

ERROR:  Invalid option "username" to user mapping
HINT:  Valid user mapping options are: user, password

CREATE USER MAPPING FOR public SERVER foodb
    OPTIONS (user 'bob', password 'salakala');

select * from pg_get_remote_connection_info('foodb');

   option   |                      value                       
------------+--------------------------------------------------
 datasource | dbname=foodb host=bar user=bob password=salakala
(1 row)

select * from pg_get_remote_connection_info('foodb', 'bob');

ERROR:  permission denied for foreign server foodb

GRANT USAGE ON SERVER foodb TO bob;

select * from pg_get_remote_connection_info('foodb', 'bob');
   option   |                      value                       
------------+--------------------------------------------------
 datasource | dbname=foodb host=bar user=bob password=salakala
(1 row)

"default" FDW

CREATE SERVER bazdb FOREIGN DATA WRAPPER "default"
    OPTIONS (tnsname 'ORCL');

CREATE USER MAPPING FOR bob SERVER bazdb OPTIONS (lusername 'scott', passw0rd 'tiger');
GRANT USAGE ON SERVER bazdb TO bob;

select * from pg_get_remote_connection_info('bazdb', 'bob');

  option   | value 
-----------+-------
 tnsname   | ORCL
 lusername | scott
 passw0rd  | tiger
(3 rows)

DBI and Excel

CREATE FOREIGN DATA WRAPPER dbi LIBRARY 'default_fdw';

CREATE SERVER dbdtest FOREIGN DATA WRAPPER dbi
    OPTIONS (
        datasource 'DBI:Excel:file=dbdtest.xls',
        attributes '{xl_vtbl => {TESTV => { ... } }}'
    );

CREATE USER MAPPING FOR current_user SERVER dbdtest;

select * from pg_get_remote_connection_info('dbdtest');

   option   |              value               
------------+----------------------------------
 datasource | DBI:Excel:file=dbdtest.xls
 attributes | {xl_vtbl => {TESTV => { ... } }}
(2 rows)

plproxy cluster

An example of how a plproxy cluster might be described. In reality 'default_fdw' would be replaced by the actual plproxy library.

CREATE FOREIGN DATA WRAPPER plproxy LIBRARY 'default_fdw';

CREATE SERVER userdb TYPE 'plproxy_cluster'
    FOREIGN DATA WRAPPER plproxy
    OPTIONS (
        server1 'dbname=userdb_p0 host=127.0.0.1 port=6000',
        server2 'dbname=userdb_p1 host=127.0.0.1 port=6000',
        server3 'dbname=userdb_p2 host=127.0.0.1 port=6000',
        server4 'dbname=userdb_p3 host=127.0.0.1 port=6000',
        connection_lifetime=3600
    );

CREATE USER MAPPING FOR current_user SERVER userdb;

select * from pg_get_remote_connection_info('userdb');
       option        |                   value                   
---------------------+-------------------------------------------
 server1             | dbname=userdb_p0 host=127.0.0.1 port=6000
 server2             | dbname=userdb_p1 host=127.0.0.1 port=6000
 server3             | dbname=userdb_p2 host=127.0.0.1 port=6000
 server4             | dbname=userdb_p3 host=127.0.0.1 port=6000
 connection_lifetime | 3600
(5 rows)

dblink

An example of how dblink can be used with foreign servers.

CREATE SERVER foo FOREIGN DATA WRAPPER pgsql OPTIONS (host 'localhost', port '5432', dbname 'test');

CREATE USER MAPPING FOR current_user SERVER foo OPTIONS (user 'test', password 'test');

SELECT * FROM pg_get_remote_connection_info('foo');
 option_name |                         option_value                         
-------------+--------------------------------------------------------------
 datasource  | host=localhost port=5432 dbname=test user=test password=test
(1 row)

SELECT * FROM dblink_connect_s('c1', 'foo');
 dblink_connect_s
-----------------
 OK
(1 row)

SELECT * FROM dblink('c1', 'select proname, prosrc from pg_proc') 
    AS t1(proname name, prosrc text) WHERE proname LIKE 'byteal%';
  proname  |  prosrc   
-----------+-----------
 bytealt   | bytealt
 byteale   | byteale
 bytealike | bytealike
(3 rows)

Alternatively:

SELECT * FROM dblink_s('foo', 'select proname, prosrc from pg_proc') 
    AS t1(proname name, prosrc text) WHERE proname LIKE 'byteal%';
  proname  |  prosrc   
-----------+-----------
 bytealt   | bytealt
 byteale   | byteale
 bytealike | bytealike
(3 rows)

Issues

• Some ...

Links

• some discussions
• more discussions
• and more ...
• SQL:2008 standard