amacneil / amacneil/dbmate

Separating `schema_migration` from the search_path

Open
#429 5 comments 3 reactions 0 assignees View on GitHub
bug needs more info
Dominant language
Go
Stars
7.4k
Forks
379
Avg merge
6m
Merged PRs (30d)
1

Description

**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**

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.