Re: Role Attribute Bitmask Catalog Representation

All,

> I think if we're going to do this - and I'm not yet convinced that
>> that's the best route, we should add returns all permissions a user
>> has. Right now that's quite easily queryable, but it won't be after
>> moving everything into one column. You'd need to manually use all has_*_
>> functions... Yes, you've added them already to pg_roles, but there's
>> sometimes good reasons to go to pg_authid instead.
>>
>
> This is a good point. I'll start looking at this and see what I can come
> up with.
>

Giving this some thought, I'm curious what would be acceptable as an end
result, specifically related to how a query on pg_authid might look/work.
I was able to preserve the structure of results from pg_roles, however,
that same approach is obviously not possible with pg_authid. So, I'm
curious what the thoughts might be on how to best solve this while
minimizing impact (perhaps not possible) on users. Currently, my thought
is to have a builtin function called 'get_all_role_attributes' or similar,
that returns an array of each attribute in string form. My thoughts are
that it might look something like the following:

SELECT rolname, get_all_role_attributes(rolattr) AS attributes FROM
pg_authid;

| rolname | attributes |
+---------+-------------------------------------+
| user1 | {Superuser, Create Role, Create DB} |

Another approach might be that the above function return a string of comma
separated attributes, similar to what \du in psql does. IMO, I think the
array approach would be more appropriate than a string but I'm willing to
accept that neither is acceptable and would certainly be interested in
opinions.

Thanks,
Adam

--
Adam Brightwell - adam(dot)brightwell(at)crunchydatasolutions(dot)com
Database Engineer - www.crunchydatasolutions.com