Incompatibility with postgres 15

[flaviu2001]

Describe the Bug Using the migrate tool with the newest docker image postgres:15 fails when running the command below

migrate -database "postgres://${NEW_PGUSER}:${NEW_PGPASSWORD}@${PGHOST}:${PGPORT}/${NEW_PGDATABASE}?sslmode=disable" -path scripts up

with the error

pq: permission denied for schema public in line 0: CREATE TABLE IF NOT EXISTS "public"."schema_migrations" (version bigint not null primary key, dirty boolean not null)

The issue is fixed by reverting to postgres:14.5

Steps to Reproduce Steps to reproduce the behavior:

1. My migrations look like: not relevant
2. I ran migrate with the following options: see command above
3. See error: see above

Expected Behavior Scripts run without error. The database was empty beforehand, migrate in this case is simply used to run the create table queries in integration testing.

Migrate Version v.4.15.2

Loaded Source Drivers Source drivers: file

Loaded Database Drivers Database drivers: postgres, postgresql, stub

Go Version 1.18

Stacktrace No stacktrace available.

Additional context None

[dhui]

permission denied for schema public Make sure the user you're running migrate with has the correct permissions on your new db

[jeffckc]

I also started experiencing this issue in my actions on 10/17. Thanks for sharing the workaround @flaviu2001. @dhui - regards trying to work forwards and manually resolve what used to work by default, I am now running these grants, but still getting the issue. Do you see anything I am missing?:

GRANT CONNECT ON DATABASE my_database TO my_db_owner; GRANT USAGE ON SCHEMA public TO my_db_owner; GRANT ALL ON ALL TABLES IN SCHEMA public TO my_db_owner;

migrate -path internal/adapter/persistence/schema -database "postgres://my_db_owner:REDACTED@localhost:5432/my_database?sslmode=disable" up Running migrate error: pq: permission denied for schema public in line 0: CREATE TABLE IF NOT EXISTS "schema_migrations" (version bigint not null primary key, dirty boolean not null)

[juztin]

I too am experiencing the same issue. I've specified the database name in the URL, but it seems like migrate is ignoring this and trying to create the schema_migrations within the public database.

URL: postgres://myuser:[email protected]:5432/mydbname?connect_timeout=10&dbname=mydbname

ERROR: failed to run postgres migrations: pq: permission denied for schema public in line 0: CREATE TABLE IF NOT EXISTS "public"."schema_migrations" (version bigint not null primary key, dirty boolean not null)

---

UPDATE

It DOES work, I just had to correct some permissions: https://stackoverflow.com/questions/74110708/postgres-15-permission-denied-for-schema-public

I had initially run: grant all privileges on database mydbname to myuser;

But also needed this: alter database mydbname owner to myuser;

[emzola]

I also experienced the same issue but @juztin's update did it for me. While setting up the db, I initially granted all privileges to the dbuser, but didn't set the db owner to the dbuser. So, doing that solved the problem

[cornejodev]

@juztin's update also worked for me

[jp-dakota]

@juztin 's suggestion didn't work, I got

ERROR:  must be member of role myuser