5

pg_dump and pg_restore will set search_path to an empty string, which will fail if there are functions that call functions in the public schema without explicitly specifying the schema name (ie: uuid_generate_v4() vs public.uuid_generate_v4()).

How may I force pg_restore to use a specified search_path such as public?

1 Answer 1

3

You cannot. You will have to fix your insecure function definitions.

If you cannot do that, because you only have the dump, you can convert it to an SQL script with pg_restore -f sql_file dump_file and edit the function definitions in that SQL script.

4
  • How may I do that if the dump is in directory format? Nov 2, 2021 at 3:58
  • 1
    Just the same. Think dump_dir rather than dump_file in my answer. Nov 2, 2021 at 3:59
  • Is considered insecure invoking functions without adding schema prefix? I'm thinking in small projects with small databases. When working in large environments I'm used to it but in projects with only a bunch of tables no.
    – EAmez
    Jan 23 at 9:10
  • 1
    @EAmez It is insecure only if untrusted users can create functions (e.g., you forgot to REVOKE the CREATE privilege on schema public). If that is not possible, it is not a security problem, but you still can break the application by changing search_path. Best practice is to either schema qualify your functions or to explicitly control the setting of search_path in your application. Jan 23 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.