!rMMJVYqfOEOLttKGza:nixos.org

NixOS Matrix Suggestions

386 Members
Actionable suggestions for the Matrix space. Please hold discussion in #matrix-discussion:nixos.org, then bring the suggestion and context here. | https://pad.lassul.us/Nix-Matrix-Suggestions#127 Servers

Load older messages


SenderMessageTime
10 May 2023
@zhaofeng:zhaofeng.liZhaofeng Li

ok, I leave this to you guys, my lack in SQL-foo is definitely showing:

select * from remote_media_cache
right join (
  select
    split_part(split_part(json::json->'content'->>'url', '//', -1), '/', 1) as media_origin,
    split_part(json::json->'content'->>'url', '/', -1) as media_id
  from event_json where event_id in ('$0e2xilbm_gz_eTIoPbyRIOmdDnT2LHVv2NXrE08d8b8', '$0ROVLmCOh76LGDg3Mruq16A8Uk-zjlMnWX-q738Uzn4', '$0UMBLsIbKtLaWBI4jZWQoQ1S7r-rywa6TxPjebl6wgM', '$0UW1yDg4gXhdCoNV4gUGOvDkSToAEydyj11o6T51MNQ', '$0W1r4_-t9NhbyP0M9t_JyC2tUSancZKdomLZcvJFeRM', '$19xwyv55nkdx7KQMZ00STAeKCivd9dKSjMmTMu4aNaU', '$1qiSOCIv_hLAClCY4Zm5y93bDv7uAq_DxoDAcvWt7zs', '$2Zpj4F1NxpVxT5U_5OvFkg4Wo7Pbm6Y-9T_NJbUQ_tU', '$3mAzOyFeoP43nGKOuS5_R0aFs1uOgqWldKrUcGuNX54', '$3mzsz6vTgHv31ioE47kYrcllrtLOOMVYrDvB2UObTII', '$3S5nWOywgZOvNmmqnNwNCnrNZCsYQirt5v8ycEOX-tQ', '$4DPzd3S5pv0GY4NC3RzLji4NETJIsWw0mtBkKzotT9U', '$4Fkgv7OwJLj9k-DTHRy3TpZq1KPP2_JekbqvMEqbYa0', '$5-K51JU9C6q_Lk2JxY3NnROXodafPYvGHT1jxVirX_0', '$5opM2Y_ON9-UnETHmyxyk-Al3FjXp4clJodeBJHY9RA', '$5uosnnMMfjoiD8nIfZoMSgLBBqhRY9GqH0GlJkn3isc', '$6N3FJm3I4Go7xMrbfjoTt0TPXsiWMPupp1bC-oFmFeo', '$77Axn5B9XBbipsBv8MNryneskcDC4zjT3jBmWo3qZKw', '$7NgHhZmEIDvQpQtzOVD09cDoHuR7rmu6ckggNcelVFA', '$8GfoUdhOVlgwopoHjDPEFSj3uWgyKJ2SrDiH-54KrRw', '$8v-2opzETbwErm6HfoSXxD_1lLVggHA7WCDsKp6g7Pg', '$8ztgXacZIbQNCiiAVR6ZBFsmH8quqTqRGfx-QgQXqE0', '$9A0-hMg-ezKyQza2T_i_B6_5HRDJyVMSnXwXqlBFMGQ', '$-9kIqxd051--C_s-_pPsJ3WaJZcJ67rfefU0Zh7BdSw', '$AA1lo2y4KQ7vuah3IBiXNILP_QSIKoJCB1PVmWFnCFo', '$ALaDK1zGKWYYeNtawHZk_U30VXKkMqQiaVU7U3WeuyQ', '$AM-dFSd_rJBTe575Fb6r46CmszSTWulLBevViJTVMCg', '$anZcKcKmMTNY2gNXbcT3IMDUqp-JKrkzDAW0-hzD1TU', '$APdihM6ltZC8fLBmEPW3yhYUqPQI5gg-jnxEI56jF34', '$bnRA3Q4dC91I7pURE_yl6rlsVetKV2dpnnfVZ5Ch5TQ', '$b_nu53p_paOY-JXIDnJXA4yOdEwt3oX1oHeMn2oTn24', '$BT9s4-yx8lIQt0_PosheVUe9lRDFAU-8xJVuNugru6E', '$BtW9zI3NnmY4Tj0ag5S0WQRwdr1ihGq8hA4gIf4DQug', '$CNTLW2rET4svHeYdu4ztkoBH2xl8PrE1VtCgTEU6P0Y', '$dLQecBIiinSLJuLyrqmX82hTiJ4R5TXPQCmTJfAyWNc', '$duaixjvgIk_Y-PEAvxK9lUXATPm0lA2I40fwzokDghA', '$EAyoR7iMPX7q89xnnK9SPimmNNprSsB6y-Mc08DAzmo', '$_ECGzQY93azFfCYO95keRgNxsUNWd0LCq2oJLTQ4p6k', '$EPpvfM0D4eJoCnRQbnlz93UMyOiCYPIROt56nckxJMU', '$EqqhWeh1o6x5zIwp1lKW3afbaiK53JN74hCstPH41HE', '$Et4RrKSHI9wzqZAveYqz_6PwbL-QWJ1BRemJ8toMglA', '$eXC75xL0_nXnJ6_CYsyWBE6J-wE9M_Ca9g8QFvI_Oqg', '$fbi0loOHy6sIkBBp3rceDLfKq2SsVahfQDRKV1UVK8Q', '$FhXTlIMtMAhzqONvEN3G3CIJ2JfFZXkGIxJepAaPHAU', '$fjIag6tCOXw7Mv22eQCuPcnUnlJPK72QPjVT7e1X2nY', '$fkR8OR16ZENu9c0mNIlM7HFk45wMN_fECoafAJc8djY', '$fxTcBGPBYBQBaMsyr6zeXHJaucFb5XjazkTY6ARvrvU', '$fZlK-ZmOuTkLExuXZ3wfQxJHSJlYkaUroLuChK5MOS0', '$g5JZ4ccSq9yEwMJeU-GCR4T-AmnUJXyyh9a8yVwzMjI', '$g5LQpy2FZZQbHZSwX2QWippRY41D96lqDBh6NTXdBg4', '$GaQx0Lg6ey4CAPDA8WOdX9RHj5p2Zx4N-RxykhvCpUw', '$GEMYPPGyTKTATihc_-u5iEHOLwKMj4odzUPkua8osA8', '$gh-86z9yui90TCBBuamzE2xTIoDAKLHSA4uDAZNpQYg', '$GHkrxwW1HNH4WVBRD96bA5YF5wwItHdHPvYc0i6Oz2g', '$gKArIXJPIcdXNr_7SEF-kOP9vE2PskQ7IJBQkrbDAeQ', '$GOSsxb4_jl6WhcT5BTmKt-ztik5-pyX61jBXAhi9BcQ', '$Gs3War7Gj58728qsFsJOIqiwqRMnAtaqkFxqmRwcbFM', '$gySWNmO6QsHuwjxEdZV_VYNO0gpqe0krCFL1Ekq_3JA', '$H1oL1_Bijp3rehVbTE5dcZXq5k3yiLFfNvHq0Qp8tMU', '$hCzkxZcntme9nePfYmeOap5t_OrTcTLtKchN1nVApa4', '$hJCRFC_ue2aaZHN8aSuOYcHQcpaZ3uwHu4ynDEKoOVA', '$Jaj6ZUU49ivvsdg9FfbAeE7VBftUhyN3txVIzbSzDus', '$JAN7LP3bmkGBEWtTVUw57bMJycAAIDWCF_3hKM2JVj8', '$jIrOGMZIOHVigTG_3w1d4p2JlN3GHYJvztOaRkSAC24', '$JIySK4QeBqVLu0s11M4JtFF7oBNXJHfZ4u2L-5qNxDM', '$jRbFY0w7yHfc5uZF8MnOZCcqLUeAu_yMXyJLLNPZrvk', '$jrqhe7h20UgToNqhhO9ZC-gyxrqq5IC1TdHpX6I9gsQ', '$jsWxM9Lj4oSYuJYZv4J7FLx3qBdJpTJ_I2CGtzXNm-E', '$K22BTWWH88SjqvTw3vNrF2aZvmy_10HGeNv0KY5gXv8', '$KsMkUhpG9QGHSR7U14koF6kPKrA2bq4xstkVupHhdHg', '$l_5Jett_F0emKwav_6LwVg0ZPV7DaBdeYdtdEBdroz8', '$lCCluT4vhZZ45evd_xRL13rndQRSfVMNbkzPVLNTMwE', '$LTFxGLPjLSmJlIXSrJ57YrbX5u9rf2Jf4BGf-lYv75Y', '$MYqBFzmWvlogkQkwG3z7NM3MlzVVHZp-KStI3FeqIm0', '$n1eWmlczUHLgHs-SSwY3Nq_5s9wciNRjpILrLhwugGM', '$NmduOEAK6TYdPwL0SXKiFkf7CC2_q6lcBj2_GoLaYSk', '$_nNsn4mO-xo_jp8BZ6Rw7bUQfgnMG3TAQy9BGRSPskw', '$NVxgUdf1wCSpKL1XUj-_ENKNoQAlbajkaCvzg0M_YXE', '$o1DTh5skSaZbm9PgIjCUOZS-Osw9F-Dm62nTZ0nTkO0', '$O5dftpe87d2GDLqQ8Wfq0JBEjZHmg9C-qOea6wiLtE8', '$oduHGfmZ8HgxW_ez73T94Bo8IAi0L8884ZiIhZPnWWI', '$ok1J79Pbls1Q2zL8NbqwqRwMorXAXyDJnhJKhg0OtVg', '$OKCRzY8tsungxf3xEAiYsuqTg0c3_piqEtpQO3dwzCA', '$PbWFBMWUeKLcDBs_HZe7MboS9apZS8otfXvgtsMHFdw', '$pMh3fIMoD-3H9mXB2HAj45Qb0AFWgJCkk7FJ_3mPRNk', '$Qf0jt2YOwrs7gerkRYxmP1lA2zKYuuD3wuyCg4ZBh7A', '$q_FhI7OO9OSAOjsL32osPjdAIsssriMkar1MoR2gM4A', '$QKa4rmkfLmQBxdddZ6lfQS12xXPMc0DSLXaSUvPs58Q', '$QOtK5HmkRSiLl-xg8I8fPNJoTLbdZ9PpMSLu6hA7Mjo', '$Qw1auS2QCQP5RYkn5QB-ENBV1CU6jmYSiHgE1I0KtDo', '$Q-wL_1Sa8WDKbZ_KsOK-pRGRo4YoyEOk5T_ERAdr0fo', '$rcptoq6q9YyEQt10SrJBHatQUu_-L8IomQzaQl2fD1k', '$rgFs6tnlbNlRhGKKywUoIyAJ5dxJ_CrSCAHN_HwA4lc', '$rJ0BRw_uDiJYgly9Ly0OLg5UJuZHU8p2r9F-GxCl464', '$rnG3EAkWxmCCT6H30ELRDQN4Z37TqrZERSKRYbfFCbE', '$_RzXu7zELz3H5ccI_QjfmJhf_f3xiifsKn3hj3Z4cik', '$seS_HB-6wuY6gnkVwZOzqf02X7agV5aSHmJACmGt1dE', '$Sn8kCJkPIhik_EVDi3frc6jcIA3C03zq7P6edCQRzqg', '$T0PXPq6cUcFN4S0Y9W3cfqQ_VEQiwPLinptYI6kKNNA', '$t4MMQpAE6XtUyjjocEGVcABshcHcH7D1paKpDIk2Hsc', '$T5pWbHxqj-uISYVILnhI29MojmpiA6elwE1cLlLbQlc', '$TCcmzcaQ3ZssksJQhBFtwx3ZisKcRHNH7Ccfq82rRQ0', '$tHeh7aPHjc1zp_fZuIJHdpcm-nvjcFDGfet96_UtvLk', '$TI1B6ztNCSVa0xMt6kk8unalGyd5k0mEZ0CVD7Yc7xY', '$tiJPfE1WbuqWex2yOAKrVg83SZc9RGsylWQzxt3koSo', '$TKJTaEIjbfzC8mgt9jBbE98nXeTcql0Vg7vYFzWFfBc', '$TlEX3CCDXr546Z8epDCDwmD8Z93hRtJEts7D9U5Asag', '$TOGo6tKeXhOiyx_zqOx_iUP_EVkbb0gJWSh4zQqGPhc', '$tyG9O2dmTQXkhpuITsqAVhooZiWYPfP9a1RzZhyh4Bc', '$TYWTe1tdI79GgKl07i9EQrqlw3TTilAnLNmkum6MXtY', '$us7q2SW8w6Vj_epKi4Ow-7PhFOv5icUqOzTLYyz8CX8', '$uYmftM9orp8pPnfjLRpwB7nuuIDcXcKMOtxxoVulSMw', '$V6lM9nHcxn66S3n_gYK0ZHZ0N7eCZzwLklbhFR4e9_A', '$v_DepjkILgd90nODurUouAT7MYD3QDdQomEQB7mZwP4', '$veqykjVoep2KKRZkZbW4EGfDJ7TsBTt1mSVvg_7Cq5Q', '$VkUJEXciSLYWENshvsnQwpeEb20-29W9cZXQ7RPTmGI', '$W5qCpOYu2HRBg2rsD5_PMeTnUf-chOvjpe1GTLO0hZg', '$Wa4nfdTST3Id12mJ6PVR4SDIEDSecigdO-cAzVTMDy0', '$X4a_LeL18CXh0bO2v4WI7lZDK28DBUqMfkO_v0DyWik', '$xAi2cVtzid4ZcjZNu1_-vfAKvwFNPLF3YjLX49Dls3Y', '$XuPF9eu8IMab9jwYeQh5BFLukCxFSL5Q4P7EMW3ZZWc', '$yeRgdFvEBUc9X8Q39W3ZKZIRbIfDoWi9nV1zhEjWwe0', '$YlBvuJJeylVK5XYX-hW_qXqxHJEbj9Pi9J-p_YUmzsM', '$YYFHwClLH9vtVQX88_ZC0sJDegrM6QUEr6W0IJoKNQM', '$yYu8OfUe0wQv0VXxmxdW14UgTkDmjRcmNCbVJeYM4V8')
) as bad_media
on remote_media_cache.media_origin = bad_media.media_origin and remote_media_cache.media_id = bad_media.media_id
where filesystem_id is not null and bad_media.media_origin is not null and bad_media.media_id is not null;
21:05:44
@zhaofeng:zhaofeng.liZhaofeng Li regexp_replace(filesystem_id, '(..)(..)(.+)', concat(remote_media_cache.media_origin, '/\1/\2/\3')) as path gives you the path 21:10:46
@hexa:lossy.networkhexa
synadm media quarantine -i <media_id> -s <originating_server>
21:15:39
@hexa:lossy.networkhexamatrix-synapse.tools.synadm21:16:14
@hexa:lossy.networkhexa * matrix-synapse.tools.synadm 21:16:19
@hexa:lossy.networkhexa

quarantine Quarantine media in rooms, by users or by media ID.

21:16:33
@hexa:lossy.networkhexaapparently it also works by username21:16:40
@sumner:nevarro.spaceSumner Evansthat only quarantines, it doesn't actually delete from the server21:16:50
@hexa:lossy.networkhexaright, but it also stops your server from serving it21:17:05
@zhaofeng:zhaofeng.liZhaofeng LiOh right, do homeservers serve medias from other homeservers publicly as well?21:23:38
@sumner:nevarro.spaceSumner Evansclients only ever request media from their homeserver21:24:09
@k900:0upti.meK900Yeah it's all proxied through your HS21:25:12
@k900:0upti.meK900So quarantining basically makes it never fetch the quarantined file21:25:23
@zhaofeng:zhaofeng.liZhaofeng LiOh wow, this thing is much better than IPFS: https://matrix.naive.network/_matrix/media/r0/download/entropia.de/nKQHSPtoauLZMUBeNVqUdUMH21:27:08
@zhaofeng:zhaofeng.liZhaofeng LiCrazy that it hasn't been abused yet21:27:23
@zhaofeng:zhaofeng.liZhaofeng LiAlso works for homeservers with no users in the room: https://matrix.nevarro.space/_matrix/media/r0/download/entropia.de/nKQHSPtoauLZMUBeNVqUdUMH21:29:50
@zhaofeng:zhaofeng.liZhaofeng LiWIth smaller servers that never clean up their caches (I'm guilty of that), this is effectively a free image CDN with infinite TTL21:31:12
@zhaofeng:zhaofeng.liZhaofeng Li * WIth smaller servers that never clean up their caches (I'm guilty of that), this is effectively a free CDN with infinite TTL21:31:19
@zhaofeng:zhaofeng.liZhaofeng LiI'm honestly a bit disappointed that it's not authenticated in any form (either a user or a "related" homeserver)21:33:10
@k900:0upti.meK900Synapse cleans caches by default IIRC? 21:33:17
@zhaofeng:zhaofeng.liZhaofeng Li
In reply to @k900:0upti.me
Synapse cleans caches by default IIRC?
Huh, then there may be something wrong with my setup then. I'm pretty sure I never touched the caching configs and yet the directory is 11GB
21:35:24
@zhaofeng:zhaofeng.liZhaofeng LiBut still, it's a big global CDN that's entirely free, run by benevolent people that may not have an idea how cruel the outside world is21:36:29
@0x4a6f:matrix.org[0x4A6F] Isn't #moderation:nixos.org the intended room for these conversations? 21:37:36
@sumner:nevarro.spaceSumner Evans
In reply to @zhaofeng:zhaofeng.li
I'm honestly a bit disappointed that it's not authenticated in any form (either a user or a "related" homeserver)
there's an MSC for that: https://github.com/matrix-org/matrix-spec-proposals/pull/3916
21:37:36
@qyliss:fairydust.spaceAlyssa Ross I'd rather the moderation room was left for things that require actual attention from a moderator 21:38:50
@sumner:nevarro.spaceSumner Evans #matrix-discussion:nixos.org is probably a better room for this 21:39:16
@qyliss:fairydust.spaceAlyssa Ross(this situation in general requires moderator attention, of course, but the ins and outs of how matrix servers work does not)21:39:59
@qyliss:fairydust.spaceAlyssa RossI think it's fine to continue this conversation in here for now. That ship has sailed. :)21:42:18
@zhaofeng:zhaofeng.liZhaofeng LiBut anyways, this is seriously a neat trick that I'll use gently in an altruistic way. Imagine Firefox Send but decentralized and wget-able21:43:25
@qyliss:fairydust.spaceAlyssa Ross having some place for people to be able to talk about any response they might need to make is useful, and this seems to have ended up being that place 21:43:41

Show newer messages


Back to Room ListRoom Version: 6