The kernel is a database used by the management 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 sdb_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
    ) 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
    ) 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$;