5

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.

spoonboy
  • 2,570
  • 5
  • 32
  • 56
  • 1
    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? – redneb Sep 13 '16 at 00:14
  • 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". – spoonboy Sep 13 '16 at 00:37

2 Answers2

13

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 2
    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. – spoonboy Sep 13 '16 at 00:49
0

Using python/psycopg2/SQLAlchemy, I had to use

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