Separating `schema_migration` from the search_path
- 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.