Lately the search_path feature has been
getting a bum wrap. People deriding it over security concerns, usability issues, and down right lack of usefulness. Well my friends, I say don’t listen to the haters! The search_path feature is a really handy tool that you can use in a lot of interesting ways. I’m not just talking about making your database look like schema’s don’t exist (a primary argument for the feature way back in 7.3), but enabling functionality that would otherwise be somewhat of a pain. So I give you three cheers for the search_path:
Cheer One
One application I have been involved with development on has a feature to support multiple organizations. This is accomplished by adding a defined set of tables/function/objects for each organization into a new schema, and then having the application manipulate the search_path to act upon the right schema for each organization. Common information can be stored in the PUBLIC schema if desired. This is much easier to manage than adding new databases for each organization, and given the limited cross-database querying support in Postgres, allows for search data across organizations as needed. It’s also far easier to manage that the old “entity_id column” database pattern, where you append an organizational identifier for each row, and then filter rows based on the organization your working with. This could be achieved using table prefix naming, but that certainly can’t be considered a more elegant solution that manipulating the search_path.
Cheer Two
Managing contrib modules is the other place where search_path comes into play. Given a choice, we always put contrib modules into their own separate schema, one schema for each contrib module. This makes upgrading databases far easier, as you can easily drop/exclude individual schemas. For search_path management, the typical way we handle this is by setting the search_path at the database level. And if we really need to do schema manipulation beyond that?
pagila=# select set_config('search_path','information_schema,' || (select current_setting('search_path')),'f');
set_config
-----------------------------------
information_schema,"$user",public
(1 row)
Cheer Three
And now one for the road. At
OmniTI, we manage some of the largest (Size or transaction load) Postgres databases on the planet. One of the tools we make use of is
check_postgres. However check_postgres has the unfortunate side effect of needing super user privileges to get some of its information. We also run Postgres databases that require enough security that they aren’t going to let their nagios boxes be configured with superuser level database access. Enter the search_path. The information check_postgres needs is all maintained in the system catalogs, but the code in check_postgres doesn’t schema qualify anything, which means if you want, you can actually create filtering functions and views and put them in between the user and the system tables. Now you can make a non-superuser role, set their search_path to start with the security enabled schema, and go to town. (Btw, there’s a copy of this on
my github, if you are so inclined).
So there you have it, three ways the search_path is making my life, and hopefully yours too, a better place. It’s not perfect, but it sure is handy, and I for one hope it sticks around.