Spring Session with JDBC - how do I specify the schema for the session tables

Question

I am using Spring Session with Postgres to store user sessions for a spring boot app. Spring Session is looking for the spring_session and spring_session_attributes tables in the public schema. Is there a way to tell Spring Session to look for the session tables in a schema other than public? spring.jpa.properties.hibernate.default_schema in application.properties seems to have no affect on this.

Answer 1

I managed to solve the problem by specifying the schema in the connect string/datasource URL as follows (also as described in an answer here):

spring.datasource.url=jdbc:postgresql://localhost:9999/db_name?currentSchema=<schema_name>

Still not sure why the spring.session.jdbc.schema property in application.properties has no affect on the schema used by spring session...

Answer 2

Actually the parameter spring.session.jdbc.schema does not means the "database schema", but the SQL schema (the SQL that defines the data structure where the session data is stored).

The schemas for different DBs are available at:

https://github.com/spring-projects/spring-session/tree/master/spring-session-jdbc/src/main/resources/org/springframework/session/jdbc

Answer 3

You can set the table name to be used by Spring Session in your application.properties like this:

spring.session.jdbc.table-name = MY_SCHEMA.SPRING_SESSION

As a result the SQL queries issued by Spring Session will go against the tables of your specified schema. See also org.springframework.session.jdbc.JdbcOperationsSessionRepository.