Foreign tables with Foreign Data Wrappers in PostgreSQL

This short article explains briefly how to access to data stored in external PostgreSQL servers through the native postgres_fdw module.

PostgreSQL has an useful feature called Foreign Data Wrappers (FDW), introduced since version 9.3.

The Foreign Data Wrapper allows access to data hosted on an external database as if it were kept on a normal local table.

The postgres_fdw extension is an evolution of the dblink extension, but provides more transparent and standards-compliant syntax for accessing remote tables, and can give better performance in many cases. You can indeed create foreign tables as described in SQL/MED (Management of External Data).

This short guide refers to version 11 of PostgreSQL and later, but the content also applies to previous versions (especially since PostgreSQL 9.6, where the latest improvements were applied).

Let’s have a look at a simple example of how to use a FDW.

Suppose we have two PostgreSQL 11 databases in two different servers:

• woody
• IP: 192.168.33.10
• Database: source
• Owner: user01
• buzz
• IP: 192.168.33.11
• Database: destination
• Owner: user02

As first thing, we connect to the source database as user01 and create a few test tables useful for this demonstration:

CREATE TABLE test1 AS SELECT id, md5(random()::text) FROM generate_series(1,5) id;
CREATE TABLE test2 AS SELECT id, md5(random()::text) FROM generate_series(6,10) id;
CREATE TABLE test3 AS SELECT id, md5(random()::text) FROM generate_series(11,15) id;

Now, suppose we want to access the data stored in woody from buzz.

To configure the remote access using postgres_fdw we have to simply connect to buzz and follow the steps below as database superuser (i.e. postgres).

1. Install the postgres_fdw extension:

\c destination
CREATE EXTENSION postgres_fdw;

1. Create a foreign server object to represent each remote database you want to connect to:

CREATE SERVER woody_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
host '192.168.33.10',
port '5432',
dbname 'source' );

1. GRANT the USAGE permission on the foreign server to the owner user of the destination database:

GRANT USAGE ON FOREIGN SERVER woody_server TO user02;

1. Create a user mapping for each database user that must access this foreign server:

CREATE USER MAPPING
FOR user02
SERVER woody_server
OPTIONS (
user 'user_fdw',
password 'eThah5ae');

Note: As a solution to CVE-2007-6601 and CVE-2007-3278, postgres_fdw requires that connections by non-superusers specify a password in the user mapping, and that the server actually uses that password when authenticating the user. Check the related KB article.

In order to actually use the user_fdw user from the buzz instance we need to connect to the woody server, create the user_fdw user and GRANT it the SELECT permission (at least):

CREATE USER user_fdw;
\password user_fdw
Enter new password:
Enter it again:

Note: You must specify the same password you used in the CREATE USER MAPPING statement.

Then, as owner of the source database (user01), GRANT to user_fdw user the SELECT permission on those tables:

GRANT SELECT ON TABLE test1 TO user_fdw;
GRANT SELECT ON TABLE test2 TO user_fdw;
GRANT SELECT ON TABLE test3 TO user_fdw;

Now we have to be sure that pg_hba.conf in the woody server allows access as user_fdw user to the source database and only from the buzz server (192.168.33.11):

host source user_fdw 192.168.33.11/32 md5

Note: For the sake of simplicity of this example we have set an unencrypted connection (without SSL) with the md5 authentication method. We strongly recommend to use SSL connections between different servers and use the scram-sha-256 authentication method for real use case.

Then, as the postgres user, reload the PostgreSQL server configuration from the command line on woody:

psql -c 'SELECT pg_reload_conf()'

Now we have two possible choices:

1. create a foreign table for each remote table you want access using CREATE FOREIGN TABLE as user02:

CREATE FOREIGN TABLE test1_ft (
id integer,
md5 text)
SERVER woody_server
OPTIONS (
table_name 'test1');

We can now compare the content of the two test tables on the respective servers.

On the woody server:

source=# SELECT * FROM test1;
id | md5
1 | b2862260a8471c95cca19a7e9a299d62
2 | fd4a2bfe9e30ad249087ce5a532386d9
3 | 5af85340ff971fe2c5e7cdcb354eb73e
4 | b44014541daedd1044e02a746a017b99
5 | ac604607259fe7adf680f40af0494a9e
(5 rows)

And on the buzz server:

destination=# SELECT * FROM test1_ft ;
id | md5
1 | b2862260a8471c95cca19a7e9a299d62
2 | fd4a2bfe9e30ad249087ce5a532386d9
3 | 5af85340ff971fe2c5e7cdcb354eb73e
4 | b44014541daedd1044e02a746a017b99
5 | ac604607259fe7adf680f40af0494a9e
(5 rows)

1. Alternatively, since PostgreSQL 9.5, we can use IMPORT FOREIGN SCHEMA which allows importing an entire schema from an external data source avoiding to specify the structure of each table.

On the destination database, as the owner user (user02), issue the following statements:

CREATE SCHEMA imported_schema;
IMPORT FOREIGN SCHEMA public
FROM SERVER woody_server
INTO imported_schema;

Let’s make a quick inspection of all the tables on the target database to observe the outcome of the schema import:

destination=# \dE *.*
List of relations
Schema | Name | Type | Owner
imported_schema | test1 | foreign table | postgres
imported_schema | test2 | foreign table | postgres
imported_schema | test3 | foreign table | postgres
(3 rows)

If it is not necessary to import an entire schema, it is possible to use the LIMIT TO clause to import only a list of tables we are interested in (e.g. test2 table):

IMPORT FOREIGN SCHEMA public
LIMIT TO (test2)
FROM SERVER woody_server
INTO imported_schema;

On the other hand, if we want to exclude certain tables from the schema, it is possible to filter them using the EXCEPT clause (e.g. excluding test3 table):

IMPORT FOREIGN SCHEMA public
EXCEPT (test3)
FROM SERVER woody_server
INTO imported_schema;

Thanks to this feature, data and schema migrations become increasingly simpler and quicker. The Foreign Data Wrappers provided natively with the PostgreSQL distribution are postgres_fdw and file_fdw, giving access to external Postgres databases and text files respectively. Note finally that there are several unofficial PostgreSQL foreign data wrappers for accessing other kinds of databases.