所以问题是user = payload.user_id，所以现在user是用户id，是int，后来当你试图得到user.guild时，用户是id（也是int），没有像guild这样的属性。
你做了什么：

@commands.Cog.listener()
    async def on_raw_reaction_add(self, payload):
        reaction_message_id = 1067231301452701748
        user = payload.user_id # returns 1234567890
        user_reaction = str(payload.emoji)
        if reaction_message_id != payload.message_id:
            return`
        if user_reaction == '<a:rose-1:1066240347769942066>':
            role = discord.utils.get(user.guild.roles, name="Red") # here its the 123456789
            await user.add_roles(role)

正确的方法

@commands.Cog.listener()
    async def on_raw_reaction_add(self, payload):
        reaction_message_id = 1067231301452701748
        user = payload.member # here it returns memebr object 
        user_reaction = str(payload.emoji)
        if reaction_message_id != payload.message_id:
            return`
        if user_reaction == '<a:rose-1:1066240347769942066>':
            role = discord.utils.get(user.guild.roles, name="Red")
            await user.add_roles(role)