the user "mmuser" is not owner of the "public" schema

[DJIronic]

Hello,

I am having issue with migrating from MySQL to Postgres.

I am following the tutorial here: https://docs.mattermost.com/deploy/postgres-migration-assist-tool.html and I also followed the prepare your database document: https://docs.mattermost.com/install/prepare-mattermost-database.html

I installed a clear Postgress install, didi all the command and when I try to the command

./migration-assist postgres "postgres://mmuser:pass@localhost:5432/mattermost" --run-migrations --mattermost-version="9.10.1"
it will fail on

2024-08-13 16:10:13 pinging postgres...
2024-08-13 16:10:13 connected to postgres successfully.
An Error Occurred: could not check schema owner: the user "mmuser" is not owner of the "public" schema

I checked who was the owner of the DB it was mmuser, i also tried some command for setting the owner and grants that were listed on MM forums, but I was unable to fix the issue.

I also reinstalled Postgres and recreated the cluster to be sure.

Any idea what can help me fix the issue?

I am runnig Postgres 16.1 on ubuntu 22.04.4.

[agnivade]

mmuser might be the owner of the DB, but it doesn't look like it's the owner of the schema.

To fix this:

Run: sudo -u postgres psql to get in the console.

And then: \c mattermost. Verify it worked by running SELECT current_database();.

Then run ALTER SCHEMA public OWNER TO mmuser;
and GRANT ALL ON SCHEMA public to mmuser;

This should fix it. Let me know if it doesn’t.

[polygon0323]

Same problem.
apply the fix and continue, get the following error.

$ pgloader migration.load > migration.log
KABOOM!
FATAL error: Database error 42501: permission denied for database mattermost
QUERY: ALTER SCHEMA public RENAME TO mattermost;
An unhandled error condition has been signalled:
   Database error 42501: permission denied for database mattermost
QUERY: ALTER SCHEMA public RENAME TO mattermost;




What I am doing here?

Database error 42501: permission denied for database mattermost
QUERY: ALTER SCHEMA public RENAME TO mattermost;

Mattermost 9.10.1
Ubuntu 20.04.6 LTS
MySQL 8.0.39-0ubuntu0.20.04.1 (x64)
PostgreSQL 12+214ubuntu0.1

[DJIronic]

mmuser might be the owner of the DB, but it doesn't look like it's the owner of the schema.

To fix this:

Run: sudo -u postgres psql to get in the console.

And then: \c mattermost. Verify it worked by running SELECT current_database();.

Then run ALTER SCHEMA public OWNER TO mmuser; and GRANT ALL ON SCHEMA public to mmuser;

This should fix it. Let me know if it doesn’t.

Yes, this helped me.

[DJIronic]

Okay, I hit another issue with the migration

Importing the calls, playbooks and boards will fail:

simonc@mm:~$ pgloader boards.load > boards_migration.log
KABOOM!
FATAL error: Database error 42P01: relation "mattermost.focalboard_blocks" does not exist
QUERY: UPDATE mattermost.focalboard_blocks SET "fields" = '{}'::json WHERE "fields"::text = '';
An unhandled error condition has been signalled:
   Database error 42P01: relation "mattermost.focalboard_blocks" does not exist
QUERY: UPDATE mattermost.focalboard_blocks SET "fields" = '{}'::json WHERE "fields"::text = '';




What I am doing here?

Database error 42P01: relation "mattermost.focalboard_blocks" does not exist
QUERY: UPDATE mattermost.focalboard_blocks SET "fields" = '{}'::json WHERE "fields"::text = '';

As I do not have any playbooks or board ad calls history is not important, I tried to run MM even after those pgloader commands fail, but it was unable to start.

MM will not start with this error:

srp 14 07:41:11 mm mattermost[1008557]: {"timestamp":"2024-08-14 07:41:11.305 Z","level":"info","msg":"EXPERIMENTAL: Starting Bleve","caller":"bleveengine/bleve.go:186"}
srp 14 07:41:11 mm mattermost[1008557]: {"timestamp":"2024-08-14 07:41:11.309 Z","level":"info","msg":"Pinging SQL","caller":"sql/sql_utils.go:67","database":"master","dataSource":"postgres://%2A%2A%2A%2A:%2A%2A%2A%2A@localhost:5432/mat>srp 14 07:41:11 mm mattermost[1008557]: Error: failed to initialize platform: cannot create store: failed to apply database migrations: driver: postgres, message: failed to fetch current schema, command: current_schema, originalError: s>srp 14 07:41:11 mm mattermost[1008557]: SELECT CURRENT_SCHEMA()
srp 14 07:41:11 mm mattermost[1008557]: {"timestamp":"2024-08-14 07:41:11.315 Z","level":"error","msg":"failed to initialize platform: cannot create store: failed to apply database migrations: driver: postgres, message: failed to fetch >srp 14 07:41:11 mm mattermost[1008557]: Usage:

[agnivade]

@DJIronic - Your MM is not starting because your search_path somehow is set to empty.

I'd suggest to log in to the DB. psql -h localhost -U mmuser -d mattermost, again verify you are in the right database with select current_database();.

Now type select current_schema();. This should return nil. Then show search_path; should also be empty.

To fix this, run ALTER user mmuser in database mattermost set search_path to 'public';

And then logout, and login again. And run select current_schema(); to confirm that you see public in the output.

That should fix it.

[agnivade]

@polygon0323 - Have you ensured that mmuser is the owner of mattermost database? Run ALTER DATABASE mattermost OWNER TO mmuser; if that's not the case.

[polygon0323]

It seems that if an error occurs in pgloader, such as MySQL authentication failure, it will not work correctly.
If an error occurs during the process, I retry from the beginning, but the following error occurs.

Perhaps my environment is not correct and I will continue to try.

$ pgloader migration.load  > migration.log
KABOOM!
UNDEFINED-TABLE: Database error 42P01: relation "mattermost.systems" does not exist
QUERY: TRUNCATE TABLE mattermost.systems;
An unhandled error condition has been signalled:
   Database error 42P01: relation "mattermost.systems" does not exist
QUERY: TRUNCATE TABLE mattermost.systems;

What I am doing here?

Database error 42P01: relation "mattermost.systems" does not exist
QUERY: TRUNCATE TABLE mattermost.systems;

(Sorry for the incorrect English due to translation)

[polygon0323]

I have successfully migrated!
Thanks for the support.

What I did:

• Build pgloader from source (3.6.1 → 3.6.29afa9d)
• Drop PostgreSQL schema and recreate public schema
• Modified MySQL configuration (mysql_native_password, mmuser@% -> mmuser@localhost)

[DJIronic]

@agnivade

I am afraid something is not as exptected. This is the output of those first two commands, they are not empty.

But I tried to ALTER user mmuser in database mattermost set search_path to 'public';

But it did not help.

[agnivade]

Something is not right with the search path. What happens if you do set search_path to 'public'; and then select current_schema(); in the same session? I think there's some other override, perhaps at the DB level which is messing this.

[DJIronic]

Here you go.
I installed fresh postgress just for Mattermost on the VM.

here is the log:

PS: Ignore the error, that was a spelling mistake :)

[agnivade]

Interesting, and you have run

• ALTER DATABASE mattermost OWNER TO mmuser;
• ALTER SCHEMA public OWNER TO mmuser;
• and GRANT ALL ON SCHEMA public to mmuser; correct?

Ensure that you run it from the mattermost database.

[DJIronic]

Yes I did everything thats in the "Prepare your database tutorial".

but now, the second command fails:

mattermost=> ALTER SCHEMA public OWNER TO mmuser;
ERROR:  schema "public" does not exist

[polygon0323]

I re-created below and it worked.

DROP SCHEMA mattermost CASCADE;
CREATE SCHEMA public;
ALTER SCHEMA public OWNER TO mmuser;
GRANT ALL ON SCHEMA public to mmuser;

[DJIronic]

I recreated the schema as suggested and imported all data again.

Importing the migration.load and calls.load worked. but playbooks and boards failed:

simonc@mm:~$ pgloader playbooks.load > playbooks_migration.log
KABOOM!
FATAL error: Database error 42P01: relation "mattermost.ir_channelaction" does not exist
QUERY: ALTER TABLE mattermost.IR_ChannelAction ALTER COLUMN ActionType TYPE varchar(65536);
An unhandled error condition has been signalled:
   Database error 42P01: relation "mattermost.ir_channelaction" does not exist
QUERY: ALTER TABLE mattermost.IR_ChannelAction ALTER COLUMN ActionType TYPE varchar(65536);




What I am doing here?

Database error 42P01: relation "mattermost.ir_channelaction" does not exist
QUERY: ALTER TABLE mattermost.IR_ChannelAction ALTER COLUMN ActionType TYPE varchar(65536);