Postgres driver doesn't respect configuration

[segfault16]

Describe the Bug
When opening a new connection to the database with

	config := postgres.Config{
		SchemaName:      "myschema",
		DatabaseName:    "db",
		MigrationsTable: "my_migrations",
	}
	driver, err := postgres.WithInstance(db, &config)

the method doesn't respect a given configuration but rather uses SELECT CURRENT_DATABASE() and SELECT CURRENT_SCHEMA() instead.

https://github.com/golang-migrate/migrate/blob/master/database/postgres/postgres.go#L52-L106

Steps to Reproduce

import (
    "database/sql"
    _ "github.com/lib/pq"
    "github.com/golang-migrate/migrate/v4"
    "github.com/golang-migrate/migrate/v4/database/postgres"
    _ "github.com/golang-migrate/migrate/v4/source/file"
)

func main() {
    db, err := sql.Open("postgres", "postgres://localhost:5432/database?sslmode=enable")
	config := postgres.Config{
		SchemaName:      "myschema",
		DatabaseName:    "db",
		MigrationsTable: "my_migrations",
	}
    driver, err := postgres.WithInstance(db, &config)
    m, err := migrate.NewWithDatabaseInstance(
        "file:///migrations",
        "postgres", driver)
    m.Steps(2)
}

Expected Behavior
Expected migrate to create migrations table named my_migrations in schema myschema.

Migrate Version
v4.5.0

Loaded Source Drivers
file

Loaded Database Drivers
postgres

Go Version
e.g. go version go1.11 linux/amd64
Obtained by running: go version

Stacktrace
Please provide if available

Additional context
Due to security guidelines we're not allowed to use pg_catalog but have to use a different schema with dedicated users having access to the schema.

[dhui]

This is fixed in master now and will go out with the next release.

[segfault16]

@dhui Great, thanks!

[dhui]

fixed in v4.7.0

[aldidana]

I think this issue is still persist

[feketegy]

This still doesn't work. If I create a new schema named "my_test_schema" and set:

postgres.Config{
    DatabaseName: "testdb",
    SchemaName: "my_test_schema",
}

The migration table and all the migrations will be created in the public schema.

I think the issue is that in the sql files I'm not using the schema name prefixed to the table names and this is why it creates it in the public schema.

I think a "SET SCHEMA " should be issued before running the migrations to make sure any sql that's read is appended on the correct schema.

[feketegy]

As per my comment above, further tested this, and I'm certain that is the problem. Currently I need to open a connection before the migration and execute SET SCHEMA 'my_schema'; and pass that connection instance to migrate and that way it's working.

[aldidana]

That's what I'm doing right now, pass the SET SCHEMA to connection instance.

But for MySQL there is no SET SCHEMA 'my_schema'. A schema is synonymous with a database in MySQL.

[feketegy]

This is specifically for the Postgres driver.

[dhui]

The issue is b/c the configured schema isn't used before migrations are run. The issue and fix involved is different from the original issue, so I've opened a new one.