dblink Functions
dblink Functions
The dblink module supports connections to other Greenplum Database databases from within a database session. These databases can reside in the same Greenplum Database system, or in a remote system.
Greenplum Database supports dblink connections between databases in Greenplum Database installations with the same major version number. dblink may also connect to other Greenplum Database installations that use compatible libpq libraries.
You create a dblink connection to a database and execute an SQL command in the database as a Greenplum Database user. The user must have the appropriate access privileges to the database tables referenced in the SQL command. If the database is in a remote system, the user must be defined as a Greenplum Database user in the remote system with the appropriate access privileges.
dblink is intended for database users to perform short ad hoc queries in other databases. dblink is not intended for use as a replacement for external tables or for administrative tools such as gpcopy or gptransfer (deprecated).
Refer to dblink in the PostgreSQL documentation for more information about individual dblink functions.
Limitations
gpadmin=# CREATE TABLE testdbllocal (a int, b text) DISTRIBUTED BY (a); CREATE TABLE gpadmin=# INSERT INTO testdbllocal select * FROM dblink('dbname=postgres', 'SELECT * FROM testdblink') AS dbltab(id int, product text); INSERT 0 2
- dblink_send_query()
- dblink_is_busy()
- dblink_get_result()
Using dblink
The following procedure identifies the basic steps for configuring and using dblink in Greenplum Database. The examples use dblink_connect() to create a connection to a database and dblink() to execute an SQL query.
Only superusers can use dblink_connect() to create connections that do not require a password. If non-superusers need this capability, use dblink_connect_u() instead. See Using dblink as a Non-Superuser.
- Begin by creating a sample table to query using the dblink functions.
These commands create a small table in the postgres database, which you
will later query from the testdb database using
dblink:
$ psql -d postgres psql (8.3.23) Type "help" for help. postgres=# CREATE TABLE testdblink (a int, b text) DISTRIBUTED BY (a); CREATE TABLE postgres=# INSERT INTO testdblink VALUES (1, 'Cheese'), (2, 'Fish'); INSERT 0 2 postgres=# \q $
- Log into a different database as a superuser. In this example, the superuser
gpadmin logs into the database testdb. If the
dblink functions are not already available, install them using the
$GPHOME/share/postgresql/contrib/dblink.sql
script:
$ psql -d testdb psql (9.4beta1) Type "help" for help. testdb=# \i /usr/local/greenplum-db/share/postgresql/contrib/dblink.sql SET CREATE FUNCTION CREATE FUNCTION ...
- Use the dblink_connect() function to create either an implicit or a
named connection to another database. The connection string that you provide should be a
libpq-style keyword/value string. This example creates a connection named
mylocalconn to the postgres database on the local
Greenplum Database system:
testdb=# SELECT dblink_connect('mylocalconn', 'dbname=postgres user=gpadmin'); dblink_connect ---------------- OK (1 row)
Note: If a user is not specified, dblink_connect() uses the value of the PGUSER environment variable when Greenplum Database was started. If PGUSER is not set, the default is the system user that started Greenplum Database. - Use the dblink() function to query a database using a configured
connection. Keep in mind that this function returns a record type, so you must assign the
columns returned in the dblink() query. For example, the following
command uses the named connection to query the table you created
earlier:
testdb=# SELECT * FROM dblink('mylocalconn', 'SELECT * FROM testdblink') AS dbltab(id int, product text); id | product ----+--------- 1 | Cheese 2 | Fish (2 rows)
To connect to the local database as another user, specify the user in the connection string. This example connects to the database as the user test_user. Using dblink_connect(), a superuser can create a connection to another local database without specifying a password.
testdb=# SELECT dblink_connect('localconn2', 'dbname=postgres user=test_user');
testdb=# SELECT dblink_connect('host=remotehost port=5432 dbname=postgres user=gpadmin password=secret');
Using dblink as a Non-Superuser
testdb=> SELECT dblink_connect('host=mdw port=5432 dbname=postgres user=test_user password=secret');
If non-superusers need to create dblink connections that do not require a password, they can use the dblink_connect_u() function. The dblink_connect_u() function is identical to dblink_connect(), except that it allows non-superusers to create connections that do not require a password.
In some situations, it may be appropriate to grant EXECUTE permission on dblink_connect_u() to specific users who are considered trustworthy, but this should be done with care.
Also, even if the dblink connection requires a password, it is possible for the password to be supplied from the server environment, such as a ~/.pgpass file belonging to the server's user.
- As a superuser, grant the EXECUTE privilege on the
dblink_connect_u() functions in the user database. This example
grants the privilege to the non-superuser test_user on the functions
with the signatures for creating an implicit or a named dblink
connection.
testdb=# GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO test_user; testdb=# GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO test_user;
- Now test_user can create a connection to another local database
without a password. For example, test_user can log into the
testdb database and execute this command to create a connection named
testconn to the local postgres database.
testdb=> SELECT dblink_connect_u('testconn', 'dbname=postgres user=test_user');
Note: If a user is not specified, dblink_connect_u() uses the value of the PGUSER environment variable when Greenplum Database was started. If PGUSER is not set, the default is the system user that started Greenplum Database. -
test_user can use the dblink() function to execute a
query using a dblink connection. For example, this command uses the
dblink connection named testconn created in the
previous step. test_user must have appropriate access to the
table.
testdb=> SELECT * FROM dblink('testconn', 'SELECT * FROM testdblink') AS dbltab(id int, product text);
Using dblink with SSL-Encrypted Connections to Greenplum
When you use dblink to connect to Greenplum Database over an encrypted connection, you must specify the sslmode property in the connection string. Set sslmode to at least require to disallow unencrypted transfers. For example:
testdb=# SELECT dblink_connect('greenplum_con_sales', 'dbname=sales host=gpmaster user=gpadmin sslmode=require');
Refer to SSL Client Authentication for information about configuring Greenplum Database to use SSL.