The schema to use for migration cannot be configured.

[issuefiler]

Past Issues Searched

• [x] I have searched open and closed issues to make sure that the bug has not yet been reported

Issue is a Bug Report

• [x] This is a bug report and not a feature request, nor asking for self-hosted support

Using official Plausible Cloud hosting or self-hosting?

Self-hosting

Describe the bug

I cannot configure the schema to use for migration whatsoever. I have tried everything I can possibly imagine other than directly patching the source code. Plausible is persistently trying to refer to the public schema, which does not even exist in my database.

postgresql.conf

search_path = 'pg_temp'

PostgreSQL initialization script

# CREATE DATABASE database;
CREATE ROLE plausible WITH LOGIN PASSWORD
	'SCRAM-SHA-256$4096:W77u3MgIb7Y35iRDHBzmQw==$'
	'MRjoQaSf+n9y4FVEJE43bS8v8y7wAUuwVvhGX6yhfXQ=:'
	'N5NxvOu0YzqWSl+NZR71ZSM85H7QrGTzh9d87LvH64c=';
GRANT CONNECT ON DATABASE database TO plausible;
CREATE SCHEMA plausible AUTHORIZATION plausible;
ALTER ROLE plausible SET search_path TO plausible;
\connect database
CREATE EXTENSION citext SCHEMA plausible;
GRANT USAGE ON TYPE plausible.citext TO plausible;
GRANT CREATE, USAGE ON SCHEMA plausible TO plausible;

Extra .exs configuration (EXTRA_CONFIG_PATH)

import Config
config :plausible,
	Plausible.Repo,
	[
		migration_default_prefix: "plausible",
		show_sensitive_data_on_connection_error: true,
		after_connect: {
			Postgrex,
			:query!,
			["SET search_path TO plausible;", []]
		},
		parameters: [
			application_name: "Plausible",
			search_path: "plausible"
		]
	]

config :plausible, Oban, [repo: Plausible.Repo, prefix: "plausible"]

defmodule Plausible.Repo.Migrations.AddPrefixedObanJobsTable do
	use Ecto.Migration

	def up, do: Oban.Migrations.up(prefix: "plausible")
	def down, do: Oban.Migrations.down(prefix: "plausible")
end

Plausible container log

Loading plausible..
Starting dependencies..
Starting repos..
** (Postgrex.Error) ERROR 42501 (insufficient_privilege) permission denied for schema public
    (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1096: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.18.3) lib/enum.ex:1714: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1203: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.12.1) lib/ecto/migration/runner.ex:348: Ecto.Migration.Runner.log_and_execute_ddl/3
    (elixir 1.18.3) lib/enum.ex:1714: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.12.1) lib/ecto/migration/runner.ex:311: Ecto.Migration.Runner.perform_operation/3
    (stdlib 6.2.2) timer.erl:595: :timer.tc/2

PostgreSQL container log

STATEMENT:  DO $$
	BEGIN
	IF NOT EXISTS (SELECT 1 FROM pg_type
	               WHERE typname = 'oban_job_state'
	                 AND typnamespace = 'public'::regnamespace::oid) THEN
	    CREATE TYPE "public".oban_job_state AS ENUM (
	      'available',
	      'scheduled',
	      'executing',
	      'retryable',
	      'completed',
	      'discarded'
	    );
	  END IF;
	END$$;

---

It turned out it was because the use of the public schema is hard-coded.

https://github.com/plausible/analytics/blob/e7e553cf9d27991c640bc52ef797297435aac0a2/priv/repo/migrations/20220408080058_swap_primary_oban_indexes.exs#L8-L19

https://github.com/plausible/analytics/blob/e7e553cf9d27991c640bc52ef797297435aac0a2/priv/repo/structure.sql#L13-L2738

Expected behavior

The schema to use on migration/querying has to be configurable.

Screenshots

No response

Environment

The Docker image `ghcr.io/plausible/community-edition:v3.0.1`