Our company has a policy where each app creates a dedicated database schema and two users per app schema - a runtime appname user and an admin appname-admin user (for running migrations). I was migrating an existing application to this pattern, trying to do what we have done in the other apps, and all tests seem to work fine. Then I tried firing up the application, which ran the Liquibase migrations just fine and then I started getting errors when queries against the table foo were executed.

ERROR: relation "foo" does not exist

I could verify in psql that the Liquibase migrations had been working fine, so the tables were there, in the right schema, so this seemed like an obvious thing: the Postgres SEARCH_PATH did not include the correct schema on the runtime user. I had done this a number of times before, but unfortunately I could not get it working, no matter what I tried!

Work done

GRANTs and DDLs

We created a Liquibase changeset that would run on each run to ensure all permissions are set correctly:

    <changeSet runAlways="true" id="set_admin_role_and_give_grants" dbms="postgresql">
        <sql>
            ALTER TABLE databasechangelog OWNER TO "myapp-admin";
            ALTER TABLE databasechangeloglock OWNER TO "myapp-admin";
            SET ROLE TO "myapp-admin";
            GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "myapp" TO "myapp";
            ALTER DEFAULT PRIVILEGES IN SCHEMA "myapp" GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "myapp";
            GRANT USAGE ON ALL SEQUENCES IN SCHEMA "myapp" TO "myapp";
            ALTER DEFAULT PRIVILEGES IN SCHEMA "myapp" GRANT USAGE ON SEQUENCES TO "myapp";
        </sql>
    </changeSet>

init.sql

This is being used both by the Docker Compose setup and our Testcontainers in integration tests:

CREATE SCHEMA "myapp";
SET SEARCH_PATH = 'myapp';
CREATE ROLE "myapp" LOGIN PASSWORD 'myapp';
ALTER DATABASE "myapp" SET SEARCH_PATH TO "myapp";
ALTER USER "myapp" SET SEARCH_PATH = 'myapp';

Docker Compose

For our Docker Compose setup, it runs the init like this:

name: myapp

services:
  db:
    image: postgres
    restart: always
    ports:
      - "54341:5432"
    environment:
      POSTGRES_DB: myapp
      POSTGRES_USER: myapp-admin
      POSTGRES_PASSWORD: myapp
    volumes:
      - ./src/main/resources/local-db-init.sql:/docker-entrypoint-initdb.d/init.sql

Testcontainers

Reusing the same setup as Docker Compose

    @Container
    @ServiceConnection
    static JdbcDatabaseContainer<?> postgreSQLContainer = new PostgreSQLContainer<>("postgres:16")
        .withDatabaseName("myapp")
        .withUsername("myapp-admin")
        .withPassword("myapp")
        .withInitScript("./local-db-init.sql");

Hikari schema

In Spring Boot's application.yaml I set the Hikari schema:

spring.datasource.hikari.schema: "myapp"

Append the schema to the JDBC URL

In Spring Boot's application.yaml I set the Postgres search path directly on the URL using the currentSchema parameter (available since Postgres 9):

spring.datasource.url: jdbc:postgresql://localhost:5432/mydatabase?currentSchema=myschema

This should not at all be necessary, given the Hikari schema settings, but tried it regardless ...

Verification

The really weird thing is that when I debugged this in IntelliJ, I set a breakpoint just before the query ran and inspected the DataSource. The URL was right and the schema was right. But when the query ran, it did not see the tables in that schema. So strange, what gives?

The simple detail that was missing: granting usage rights ...

The Liquibase migration that sets up permissions was granting all kinds of concrete rights on that schema for the runtime user. Unfortunately, there is a permission higher up that needs to be granted:

GRANT USAGE ON SCHEMA "myapp" TO "myapp";

Once that was in place, everything works fine. It would, of course, be nice if we were alerted of this somehow by a more telling error message, but alas.