current_schema: converting NULL to string is unsupported

[StephaneBunel]

Describe the Bug
SQL error when creating driver using postgres.WithInstance()
sql: Scan error on column index 0, name "current_schema": converting NULL to string is unsupported in line 0: SELECT CURRENT_SCHEMA()

Steps to Reproduce

pgConn, err := sql.Open("postgres", "postgres://login:pass@localhost/db?sslmode=disable&search_path=dev")
driver, err := postgres.WithInstance(pgConn, &postgres.Config{})

Expected Behavior
no error

Migrate Version
v4.15.1

Postgres Version
v14.1

Go Version
go1.17.5 linux/amd64

[StephaneBunel]

Was a missing grant

[trialblaze-bt]

@StephaneBunel What was the missing grant that you mentioned above? Sorry, I know it's a long time ago. I am getting this error while using go-migrate on a GCP Cloud SQL (Postgres) instance and can't seem to find what's wrong with it. I have even tried running the migration using the postgres user.

[trialblaze-bt]

I had to set search_path=xxxx in the connection string to get it to work as expected.

[happilymarrieddad]

migrate -path db/migration -database "postgresql://<user>:<password>@localhost:5432/<database>?sslmode=disable&search_path=<schema>" -verbose up

This is my connection string and I'm getting the above error. I didn't want to post the actual user name, password, database and schema but I did use actual values that work. What was the grant?

[kutterback]

For anyone getting here with a similar error. There is a schema called public on every newly created PostgreSQL database that you can use. I am surprised that this isn't the fallback if no search_path is given. At least a helpful error would be returned for this fallback using the verbose flag.