Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4334695

Browse files
committed
Add support for using SQL/MED compliant FOREIGN DATA WRAPPER, SERVER,
and USER MAPPING as method to supply dblink connect parameters. Permailing list and PGCon discussions.
1 parentaf98bb2 commit4334695

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
staticvoiddblink_connstr_check(constchar*connstr);
9798
staticvoiddblink_security_check(PGconn*conn,remoteConn*rconn);
9899
staticvoiddblink_res_error(constchar*conname,PGresult*res,constchar*dblink_context_msg,boolfail);
100+
staticchar*get_connect_string(constchar*servername);
101+
staticchar*escape_param_str(constchar*from);
99102

100103
/* Global */
101104
staticremoteConn*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
elseif (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+
staticchar*
2374+
get_connect_string(constchar*servername)
2375+
{
2376+
ForeignServer*foreign_server=NULL;
2377+
UserMapping*user_mapping;
2378+
ListCell*cell;
2379+
StringInfobuf=makeStringInfo();
2380+
ForeignDataWrapper*fdw;
2381+
AclResultaclresult;
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+
Oidserverid=foreign_server->serverid;
2390+
Oidfdwid=foreign_server->fdwid;
2391+
Oiduserid=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+
returnbuf->data;
2427+
}
2428+
else
2429+
returnNULL;
2430+
}
2431+
2432+
/*
2433+
* Escaping libpq connect parameter strings.
2434+
*
2435+
* Replaces "'" with "\'" and "\" with "\\".
2436+
*/
2437+
staticchar*
2438+
escape_param_str(constchar*str)
2439+
{
2440+
constchar*cp;
2441+
StringInfobuf=makeStringInfo();
2442+
2443+
for (cp=str;*cp;cp++)
2444+
{
2445+
if (*cp=='\\'||*cp=='\'')
2446+
appendStringInfoChar(buf,'\\');
2447+
appendStringInfoChar(buf,*cp);
2448+
}
2449+
2450+
returnbuf->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+
CREATEUSERdblink_regression_test;
370+
371+
CREATE FOREIGN DATA WRAPPER postgresql;
372+
CREATE SERVER fdtest FOREIGN DATA WRAPPER postgresql OPTIONS (dbname'contrib_regression');
373+
CREATEUSERMAPPING FOR public SERVER fdtest;
374+
GRANT USAGEON FOREIGN SERVER fdtest TO dblink_regression_test;
375+
GRANT EXECUTEON 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(aint, btext, ctext[]);
384+
385+
\c- :ORIGINAL_USER
386+
REVOKE USAGEON FOREIGN SERVER fdtestFROM dblink_regression_test;
387+
REVOKE EXECUTEON FUNCTION dblink_connect_u(text,text)FROM dblink_regression_test;
388+
DROPUSER dblink_regression_test;
389+
DROPUSER 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)
[8]ページ先頭
©2009-2025 Movatter.jp