Postgres security for functions with "security definer"

Question

I was reading about possible security issues when creating functions in Postgres with "security definer". Official documentation suggests that search_path is set to some trusted schema followed by pg_temp. All examples I could find had pg_temp at the end. Do I have to put pg_temp at the end, what would happen if set search_path only to the trusted schema? Would it open any security holes? And why would you put pg_temp in search_path anyway?

Answer 1

pg_temp is always in the search path. If you don't add it to search_path parameter explicitly, it will behave as if it were there at the beginning of the setting. That means the user could create tables which masked the ones the function was supposed to operate on.

• Danil: I have tried calling function which was in pg_temp but not in the trusted schema and got an error that function not found
• Danil: "However, the temporary schema is only searched for relation (table, view, sequence, etc)" I see