[SOLVED] Postgres set working schema from inside of function

Issue

I am facing a problem I have a function which used to create schema and tables inside that schema after table creation I am calling a function which supposed to populate this schema however feels like the second function doesn’t set the working schema and throws the error that object doesn’t exits (

ERROR:  relation "table" does not exist
LINE 1: INSERT INTO table

here is what the function looks like.

    CREATE OR REPLACE FUNCTION create_schema(
        t_shema character varying,
        t_country TEXT
        )
        RETURNS character varying
        LANGUAGE 'plpgsql'
        COST 100
        VOLATILE PARALLEL UNSAFE
    AS $BODY$
    DECLARE
    
        tname text := t_shema;
        tschem_name text := tname||'_work';
        tsql_dyn text ;
        tschema_check numeric := 0 ;
    
     BEGIN
    
        SELECT 1 
          INTO TSCHEMA_CHECK
          FROM PG_NAMESPACE
         WHERE NSPNAME = TSCHEM_NAME;
    
     IF TSCHEMA_CHECK  = 1 THEN 
     
        RETURN 'Schema '||tschem_name ||' Already exists';
        
     ELSE 
        tsql_dyn := 'CREATE SCHEMA '||tschem_name||';';
        raise notice 'EXECUTE %', tsql_dyn;
            EXECUTE tsql_dyn;
    
        tsql_dyn := 'SET search_path TO  '||tschem_name ||';';
        raise notice 'EXECUTE %', tsql_dyn;
            EXECUTE tsql_dyn;
    
    
    --other DDLs
---execute of function which populates freshly created schema

    SELECT public.populate_empty_schema(tname, t_country);
    
    RETURN tname ||' created';
    
    END IF;
    
     END;
    $BODY$;

The second function also has a statement as first which sets the working schema.
Both functions work fine if get called separately, trows error only if second get called from the first

Solution

Your function is vulnerable to SQL injection.

Instead of

tsql_dyn := 'CREATE SCHEMA '||tschem_name||';';

write

tsql_dyn := format('CREATE SCHEMA %I', tschem_name);

To set the search_path in populate_empty_schema, pass the schema name to the function and have it execute

PERFORM set_config('search_path', v_schema, TRUE);

Answered By – Laurenz Albe

Answer Checked By – Timothy Miller (BugsFixing Admin)