1DBLINK_CONNECT(3)        PostgreSQL 14.3 Documentation       DBLINK_CONNECT(3)
2
3
4

NAME

6       dblink_connect - opens a persistent connection to a remote database
7

SYNOPSIS

9       dblink_connect(text connstr) returns text
10       dblink_connect(text connname, text connstr) returns text
11

DESCRIPTION

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
21       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

ARGUMENTS

28       connname
29           The name to use for this connection; if omitted, an unnamed
30           connection is opened, replacing any existing unnamed connection.
31
32       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 34.1.1.
36           Alternatively, the name of a foreign server.
37

RETURN VALUE

39       Returns status, which is always OK (since any error causes the function
40       to throw an error instead of returning).
41

NOTES

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
50       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
54       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

EXAMPLES

59           SELECT dblink_connect('dbname=postgres options=-csearch_path=');
60            dblink_connect
61           ----------------
62            OK
63           (1 row)
64
65           SELECT dblink_connect('myconn', 'dbname=postgres options=-csearch_path=');
66            dblink_connect
67           ----------------
68            OK
69           (1 row)
70
71           -- 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           --       ERROR:  password is required
75           --       DETAIL:  Non-superuser cannot connect if the server does not request a password.
76           --       HINT:  Target server's authentication method must be changed.
77
78           CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'contrib_regression');
79
80           CREATE USER regress_dblink_user WITH PASSWORD 'secret';
81           CREATE USER MAPPING FOR regress_dblink_user SERVER fdtest OPTIONS (user 'regress_dblink_user', password 'secret');
82           GRANT USAGE ON FOREIGN SERVER fdtest TO regress_dblink_user;
83           GRANT SELECT ON TABLE foo TO regress_dblink_user;
84
85           \set ORIGINAL_USER :USER
86           \c - regress_dblink_user
87           SELECT dblink_connect('myconn', 'fdtest');
88            dblink_connect
89           ----------------
90            OK
91           (1 row)
92
93           SELECT * FROM dblink('myconn', 'SELECT * FROM foo') AS t(a int, b text, c text[]);
94            a  | b |       c
95           ----+---+---------------
96             0 | a | {a0,b0,c0}
97             1 | b | {a1,b1,c1}
98             2 | c | {a2,b2,c2}
99             3 | d | {a3,b3,c3}
100             4 | e | {a4,b4,c4}
101             5 | f | {a5,b5,c5}
102             6 | g | {a6,b6,c6}
103             7 | h | {a7,b7,c7}
104             8 | i | {a8,b8,c8}
105             9 | j | {a9,b9,c9}
106            10 | k | {a10,b10,c10}
107           (11 rows)
108
109           \c - :ORIGINAL_USER
110           REVOKE USAGE ON FOREIGN SERVER fdtest FROM regress_dblink_user;
111           REVOKE SELECT ON TABLE foo FROM regress_dblink_user;
112           DROP USER MAPPING FOR regress_dblink_user SERVER fdtest;
113           DROP USER regress_dblink_user;
114           DROP SERVER fdtest;
115
116
117
118PostgreSQL 14.3                      2022                    DBLINK_CONNECT(3)
Impressum