DBLINK_CONNECT(3)

💖 DBLINK_CONNECT(3) — the girlies' guide to opening a persistent connection 💖

okay so bestie, buckle up, we're looking at dblink_connect from the PostgreSQL 18.6 Documentation ✨ it's manual section 3, and it's genuinely one of the most useful little functions in the whole dblink fam. no cap.

---

🌸 NAME 🌸

dblink_connect — opens a persistent connection to a remote database 🫶

like... it literally just connects you. to elsewhere. iconic behaviour tbh.

---

✨ SYNOPSIS ✨

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

two flavours, one vibe. either you name your connection or you don't 💅

---

🦋 DESCRIPTION 🦋

so dblink_connect() establishes a connection to a remote PostgreSQL database. the server and the database you're sliding into are identified through a standard libpq connection string — yep, the same kind of string you already know and love. and if you want, you can assign a name to that connection 🎀

here's the tea: you can have multiple named connections open at once, but only one unnamed connection is permitted at a time. that's the rule, periodt. and the connection just... persists? until it's closed, or until the database session is ended. it's giving loyal 🫶

also!! the connection string can also be the name of an existing foreign server. cute, right?

it is recommended that you use the foreign-data wrapper dblink_fdw when you're defining that foreign server. see the example down below, plus CREATE SERVER (CREATE_SERVER(7)) and CREATE USER MAPPING (CREATE_USER_MAPPING(7)) 💜

---

🎀 ARGUMENTS 🎀

• connname — the name to use for this connection. if you omit it, an unnamed connection is opened, which replaces any existing unnamed connection 😭 so like, don't be sloppy with your unnamed connects, bestie.
• connstr — libpq-style connection info string, for example hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd options=-csearch_path=. for all the deets see Section 32.1.1. alternatively, this can be the name of a foreign server 👑

---

⭐ RETURN VALUE ⭐

returns status, which is always OK.

why always? because any error causes the function to throw an error instead of returning. so you never get a sad little return value — you get a whole exception. main character energy honestly 💅

---

🔥 NOTES 🔥

okay this part is the serious part, so listen up bestie 🫶

if untrusted users have access to a database that has not adopted a secure schema usage pattern, then you should begin each session by removing publicly-writable schemas from search_path. one way to do that: add options=-csearch_path= to connstr. and before you're like "why is dblink so dramatic" — this consideration is not specific to dblink. it applies to every interface for executing arbitrary SQL commands. so like, it's a whole lifestyle, not a dblink thing 🦋

---

the foreign-data wrapper dblink_fdw has an additional Boolean option called use_scram_passthrough that controls whether dblink will use the SCRAM pass-through authentication to connect to the remote database. you can specify it for a foreign server or a user mapping. and a user mapping setting overrides the foreign server setting, so, hierarchy 💅

with SCRAM pass-through authentication, dblink uses SCRAM-hashed secrets instead of plain-text user passwords to connect to the remote server. which means it avoids storing plain-text user passwords in PostgreSQL system catalogs — that's a slay, genuinely 🔥 for further details and restrictions, see the documentation of the equivalent use_scram_passthrough option of postgres_fdw.

---

only superusers may use dblink_connect to create connections that use neither password authentication, SCRAM pass-through, nor GSSAPI-authentication. if non-superusers need this capability, use dblink_connect_u instead. that's the whole point of that function existing 🙈

---

and lastly: it is unwise to choose connection names that contain equal signs, because this opens a risk of confusion with connection info strings in other dblink functions. like, myconn=oops is gonna vibe-check badly somewhere down the line. don't do it, bestie 😭

---

🌈 EXAMPLES 🌈

here's the unnamed one, replacing any existing unnamed connection:

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

and here's the named one — say hi to myconn 👑

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

now the foreign-data-wrapper glow-up, notes and all 💅

-- FOREIGN DATA WRAPPER functionality
-- Note: local connections that don't use SCRAM pass-through 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;

look at that cleanup at the end. responsible queen behaviour 🧚✨

---

and that's the whole page, bestie 💖 this was PostgreSQL 18.6, dated 2026, and it lives in manual section DBLINK_CONNECT(3). go forth and connect responsibly 🫶🎀