The sandbox framework (1)

To meet the requirements of a customer who wants that each user of his system has its own private database connected with several corporate databases, I build a framework with the FDW protocol.

This framework is a set of functions that will be described in several posts.

Specifications

Each user has its own database called herafter the sandbox or simply the sdbx.

There are several databases on several servers that contain corporate data. Some databases have the same structure but not the same content.

The front end application is connected with the sdbx only.

The end user chooses the type of application he wants and the framework takes the necessary databases and within those databases the necessary tables.

All information about users (name and permissions) and databases (name and location) are stored in a database called kernel that is connected with the sdbx at the login.

Those tables are mapped into the sdbx via the Foreign Data Wrapper protocol.

Usage

The framework is a set of five primitives that are called from the front_end.

Those functions are PL/pgSQL functions stored in the sdbx :

connect_kernel(host_name, user_name, password)

connect_database(database_name, user_name, password)

attach_table(database_name, from_table_name, to_table_name, 
                   from_schema_name, to_schema_name)

attach_schema(database_name, from_schema_name, to_schema_name)

disconnect_database(db_name)

All these function return the boolean true if the job is done or false othewise.

In case of error, all these functions write a record in a log table with the last SQL statement executed, the error message and a timestamp. This table log belongs to the sdbx.

Structure

The FDW protocol is used as follows :

create server fdw_server_name
   foreign data wrapper postgres_fdw
   options (host ‘host_address’, port ‘port_number’, dbname ‘database_name’);

create user mapping for public server fdw_server_name
   options (user ‘user_name’, password ‘password’)

create foreign_table_name (
   col1 data_type1,
   col2 data_type2,
   col3 data_type3,
   ...
   )
   server fdw_server_name
   options (schema_name ‘schema_name’, table_name ‘table_name’)

drop server if exists fdw_server_name cascade;

All the colored strings are variables sent by the framework that receives them from the front end application.

All those function are build dynamically by the framework

The create foreign table is dynamically done as follows :

Each database has a view list_fdw_definitions that contains for all tables and views the current values of

• schema_name
• table_name
• create foreign table statement

This view is mapped in the sdbx when the connect_database is issued with the name database_name_list_fdw_definitions.

The create foreign table has placeholders that are substituted by actual values by the framework :

• database_name
• from_table_name
• to_table_name
• from_schema_name
• to_schema_name

The kernel

The kernel is mapped into the sdbx at login time but only the management has write access to the data of the kernel.

• the view permission_matrix that gives for each user the list of databases he can connect and for each database, the IP host address.
At this stage, the kernel contains two tables :

• the table sdbx_tables_2_attach that gives for each type of application the tables that must be mapped in the sdbx and linked to the front end.

Precautions

Each database on the network is unique. The framework can be enhanced by defining the databases with the triplet (host_name, port_number, database_name) but it is a good practice to keep distinct names for discinct databases.

When a fdw server is dropped, all objects are dropped. The sdbx is thus cleaned.