dblink
The dblink module supports connections to other databases from within a database session. The other database can be in the same Apache Cloudberry cluster, in another Apache Cloudberry cluster, or in a PostgreSQL server.
dblink is intended for short ad hoc queries in other databases. It is not a replacement for external tables, foreign tables, or data migration tools.
The Apache Cloudberry dblink module is a modified version of the PostgreSQL dblink module. See MPP considerations for the restrictions that apply.
Install and register the module
dblink is built and installed by a default Apache Cloudberry build. Register the extension in each database in which you want to use it:
CREATE EXTENSION dblink;
MPP considerations
-
A named or implicit (unnamed)
dblinkconnection exists only in the coordinator process that serves your session. A statement that modifies table data, such asINSERT INTO ... SELECT ... FROM dblink(...), runs thedblink()call in a different process, which cannot see that connection. In such statements, pass the connection string directly todblink(). The following example reads thetestdblinktable that is created in step 1 of Use dblink:CREATE TABLE testdbllocal (a int, b text) DISTRIBUTED BY (a);INSERT INTO testdbllocalSELECT * FROM dblink('dbname=postgres', 'SELECT * FROM testdblink') AS dbltab(id int, product text);If you use a connection name instead, the statement fails with the error
could not establish connection, and the detailmissing "=" after "<connection name>" in connection info string. -
Asynchronous queries are not supported. The extension does not create the
dblink_send_query(),dblink_is_busy(), anddblink_get_result()functions. -
A non-superuser must specify both
hostandpasswordin the connection string ofdblink_connect()anddblink(), even to connect to a local database. Otherwise, the call fails withhost is requiredorpassword is required. If a non-superuser does not specifyuser, the name of the current database user is used.
Use dblink
The following steps show the basic use of dblink. The examples use dblink_connect() to create a connection to a database and dblink() to run an SQL query.
-
Create a sample table in the
postgresdatabase:CREATE TABLE testdblink (a int, b text) DISTRIBUTED BY (a);INSERT INTO testdblink VALUES (1, 'Cheese'), (2, 'Fish'); -
Log in to a different database, for example
testdb, as a superuser, and register the extension if you have not done so:CREATE EXTENSION dblink; -
Use
dblink_connect()to create either an implicit or a named connection to another database. The connection string is alibpq-style keyword/value string. This example creates a connection namedmylocalconnto thepostgresdatabase on the local cluster:SELECT dblink_connect('mylocalconn', 'dbname=postgres user=gpadmin');dblink_connect----------------OK(1 row) -
Use
dblink()to run a query over the connection. The function returnsrecord, so you must specify the names and types of the returned columns:SELECT * FROM dblink('mylocalconn', 'SELECT * FROM testdblink') AS dbltab(id int, product text);id | product----+---------1 | Cheese2 | Fish(2 rows) -
Close the connection when you no longer need it:
SELECT dblink_disconnect('mylocalconn');
To connect to a remote system, include the host and password information in the connection string. For example, to create an implicit connection:
SELECT dblink_connect('host=remotehost port=5432 dbname=postgres user=gpadmin password=secret');
Use dblink as a non-superuser
A non-superuser must include the host and the password in the connection string of dblink_connect(). For example, the user test_user can create a connection to the local coordinator host cdw with this command:
SELECT dblink_connect('host=cdw port=5432 dbname=postgres user=test_user password=secret');
In addition, the remote server must actually request a password for the connection. If it does not (for example, with trust authentication), the call fails with password is required.
To let non-superusers create connections that do not require a password, use the dblink_connect_u() function. It is identical to dblink_connect(), except that it skips these checks. dblink_connect_u() is installed with all privileges revoked from PUBLIC, so only superusers can call it until you grant the EXECUTE privilege on it.
Grant EXECUTE on dblink_connect_u() only to users that you trust. If the target system has users with an authentication method that does not involve a password, a non-superuser who can call dblink_connect_u() can impersonate such a user and escalate privileges. Also, a password can be supplied from the server environment, such as a ~/.pgpass file that belongs to the operating system user that runs the server.
-
As a superuser, grant the
EXECUTEprivilege on thedblink_connect_u()functions:GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO test_user;GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO test_user; -
As
test_user, create a connection without a password, and use it:SELECT dblink_connect_u('testconn', 'dbname=postgres user=test_user');SELECT * FROM dblink('testconn', 'SELECT * FROM testdblink') AS dbltab(id int, product text);
Use dblink with SSL-encrypted connections
To connect over an encrypted connection, specify the sslmode keyword in the connection string. Set sslmode to at least require to disallow unencrypted connections. For example:
SELECT dblink_connect('con_sales', 'dbname=sales host=cdw user=gpadmin sslmode=require');
Module documentation
See dblink in the PostgreSQL documentation for details about the individual functions in this module.