User:Martin.pihlak/ConnectionManagerDraft

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 these objects, implement as grantable privileges if the need arises.
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

validate server option(key, value): Validate the generic options for server.

validate user mapping option(key, value): Validate the generic options for user mapping.

connection lookup(server, user): Provide connection details for specified server and user mapping.

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 routines 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. Minimally we need:

connection lookup(name): lookup server, FDW and user mapping. Call FDW for connection details.

Additional functions maybe required for obtaining details about servers, user mappings and FDW:

server lookup(name): Server details: TYPE, VERSION etc.

user mapping lookup(server): User mapping details for the session user.

foreign data wrapper lookup(name): Foreign data wrapper details.

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 seperate tuples for username and password:

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

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.
Should we consider search_path here, and how does pg_relation_size solve it?

psql support

Need additional \dX commands for displaying remote connection information.

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 WRAPPPER pgsql
    OPTIONS (dbname='foodb', host='bar');

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

alice# select * from pg_get_remote_connection_info('foodb');

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

DBI and Excel

CREATE FOREIGN DATA WRAPPER dbi LIBRARY 'dbi_wrapper' LANGUAGE C;

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

CREATE USER MAPPING FOR bob SERVER dbdtest
    OPTIONS (username NULL, password NULL);

bob# select * from pg_get_remote_connection_info('dbdtest');

key         value
----------- ------------------------------------------------------------
datasource  DBI:Excel:file=dbdtest.xls
attributes  {xl_vtbl => {TESTV => { ... } }}
username    NULL
password    NULL
(4 rows)

plproxy cluster

CREATE SERVER userdb
    FOREIGN DATA WRAPPER plproxy
    OPTIONS (cluster '{userdb_p0,userdb_p1}');

CREATE SERVER userdb_p0
    FOREIGN DATA WRAPPER plproxy
    OPTIONS (dbname 'userdb_p0', host '127.0.0.1', port '6000')

bob# select * from pg_get_remote_connection_info('userdb');
key         value
----------- ------------------------------------------------
cluster     {userdb_p0,userdb_p1}
(1 row)

bob# select * from pg_get_remote_connection_info('userdb_p0');
key         value
----------- ------------------------------------------------
datasource  dbname=userdb_p0 host=127.0.0.1 port=600
(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');

bob# select * from pg_get_remote_connection_info('bazdb');
key         value
----------- ------------------------------------------------
tnsname     ORCL
lusername   scott
passw0rd    tiger
(3 rows)

Issues

A lot ...

User:Martin.pihlak/ConnectionManagerDraft

Contents

Design notes

SQL syntax

Additional system catalogs

Foreign data wrapper API

Connection manager C API

SQL API

psql support

pg_dump support

Usage and statistics logging

Examples

pgsql

DBI and Excel

plproxy cluster

"default" FDW

Issues

Links

Navigation menu

Page actions

Page actions

Personal tools

Navigation

Tools

Search