Database issues when restoring/removing

[MayeulC]

Remove:

2022-08-31 16:30:39,193: WARNING - ERREUR:  erreur de syntaxe sur ou près de « @ »
2022-08-31 16:30:39,193: WARNING - LIGNE 1 : DROP OWNED BY @signalbot:tld.com;

Restore:

Info : [#####++++++++++.....] > Restoring the PostgreSQL database...
Attention : ERREUR:  erreur de syntaxe sur ou près de « WITH »
Attention : LIGNE 1 : CREATE USER  WITH ENCRYPTED PASSWORD 'xxxxxx...
Attention :                        ^
Attention : ERREUR:  erreur de syntaxe sur ou près de « ; »
Attention : LIGNE 1 : CREATE DATABASE ;
Attention :                           ^

[MayeulC]

I really think these lines are useless and don't do anything:

@yalh76, can you comment why you added them in b2c96f3 ?

mautrix_signal_ynh/scripts/remove

Lines 69 to 74 in 43ff5e3

	ynh_psql_execute_as_root --database=$synapse_db_name --sql="DROP OWNED BY ""$bot_synapse_db_user"";"
	ynh_psql_execute_as_root --database=$synapse_db_name --sql="DROP USER ""$bot_synapse_db_user"";"
	ynh_psql_execute_as_root --database=$synapse_db_name --sql="DROP OWNED BY ""$botname"";"
	ynh_psql_execute_as_root --database=$synapse_db_name --sql="DROP USER ""$botname"";"
	ynh_psql_execute_as_root --database=$synapse_db_name --sql="DROP OWNED BY ""$signald_user"";"
	ynh_psql_execute_as_root --database=$synapse_db_name --sql="DROP USER ""$signald_user"";"

There is only one user in the db, it's mautrix_signal. On my system:

matrix_synapse=# SELECT rolname FROM pg_roles;
          rolname          
---------------------------
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 invidious
 matrix_synapse
 mautrix_signal
 onlyoffice
 postgres
(13 lignes)

If you want to DROP OWNED BY mautrix_signal, this has to be performed before deleting the role, so the two lines before are useless as well. Moreover, I double-checked that mautrix_signal only owns tables in the mautrix_signal db.

Error log output in CI or when removing:

14025 WARNING ERROR:  role "mautrix_signal" does not exist
14571 WARNING ERROR:  role "mautrix_signal" does not exist
15123 WARNING ERROR:  syntax error at or near "@"
15124 WARNING LINE 1: DROP OWNED BY @signalbot:domain.tld;
15125 WARNING                       ^
15668 WARNING ERROR:  syntax error at or near "@"
15669 WARNING LINE 1: DROP USER @signalbot:domain.tld;
15670 WARNING                   ^
16228 WARNING ERROR:  role "signalbot" does not exist
16794 WARNING ERROR:  role "signalbot" does not exist
17347 WARNING ERROR:  role "signald" does not exist
17911 WARNING ERROR:  role "signald" does not exist

I suggest reverting b2c96f3. What was even the idea behind? To purge all messages from the bot, and portal rooms? I am not sure if that's desirable, but that surely doesn't have anything to do with psql's role system.

[yalh76]

I really think these lines are useless and don't do anything:

@yalh76, can you comment why you added them in b2c96f3 ?

They come from https://github.com/YunoHost-Apps/mautrix_whatsapp_ynh/blob/aa421e61f99f25a1f373bcb54b986f9c00416579/scripts/remove#L63-L71

There was no reason for one mautrix_*_ynh needed it and not the other ones, I didn't dig down into it, just copy/pasted

[MayeulC]

Well, mautrix_whatsapp uses the Go mautrix library, while telegram, signal and facebook use the python one. I may dig a bit more, but I think we might as well remove these lines.

[yalh76]

Well, mautrix_whatsapp uses the Go mautrix library, while telegram, signal and facebook use the python one. I may dig a bit more, but I think we might as well remove these lines.

Feel free to do then ;)