How to access foreign databases from Postgres

Customers often need to integrate Play SQL Spreadsheets with their own business database. In the following document, we describe how to set up this situation:

• Users can create spreadsheets in their PostgreSQL database as usual,
• The tables of their other database are visible in read-only mode.

It leverages a feature of PostgreSQL named "foreign tables" and "foreign data wrappers".

Installing the Foreign Data Wrapper (FDW)

There are a dozen FDWs documented in the wiki. We'll set up the one for MySQL, which is provided by EnterpriseDB, the company who provides enterprise support for PostgreSQL. The following snippet is extracted from their installation guide.

git clone git@github.com:EnterpriseDB/mysql_fdw.git
# Please replace the paths as needed
export PATH=/usr/lib/postgresql/9.3/bin/:/usr/bin/mysql:$PATH make USE_PGXS=1
sudo PATH=/usr/lib/postgresql/9.3/bin/:/usr/bin/mysql:$PATH make USE_PGXS=1 install

Creating the connection

Log into the database using the system account then execute the following commands (CREATE EXTENSION and CREATE SERVER):

-- Add the extension
CREATE EXTENSION postgis;
 
-- Add the server
CREATE SERVER mysql_local 
TYPE 'mysql' 
FOREIGN DATA WRAPPER mysql_fdw 
OPTIONS (
    host '192.168.1.61',
    port '3306'
);

PostgreSQL uses "User Mappings" to provide access to those servers to some users. In the following snippet, we define a user mapping for PUBLIC. To make it read-only, the MySQL user must only have read privileges.

Documentation: CREATE USER MAPPING

-- Create a user mapping
CREATE USER MAPPING
FOR PUBLIC
SERVER mysql_local
OPTIONS (
    password 'test',
    username 'test'
);

Creating the table

There are two ways to create the table. The first way (mentioned by this blog post) is to make a hard copy of the data in PostgreSQL. It improves performance, but it requires triggering the command every time the synchronization must happen. The other way is to create a foreign table, which dynamically displays the remote one:

Documentation: CREATE FOREIGN TABLE

CREATE FOREIGN TABLE foreigntable (
    "ID" integer,
    name text,
    description text,
    "int" integer
)
SERVER mysql_local
OPTIONS (
    dbname 'test3',
    table_name 'table1'
);
 
GRANT ALL PRIVILEGES ON foreigntable TO user_a;

Every table of the foreign database must be imported. Foreign tables act like local tables, they belong to a schema and they are submitted to the privilege rules.

Related articles

• The official Foreign Data Wrapper of EnterpriseDB for MySQL
• Playing with Foreign Data Wrappers in PostgreSQL by Admire Nyakudya
• List of Foreign Data Wrappers in the Postgresql.org wiki