DBLINK_CONNECT(3)

📦 version 13.23, in opensuse-leap 16.0

okay so bestie, welcome to the <pink>PostgreSQL 13.23</pink> docs, section 3, and today's main character is dblink_connect — the function that opens a persistent connection to a remote database. like, it doesn't just wave hi and leave, it STAYS. 🫶

✨ NAME ✨

dblink_connect — opens a persistent connection to a remote database. no cap, that's literally the whole vibe. 🦋

🎀 SYNOPSIS 🎀

dblink_connect(text connstr) returns text
dblink_connect(text connname, text connstr) returns text

two forms, pick your fighter. 💅

🌸 DESCRIPTION 🌸

okay so dblink_connect() establishes a connection to a remote PostgreSQL database. the server and the database you're contacting? those are identified through a standard libpq connection string. iconique behavior. ✨

optionally, a name can be assigned to the connection. and listen — multiple named connections can be open at once, but only one unnamed connection is permitted at a time. that's the rule, periodt. the connection will persist until it's closed or until the database session is ended. 🎯

also, the connection string may also be the name of an existing foreign server. it's recommended to use the foreign-data wrapper dblink_fdw when defining the foreign server. see the example below, as well as CREATE SERVER (CREATE_SERVER(7)) and CREATE USER MAPPING (CREATE_USER_MAPPING(7)). the way that's such a clean pattern, honestly obsessed. 🧚

💜 ARGUMENTS 💜

  • connname — the name to use for this connection; if it's omitted, an unnamed connection is opened, replacing any existing unnamed connection. yes, replacing. like it said, one unnamed at a time, bestie. 🔥
  • connstr — a libpq-style connection info string, for example hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd options=-csearch_path=. for the deets, see Section 33.1.1. alternatively? the name of a foreign server. iykyk. 🦋

⭐ RETURN VALUE ⭐

returns status, which is always OK — and that's because any error causes the function to throw an error instead of returning. so like, if you got a return value at all, congrats, it's OK. 🎉

🌈 NOTES 🌈

🚨 okay this one matters, so listen up: if untrusted users have access to a database that has not adopted a secure schema usage pattern, begin each session by removing publicly-writable schemas from search_path. one could, for example, add options=-csearch_path= to connstr. this consideration is not specific to dblink — it applies to every interface for executing arbitrary SQL commands. fr fr, it's everywhere. 💀
  • Only superusers may use dblink_connect to create non-password-authenticated connections. if non-superusers need this capability, use dblink_connect_u instead. slay for dblink_connect_u, honestly. 👑
  • it is unwise to choose connection names that contain equal signs, as this opens a risk of confusion with connection info strings in other dblink functions. like, don't do that. just don't. 🙈

🔥 EXAMPLES 🔥

first up, the anonymous connection, living its best unnamed life:

SELECT dblink_connect('dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

and now the named one — myconn girlies rise:

SELECT dblink_connect('myconn', 'dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

okay and here's the whole FOREIGN DATA WRAPPER saga, with the commentary left exactly where the warning lives:

-- FOREIGN DATA WRAPPER functionality
-- Note: local connection must require password authentication for this to work properly
--       Otherwise, you will receive the following error from dblink_connect():
--       ERROR:  password is required
--       DETAIL:  Non-superuser cannot connect if the server does not request a password.
--       HINT:  Target server's authentication method must be changed.

CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'contrib_regression');

CREATE USER regress_dblink_user WITH PASSWORD 'secret';
CREATE USER MAPPING FOR regress_dblink_user SERVER fdtest OPTIONS (user 'regress_dblink_user', password 'secret');
GRANT USAGE ON FOREIGN SERVER fdtest TO regress_dblink_user;
GRANT SELECT ON TABLE foo TO regress_dblink_user;

\set ORIGINAL_USER :USER
\c - regress_dblink_user
SELECT dblink_connect('myconn', 'fdtest');
 dblink_connect
----------------
 OK
(1 row)

SELECT * FROM dblink('myconn', 'SELECT * FROM foo') AS t(a int, b text, c text[]);
 a  | b |       c
----+---+---------------
  0 | a | {a0,b0,c0}
  1 | b | {a1,b1,c1}
  2 | c | {a2,b2,c2}
  3 | d | {a3,b3,c3}
  4 | e | {a4,b4,c4}
  5 | f | {a5,b5,c5}
  6 | g | {a6,b6,c6}
  7 | h | {a7,b7,c7}
  8 | i | {a8,b8,c8}
  9 | j | {a9,b9,c9}
 10 | k | {a10,b10,c10}
(11 rows)

\c - :ORIGINAL_USER
REVOKE USAGE ON FOREIGN SERVER fdtest FROM regress_dblink_user;
REVOKE SELECT ON TABLE foo FROM regress_dblink_user;
DROP USER MAPPING FOR regress_dblink_user SERVER fdtest;
DROP USER regress_dblink_user;
DROP SERVER fdtest;

see how it puts everything back? that's that clean-up-after-yourself energy. we love a responsible queen. 🧹💖

that's the whole page, bestie — go forth and connect 💅

signing off with the receipts: this was PostgreSQL 13.23, dated 2025, and yes the page is literally called DBLINK_CONNECT(3). mwah. 🍓💜

📦 where this page comes from