The sandbox framework : 7. Usage

Let’s play a bit with the framework !

We are connected to the sandbox of a user.

We reset the table log :

delete from log;

We connect to the kernel :

select public.connect_kernel('192.167.1.156','micheld', 'password')

We connect a database :

select public.connect_database('contacts','micheld', 'password')

We verify that the foreign servers are initialized :

select public.list_fdw_servers();

We must obtain something like that (mgr is the owner of the databases) :

(fdw_contacts,mgr,postgres_fdw,"{host=192.167.1.156,dbname=contacts,port=5432}")
(fdw_kernel,mgr,postgres_fdw,"{host=192.167.1.156,dbname=kernel,port=5432}")

We map the tables of the database database in the sandbox.

select public.attach_database('contacts')

and we go with pgAdmin on the tab Foreign tables to verify that everything works fine.

We disconnect now from the corporate database and all foreign tables will be removed from the sandbox :

select public.disconnect_database('contacts')

We verify in the log table that everything is OK :

select * from log order by 1 desc

That’s simple ! Those functions must be called from the front end applications with the appropriate syntax.

In fact, the function attach_database could be called directly by the function connect_database.

The sandbox framework : 6. The attach functions

The following function is central : it maps a foreign table in the sandbox.

create or replace function public.attach_table(
	this_database_name text,
	this_from_table_name text,
	this_to_table_name text default null,
	this_from_schema_name text default 'public',
	this_to_schema_name text default 'public')
    returns boolean
    language 'plpgsql'
    cost 100
    volatile 
as $BODY$

declare
    this_fdw_server_name text;
    this_fdw_table_definition text;
    sql_stmt text;
    this_fdw_table_name text;
    fdw_server_exists boolean;
begin

    sql_stmt =     'select public.is_database_connected('
                || ''''
                || this_database_name
                || ''''
                || ');';

    execute sql_stmt into fdw_server_exists;
    
    if fdw_server_exists is false then
        perform public.write2log(
		      'ERROR : attach_table : '  
        	   || quote_ident(this_database_name)
        	   || ' is not connected'
   	 	);
	return false;
    end if;
    
    if this_to_table_name is null then
    	this_to_table_name = this_from_table_name;
    end if;
    
    this_fdw_server_name = 'fdw_' || this_database_name;
        
    this_fdw_table_name = this_database_name || '_fdw_table_definitions';

    sql_stmt =     'select fdw_table_definition from '
               || quote_ident(this_fdw_table_name)
               || ' where table_name = '
               || ''''
               || this_to_table_name
               || ''''
               || ' and schema_name = '
               || ''''
               || this_from_schema_name
               || ''''
               || ';';

    execute sql_stmt into this_fdw_table_definition;
    
    if this_fdw_table_definition is null then
    	 perform public.write2log(
		       'ERROR : attach_table : no fdw definition for table '  
        	    || quote_ident(this_from_table_name)
   		 );
	 return false;
    end if;
 
    -- prefix the name of the table to be mapped with the database name
    this_to_table_name = this_database_name || '_' || this_to_table_name;
    
    select replace(this_fdw_table_definition, 
                   'THIS_FDW_SERVER_NAME', 
                   quote_ident(this_fdw_server_name)
                  ) 
    	into this_fdw_table_definition;
    
    select replace(this_fdw_table_definition, 
                   'THIS_TO_TABLE_NAME', 
                   quote_ident(this_to_table_name)
                  ) 
    	into this_fdw_table_definition;
    
    select replace(this_fdw_table_definition, 
                   'QUOTE', 
                   ''''
                  ) 
     	into this_fdw_table_definition;
        
    select replace(this_fdw_table_definition, 
                   'THIS_FROM_SCHEMA_NAME', 
                   quote_ident(this_from_schema_name)
                  ) 
    	into this_fdw_table_definition;
     
    select replace(this_fdw_table_definition, 
                   'THIS_TO_SCHEMA_NAME', 
                   quote_ident(this_to_schema_name)) 
    	into this_fdw_table_definition;
    
    execute this_fdw_table_definition;

    exception when others then 
  	perform public.write2log(
		      'ERROR : attach_table for table : '  
       	 	   || quote_ident(this_from_table_name)
        	   || sqlerrm
    	         );
		return false;
end 
$BODY$;

This one maps all the relevant tables of a database (the tables for this database type) in the sandbox. And for each table, this function calls attach_table.

create or replace function public.attach_database(this_database_name text)
    returns boolean
    language 'plpgsql'
    cost 100
    volatile 
as $BODY$
declare
    r record;
begin
    for r in     
        select     
            distinct pg_table_name, pg_schema_name     
        from         
            permissions_matrix p         
        inner join         
            sdbx_tables_2_attach s        
        on           
            p.database_type = s.database_type          
        where             
            p.pg_database_name = this_database_name
    loop
        
    	perform public.attach_table(
       		this_database_name,
               	r.pg_table_name,
                r.pg_table_name,
        	r.pg_schema_name
       		);
        
    end loop;
	
    return true;
    
    exception when others then 
        perform public.write2log(
               'ERROR : attach_database for database : '  
            || quote_ident(this_database_name)
            || ', '
            || sqlerrm
        );
        return false;
end 
$BODY$;

The sandbox framework : 5. Utililities

The following function gives the IP address of the server where this database resides and the port number. The usual value is 5432. If we have a server with multiple instances of PostgreSQL servers, this value can be different.

create or replace function public.get_database_parameters(
	    this_database_name text,
	    this_username text,
	out pg_server_address text,
	out pg_port_number text)
    returns record
    language 'sql'
AS $BODY$

    select 
    	pg_server_address,
        pg_port_number
    from
    	permissions_matrix
    where
    	pg_database_name = this_database_name
    and
    	pg_user_name = this_username;
        
$BODY$;

The two following functions dont need commentaries :

create or replace function public.is_database_connected(this_database_name text)
    returns boolean
    language 'plpgsql'
as $BODY$
declare
	r 	text;
begin
    select 
	srvname
    into
    	r
    from 
    	pg_foreign_server
    join 
    	pg_foreign_data_wrapper w 
    on 
    	w.oid = srvfdw
    where
    	srvname = 'fdw_' || this_database_name;
        
    if r is not null then 
        return true; 
    else 
        return false; 
    end if;
  
end $BODY$;
create or replace function public.list_fdw_servers()
    returns setof record 
    language 'sql'
as $BODY$

    select 
        srvname as name, 
        srvowner::regrole as owner, 
        fdwname as wrapper, 
        srvoptions as options
    from 
        pg_foreign_server
    join 
        pg_foreign_data_wrapper w 
    on 
        w.oid = srvfdw;

$BODY$;

This one is the most important of the framework :

create or replace function public.write2log(msg text)
    returns void
    language 'plpgsql'
as $BODY$
begin

        insert into public.log 
		(info, timestamp)
	values   
		(msg, now());

end $BODY$

A simple

select * from log order by 1 desc

gives the last error messages.

An amusing detail : when we give access rights to the table log, we must also give access rights to the sequence. It is not automatically done by PostgreSQL.

The sandbox framework : 4. The function connect_database

The front end applications select the databases to be connected to the sandbox.

The function connect_database creates in the sandbox the foreign data wrapper for the selected database and maps in the sandbox the view fdw_table_definitions of this database with the name database_name_fdw_table_definitions.

This view will be used by the function attach_table that will map the selected tables.

As usual, the name of the variables are self explanatory. Comments are seldom used. It minimizes the risk to have a contradiction between the code and the comments.

create or replace function public.connect_database( 
    this_database_name text, 
    this_user_name text, 
    this_password text
    ) 
    returns boolean 
    language 'plpgsql' 
    cost 100 
    volatile security definer  
as $BODY$ 
declare 
    sql_stmt text; 
    fdw_server_exists boolean; 
    this_fdw_server_name text; 
    this_host_address text; 
    this_port_number text; 
    this_fdw_table_name text; 
begin

    sql_stmt =     'select pg_server_address, pg_port_number '
    		|| 'from public.get_database_parameters('
                || ''''
                || this_database_name
                || ''''
                || ','
                || ''''
                || this_user_name
                || '''' 
                || ')';
                
    execute sql_stmt into this_host_address, this_port_number;
    
    
    this_fdw_server_name = 'fdw_' || this_database_name;
    
    sql_stmt =     'select public.is_database_connected('
                || ''''
                || this_database_name
                || ''''   
                || ');';

    execute sql_stmt into fdw_server_exists;

    if fdw_server_exists is true then
        return true;
    end if;

    sql_stmt = 	   'create server '
    		|| quote_ident(this_fdw_server_name)
		|| ' foreign data wrapper postgres_fdw '
		|| ' options (host '
                || ''''
                || this_host_address
                || ''''
                || ', dbname '
		|| ''''
		|| this_database_name
                || ''''
                || ', port '
                || ''''
                || this_port_number
		|| ''''
                || ')';
 
    execute sql_stmt;
    
    
    sql_stmt = 	   'create user mapping for public '
    		|| ' server '
                || this_fdw_server_name
                || ' options (user '
                || ''''
                || this_user_name
               	|| ''''
                || ', password '
                || ''''
                || this_password
                || ''''
                || ')';
  
    execute sql_stmt;
    
    
    sql_stmt =     'grant usage on foreign server '
    		|| quote_ident(this_fdw_server_name)
                || ' to '
                || quote_ident(this_user_name);
                
               
    execute sql_stmt;
     
    this_fdw_table_name = this_database_name || '_fdw_table_definitions';

    sql_stmt =     'create foreign table ' 
            	|| quote_ident(this_fdw_table_name)
            	|| ' ('
            	|| 'schema_name text,'
            	|| 'table_name text,'
            	|| 'fdw_table_definition text'
            	|| ') server '
            	|| quote_ident(this_fdw_server_name)
            	|| ' options ('
            	|| ' schema_name '
            	|| ''''
            	|| 'public'
            	|| ''''
            	|| ','
            	|| ' table_name '
            	|| ''''
            	|| 'fdw_table_definitions'
            	|| ''''
            	|| ')';

    execute sql_stmt;
    
    perform public.write2log(
		  'connect_database : '  
               || quote_ident(this_database_name)
               || ' connected'
    );
    return true;
    
    exception when others then 
        perform public.write2log(
		        'ERROR : connect_database : '  
                     || sql_stmt 
        	     || ', ' 
        	     || sqlerrm
         );
	return false;
    
end 

$BODY$;

The Sandbox framework : 3. The kernel

 

The kernel is a database used by the system administrators to set :
• the databases and the IP address of the servers where they reside
• the name of the users and the databases they are allowed to connect to
• the tables to be mapped in function of the application

The first two pieces of information are given by the view permissions_matrix and the third one by the table sdbx_tables_2_attach.

The kernel must be connected at the sandbox at login time with the instruction

select public.connect_kernel(main_server, user_name, password)

The main server is the server where the sandboxes and the kernel reside. The corporate databases can reside on other servers.

create or replace function public.connect_kernel(
	this_host_address text,
	this_user_name text,
	this_password text,
	this_port_number text default '5432'
    )
    returns boolean
    language 'plpgsql'
    volatile security definer
AS $$
declare
	sql_stmt text;
   	fdw_server_exists boolean;
begin

    sql_stmt =     'select public.is_database_connected('
                || ''''
                || 'kernel'
                || ''''
                || ');';

    execute sql_stmt into fdw_server_exists;

    if fdw_server_exists is true then
        return true;
    end if;

    sql_stmt = 	   'create server '
    		|| 'fdw_kernel'
		|| ' foreign data wrapper postgres_fdw '
		|| ' options (host '
                || ''''
                || this_host_address
                || ''''
                || ', dbname '
		|| ''''
		|| 'kernel'
                || ''''
                || ', port '
                || ''''
                || this_port_number
		|| ''''
                || ');';
                
    execute sql_stmt;
    
    
    sql_stmt = 	   'create user mapping for public '
    		|| ' server '
                || 'fdw_kernel'
                || ' options (user '
                || ''''
                || this_user_name
               	|| ''''
                || ', password '
                || ''''
                || this_password
                || ''''
                || ');';
                
    execute sql_stmt;
    
    sql_stmt =     'grant usage on foreign server fdw_kernel to '
                || this_user_name;
                
    execute sql_stmt;
     
    create foreign table permissions_matrix (
	pg_user_name text,
        pg_permission text,
    	pg_database_name text,
    	pg_server_name text,
    	pg_server_address text,
        pg_port_number text,
        database_type text
    ) server fdw_kernel
        options (
            schema_name 'public',
            table_name 'permissions_matrix'
    );
 	

    create foreign table sdbx_tables_2_attach (
        database_type text, 
	front_end_table_name text,
	pg_table_name text, 
	pg_pk text,
        database_type text
    ) server fdw_kernel
        options (
            schema_name 'public',
            table_name 'sdbx_tables_2_attach'
    );
    
    perform public.write2log(
           'connect_database : '  
        || ' kernel connected'
    	);
	return true;
    
    exception when others then 
  	perform public.write2log(
              'ERROR : connect_kernel : ' 
           || sql_stmt
           || ', '
           || sqlerrm
    	 );
	 return false;
    
end 
$BODY$;

There are several databases having the same structure but not the same data.

The table sdbx_tables_2_attach has a column database_type and,  for each database type, this table gives the name of the tables to be mapped with fdw.

A join on the column database_type between permissions_matrix that gives the database name and sdbx_tables_2_attach will give the list of the tables to be mapped from this database.

The column pg_pk of  sdbx_tables_2_attach is specific to the application I develop for my customer.

The sandbox framework : 2. A fdw table definitions server

To create a foreign table, we have to write :

create foreign_table_name (
 col1 data_type1,
 col2 data_type2,
 col3 data_type3,
 ...
 )
 server fdw_server_name
 options (schema_nameschema_name’, table_nametable_name’)

If you have a lot of tables to map with fdw, it is tedious to write for each such a statement.

Furthermore, if you modify tables on the source database, you must modify the create foreign table statements.

Our solution solves both problems.

On each database from which we want take tables, we have a view fdw_table_definitions with the create foreign table statement for all tables and views.

This string contains placeholders for variables that will be given when we map the table in the destination database :
• to_table_name
• from_schema_name
• to_schema_name

In the function where we create the fdw server (that will be explained later), we create a foreign table that maps this view.

We give to this view the name database_name_fdw_table_definitions where database_name is the name of the source database.

Each time that the application needs to map a table, a function attach_table is issued that substitutes placeholders with actual values and executes the statement create foreign table.

This function reads the view database_name_fdw_table_definitions, creates dynamically the create foreign table statement and executes it.

The query to create the fdw table definitions comes from the excellent site Postgres OnLine Journal. This query has been customised for the sandbox framework.

Let’s start with the view fdw_table_definitions :

select 
  tables.table_schema as schema_name
 ,tables.table_name
 ,get_fdw_table_definition(tables.table_schema, tables.table_name) 
                       as fdw_table_definition
from 
  information_schema.tables
where 
  tables.table_schema not in ('pg_catalog', 'information_schema');

This view calls the function get_fdw_table_definition :

create or replace function public.get_fdw_table_definition(
    this_schema_name text, 
    this_table_name text
    )
    returns text
    language 'sql'
as $body$

  with cols as (
    select
         cl.relname as table_name
        ,na.nspname as table_schema
        ,att.attname as column_name
        ,format_type(ty.oid,att.atttypmod) as column_type
        ,attnum as ordinal_position
    from
        pg_attribute as att
    join pg_type as ty
        on ty.oid = atttypid
    join pg_namespace as tn
        on tn.oid = ty.typnamespace
    join pg_class as cl
        on cl.oid = att.attrelid
    join pg_namespace as na 
        on na.oid = cl.relnamespace
    left outer join pg_type as et 
        on et.oid = ty.typelem
    left outer join pg_attrdef as def 
        on adrelid = att.attrelid and adnum = att.attnum
    where 
        cl.relkind in ('v','r')   -- take only tables and views
    and
        na.nspname = this_schema_name 
    and 
        cl.relname = this_table_name 
    and 
        att.attnum > 0
    order by
        att.attnum
)
select
     'create foreign table THIS_TO_TABLE_SCHEMA.THIS_TO_TABLE_NAME ('
  || string_agg(quote_ident(column_name)
  || ' '
  || column_type,  ', ' order by ordinal_position)
  || ')  server this_fdw_server_name options (schema_name '
  || ''''
  || 'THIS_FROM_SCHEMA_NAME'
  || ''''
  || ','
  || 'table_name '
  || ''''
  || table_name
  || ''''
  || '); '
from
  cols
group by
  table_schema, table_name

$body$;

THIS_TO_TABLE_SCHEMA, THIS_TO_TABLE_NAME and THIS_FROM_SCHEMA_NAME are the placeholders.

The sandbox framework : 1. Architecture

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.

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 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.

Usage

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.

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;

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 :
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 :
to_table_name
from_schema_name
to_schema_name

The kernel

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.

Notes

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.