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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s