1DBLINK_CONNECT(3) PostgreSQL 12.2 Documentation DBLINK_CONNECT(3)
2
6 dblink_connect - opens a persistent connection to a remote database
7
9 dblink_connect(text connstr) returns text
10 dblink_connect(text connname, text connstr) returns text
11
13 dblink_connect() establishes a connection to a remote PostgreSQL
14 database. The server and database to be contacted are identified
15 through a standard libpq connection string. Optionally, a name can be
16 assigned to the connection. Multiple named connections can be open at
17 once, but only one unnamed connection is permitted at a time. The
18 connection will persist until closed or until the database session is
19 ended.
20 The connection string may also be the name of an existing foreign
22 server. It is recommended to use the foreign-data wrapper dblink_fdw
23 when defining the foreign server. See the example below, as well as
24 CREATE SERVER (CREATE_SERVER(7)) and CREATE USER MAPPING
25 (CREATE_USER_MAPPING(7)).
26
28 connname
29 The name to use for this connection; if omitted, an unnamed
30 connection is opened, replacing any existing unnamed connection.
31 connstr
33 libpq-style connection info string, for example hostaddr=127.0.0.1
34 port=5432 dbname=mydb user=postgres password=mypasswd
35 options=-csearch_path=. For details see Section 33.1.1.
36 Alternatively, the name of a foreign server.
37
39 Returns status, which is always OK (since any error causes the function
40 to throw an error instead of returning).
41
43 If untrusted users have access to a database that has not adopted a
44 secure schema usage pattern, begin each session by removing
45 publicly-writable schemas from search_path. One could, for example, add
46 options=-csearch_path= to connstr. This consideration is not specific
47 to dblink; it applies to every interface for executing arbitrary SQL
48 commands.
49 Only superusers may use dblink_connect to create
51 non-password-authenticated connections. If non-superusers need this
52 capability, use dblink_connect_u instead.
53 It is unwise to choose connection names that contain equal signs, as
55 this opens a risk of confusion with connection info strings in other
56 dblink functions.
57
59 SELECT dblink_connect('dbname=postgres options=-csearch_path=');
60 dblink_connect
61 ----------------
62 OK
63 (1 row)
64 SELECT dblink_connect('myconn', 'dbname=postgres options=-csearch_path=');
66 dblink_connect
67 ----------------
68 OK
69 (1 row)
70 -- FOREIGN DATA WRAPPER functionality
72 -- Note: local connection must require password authentication for this to work properly
73 -- Otherwise, you will receive the following error from dblink_connect():
74 -- ----------------------------------------------------------------------
75 -- ERROR: password is required
76 -- DETAIL: Non-superuser cannot connect if the server does not request a password.
77 -- HINT: Target server's authentication method must be changed.
78 CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'contrib_regression');
80 CREATE USER regress_dblink_user WITH PASSWORD 'secret';
82 CREATE USER MAPPING FOR regress_dblink_user SERVER fdtest OPTIONS (user 'regress_dblink_user', password 'secret');
83 GRANT USAGE ON FOREIGN SERVER fdtest TO regress_dblink_user;
84 GRANT SELECT ON TABLE foo TO regress_dblink_user;
85 \set ORIGINAL_USER :USER
87 \c - regress_dblink_user
88 SELECT dblink_connect('myconn', 'fdtest');
89 dblink_connect
90 ----------------
91 OK
92 (1 row)
93 SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
95 a | b | c
96 ----+---+---------------
97 0 | a | {a0,b0,c0}
98 1 | b | {a1,b1,c1}
99 2 | c | {a2,b2,c2}
100 3 | d | {a3,b3,c3}
101 4 | e | {a4,b4,c4}
102 5 | f | {a5,b5,c5}
103 6 | g | {a6,b6,c6}
104 7 | h | {a7,b7,c7}
105 8 | i | {a8,b8,c8}
106 9 | j | {a9,b9,c9}
107 10 | k | {a10,b10,c10}
108 (11 rows)
109 \c - :ORIGINAL_USER
111 REVOKE USAGE ON FOREIGN SERVER fdtest FROM regress_dblink_user;
112 REVOKE SELECT ON TABLE foo FROM regress_dblink_user;
113 DROP USER MAPPING FOR regress_dblink_user SERVER fdtest;
114 DROP USER regress_dblink_user;
115 DROP SERVER fdtest;
116PostgreSQL 12.2 2020 DBLINK_CONNECT(3)