Search code examples
postgresqljdbcsearch-path

search_path doesn't work as expected with currentSchema in URL

I encounter difficulties when configuring a custom search path for SQL queries, specifically when trying to locate objects within the public schema. The problem arises when I specify the currentSchema parameter in the database connection URL, as it prevents successful access to the public schema, which is part of the default search path. How could this be fixed?

The long story:

  1. I have an application schema app1.

  2. The DB has the Postgis extension installed in the public schema (and we want to keep it there).

  3. The DB search_path is configured like this:

     ALTER DATABASE tst SET search_path = "$user", public

  4. When connecting to the DB without specifying current schema in URL, the default schema is public and so it finds all the geo functions and objects. But I have to specify the app1 schema prefix when addressing objects from app1, e.g.:

     select st_asgeojson(geometry,15,4) from app1.shapes limit 5

  5. This is not convenient. So I added "app1" as a current schema parameter to the connection URL like this:

     jdbc:postgresql://localhost:5432/tst?currentSchema=app1

  6. Now, when I connect to the DB, I don't have to specify the app1 prefix when addressing objects from app1 schema. However, requests that involve Postgis objects don't work anymore and fail with:

ERROR: function st_asgeojson(public.geometry, integer) does not exist

My understanding is that it should search for the objects in the search_path and find them in the public schema but it doesn't happen for some reason. I've tried specifying search path on a user level as well but it still didn't work.

Solution

  • The parameter name currentSchema is a bit misleading. It takes the whole search_path, not just the "current schema". The documentation:

    • currentSchema = String

    Specify the schema to be set in the search-path. This schema will be used to resolve unqualified object names used in statements over this connection.

    So try:

    jdbc:postgresql://localhost:5432/tst?currentSchema=app1,public

    There are various ways to pre-set the searchpath for a given user. Then you don't need anything extra in the connection string.

    You can set the search_path for a given user (applies to all DBs in the DB cluster):

    ALTER ROLE my_app1_user SET search_path = app1,public;

    Or set the search_path only for the given role in the given database:

    ALTER ROLE my_app1_user IN DATABASE tst SET search_path = app1,public;

    Or, if your user name happens to be app1, then the search path setting "$user", public resolves to app1, public automatically, and you don't need to do anything extra.

    See: