Separating schema_migration from the search_path

[DevopsMercenary]

Description

I have a security situation where I can not allow the schema_migration table to be located in the same schema that my tables and such are located in.

I've tried a couple of different ways to do this ( have the schema_migration in its own schema )

My first method was to specify the DBMATE_MIGRATIONS_TABLE by prepending the schema name.

file: .env

DATABASE_URL="postgres://postgresUser:postgresPW@localhost:5432/test_database?search_path=test_schema&sslmode=disable"
DBMATE_MIGRATIONS_TABLE=dbmate.schema_migration

In this case I expected the schema_migration table be in a new dbmate schema AND it is.
I expected my tables to be created in the test_schema BUT my new tables are in the dbmate schema

Trying a second method, i see in the documentation that.

If multiple comma-separated schemas are passed, the first will be used for the schema_migrations table.

I assumed then if the first schema is for the schema_migrations table then the second would be used for my objects like tables etc.

My .env I tried the following

DATABASE_URL="postgres://postgresUser:postgresPW@localhost:5432/test_database?search_path=dbmate,test_schema&sslmode=disable"

But again everything is created in the dbmate schema.

I think dbmate is working as designed and the issue is that I'm trying to something that is not supported. A schema_migrations schema name variable would be nice to be able to explicitly put and use the schema_migrations table in another schema and not effect the search_path.

• Version: 2.2.0
• Database: psql (14.7 (Homebrew), server 15.2 (Debian 15.2-1.pgdg110+1))
• Operating System: MacOS Ventura 13.2 (22D49)

Steps To Reproduce

Expected Behavior

[dossy]

@DevopsMercenary Can you include the migration SQL file that you're using in your test that is creating objects in the dbmate schema incorrectly?

There are postgres driver tests that cover the custom schema functionality you're referring to.

If your migration files themselves do not explicitly specify a schema, then according to the PostgreSQL documentation on schemas it says:

Also, since myschema is the first element in the path, new objects would by default be created in it.

Can you see what the output of show search_path is when connecting to the database with the user you're using with dbmate? Specifically, I'm wondering if someone has set the search_path for the user at either the database or role level with ALTER DATABASE <name> SET search_path TO <new_search_path> or ALTER ROLE <user> SET search_path TO <new_search_path> and maybe that's interfering with the search_path you are trying to specify in the database connection string.

c.f. https://dba.stackexchange.com/questions/56023/what-is-the-search-path-for-a-given-database-and-user for more about setting database and user role level search_path

[gregwebs]

What exact commands are you running? Can you try creating the DB yourself first without using dbmate?

[dossy]

@DevopsMercenary Take a look at the comment I just added to #440. I suspect it's related to what you're seeing, as well.

I'll work on coming up with a reproducible test that better exercises multiple schemas and being able to have the schema migrations table live in one schema, while having the migrations themselves apply in the session's default schema.

Just wanted to update this issue, for those who are still tracking it.

[detj]

Faced the same issue as described by @DevopsMercenary

I mitigated by adding ?search_path=dbmate,public to the DATABASE_URL and prefixing my migration SQLs with the desired schema name. In my case it was public.

Here's how a simple create table migration looked like:

-- migrate:up
create table if not exists public.employees (
    id uuid primary key not null,
    name text
);

-- migrate:down
drop table if exists public.employees;

Upon running dbmate migrate it created a dbmate.schema_migrations table and created public.employees as expected.