Search Path and Extensions

Using search_path to implement polymorphism is one of those conveniences in PostgreSQL that can bite hard. Relying on it to select the schema for tables and functions may seem useful, but there is an entire PostgreSQL wiki page dedicated to avoiding harm from malicious use of search_path.

One recommendation from that page is to remove the public schema from search_path altogether. When I tried that I ran afoul of the placement of extensions into the public schema. This is not mentioned on the above page, but does appear in one of its references. Extensions may be “relocatable” to different schemas, but it is not clear to me how common that is.

The more accommodating recommendation is to forbid the creation of objects in public by any non-admin role. That at least avoids the issue of extension functionality disappearing under a restricted search_path.