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.
If the table is not visible for the Play SQL user, check the privileges.
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