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$;