search_path doesn't work as expected with currentSchema in URL

Question

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.

Lifesaver

You do SHOW search_path; to figure what the current search path is. Can you try to do SET search_path TO 'app1,public' followed by SHOW search_path; to see if it worked? – Wrongdoer

When I connect without currentSchema parameter in the URL, the SHOW search_path returns "app1, public". Then, I disconnect, add currentSchema=app1, and connect again. Now SHOW search_path returns "app1". – Lifesaver

Answer 1

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:

• How does the search_path influence identifier resolution and the "current schema"

Cesya

Thanks this works. However, I had to remove quotes (otherwise it doesn't work).So, the URL should be like this: jdbc:postgresql://localhost:5432/tst?currentSchema=app1,public. May be you can correct your answer as it took me a few minutes before I figure out the quotes issue. – Lifesaver

Answer 2

Using python/psycopg2/SQLAlchemy, I had to use

postgresql://localhost:5432/tst?options=-c%20search_path=app1,public

Innominate

= near search_path needs to be encoded as %3D , otherwise psycopg2 throws: "error when trying to initialize the conenction extra key/value separator "=" in URI query parameter: "options"" – Eachern