PostgreSQL dblink extension use example

This is a getting started tutorial for using dblink_fdw.  dblink is a PostgreSQL contrib extension that allows users to connect to other databases and to run arbitrary queries in them. 

The FDW acronym at the end of dblink_fdw  comes from the words Foreign Data Wrapper which is a unified way for PostgreSQL to define remote FOREIGN SERVER which to access. It allows to set the DB connection details like hostnames in a single
place and to create a USER MAPPING  for remote connections with the needed passwords only once.  

dblink_fdw example

First off we create the needed extension and database as the avnadmin user. Note that the database username and password only needs to be entered once using the CREATE USER MAPPING statement.

$ psql "postgres://avnadmin:adminpassword@postgres.demoproject.aivencloud.com:11254/defaultdb?sslmode=require"

-- 'user1' will be able to create a dblink from 'defaultdb' to a new database 'db2'

defaultdb= > CREATE USER user1 PASSWORD 'secret1';
CREATE ROLE

-- 'user2' will be the owner of the new database 'db2'

defaultdb= > CREATE USER user2 PASSWORD 'secret2';
CREATE ROLE

-- we need to grant the 'user2' to the current user (avnadmin) so that it can give user2 privileges to the new database 'db2'

GRANT user2 TO CURRENT_USER;
GRANT ROLE

-- create the database that we will access over a dblink connection

defaultdb= > CREATE DATABASE db2 OWNER user2;
CREATE DATABASE

defaultdb= > CREATE EXTENSION dblink;
CREATE EXTENSION

defaultdb= > CREATE SERVER db2remote
                FOREIGN DATA WRAPPER dblink_fdw
                OPTIONS (host 'postgres.demoproject.aivencloud.com',
                         dbname 'db2', port '11254');
CREATE SERVER

-- 'user1' will automatically authenticate as 'user2' with the remote 'db2' database when using the dblink connections

defaultdb= > CREATE USER MAPPING FOR user1
                SERVER db2remote
                OPTIONS (user 'user2', password 'secret2');
CREATE USER MAPPING

-- allow user1 to user the remote db2remote connection

defaultdb= > GRANT USAGE ON FOREIGN SERVER db2remote TO user1;
GRANT

Next, we log into the db2  database as its owner user2  both of which we created above. We will create a table with some data in it:

$ psql "postgres://user2:secret2@postgres.demoproject.aivencloud.com:11254/db2?sslmode=require"

db2= > CREATE TABLE foo (id int);
CREATE TABLE

db2= > INSERT INTO foo (id) VALUES (100), (200), (300);
INSERT 0 3

Now that we have data in db2 we can access it as the user1 user from the original defaultdb database using a dblink:

$ psql "postgres://user1:secret1@postgres.demoproject.aivencloud.com:11254/defaultdb?sslmode=require"

-- establish a dblink connection to the remote database

defaultdb= > SELECT dblink_connect('myconn', 'db2remote');
 dblink_connect
────────────────
 OK
(1 row)

-- execute a test query using the dblink connection

defaultdb= > SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int);
  a
─────
 100
 200
 300
(3 rows)

The contents of the foo  table in the db2 database are returned correctly from the dblink query.