Thread: BUG #18249: pg_dump/pg_restore single schema with function1 calling function2

The following bug has been logged on the website:

Bug reference:      18249
Logged by:          Wilma Wantren
Email address:      wilma.wantren@web.de
PostgreSQL version: 13.4
Operating system:   All
Description:

The company I work for used to only use Oracle databases for their program
and now also supports PostgreSQL.
With Postgres, we create a database, a user and a schema, which all have the
same name. The name is chosen by the customers.
Nevertheless, the customers would like to export the data and definitions
and import them under a new name.
This works well by using the -O (no owner) option for pg_dump/pg_restore and
only exporting and importing the schema. After the import the schema is
renamed. So far so simple and easy!
But there is one problem: we have a database function function1, which calls
another function function2. The call is made without a schema prefix because
these are just two functions that we make available to the customers and we
don't think an extra schema for these two functions is worthwhile. However,
function1 is used in an index and the restore of the index fails if there is
data in the corresponding table column. This is because pg_dump/pg_restore
set the search_path to '' when restoring, which means that function1 cannot
find the function2.
Is there any way to solve this? It is not a big problem and we can tell the
customer to ignore the error message and recreate the indices after the
import, but it would be nicer without an error message.

Here are the steps to reproduce:
Contents of file "create_dumper.sql":
create user dumper;
create database dumper owner dumper;
alter user dumper set search_path to dumper;
\c dumper dumper;
create schema dumper;
create or replace function function2(str varchar) returns varchar as $$
begin
return str || str;
end;
$$ language plpgsql strict immutable;

create or replace function function1(str varchar) returns varchar as $$
begin
return str || 'hallo' || function2(str);
end;
$$ language plpgsql strict immutable;

create table table1(str varchar not null);
insert into table1 values('test');
create index index1 on table1(function1(str));

Call with
psql -f create_dumper.sql

Export:
pg_dump -h localhost -p 5432 -U dumper --schema dumper -O -f dumper.sql

Contents of file "create_restorer.sql":
create user restorer;
create database restorer owner restorer;

Call with:
psql -f create_restorer.sql

Import:
psql restorer restorer -f dumper.sql

On Fri, 2023-12-15 at 09:09 +0000, PG Bug reporting form wrote:
> [...] pg_dump/pg_restore [...]
>
> But there is one problem: we have a database function function1, which calls
> another function function2. The call is made without a schema prefix because
> these are just two functions that we make available to the customers and we
> don't think an extra schema for these two functions is worthwhile. However,
> function1 is used in an index and the restore of the index fails if there is
> data in the corresponding table column. This is because pg_dump/pg_restore
> set the search_path to '' when restoring, which means that function1 cannot
> find the function2.
> Is there any way to solve this?

This is a bug, but not a PostgreSQL bug.  The bug is in your object definition.
Relying on "search_path" like that is a security risk, particularly in
functions used in an index definition.  If a privileged user inserts a value
in an environment where somebody else can manipulate "search_path", anything
can happen.

The best fix is to

  ALTER FUNCTION ... SET search_path = only, safe, schemas;

Yours,
Laurenz Albe

Not to be trite, but schema qualifying your function/tables would have prevented this.

create or replace function MYSCHEMA.function1(str varchar) returns varchar as $$ begin return str || 'hallo' ||
function2(str);end; $$ language plpgsql strict immutable;
 

relying on search_path is a dangerous games.  Be specific in your SQL and you'll get better long term results.

Then if the schema name changes, everything downstream changes too.  Or you can edit the dump.sql


-----Original Message-----
From: PG Bug reporting form <noreply@postgresql.org> 
Sent: Friday, December 15, 2023 1:09 AM
To: pgsql-bugs@lists.postgresql.org
Cc: wilma.wantren@web.de
Subject: [EXTERNAL] BUG #18249: pg_dump/pg_restore single schema with function1 calling function2



The following bug has been logged on the website:

Bug reference:      18249
Logged by:          Wilma Wantren
Email address:      wilma.wantren@web.de
PostgreSQL version: 13.4
Operating system:   All
Description:        

The company I work for used to only use Oracle databases for their program and now also supports PostgreSQL.
With Postgres, we create a database, a user and a schema, which all have the same name. The name is chosen by the
customers.
Nevertheless, the customers would like to export the data and definitions and import them under a new name.
This works well by using the -O (no owner) option for pg_dump/pg_restore and only exporting and importing the schema.
Afterthe import the schema is renamed. So far so simple and easy!
 
But there is one problem: we have a database function function1, which calls another function function2. The call is
madewithout a schema prefix because these are just two functions that we make available to the customers and we don't
thinkan extra schema for these two functions is worthwhile. However,
 
function1 is used in an index and the restore of the index fails if there is data in the corresponding table column.
Thisis because pg_dump/pg_restore set the search_path to '' when restoring, which means that function1 cannot find the
function2.
Is there any way to solve this? It is not a big problem and we can tell the customer to ignore the error message and
recreatethe indices after the import, but it would be nicer without an error message.
 

Here are the steps to reproduce:
Contents of file "create_dumper.sql":
create user dumper;
create database dumper owner dumper;
alter user dumper set search_path to dumper; \c dumper dumper; create schema dumper; create or replace function
function2(strvarchar) returns varchar as $$ begin return str || str; end; $$ language plpgsql strict immutable;
 

create or replace function function1(str varchar) returns varchar as $$ begin return str || 'hallo' || function2(str);
end;$$ language plpgsql strict immutable;
 

create table table1(str varchar not null); insert into table1 values('test'); create index index1 on
table1(function1(str));

Call with
psql -f create_dumper.sql

Export:
pg_dump -h localhost -p 5432 -U dumper --schema dumper -O -f dumper.sql

Contents of file "create_restorer.sql":
create user restorer;
create database restorer owner restorer;

Call with:
psql -f create_restorer.sql

Import:
psql restorer restorer -f dumper.sql