Why do Postgres schemas different from "public" sometimes not work with Craft (DDEV / Nitro)

Question

Due to limits in the production environment of our website, we can not use Postgres' standard schema public.

Therefore we configured something different (craftcms) in .env:

DB_DRIVER=pgsql
DB_SERVER=<hostname>
DB_PORT=5432
DB_DATABASE=<database>
DB_USER=<user>
DB_PASSWORD=<pw>
DB_SCHEMA=craftcms
DB_TABLE_PREFIX=

Interestingly this works in our production environment, but not locally when developing with DDEV or Nitro.

Only difference: the username of the DB connection is craftcms on production, but locally it's nitro.

Why is that?

Matthias Redl-Mann · Accepted Answer · 2022-07-29 10:27:09Z

To understand why this works on production but not locally one must understand schemas and Postgres search_path

Schemas are like an additional layer in Postgres. A database might contain an info table in the standard schema public and another one in a different schema otherschema
If you query this database for a value in the info table, Postgres will look into all schemas mentioned in a Postgres setting called search_path in the order of listing.
If search_path is set to otherschema, public, Postgres will first look in otherschema and then in public
Now the default setting for search_path is "$user", public, where $user contains the name of the user used for the DB connection. In the example from the question above, search_path would therefore be set to craftcms, public.
Queries SELECTing values FROM the database can, but not necessarily need to prefix table names with a schema, so both SELECT * FROM info; and SELECT * FROM schema.info; are valid queries. However when a schema is prefixed, the search_path will be ignored.
And here starts the problem with Yii2 and Craft and DDEV (or Nitro) . Yii2 is not capable of prefixing tables with a schema in its queries. That means, if the schema of the database is different than public, Postgres will normally not look into it for Craft's tables, even if the schema is configured in .env. Craft will believe it is not installed yet.

What solutions do we have:

You can use a Postgres user with a name equal to the schema
You can change the search_path to contain the schema name on a database level with the following query: ALTER DATABASE mydb SET search_path = otherschema;. Disadvantage: You have to redo it after a dump import. Advantage: it will not interfere with other projects.
You can change the search_path to contain the schema name on a user level: ALTER ROLE someuser SET search_path = otherschema; Advantage: will persist even after a dump import. Disadvantage: might interfere with other projects.

1 Answer 1