How to SELECT privilege_type in an ARRAY?

Question

so I want to put together some values because they have the same grantee. But unfortunately, I cannot find the right query.

Here is the basic query (so, that works but there's to much rows because of "privilege_type"):

SELECT 
    table_schema,
    table_name,
    grantee,
    privilege_type 
FROM 
    information_schema.role_table_grants 
WHERE
    table_schema='public';

The result : https://image.noelshack.com/fichiers/2019/36/5/1567753951-capture.png

So now I want to only make one row by "grantee". So I need to put all the values of "privilege_type" in an ARRAY per "grantee".

I have tried this but it doesn't work :

SELECT 
    table_schema,
    table_name,
    grantee,
    ARRAY(SELECT privilege_type
        FROM 
            information_schema.role_table_grants 
        GROUP BY grantee) as privileges 
FROM 
    information_schema.role_table_grants 
WHERE
    table_schema='public';

How can I do that...? So I can get (for example with the previous image) only 2 rows... I really don't get it...

Answer 1

You can use a group by with array_agg()

SELECT table_schema,
       table_name,
       grantee,
       array_agg(privilege_type::text) as privileges
FROM information_schema.role_table_grants 
WHERE table_schema='public'
GROUP BY table_schema, table_name, grantee;