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 Foreign Data Wrapper protocol of PostgreSQL.
This framework is a set of functions that will be described in several posts.
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 connects the necessary databases and, within those databases, attachs 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 attached to the sdbx via the Foreign Data Wrapper protocol.
The framework is a set of five primitives that are called from the front_end applications.
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 functions return the boolean true if the job is done or false otherwise.
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.
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;
Those colored strings are variables sent by the framework that receives them from the front end application and those colored strings are parameters given by the view list_fdw_definitions described below.
All those functions are build dynamically by the framework
The create foreign table is dynamically generated as follows :
Each database has a view list_fdw_definitions that contains for all tables and views the current values of :
• 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 :
The kernel is connected to the sdbx at login time but only the management has write access to the data of the kernel.
At this stage, the kernel contains two tables :
• the view permission_matrix that gives for each user the list of databases he can connect and for each database, the IP host address.
• 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.
Each database name 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 distinct databases.
When a fdw server is dropped, all dependant objects are dropped. The sdbx is thus cleaned of fdw stuff.