Postgres Search Path between connections

Question

I'm a noob to Postgres, and am setting up a multi-tenant database using one schema per tenant.

To target each tenant I need to set search_path accordingly.

I'm worried about accidentally executing something intended for one tenant on another tenant, if the wrong search path is picked up somehow.

I will be executing Functions from .Net code (via NpgSql). The functions will reside in the public schema. Each function will have the search path set inside it, like so:

BEGIN
 PERFORM set_config('search_path', tenant, true);
...
END

Is there any danger, via concurrency or connection pooling, that the search paths for two calls might be shared, for instance if the 'true' parameter was forgotten or set to false.

I really want to be sure that there's no danger of data from one tenant crossing over in to another.

Thanks in advance.

Answer 1

You should clean the state when you return a connection to the pool.

This is typically done with DISCARD ALL.

If all you need is to reset configuration parameters, you should use RESET ALL.