I’ve been seeing emojis breaking on a public site I operate, the acl is set to private and is marked as secure with the reason access control post dictates security | source: post creator.
Running the uploads:secure_upload_analyse_and_update task fixes the problem but then it occurs again a few days later. This also happened with the site logo once but never happened again.
Hey @Wolftallemo , this is kind of a tricky problem when custom emojis (or any publicly accessible upload that may be used in a post) and secure uploads are combined. A while ago we started using an UploadReference table to see what is linked to what upload, however when we migrated to this table we set all the created_at datetimes to the same thing. The consequence of this is that when we are checking whether the first use of an upload is public or not for security purposes, sometimes the ordering is off and we use the wrong thing, since if both things have the exact same created_at then PostgreSQL just does its own thing:
Your mention of this made me re-examine this once more, and I think we can deal with this better by ordering on created_at ASC then id ASC, so if there are any of these conflicts the real first record should be used.
If problems persist after this is merged (I will try to get it merged today) and you have uploaded your site, then we can discuss further options, but I do have a suspicion that this is the issue for you. You can confirm it by doing this and comparing results on your site:
CustomEmoji.find_by(name: "success").upload.upload_references.order("created_at ASC")
CustomEmoji.find_by(name: "success").upload.upload_references.order("created_at ASC, id ASC")
The first should have a Post as the target_type, the second should have the CustomEmoji as the target type.
It started happening again, and it seems to be the same emoji every time.
Can you run the queries @martin shared in the post above?
Looking at the results I’m actually getting the exact opposite (first returns CustomEmoji and second returns Post)
Interesting, it is returning the correct thing then. For the linked upload, can you post the security_last_changed_reason and security_last_changed_at values and see whether access_control_post_id is filled in? It should not be marking that upload secure when the first reference is the custom emoji. Try this too:
Upload.find(ID)
.upload_references
.joins(<<~SQL)
LEFT JOIN posts ON upload_references.target_type = 'Post' AND upload_references.target_id = posts.id
SQL
.where("posts.deleted_at IS NULL")
.order("upload_references.created_at ASC, upload_references.id ASC")
.first
And see what record it gives you.
I got access control post dictates security | source: post creator with a date of Wed, 22 Mar 2023 09:13:10.341411000 UTC +00:00
It gave me a control post id for a post in a private category that was created over three years ago and was never edited.
Interesting, and was the custom emoji used in that post? What gets returned when you run that upload reference code above with that upload ID? Running this with the upload record and posting the result would be helpful too:
UploadSecurity.new(upload).should_be_secure_with_reason
It was used in that post
The query returned the following:
id: 752,
upload_id: 236,
target_type: "Post",
target_id: 37246,
created_at: Mon, 25 Nov 2019 22:24:50.002473000 UTC +00:00,
updated_at: Sun, 29 May 2022 08:13:24.844072000 UTC +00:00>
The target id points to a post which does not contain the emoji at all.
Upload security returns [true, "access control post dictates security"]
That’s really unexpected, running this should give you all uploads linked to the post via UploadReference:
UploadReference.where(
target_type: "Post",
target_id: 37246,
)
If the post has references but doesn’t appear to have any uploads in the UI, then maybe there has been a migration issue?
Turns out I typed the id wrong 
Yeah it contains the emoji, although the parent topic was deleted.
Heh, no problem. So if you run this again with the correct upload what do you get?
UploadSecurity.new(upload).should_be_secure_with_reason
And
upload
.upload_references
.joins(<<~SQL)
LEFT JOIN posts ON upload_references.target_type = 'Post' AND upload_references.target_id = posts.id
SQL
.where("posts.deleted_at IS NULL")
.order("upload_references.created_at ASC, upload_references.id ASC")
.first
If we really need to, you could just set the CustomEmoji UploadReference record’s created_at date + time to something earlier than any of the other UploadReference records for that upload, but you shouldn’t have to.
I should have clarified. The upload id was correct, the post id was not (which was how I ended up with the post not containing the emoji - the one i pasted here was correct, but I typed it wrong when trying to find it)
All of the other commands still return the same thing
If this is the case, then indeed there is some weirdness around the dates, possibly caused by our automatic migration. Run this:
CustomEmoji.find_by(name: "success").upload.upload_references.order("created_at ASC, id ASC")
To find the UploadReference record that says target_type: "CustomEmoji", then do:
UploadReference.find(ID).update!(created_at: UploadReference.find(752).created_at - 1.day))
Then run this on the troublesome upload:
upload.update_secure_status
And it should be resolved…I really think this will mostly be an issue with older uploads since this migration. Another thing you may be able to do is delete the custom emoji, re-upload it, then run:
Jobs.enqueue(:rebake_custom_emoji_posts, name: EMOJI_NAME)
The date update will be easier though. Sorry for the muck-around on this!
For now the emoji is not secure anymore 
I guess I’ll find out in a few days if it actually worked or not
Hey @Wolftallemo 
Did this work out for you?
Nothing has broken yet