您必须使用GROUP BY子句，以便在一个组中将多个记录聚合在一起。这与不必要的（我相信）嵌套SQL一起将导致错误的结果。
请考虑以下事项：

SELECT users.user_name, user_profiles.first_name, user_profiles.last_name,
        string_agg (roles.name, ',') as name
    FROM users 
      JOIN user_profiles 
        ON users.id = user_profiles.user_id
      JOIN user_roles
        ON user_roles.user_id = users.id
      JOIN roles ON user_roles.role_id = roles.id
    GROUP BY users.user_name, user_profiles.first_name, user_profiles.last_name

Online demo

您在最里面的子查询中忘记了from：

SELECT  users.user_name, 
        user_profiles.first_name, 
        user_profiles.last_name,
        (SELECT string_agg (roles.name, ', ') 
         from  roles 
         where roles.id 
         in (   select u.role_id 
                from user_roles u --this was missing
                where users.id = u.user_id)) as name
FROM users 
    JOIN user_profiles 
        ON users.id = user_profiles.user_id
--  JOIN user_roles --you're already getting roles in the innermost query
--      ON user_roles.user_id = users.id;

如果没有它，子查询就只是选择一个角色，即当前行的role_id，而不是像您所期望的那样独立地获取用户的所有角色。请参见demo。
请注意，一旦这个问题得到解决，您还需要去掉最后一个连接（在我的示例中注解掉了），以避免为每个用户的角色增加用户--反正您已经在最内层的子查询中获得了他们的角色。
使用常规的group by会更容易：

select  users.user_name, 
        user_profiles.first_name, 
        user_profiles.last_name,
        string_agg(roles.name,', ')
from users 
    inner join user_profiles 
        on users.id=user_profiles.user_id
    inner join user_roles 
        on user_roles.user_id=users.id
    inner join roles 
        on roles.id=user_roles.role_id
group by 1,2,3;