Skip to content

Commit 4334695

Browse files
jconwayjconway
committed
Add support for using SQL/MED compliant FOREIGN DATA WRAPPER, SERVER,
and USER MAPPING as method to supply dblink connect parameters. Per mailing list and PGCon discussions.
1 parent af98bb2 commit 4334695

File tree

4 files changed

+228
-5
lines changed

4 files changed

+228
-5
lines changed

‎contrib/dblink/dblink.c

Lines changed: 100 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Darko Prenosil <Darko.Prenosil@finteh.hr>
99
* Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1010
*
11-
* $PostgreSQL: pgsql/contrib/dblink/dblink.c,v 1.78 2009/06/02 03:21:56 joe Exp $
11+
* $PostgreSQL: pgsql/contrib/dblink/dblink.c,v 1.79 2009/06/06 21:27:56 joe Exp $
1212
* Copyright (c) 2001-2009, PostgreSQL Global Development Group
1313
* ALL RIGHTS RESERVED;
1414
*
@@ -46,6 +46,7 @@
4646
#include "catalog/pg_type.h"
4747
#include "executor/executor.h"
4848
#include "executor/spi.h"
49+
#include "foreign/foreign.h"
4950
#include "lib/stringinfo.h"
5051
#include "miscadmin.h"
5152
#include "nodes/execnodes.h"
@@ -96,6 +97,8 @@ static char *generate_relation_name(Oid relid);
9697
static void dblink_connstr_check(const char *connstr);
9798
static void dblink_security_check(PGconn *conn, remoteConn *rconn);
9899
static void dblink_res_error(const char *conname, PGresult *res, const char *dblink_context_msg, bool fail);
100+
static char *get_connect_string(const char *servername);
101+
static char *escape_param_str(const char *from);
99102

100103
/* Global */
101104
static remoteConn *pconn = NULL;
@@ -165,7 +168,11 @@ typedef struct remoteConnHashEnt
165168
} \
166169
else \
167170
{ \
168-
connstr = conname_or_str; \
171+
connstr = get_connect_string(conname_or_str); \
172+
if (connstr == NULL) \
173+
{ \
174+
connstr = conname_or_str; \
175+
} \
169176
dblink_connstr_check(connstr); \
170177
conn = PQconnectdb(connstr); \
171178
if (PQstatus(conn) == CONNECTION_BAD) \
@@ -210,6 +217,7 @@ PG_FUNCTION_INFO_V1(dblink_connect);
210217
Datum
211218
dblink_connect(PG_FUNCTION_ARGS)
212219
{
220+
char *conname_or_str = NULL;
213221
char *connstr = NULL;
214222
char *connname = NULL;
215223
char *msg;
@@ -220,16 +228,21 @@ dblink_connect(PG_FUNCTION_ARGS)
220228

221229
if (PG_NARGS() == 2)
222230
{
223-
connstr = text_to_cstring(PG_GETARG_TEXT_PP(1));
231+
conname_or_str = text_to_cstring(PG_GETARG_TEXT_PP(1));
224232
connname = text_to_cstring(PG_GETARG_TEXT_PP(0));
225233
}
226234
else if (PG_NARGS() == 1)
227-
connstr = text_to_cstring(PG_GETARG_TEXT_PP(0));
235+
conname_or_str = text_to_cstring(PG_GETARG_TEXT_PP(0));
228236

229237
if (connname)
230238
rconn = (remoteConn *) MemoryContextAlloc(TopMemoryContext,
231239
sizeof(remoteConn));
232240

241+
/* first check for valid foreign data server */
242+
connstr = get_connect_string(conname_or_str);
243+
if (connstr == NULL)
244+
connstr = conname_or_str;
245+
233246
/* check password in connection string if not superuser */
234247
dblink_connstr_check(connstr);
235248
conn = PQconnectdb(connstr);
@@ -2353,3 +2366,86 @@ dblink_res_error(const char *conname, PGresult *res, const char *dblink_context_
23532366
errcontext("Error occurred on dblink connection named \"%s\": %s.",
23542367
dblink_context_conname, dblink_context_msg)));
23552368
}
2369+
2370+
/*
2371+
* Obtain connection string for a foreign server
2372+
*/
2373+
static char *
2374+
get_connect_string(const char *servername)
2375+
{
2376+
ForeignServer *foreign_server = NULL;
2377+
UserMapping *user_mapping;
2378+
ListCell *cell;
2379+
StringInfo buf = makeStringInfo();
2380+
ForeignDataWrapper *fdw;
2381+
AclResult aclresult;
2382+
2383+
/* first gather the server connstr options */
2384+
if (strlen(servername) < NAMEDATALEN)
2385+
foreign_server = GetForeignServerByName(servername, true);
2386+
2387+
if (foreign_server)
2388+
{
2389+
Oid serverid = foreign_server->serverid;
2390+
Oid fdwid = foreign_server->fdwid;
2391+
Oid userid = GetUserId();
2392+
2393+
user_mapping = GetUserMapping(userid, serverid);
2394+
fdw = GetForeignDataWrapper(fdwid);
2395+
2396+
/* Check permissions, user must have usage on the server. */
2397+
aclresult = pg_foreign_server_aclcheck(serverid, userid, ACL_USAGE);
2398+
if (aclresult != ACLCHECK_OK)
2399+
aclcheck_error(aclresult, ACL_KIND_FOREIGN_SERVER, foreign_server->servername);
2400+
2401+
foreach (cell, fdw->options)
2402+
{
2403+
DefElem *def = lfirst(cell);
2404+
2405+
appendStringInfo(buf, "%s='%s' ", def->defname,
2406+
escape_param_str(strVal(def->arg)));
2407+
}
2408+
2409+
foreach (cell, foreign_server->options)
2410+
{
2411+
DefElem *def = lfirst(cell);
2412+
2413+
appendStringInfo(buf, "%s='%s' ", def->defname,
2414+
escape_param_str(strVal(def->arg)));
2415+
}
2416+
2417+
foreach (cell, user_mapping->options)
2418+
{
2419+
2420+
DefElem *def = lfirst(cell);
2421+
2422+
appendStringInfo(buf, "%s='%s' ", def->defname,
2423+
escape_param_str(strVal(def->arg)));
2424+
}
2425+
2426+
return buf->data;
2427+
}
2428+
else
2429+
return NULL;
2430+
}
2431+
2432+
/*
2433+
* Escaping libpq connect parameter strings.
2434+
*
2435+
* Replaces "'" with "\'" and "\" with "\\".
2436+
*/
2437+
static char *
2438+
escape_param_str(const char *str)
2439+
{
2440+
const char *cp;
2441+
StringInfo buf = makeStringInfo();
2442+
2443+
for (cp = str; *cp; cp++)
2444+
{
2445+
if (*cp == '\\' || *cp == '\'')
2446+
appendStringInfoChar(buf, '\\');
2447+
appendStringInfoChar(buf, *cp);
2448+
}
2449+
2450+
return buf->data;
2451+
}

‎contrib/dblink/expected/dblink.out

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -784,3 +784,46 @@ SELECT dblink_disconnect('dtest1');
784784
OK
785785
(1 row)
786786

787+
-- test foreign data wrapper functionality
788+
CREATE USER dblink_regression_test;
789+
CREATE FOREIGN DATA WRAPPER postgresql;
790+
CREATE SERVER fdtest FOREIGN DATA WRAPPER postgresql OPTIONS (dbname 'contrib_regression');
791+
CREATE USER MAPPING FOR public SERVER fdtest;
792+
GRANT USAGE ON FOREIGN SERVER fdtest TO dblink_regression_test;
793+
GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO dblink_regression_test;
794+
\set ORIGINAL_USER :USER
795+
\c - dblink_regression_test
796+
-- should fail
797+
SELECT dblink_connect('myconn', 'fdtest');
798+
ERROR: password is required
799+
DETAIL: Non-superusers must provide a password in the connection string.
800+
-- should succeed
801+
SELECT dblink_connect_u('myconn', 'fdtest');
802+
dblink_connect_u
803+
------------------
804+
OK
805+
(1 row)
806+
807+
SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
808+
a | b | c
809+
----+---+---------------
810+
0 | a | {a0,b0,c0}
811+
1 | b | {a1,b1,c1}
812+
2 | c | {a2,b2,c2}
813+
3 | d | {a3,b3,c3}
814+
4 | e | {a4,b4,c4}
815+
5 | f | {a5,b5,c5}
816+
6 | g | {a6,b6,c6}
817+
7 | h | {a7,b7,c7}
818+
8 | i | {a8,b8,c8}
819+
9 | j | {a9,b9,c9}
820+
10 | k | {a10,b10,c10}
821+
(11 rows)
822+
823+
\c - :ORIGINAL_USER
824+
REVOKE USAGE ON FOREIGN SERVER fdtest FROM dblink_regression_test;
825+
REVOKE EXECUTE ON FUNCTION dblink_connect_u(text, text) FROM dblink_regression_test;
826+
DROP USER dblink_regression_test;
827+
DROP USER MAPPING FOR public SERVER fdtest;
828+
DROP SERVER fdtest;
829+
DROP FOREIGN DATA WRAPPER postgresql;

‎contrib/dblink/sql/dblink.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -364,3 +364,28 @@ SELECT * from
364364
SELECT dblink_cancel_query('dtest1');
365365
SELECT dblink_error_message('dtest1');
366366
SELECT dblink_disconnect('dtest1');
367+
368+
-- test foreign data wrapper functionality
369+
CREATE USER dblink_regression_test;
370+
371+
CREATE FOREIGN DATA WRAPPER postgresql;
372+
CREATE SERVER fdtest FOREIGN DATA WRAPPER postgresql OPTIONS (dbname 'contrib_regression');
373+
CREATE USER MAPPING FOR public SERVER fdtest;
374+
GRANT USAGE ON FOREIGN SERVER fdtest TO dblink_regression_test;
375+
GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO dblink_regression_test;
376+
377+
\set ORIGINAL_USER :USER
378+
\c - dblink_regression_test
379+
-- should fail
380+
SELECT dblink_connect('myconn', 'fdtest');
381+
-- should succeed
382+
SELECT dblink_connect_u('myconn', 'fdtest');
383+
SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
384+
385+
\c - :ORIGINAL_USER
386+
REVOKE USAGE ON FOREIGN SERVER fdtest FROM dblink_regression_test;
387+
REVOKE EXECUTE ON FUNCTION dblink_connect_u(text, text) FROM dblink_regression_test;
388+
DROP USER dblink_regression_test;
389+
DROP USER MAPPING FOR public SERVER fdtest;
390+
DROP SERVER fdtest;
391+
DROP FOREIGN DATA WRAPPER postgresql;

‎doc/src/sgml/dblink.sgml

Lines changed: 60 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/dblink.sgml,v 1.6 2008/11/12 15:52:44 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/dblink.sgml,v 1.7 2009/06/06 21:27:56 joe Exp $ -->
22

33
<sect1 id="dblink">
44
<title>dblink</title>
@@ -42,6 +42,18 @@
4242
only one unnamed connection is permitted at a time. The connection
4343
will persist until closed or until the database session is ended.
4444
</para>
45+
46+
<para>
47+
The connection string may also be the name of an existing foreign
48+
server that utilizes the postgresql_fdw foreign data wrapper library.
49+
See the example below, as well as the following:
50+
<simplelist type="inline">
51+
<member><xref linkend="sql-createforeigndatawrapper" endterm="sql-createforeigndatawrapper-title"></member>
52+
<member><xref linkend="sql-createserver" endterm="sql-createserver-title"></member>
53+
<member><xref linkend="sql-createusermapping" endterm="sql-createusermapping-title"></member>
54+
</simplelist>
55+
</para>
56+
4557
</refsect1>
4658

4759
<refsect1>
@@ -113,6 +125,53 @@
113125
----------------
114126
OK
115127
(1 row)
128+
129+
-- FOREIGN DATA WRAPPER functionality
130+
-- Note: local connection must require password authentication for this to work properly
131+
-- Otherwise, you will receive the following error from dblink_connect():
132+
-- ----------------------------------------------------------------------
133+
-- ERROR: password is required
134+
-- DETAIL: Non-superuser cannot connect if the server does not request a password.
135+
-- HINT: Target server's authentication method must be changed.
136+
CREATE USER dblink_regression_test WITH PASSWORD 'secret';
137+
CREATE FOREIGN DATA WRAPPER postgresql;
138+
CREATE SERVER fdtest FOREIGN DATA WRAPPER postgresql OPTIONS (hostaddr '127.0.0.1', dbname 'contrib_regression');
139+
140+
CREATE USER MAPPING FOR dblink_regression_test SERVER fdtest OPTIONS (user 'dblink_regression_test', password 'secret');
141+
GRANT USAGE ON FOREIGN SERVER fdtest TO dblink_regression_test;
142+
GRANT SELECT ON TABLE foo TO dblink_regression_test;
143+
144+
\set ORIGINAL_USER :USER
145+
\c - dblink_regression_test
146+
SELECT dblink_connect('myconn', 'fdtest');
147+
dblink_connect
148+
----------------
149+
OK
150+
(1 row)
151+
152+
SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
153+
a | b | c
154+
----+---+---------------
155+
0 | a | {a0,b0,c0}
156+
1 | b | {a1,b1,c1}
157+
2 | c | {a2,b2,c2}
158+
3 | d | {a3,b3,c3}
159+
4 | e | {a4,b4,c4}
160+
5 | f | {a5,b5,c5}
161+
6 | g | {a6,b6,c6}
162+
7 | h | {a7,b7,c7}
163+
8 | i | {a8,b8,c8}
164+
9 | j | {a9,b9,c9}
165+
10 | k | {a10,b10,c10}
166+
(11 rows)
167+
168+
\c - :ORIGINAL_USER
169+
REVOKE USAGE ON FOREIGN SERVER fdtest FROM dblink_regression_test;
170+
REVOKE SELECT ON TABLE foo FROM dblink_regression_test;
171+
DROP USER MAPPING FOR dblink_regression_test SERVER fdtest;
172+
DROP USER dblink_regression_test;
173+
DROP SERVER fdtest;
174+
DROP FOREIGN DATA WRAPPER postgresql;
116175
</programlisting>
117176
</refsect1>
118177
</refentry>

0 commit comments

Comments
 (0)